TABLE JOINS

[Guest]

When I used the following union query, I received an error message saying
"Snytax Error (missing operator) in query expression:


SELECT Month End query 1.F1, Month End query 1.F2 AS F2A, Month End Query
2.F2 AS F2B
FROM Month End query 1 LEFT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
UNION SELECT Month End Query 2.F1, Month End query 1.F2, Month End Query 2.F2
FROM Month End query 1 RIGHT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
ORDER BY F1;

Any help would be greatly appreciated.

 

[Guest]

You omited some puncuation.
SELECT [Month End query 1].F1, [Month End query 1].F2 AS F2A, [Month End
Query 2].F2 AS F2B
FROM [Month End query 1] LEFT JOIN [Month End Query 2] ON [Month End query
1].F1 = [Month End Query 2].F1
UNION SELECT [Month End Query 2].F1, [Month End query 1].F2, [Month End
Query 2].F2
FROM [Month End query 1] RIGHT JOIN [Month End Query 2] ON [Month End query
1].F1 = [Month End Query 2].F1;

You can not ORDER a union query. Use a follow-on query to sort.

 

[John Spencer]

You CAN order a UNION query.

You use the ORDER BY clause in the last query, but you use the field
names from the first query.


SELECT [Month End query 1].F1
, [Month End query 1].F2 AS F2A
, [Month End Query 2].F2 AS F2B
FROM [Month End query 1] LEFT JOIN [Month End Query 2]
ON [Month End query 1].F1 = [Month End Query 2].F1
UNION
SELECT [Month End Query 2].F1
, [Month End query 1].F2
, [Month End Query 2].F2
FROM [Month End query 1] RIGHT JOIN [Month End Query 2]
ON [Month End query 1].F1 = [Month End Query 2].F1
ORDER BY F2A Desc, F2B ASC

The above perfectly valid SQL.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

When I used the following union query, I received an error message saying
"Snytax Error (missing operator) in query expression:


SELECT Month End query 1.F1, Month End query 1.F2 AS F2A, Month End Query
2.F2 AS F2B
FROM Month End query 1 LEFT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
UNION SELECT Month End Query 2.F1, Month End query 1.F2, Month End Query 2.F2
FROM Month End query 1 RIGHT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
ORDER BY F1;

When you use spaces or other funky characters in a name, you
must enclose the name in [ ]

SELECT [Month End query 1].F1, . . .

 

[Guest]

The advice you offered worked, however, it did not give me what I really
wanted because I did not explain my question well enough. Here’s my second
attempt.

I have 2 tables of data that is identical information.

Warehouse
Item Number
Amount on Hand

Table 1 is [Month End Query 1] and has 58 records, of which 50 match table 2.
Table 2 is [Month End Query 2] and has 56 records, of which 50 match table
1.

I need a query/report that lists all the records for each table as a
comparison. I am comparing 2 separate lists as a part of month inventory
cross check, with the idea to find where the 2 tables differ. Table 1 is a
spreadsheet where we track our inventory “liveâ€, and we only list those items
that are actually in stock/on order for that warehouse. Table 2 is a AS400
query with all items, no matter the balance on hand.


The report would look something like this:

WAREHOUSE ITEM # AMOUNT 1 AMOUNT 2
BR 500-50-ST 20,000 25,000
BR 300-50-ST 20,000 20,000
CC 500-50-ST 20,000 0
CC 300-50-ST 20,000 0
DD 500-50-ST 0 25,000

In this case, Amount 1 would come from [Month End Query 1] and Amount 2
would come from [Month End Query 2].

My problem is that I can’t figure out how to join the two tables in the
query. If I join from the left, I get 58 records from table 1, with an equal
number of records from table 2, but I am missing the 6 records from table 2
that do not match table 1.

I hope that I have clarified my situation better and hope even more that
someone can help me.

Thanks for your time and effort.

When I used the following union query, I received an error message saying
"Snytax Error (missing operator) in query expression:


SELECT Month End query 1.F1, Month End query 1.F2 AS F2A, Month End Query
2.F2 AS F2B
FROM Month End query 1 LEFT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
UNION SELECT Month End Query 2.F1, Month End query 1.F2, Month End Query 2.F2
FROM Month End query 1 RIGHT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
ORDER BY F1;

When you use spaces or other funky characters in a name, you
must enclose the name in [ ]

SELECT [Month End query 1].F1, . . .

 

[Guest]

