Union Query SQL Statement

[Celeste025]

I am having trouble creating a union query statement. I have this:

UNION SELECT [Tracking Number] FROM [Sales]; UNION SELECT [Tracking Number] FROM [Purchasing]; UNION SELECT [Tracking Number] FROM [Operations]; UNION SELECT [Tracking Number] FROM [Jobber];

But I get this error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

I am trying to create a query that takes columns which have the same label from 4 different tables into one query.

The database is set up to follow tracking numbers; the sales, purchasing and operations tables can only have a tracking number appear once, but the jobber table can have the same tracking number repeat up to 5 times. However, in my current non-union query, Im getting tracking numbers in the sales, etc. columns if there is one in the tracking number column, even if it's not in the actual table like that.

Thanks in advance

 

[KenReay]

Hi

You should not have first UNION or embedded ; so try

SELECT [Tracking Number] FROM [Sales]
UNION
SELECT [Tracking Number] FROM [Purchasing]
UNION
SELECT [Tracking Number] FROM [Operations]
UNION
SELECT [Tracking Number] FROM [Jobber];

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Celeste025]

Thanks, but that only partially works, I also need to add other fields and don't know enough SQL to figure out how. I'm also not sure that it's generating the info how I need it - it would be easier to tell with the corresponding info.

Right now it seems to be giving me a listing of every tracking number, which isnt really what I need since every tracking number will exist in the database.

Maybe it will help if I describe the db a bit. I am tracking call reports submitted by sales agents that record different types of sales calls made by an agent to a particular customer on a particular date. Each call report is given a tracking number that corresponds to each call on that report. On each call report, there are 4 different types of calls - sales, purchasing, operations, and jobber. There can be 1 sales call per sheet (tracking number), 1 purchasing and 1 operations. However, there can be 5 jobber calls per sheet so they would all have the same tracking number. If every blank on the sheet was filled out there would be 8 entries that would correspond to that one tracking number. Any combination of calls can be on the sheet.

My db has 8 tables, one Main info with tracking number, date and agent info. One with agent and agency info. One with customer info (this is linked to a JD Edwards db). One with more customer info that wasnt in the JD Edwards db the way that I needed it. And then one table for each of the 4 types of calls. In each of the call type tables, there is a tracking number column.

I need a report that will count the tracking numbers for each call type and give me the number of calls made between specific parameters. The report is already created, but I realized I had a query problem when the numbers were abnormally high. What was happening was that each time there was more than one type of call, the tracking number info got pulled into the query for each type of call for each call. So for example, if a call report had one sales call and 4 jobber calls, the query would actually show 5 jobber and 5 sales calls rather than 1 and 4.

Now with the code above, it's giving me one column that just lists all of the tracking numbers, but I still need to see each tracking number field, but I dont want a tracking number showing up in the sales field every time one shows up in a jobber field, etc.

I hope this makes sense and someone can point me in the correct direction.