Union Query - Multiple records on one report

[m0aje]

Hello,

I have a database that has over 20 tables in it and am using Access 2000. Unfortunately I cannot change the structure as it performs specific functions, so I am stuck with it.
I have created a Union Query from these tables yaking data from 5 fields using the Serial Number entered by the user.

SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
WHERE((([Workstation].UnitSerial)=[Enter Serial No.];
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[LAPTOP]
WHERE((([LAPTOP].UnitSerial)=[Enter Serial No.];
UNION
SELECT[MONITOR].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[MONITOR]
WHERE((([MONITOR].UnitSerial)=[Enter Serial No.];
UNION
SELECT[PRINTER].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[PRINTER]
WHERE((([PRINTER].UnitSerial)=[Enter Serial No.];
UNION
SELECT[SMARTUPS].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[SMARTUPS]
WHERE((([SMARTUPS].UnitSerial)=[Enter Serial No.];
Etc...
I use a bar code scanner to scan the serial number, and it goes through the tables and returns the results along with the other specified fields.
I would like to scan up to 16 or more different serial numbers and have it return the results. Perhaps scan the first 16 serial numbers, then run the query? Is that possible. The serial numbers are unique and will return a combination of laptops, printers, monitors, etc...
I have created a report from the above union query and it works perfectly with only one serial number entered.
I am a novice with Access 2000 and even more of a novice with SQL.
Any help and guidance would be most appreciated.
Many thanks!
//m0aje//

 

[plog]

I would remove the WHERE clause of all those Queries. Then create a new table to hold the serial numbers of the ones you want to find. Then make a query using the UNION query and that table, linking them appropriately. Bring down all the fields you want and run it.

 

[m0aje]

Greetings plog,

This would have to be a temporary table, as these scans would be a one time thing to create the report.
Can a temporary table be created and incorporated into the union query? If so, how?
Thanks for your reply.

r/
m0aje

 

[m0aje]

Hello again,

I did as plog suggested and created a separate table for Serial Numbers called [SERIALNO__SCAN]. My union query is called PCEQUIP. I also deleted the WHERE statements from this query.
:banghead:
SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[LAPTOP]
UNION
SELECT[MONITOR].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[MONITOR]
UNION
SELECT[PRINTER].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[PRINTER]
UNION
SELECT[SMARTUPS].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[SMARTUPS]

I am having trouble with the syntax on how and where the TABLE [SERIALNO_SCAN] should be placed. I tried:

TABLE[SERIALNO_SCAN].SerialNo
UNION
SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[WORKSTATION]
etc...
etc...

Along with a few other combinations. No joy.

Please help!

Thanks,

//m0aje//

 

[plog]

The serial number table is placed in a new query, not the UNION. You need to build a new query using the serial table and that UNION query as sources, link them via the appropriate fields, bring down what fields you want to display and run it.

 

[m0aje]

I don't understand how to use the UNION Query as the source and link them. The serial number table has only one field - SerialNo. I created a new query for just the Serial number table. My UNION QUERY has the fields I want to display in the report. I don't understand how to link all of this together.

Thanks for the reply.

//m0aje//

 

[plog]

You are over thinking this. It's query building 101. Using Access>=2007:

Create->Query Design

It will display a box that allows you to select data objects into the query, from the Tables tab bring in SERIALNO_SCAN; from the Queries tab bring in PCEQUIP. Close the data selection box.

Link SERIALNO_SCAN and PCEQUIP approrpiately (serial number field in one to serial number field in the other). Bring down the fields you want to display. Run the query.

 

[m0aje]

Hello plog,
In your previous post, you said "You need to build a new query using the serial table and that UNION query as sources, link them via the appropriate fields, bring down what fields you want to display and run it."
Access 2000 will NOT allow me to link the union query (PCEQUIP) to the serial scan table. I get a message box that says "query input must contain at least one table or query". I have already selected the SerialNo_Scan table. It only has one field which is the serial no field. The union query has five fields. I modified the Serial_No Scan table to have the same fields and still no joy. I need the union query to find the PC equip in the other tables based on the serial numbers in the SerialNo_Scan table.

r/m0aje

 

[plog]

Does PCEQUIP run by itself? When you double click just it, does it display data? My guess is that it isn't valid at all.

If that's not the case, you may have a conflict between the fields you are trying to link; one might be a text field the other a number. You cannot join fields of different types.

If neither of those work, post your database.

 

[m0aje]

No. I cannot make PCEQUIP Union Query run without the WHERE clause.

I have been sitting here for hours trying to make this work. I must be too thick headed.
I have posted the file. It contains SerialNo_Scan Table with 5 serial numbers. One from each of 5 tables: Server, Monitor, Workstation, Printer, and Laptop.
The SerialNo_Scan table has ONE field UnitSerial.
The Other 5 tables contain 5 fields: PartNo, PropertyTag, UnitSerial,Date,Technician.
My goal is to enter just serial numbers into the SerialNo_Scan table, run the query and have access return the 5 different records based on the serial numbers in the SerialNo _Scan table. Maybe Union Query is the wrong thing to use for this.

r/m0aje

 

[JHB]

Gives the query FinalResults the result you are looking for?
Database attached.

 

[plog]

m0aje, I don't know what to tell you other than you are incorrect. I downloaded your file, double clicked on PCEQUIP and it ran fine producing 26 results.

Next I created a query using PCEQUIP and SerialNo_Scan, linked them, ran it and got 5 results as expected. Although, I don't know why there are 21 blank records in SerialNo_Scan.

I don't know what to tell you other than PCEQUIP works and my instructions in post #7 will produce the results you want.

 

[m0aje]

Gives the query FinalResults the result you are looking for?
Database attached.

Greetings JHB,

Thank you for resolving my issue with my database. I had looked at the JOIN statement, but didn't understand its function. I am an amateur and have tried to research examples and parallel those examples to fit the results of what I am trying to do.
Anyway, I was able to add the rest of the UNION statements and tables and it works just like I need it to. I want to add some additional functions, but will try to resolve them myself, before asking for help.
Thanks again!



r/m0aje

 

[m0aje]

m0aje, I don't know what to tell you other than you are incorrect. I downloaded your file, double clicked on PCEQUIP and it ran fine producing 26 results.

Next I created a query using PCEQUIP and SerialNo_Scan, linked them, ran it and got 5 results as expected. Although, I don't know why there are 21 blank records in SerialNo_Scan.

I don't know what to tell you other than PCEQUIP works and my instructions in post #7 will produce the results you want.

Greetings again plog,

I appreciate your patience. As I said before, I am an amateur at this. I am not sure why it worked for you and not me. For whatever reason, I could not make it work in the manner you suggested. Over time I got really frustrated with it. Again, I thank you for trying to help me resolve my issue and I appreciate your responses. I appreciate any help or suggestions I can get. I was able to get my query working and what I was looking for from a post from JHB.
Thanks again!!!


r/m0aje

 

[JHB]

Greetings JHB,

Thank you for resolving my issue with my database.
Thanks again!

You're welcome, luck with your project.