Solved Union Query Starter Help (1 Viewer)

Malcolm17

Member
Local time
Today, 08:28
Joined
Jun 11, 2018
Messages
58
Hi,

I have a database that I use for reporting, however I would like to see the total quantity and total sales broken down by locations in one query which I would also then use in reports too. I have never used a union query, however I believe this is they way that I should go? I can't seem to get started on a union query, please can you look at my mock up database and maybe fix my union query for me please so I could copy it in the future?

I thought the following might work for me but it doesn't and as I don't have the union query knowledge yet then I cannot see where I have gone wrong.
Code:
SELECT
Query1.Product,
Query1.1,
Query1.2
FROM Query1
UNION SELECT
Query2.Product,
Query2.1,
Query2.2
FROM Query2;

Many thanks,

Malcolm
 

Attachments

  • UnionQuery.accdb
    492 KB · Views: 23

theDBguy

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
14,875
Hi Malcolm. The code you posted looks correct to me. What does it mean by "it doesn't work?"
 

Malcolm17

Member
Local time
Today, 08:28
Joined
Jun 11, 2018
Messages
58
Hi theDBguy

When I run the query I get the following pop up message error:

Invalid use of '.','!', or '()'. in query expression 'Query1.'.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
14,875
Hi theDBguy

When I run the query I get the following pop up message error:

Invalid use of '.','!', or '()'. in query expression 'Query1.'.
Sounds like you may have to fix Query1. Does it work by itself?

I'll try to download your file to take a look.
 

Malcolm17

Member
Local time
Today, 08:28
Joined
Jun 11, 2018
Messages
58
Thank you theDBguy. Both queries work on their own, it is just the union query that I cannot get to work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
14,875
Thank you theDBguy. Both queries work on their own, it is just the union query that I cannot get to work.
Okay, I see the problem. Using numbers alone as field names is not allowed. Try the following:
SQL:
SELECT
Query1.Product,
Query1.[1],
Query1.[2]
FROM Query1
UNION SELECT
Query2.Product,
Query2.[1],
Query2.[2]
FROM Query2;
 

Malcolm17

Member
Local time
Today, 08:28
Joined
Jun 11, 2018
Messages
58
Thank you, that did work for me, however it did not give me what I was hoping to achieve, maybe it is not a union query that I need. Please can you tell me how I can get the following layout in a query (it will be for a report in the end)

(LOCATION 1) (LOCATION 2)

Product 1 1 2 2

Food 8 £6.00 2 £11.00

Wet 9 £10.00 3 £3.00

The left column (Qty) in each location is from query 1, the right column (Value) in each location is from Query 2.

I have attached it as text which possibly displays what I am looking for better.

Thank you.
 

Attachments

  • Query Layout Example.txt
    226 bytes · Views: 24

theDBguy

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
14,875
Thank you, that did work for me, however it did not give me what I was hoping to achieve, maybe it is not a union query that I need. Please can you tell me how I can get the following layout in a query (it will be for a report in the end)

(LOCATION 1) (LOCATION 2)

Product 1 1 2 2

Food 8 £6.00 2 £11.00

Wet 9 £10.00 3 £3.00

The left column (Qty) in each location is from query 1, the right column (Value) in each location is from Query 2.

I have attached it as text which possibly displays what I am looking for better.

Thank you.
Okay, maybe you could start with this and see if you can tweak it to what you need.
SQL:
SELECT Query1.Product, Query1.[1], Query2.[1], Query1.[2], Query2.[2]
FROM Query1 INNER JOIN Query2 ON Query1.Product = Query2.Product;
 

Malcolm17

Member
Local time
Today, 08:28
Joined
Jun 11, 2018
Messages
58
That's brilliant and a great starting point for me, thank you its just what I am looking for, I can play with that now.

Many thanks!!

Malcolm
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
14,875
That's brilliant and a great starting point for me, thank you its just what I am looking for, I can play with that now.

Many thanks!!

Malcolm
Hi Malcom. You're welcome. Glad we could assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom