Union Query in Access 97

[Lee Attlesey]

Hi all

I've created 2 queries to summarise differing data sets on sales and responses so both have the same fields (apart from sales and responses)

I've created a union query to combine the data – code below.

SELECT
[CONTINENT_NAME],[SUB_CONTINENT_NAME],[COUNTRY_NAME],[COUNTRY_REGION],[CENTRE_CNO],[INSTITUTE_NAME],[FINANCIAL_YEAR],[QUARTER_DESCRIPTION],[MONTH_DESCRIPTION],[SESSION_DESCRIPTION],[PRODUCT_GROUP],[EXAM_GROUP],[PRODUCT],[SYLLABUS_CODE]
FROM[Union Query Entry vs CIS - Entry Extract]

UNION ALL SELECT
[CONTINENT_NAME],[SUB_CONTINENT_NAME],[COUNTRY_NAME],[COUNTRY_REGION],[CENTRE_CNO],[INSTITUTE_NAME],[FINANCIAL_YEAR],[QUARTER_DESCRIPTION],[MONTH_DESCRIPTION],[SESSION_DESCRIPTION],[PRODUCT_GROUP],[EXAM_GROUP],[PRODUCT],[SYLLABUS_CODE]
FROM[Union Query Entry vs CIS - CIS Extract]

GROUP BY
[CONTINENT_NAME],[SUB_CONTINENT_NAME],[COUNTRY_NAME],[COUNTRY_REGION],[CENTRE_CNO],[INSTITUTE_NAME],[FINANCIAL_YEAR],[QUARTER_DESCRIPTION],[MONTH_DESCRIPTION],[SESSION_DESCRIPTION],[PRODUCT_GROUP],[EXAM_GROUP],[PRODUCT],[SYLLABUS_CODE];

From this I created a final query which I aim to link a pivot table to which include the data above back with the sales and response columns of data.

Unfortunately this last query when run comes back blank, any advice would be appreciated.

 

[Russell Hauf]

On 2003-01-10 10:41, Lee Attlesey wrote:

...

From this I created a final query which I aim to link a pivot table to which include the data above back with the sales and response columns of data.

Unfortunately this last query when run comes back blank, any advice would be appreciated.

When you say, "this last query", do you mean the "final" query you mention, or the Union query? Does the Union query run?

Thank you,

Russell

 

[Bariloche]

Lee,

Check the SQL you posted above, please. It looks to me like both SELECT statements are the same. Not only that, but I don't see the fields for Sales and/or Responses.

I'm also interested in understanding why you think you need to use a UNION query.

Whatever additional info you can give us about your tables might also be helpful.

have fun

 

[Lee Attlesey]

Hi Russell

Thanks for responding. Yes, I have a final query after the union query that pulls in the sales and responses data in separate columns.

Hi Bariloche

Again thanks for responding to this one.

Within my dB I have one main table for sales and another for responses to a questionnaire.

I have previously tried to create one encompassing query to pull in data from both tables but this never worked and was told that a union query may answer my needs.

The first 2 queries run fine, summarising sales data in one and responses in the other, each pulling in data from 5 relational tables as a standard select query with no grouping - 15 columns each.

The union query shown then combines the 14 identical fields from both of sales and response queries with a group by that also works. The results of the union query provide me with just over 47,000 records.

The issue I’m having is in a final query that selects all the data from the union query. I then also add in the sales column and the response column from the previous 2 queries, this always responds as blank.

If you know a way in which I can create this in one query it would be a real help or at least have any ideas in helping resolve the issues I have with the final query…

 

[Bariloche]

Lee,

I'm pretty certain that a Union query isn't what you need. One thing, though, how many records are returned when you run each of the first two queries? You mention 47,000 for the Union, how many for the individual Select parts?

What you really want to do is create a query that has both of your 15 column tables in the table pane. You then need to join the fields that define a unique record. That's probably more than one field. It may be three fields, but it shouldn't need to be 14. Then just pull the "14" fields from one of the tables (I'd probably put Sales on the left hand side and Responses on the right and use the Sales fields) and then pull the "Sales" field down and then pull the "Responses" down (to the query grid).

A Union query will just "glue" two datasets together. One on top of the other (so to speak). That's not what you want. You want them side-by-side (14 description fields followed by Sales and then Responses).

It may help you to conceptualize this if you draw it out on a sheet of paper. Put the fields down and then "sketch in" what you think the output should look like. If you sketch out the Union query I think you'll quickly see that what is happening is you are pulling all the Sales records and all the Response records and gluing them together. When really what you want to do is set the Sales side-by-side with the Responses. For that you need to join the tables as I explained above. The fields that should define the uniqueness of a record are those that you would look at to "relate" the two datasets if you were just looking at the two Select query results on a sheet of paper.

Well, see if you can follow that. Post back with the good news.


enjoy

 

[Lee Attlesey]

Hi Bariloche

Thanks for your additional support on this, I think I’ve got it…

Instead of a union query I have created a third query linking the 2 summary queries, however this didn’t work for a while.

In the end I used 4 fields to create the link between the queries but had to change the join properties of each to include all the fields from the first and only those from the second where the join was equal otherwise the sum of sales was incorrect.

Thanks again for your help on this one. Now I’m off to link the final query to an Excel pivot table and also to create a Cognos PowerCube… data analysis, who needs it

 

[Bariloche]

Lee,

That's great. I'm glad it worked out. We were going to get to changing the joins to "Outer" joins eventually. I figured the other stuff was enough to digest at one go so I didn't get into it. Probably should have but time's a little tight in the morning. I'm glad you were able to figure it out.


take care