473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Projects/Orders Database

2
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
Dec 2 '11 #1
10 1616
sierra7
446 Expert 256MB
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.(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)
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
Dec 2 '11 #2
Hossi
2
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
Dec 2 '11 #3
Mihail
759 512MB
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.
Dec 3 '11 #4
NeoPa
32,556 Expert Mod 16PB
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.
Dec 3 '11 #5
Mihail
759 512MB
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
Dec 3 '11 #6
NeoPa
32,556 Expert Mod 16PB
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.
Dec 3 '11 #7
sierra7
446 Expert 256MB
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
Dec 4 '11 #8
NeoPa
32,556 Expert Mod 16PB
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.
Dec 5 '11 #9
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Dec 5 '11 #10
NeoPa
32,556 Expert Mod 16PB
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
Dec 5 '11 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Jack Crawford | last post by:
I am wondering if their is a strategy or tool for syncing a local database with a web-based database? Specifically I have an application where orders are entered via a web form and the data...
0
by: Chris | last post by:
Hi, I am currently designing a simple service orders database. I have played around with MySQL a bit but this is the first time I'm using it in anger, I have a few design queries to make sure I am...
4
by: nick_faye | last post by:
hi guys, hope somebody can assist me. i have two ms access database. i have to copy the entries in database1 to my database2. however, i have to copy entries from database1 that does not...
8
by: RibGnaw | last post by:
Long, long ago when Access 97 first hit the streets a sample database called Northwind was shipped with it. At that time a number of articles had been posted on the correct procedure to remove all...
18
by: Wim | last post by:
Hi, I would like to make and put a query in my database by VB.NET code. Is this possible? Thanks for your help, wim
0
by: m.roello | last post by:
Hi, Anyone can tell me how to define different custom namespaces for xsd datasets in ASP.NET web sites For example, Suppose to have the above folder tree in the App_Code directory App_Code...
76
MMcCarthy
by: MMcCarthy | last post by:
Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal...
47
by: Jo | last post by:
Hi there, I'm Jo and it's the first time I've posted here. I'm in process of creating a database at work and have come a little unstuck.....I'm a bit of a novice and wondered if anyone could...
14
by: nikul156 | last post by:
I have a Database with a products table that has min / max stock fiqures , i can query the table for products that are below the min and get it to work out how much of a product needs ordering , but...
1
by: Aleksey Timonin | last post by:
Hi guys! I'll try to describe situation. 1. We have VS database project. 2. It contains table A. 3. In post-deployment script for the table A we have 'insert' statements adding records to it....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.