Combining tables in a query

[steve1111]

Hello, i am still somewhat new to Access but i am having a hard time with a query. I have an invoice form that is from tblInvoice, and on that form i have 5 subforms all tied to 5 tables, tblRepair1, tblRepairs2, etc. When we get service done at a shop the user can enter up to 5 repair types, which all five tblRepairs are joined to the InvoiceID of the tblInvoice.

the issues is one user might put oil change in repair 1 and tire rotation in repair 2 and the other 3 repairs not used. another user may use all 5 repairs and put oil change in the repair 5 field. What i want to do is query a repair type, Oil change, and get every InvoiceID record that has oil change in it regardless of what repair table it was stored in, or in other words regardless of if it was entered in repair1 or repair5.

i have tried to "step down" the criteria referencing the cbx on the search form but if queries blank. I only want the records that have an oil change in them but still want to see all the repairs that were done with it. I am not very good at writing code yet so hopefully this can be done in design view.

thank you

 

[plog]

Your problem isn't with a form or query, its the table structure you have built. You need to redo your tables. Instead of all those repair tables, you need just 1 to hold the repair types you do. Read up on normalization: http://en.wikipedia.org/wiki/Database_normalization

Your data should look more like this:

tblInvoice
InvoiceID, InvoiceDate, CustomerID
41, 1/2/2010, 12
44, 1/8/2010, 91
48, 1/9/2011, 91

Repairs
InvoiceID, RepairType
41, "Oil Change"
41, "Tire Rotation"
44, "Oil Change"
48, "Air Filter Replaced"

This will allow you to easily enter as many (or as few) repairs for a customer. Once you have your data structured properly getting the data you want will be trivial.

 

[steve1111]

Thanks plog, i do have a separate service type table set up, for instance oil change is entered as 20 on the invoice form, same with my vendors. Are you suggesting then i hold all of my repairs in one repair table? then how do i query just oil change or "20" and still pull in all the other repair types made on those invoices? would i then set up a relationship with the invoice table and the one central repairs table?

 

[plog]

Are you suggesting then i hold all of my repairs in one repair table?

Yes.

then how do i query just oil change or "20" and still pull in all the other repair types made on those invoices?

I don't understand what you mean. You want a query of all invoices items where the invoice had an oil change? If so, you need a sub-query, first to identify all the invoices with an oil change, then you use that sub-query to pull in all items of those invoices identified in the sub query.

would i then set up a relationship with the invoice table and the one central repairs table?

Yes, one to many between the invoice table and the repairs table on InvoiceID

 

[steve1111]

Thanks so much, the restructure helped tremendously!