NEWBIE: Need some schooling on Multiple-Item Forms

[Japazo]

I am the engineer-in-charge at a small machine shop. We are making changes to the way we do things and are implementing databases to help keep track of things. I am totally new to ACCESS so I am trying to learn as quickly as possible since the company is in limbo waiting for me to get the database rolling. There aren't any reasonable resources to get training in my area ($500 for a 2 day intro-to-ACCESS course is highway robbery IMO). I plan on buying a training book as soon as I can track one down after work hours. Until then, I am scrambling to get this thing into a usable state.

Goal #1: A complete functioning "Request-For-Quote" database which will replace our pen-and-paper system. This will provide a standardized quote form for customers from each department within our company and allow us to keep track of what is getting quoted and what isn't.



Problem: We need to be able to quote, potentially, hundreds of parts on one form. I have successfully built the "header" so to speak and I can choose the customer, quoted-by, etc. The problem is that I need the form to be dynamic so that I can add any number of line-items to the quote and have all of the information saved and recalled later. I lack the understanding of relationships and lookups, etc to be able to confidently create this type of form.

Progress so far: As I said, I successfully created the main form and can select all of the title information. I have the following tables,

Customer: Contains customer names (PK), ID number, addresses.
Employees: Contains employee ID's (PK), Names, Abbreviations and contact information.
Process Codes: Contains all of our capabilities or processes (CNC Mill, CNC Lathe, etc) and process codes (PK) as an autonumber field.
Quotes: SHOULD contain all of the information for the line-items in the quote. This is where I want the multiple-items form to save data which is tied to the unique RFQ number (I think). Contains information like Line# (PK), Part #, Description, Quantity, Price each and a calculated total.
RFQ's: Contains all of the information for each unique RFQ. RFQ# (PK) uses autonumber. Pulls information from Customer, Employees, Process Codes and hopefully Quotes eventually.

My main RFQ-Form is designed to look exactly like our paper form with the same title block positioning and formatting and footer information. My original intention was to simply create ~10 field groups for 10 potential line items. This would have created 50 fields in my table for each unique RFQ number which is bulky and doesn't allow us enough room to quote all the parts we need on one form. I scrapped this idea and started researching multiple-item forms. I was able to create a multiple item form from the Quotes table and make it function how I would like, but I cannot figure out how to store all of those items relative to my unique RFQ#. I can create numerous records using the form and save them to the table but there is no RFQ reference, so every RFQ# will have the same line items which obviously defeats the purpose of the database.

Short-Story-Long: How do I save multiple records with reference to my RFQ#? Please explain this as if you were talking to a 5th grader, as I still don't understand the terminology used in ACCESS. I can create a dummy database and make up some customers, employees, process codes, etc if that would help. Just let me know what you need! I am a quick learner so once I am pointed in the right direction I can usually take the wheel and drive. I just need that initial push.

I'll be forever in debt to anyone who can help speed my learning curve! Thank you ahead of time.

[pbaldy]

The structure you describe is similar to the standard invoicing structure. That is normally handled with 2 tables with a one-to-many relationship. You would have an "Invoice" table with an invoice number, date, customer, etc. You would have an "InvoiceDetails" table with a record for each product sold. That table would have a field for the invoice number, which is what would relate it to the Invoice table. Interface-wise, that would normally be presented with a form/subform. The form would be bound to the invoice table, the subform to the invoice details table. Master/child links would keep them in sync with each other. You can review the Northwind database that ships with Access for an example. That gives you an unlimited number of items to associate with an invoice.

[Japazo]

The structure you describe is similar to the standard invoicing structure. That is normally handled with 2 tables with a one-to-many relationship. You would have an "Invoice" table with an invoice number, date, customer, etc. You would have an "InvoiceDetails" table with a record for each product sold. That table would have a field for the invoice number, which is what would relate it to the Invoice table. Interface-wise, that would normally be presented with a form/subform. The form would be bound to the invoice table, the subform to the invoice details table. Master/child links would keep them in sync with each other. You can review the Northwind database that ships with Access for an example. That gives you an unlimited number of items to associate with an invoice.

I made an attempt to look at the Northwind Database but I'm not familiar enough with the terminology and relationships to understand how everything is linked and maintained. I bought an ACCESS book last night and started at the beginning. Hopefully within a few days I can have something usable. Any more input is VERY welcome. Any pointers or tips I can get from the pro's will help me get up and running faster.

Thank you!

Can I accomplish this with only 2 tables? I'm still struggling through this one step at a time, but I'm stuck on how to save the multiple line items to the quotes table while still having the line items be an autonumber. I cant have 2 autonumber fields...

[pbaldy]

Well, 2 tables for the quotes. I'd also expect a product table, customer table, and whatever else might be appropriate to your situation. Typically the primary key for the "one" table would be an invoice number, quote number, etc. That would be a foreign key in the "many" table, which typically would have it's own autonumber as a primary key. Attached are the relevant tables from Northwind.

[Japazo]

I think I got it working how I want finally. I'm starting to understand relationships and keys a little better. Thank you for the help!

[pbaldy]

No problem! Post again if you get stuck.