Projects/Orders Database

[Hossi]

Hi,
I’m trying to create a database to keep track of our sales projects which normally have long lead times.
Some projects lead to sales and respectively orders and some are lost.
I currently have a projects table where I enter the details of each project and keep track of them.
I would like to have some order related fields to enter and keep track of the order details.
Should I just add the order related fields to the projects table where the fields will remain blank for the lost projects? or should I set up a separate table for orders and link it to the projects table (I assume by a one-to-one relationship)? Which one would the common way and the more practical one?
In case of a separate table, as I have already the projects table with a lot of existing records, how can I link the new orders table with it in order to have related records in the new table for the existing records in the projects table?
I would appreciate if somebody can help me with this.
Thanks,
Hossein

[sierra7]

Hi Hossi,
If you every have the possibility of having more than one order per customer/project then you must have two tables.

Your Projects table should have a unique identifier field on it already. If not go into table design and add a field called 'ProjID' and set it to Autonumber. When you save & close it, each record will be numbered sequentially.(E arlier versions of Access would not allow you to add an autonumber field if there was already data in the table but this is now fixed)
In you Sales table add a field called 'ProjID' but make the data-type LongInteger.
You will then have to manually enter values of ProjID into your Sales table to allow them to be joined to their Project. You will have to do this whether you want to retain two tables on a one-to-one basis, or a one-to- many, or even if you want to copy data from Sales into an extended Projects table (assuming there will not be repeat orders)

You can not rely on matching Company/Client names on two tables because people introduce spaces, abbreviations and typos that eventually stop thing matching.
This should get you started.
S7

[Hossi]

Hi Sierra,
Many thanks for your prompt help. It may be possible to have more than one orders for each projects, then I will go for a separate table.
Thanks again,
Hossi

[Mihail]

Hi !
S7, what version of Access are you running ?
I use 2007 but still I am not able to simple add an auto_number field if I have some data in my table.
Thank you.

[NeoPa]

That is true Mihail (in all versions I believe) but is fairly straightforward to get around.

1. Make a copy of the original table with the original data (a table paste option).
2. Clear all data from the original table.
3. Add the AutoNumber field to the now empty table.
4. Copy the data back from the copied table (also a table paste option).
5. Delete the copied table.

[Mihail]

Thanks, NeoPa.
This is a very simplified procedure.

Until now I use what Access teach me:
Create a new field... and so on.

Thank you !

PS:
I ask because S7 say: Earlier versions of Access would not allow you to add an autonumber field if there was already data in the table but this is now fixed

[NeoPa]

Mihail:
Earlier versions of Access would not allow you to add an autonumber field if there was already data in the table but this is now fixed

That's probably correct then. I can only speak from my own experience and I've used very little of 2007 or 2010 yet.

[sierra7]

NeoPa,
Not still using Access 97 ? :-o)!

FYI, I'm currently using Access 2010 but have just checked that Access 2003 allows you to insert a new column and designate it as Autonumber, after data has been added.
S7

[NeoPa]

I've just tested that and you're absolutely right. I'm just confused trying to think what made me think otherwise. I thought I'd tested it, but I can't imagine how the results could have been different if I did, and if not why was I so sure? I wish I could say.

[TheSmileyCoder]

The only limitations I've known of that sort in 2003 is that I cannot add a new autonumber column AND remove the old one in the same operation. Removing (or converting to number) the old, saving, adding the new works fine.

You can also not convert an existing column (even if the column USED to be a autonumber, and is now number) to an autonumber.

[NeoPa]

Smiley:
You can also not convert an existing column (even if the column USED to be a autonumber, and is now number) to an autonumber.

I think that was actually what I was confused over. Sloppy I know, but there it is. Thank you for de-confusing me :-D