Theory question on multiple forms in the feeding into the same database

[punter]

Hi,

I'm new to access but getting better at it. I'm looking to design a series of forms that data can be entered into. I want the different forms to feed back into the same database. Here is a summary of what I'm looking to do:

1-Have a form that captures Bill of Lading information.
2-Have a form that capture invoice information.
3-Have a form that captures container information.
4-Have a form that captures broker information.

The data will come at different times and in different amounts. One bill of lading may have serveral containers, there my be multiple invoices, ect.

Here are my questions:

1-Can I have various forms feeding into the same database?
2-I imagine I will need some kind of unique number that runs will group each set of records with each other. Can I have the first form that I enter into create the unique number and then enter that number into any other forms at a later time (as the data comes in) in order to have it all group together correctly.

Thank you so much in advance for you help. This website and the people on it are a wonderful resource. I'm already looking to see if my questions have been answered on the forums or the various links here. If anyone knows where I should be looking and wants to point me in the right direction that would be awesome too.

Eddie.

 

[pbaldy]

1) Yes, of course
2) Each table should have a field or fields that uniquely identify the record (perhaps a broker number for that table). However, there's no way for the program to know which broker you may want to enter an invoice for, so the typical way to enter an invoice would be a drop down box based on the broker table that you picked the appropriate one from.

 

[punter]

Thank you so much for your quick reply.

I have been doing a little digging. Do you think it would be better to have the four forms entering into one table or each form have its own table? If I gave each form its own table and had a unique number for the records from the various forms could I link them and run queries based on the unique number?

That seems like it will work.

Eddie.

 

[pbaldy]

The foundation of your application is the table design, so the first order of business is laying them out. THEN you figure out how to best design forms to interact with them. I'd read up on normalization, which will help you figure out how to properly lay out the tables. Given your example above, it sounds like those would be in 4 separate tables.

 

[punter]

Thank you so much. I'll look into normalization.