Filter Records

[Grooveline]

I have a database that associates an invoice number with many lot codes (one record, many fields). I'd like to find an easy way to filter all invoices that contain a given lot code. The lot code could be in several different fields. I thought filter by selection would do this but it only finds those lot numbers that are the same for that field. I need to find it in all of the fields.

[TheShabz]

Before we tackle your initial question, lets make sure your table design is proper. Why do you have lot numbers in multiple fields?

[Grooveline]

I have set up a database to track products associated with orders incase I ever need to recall anything. This is the process. The customer invoice is scanned via barcode into the database. That invoice could have 1 or many different products associated with it. The are also scanned via barcode so we have a record that contains the invoice number and the lot codes of all the different products on the order. There's no ryhme or reason as to what order products get scanned so any given lot code could show up in one or more of several different fields.

[TheShabz]

There's also no rhyme or reason as to how data stored in a table. I still dont see why your lot codes cannot be in one field called LotCode.

[Grooveline]

I guess they could but it could get messy. Lot codes are typically 9-12 digits and an invoice could have many products on it. Makes more sense to me to have each lot number in it's own field. I can do filter by selection for each field but there has to be a better way.

[TheShabz]

does each product only have 1 lot code? if so, the lot code should be stored on your Product table. you will have an invoice table that stores invoice data. you should also have a 3rd table to facilitate a many-to-many relationship between the two. an InvoiceLine table. this is where each item line for each invoice is stored. if you want to pull the lot code, you query the invoice number from the InvoiceLine table and join it to the Product table to pull the lot code.

something like :
SELECT tblInvoiceLine.InvoiceID
FROM tblInvoiceLine INNER JOIN tblProduct ON tblInvoiceLine.ProductID = tblProduct.ProductID
WHERE tblProduct.LotNum = 123456789
GROUP BY tblInvoiceLine.InvoiceID

That will get you all invoices that contain at least 1 item with lot code 123456789

[Grooveline]

Ok thanks, anyone else want to take a stab at this?

[TheShabz]

edited the query to actually do what you wanted. my apologies, i went off on a tangent there with the query.

That's the "proper" way to do it (IMO). however, if you just want to go with what you have:

SELECT InvoiceID
FROM myTable
WHERE lotfield1 = 123456789
OR
lotfield1 = 123456789
OR
lotfield2 = 123456789
OR
lotfield3 = 123456789
OR
lotfield4 = 123456789
OR
lotfield5 = 123456789
etc

for however many fields you have that contain lot numbers.

[Grooveline]

Yes I tried that but it didn't work. Let me give it another go and see what happens. Thank you,

[Grooveline]

Still not working. Seems like that approach should work but it doesn't. Seems like I could express this as a logic statement just not sure what the proper syntax would be.

For the criteria for each lot code field something like
if field value = Lot number I'm seeking, display the record, if not, don't

Any idea how to do something like that?

[TheShabz]

thats essentially what the second SQL i posted should do. can you send a screenshot of the table in design view?

[Grooveline]

I'm not able to paste the screen shot into this box for some reason. The table is basically an invoice field with data type "text" and then about 20 Lot fields also text. Lot fields are named lot1, lot2, lot3 etc.

[TheShabz]

i think you may have an issue with the space in the field name. try

SELECT Invoice
FROM [First Quarter 2010]
WHERE [Lot 1] = 123456789
OR
[Lot 2] = 123456789
OR
[Lot 3] = 123456789
etc

when naming tables and fields, you're really best off not using any spaces or special characters or naming them reserved words like Date, etc.

[Grooveline]

Hey this approach just isn't working. I've worked with Access for years and should be able to do this. One thing that's a real pain in the ass is the new user interface of Access 2007. I feel like I need to go buy another book just to be able to use the program again.

This approach works if I specify a lot # criteria for lot 1 and in the "or" section of lot 2. But if I continue on for lot 3 etc. it only returns 1 record which is incorrect. Thanks for giving it a shot.