Use a UNION ALL query then a crosstab query.

SELECT MikeC.Warehouse, MikeC.[Item Number], MikeC.[Amount on Hand], "Amount
1" as [x]
FROM MikeC
UNION ALL SELECT Mike_C.Warehouse, Mike_C.[Item Number], Mike_C.[Amount on
Hand], "Amount 2" as [x]
FROM Mike_C;


TRANSFORM Sum(Mike_C_.[Amount on Hand]) AS [SumOfAmount on Hand]
SELECT Mike_C_.Warehouse, Mike_C_.[Item Number]
FROM Mike_C_
GROUP BY Mike_C_.Warehouse, Mike_C_.[Item Number]
PIVOT Mike_C_.x;

--
KARL DEWEY
Build a little - Test a little

The advice you offered worked, however, it did not give me what I really
wanted because I did not explain my question well enough. Here’s my second
attempt.

I have 2 tables of data that is identical information.

Warehouse
Item Number
Amount on Hand

Table 1 is [Month End Query 1] and has 58 records, of which 50 match table 2.
Table 2 is [Month End Query 2] and has 56 records, of which 50 match table
1.

I need a query/report that lists all the records for each table as a
comparison. I am comparing 2 separate lists as a part of month inventory
cross check, with the idea to find where the 2 tables differ. Table 1 is a
spreadsheet where we track our inventory “liveâ€, and we only list those items
that are actually in stock/on order for that warehouse. Table 2 is a AS400
query with all items, no matter the balance on hand.


The report would look something like this:

WAREHOUSE ITEM # AMOUNT 1 AMOUNT 2
BR 500-50-ST 20,000 25,000
BR 300-50-ST 20,000 20,000
CC 500-50-ST 20,000 0
CC 300-50-ST 20,000 0
DD 500-50-ST 0 25,000

In this case, Amount 1 would come from [Month End Query 1] and Amount 2
would come from [Month End Query 2].

My problem is that I can’t figure out how to join the two tables in the
query. If I join from the left, I get 58 records from table 1, with an equal
number of records from table 2, but I am missing the 6 records from table 2
that do not match table 1.

I hope that I have clarified my situation better and hope even more that
someone can help me.

Thanks for your time and effort.

When I used the following union query, I received an error message saying
"Snytax Error (missing operator) in query expression:


SELECT Month End query 1.F1, Month End query 1.F2 AS F2A, Month End Query
2.F2 AS F2B
FROM Month End query 1 LEFT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
UNION SELECT Month End Query 2.F1, Month End query 1.F2, Month End Query 2.F2
FROM Month End query 1 RIGHT JOIN Month End Query 2
ON Month End query 1.F1 = Month End Query 2.F1
ORDER BY F1;

When you use spaces or other funky characters in a name, you
must enclose the name in [ ]

SELECT [Month End query 1].F1, . . .

 

[Marshall Barton]

The advice you offered worked, however, it did not give me what I really
wanted because I did not explain my question well enough. Here’s my second
attempt.

I have 2 tables of data that is identical information.

Warehouse
Item Number
Amount on Hand

Table 1 is [Month End Query 1] and has 58 records, of which 50 match table 2.
Table 2 is [Month End Query 2] and has 56 records, of which 50 match table
1.

I need a query/report that lists all the records for each table as a
comparison. I am comparing 2 separate lists as a part of month inventory
cross check, with the idea to find where the 2 tables differ. Table 1 is a
spreadsheet where we track our inventory “live”, and we only list those items
that are actually in stock/on order for that warehouse. Table 2 is a AS400
query with all items, no matter the balance on hand.


The report would look something like this:

WAREHOUSE ITEM # AMOUNT 1 AMOUNT 2
BR 500-50-ST 20,000 25,000
BR 300-50-ST 20,000 20,000
CC 500-50-ST 20,000 0
CC 300-50-ST 20,000 0
DD 500-50-ST 0 25,000

In this case, Amount 1 would come from [Month End Query 1] and Amount 2
would come from [Month End Query 2].

My problem is that I can’t figure out how to join the two tables in the
query. If I join from the left, I get 58 records from table 1, with an equal
number of records from table 2, but I am missing the 6 records from table 2
that do not match table 1.

Your original query simulated a full outer join by using a
UNION of a Left and a Right join. Are you now trying to do
it with only one join? If so, it won't do what you want so
either go back to the original approach or use Karl's
somewhat devious approach.