asked on

Access query with multiple fields

OK I'm trying to make a query in access that will list the results for different UPCs. Right now I paste the upc in a text field (Tect2) and it will list the result. I want to know in access is there a way to list the result for multiple UPCs. Here's the sql code that I use now:

SELECT Superfile.LabelStudioAbbr, Superfile.CatalogNumber, Superfile.ArtistName, Superfile.ItemTitle, Superfile.LabelStudioName, Superfile.UPC, Superfile.ConfigNumber
FROM Superfile
GROUP BY Superfile.LabelStudioAbbr, Superfile.CatalogNumber, Superfile.ArtistName, Superfile.ItemTitle, Superfile.LabelStudioName, Superfile.UPC, Superfile.ConfigNumber
HAVING (((Superfile.UPC)=[Form1]![Tect2]));

I would like to know how to search for multiplt UPCs here's two for example:
715515009621 and 715515010429

Thanks,
Chad

ASKER CERTIFIED SOLUTION

brad2575

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

taunt

ASKER

It worked I thought I tried that but looks like I did something wrong. How could I do it with large queries (over 1000) UPCs.

Thanks

brad2575

Do you mean 1000 different form items? Then you would just change it to use an Where "IN" Statmenet. SO it would look like this:

and just keep adding the form fields on inside the "IN" Statement. Or save all the form fields into a temp table and do an in statement on the temp table like the second query below

SELECT Superfile.LabelStudioAbbr, Superfile.CatalogNumber, Superfile.ArtistName, Superfile.ItemTitle, Superfile.LabelStudioName, Superfile.UPC, Superfile.ConfigNumber
FROM Superfile
GROUP BY Superfile.LabelStudioAbbr, Superfile.CatalogNumber, Superfile.ArtistName, Superfile.ItemTitle, Superfile.LabelStudioName, Superfile.UPC, Superfile.ConfigNumber
WHere (Superfile.UPC IN ([Form1]![Tect2], [Form1]![Tect2], [Form1]![Tect2])); 
 
 
 
SELECT Superfile.LabelStudioAbbr, Superfile.CatalogNumber, Superfile.ArtistName, Superfile.ItemTitle, Superfile.LabelStudioName, Superfile.UPC, Superfile.ConfigNumber
FROM Superfile
GROUP BY Superfile.LabelStudioAbbr, Superfile.CatalogNumber, Superfile.ArtistName, Superfile.ItemTitle, Superfile.LabelStudioName, Superfile.UPC, Superfile.ConfigNumber
WHere (Superfile.UPC IN (Select UPC From TempTableNameHere)) ;

Open in new window