Comparing two queries

[thechazm]

I am trying to compare two queries data and show if the records match and the records that dont match in the output but grouping on if they match.

For instance: (Field values are deliniated by the ,)
Query 1:
11, NWP, AO, 5
11, NWP, A1, 2
11, NWP, A2, 7

Query 2:
11, NWP, AO, 5
11, NWP, A2, 7
11, NWP, A5, 10

So Query 1 has a value thats not in query 2. I need the data on the query to come out like this.

11, NWP, AO, 5
11, NWP, A1, 2
11, NWP, A2, 7
11, NWP, A5, 10

And if query 2 has the missing item to put it in the list as well.

Here are my two queries that I am trying to do this with.

SELECT [(Shop Sheets) Shop Commits AWeek Flat].Shops, [(Shop Sheets) Shop Commits AWeek Flat].[Project Name], [(Shop Sheets) Shop Commits AWeek Flat].TSD, nz([CountOfTSD],0)-nz([P Week],0) AS Difference
FROM [(Shop Sheets) Shop Commits AWeek Flat] LEFT JOIN [(Shop Sheets) Requested Reference] ON ([(Shop Sheets) Shop Commits AWeek Flat].TSD = [(Shop Sheets) Requested Reference].TSD) AND ([(Shop Sheets) Shop Commits AWeek Flat].[Project Name] = [(Shop Sheets) Requested Reference].[Project Name]) AND ([(Shop Sheets) Shop Commits AWeek Flat].Shops = [(Shop Sheets) Requested Reference].Shops);

And the second one.

SELECT [(Shop Sheets) Shop Commits AWeek Flat].Shops, [(Shop Sheets) Shop Commits AWeek Flat].[Project Name], [(Shop Sheets) Shop Commits AWeek Flat].TSD, nz([CountOfTSD],0)-nz([P Week],0) AS Difference
FROM [(Shop Sheets) Shop Commits AWeek Flat] LEFT JOIN [(Shop Sheets) Requested Reference] ON ([(Shop Sheets) Shop Commits AWeek Flat].TSD = [(Shop Sheets) Requested Reference].TSD) AND ([(Shop Sheets) Shop Commits AWeek Flat].[Project Name] = [(Shop Sheets) Requested Reference].[Project Name]) AND ([(Shop Sheets) Shop Commits AWeek Flat].Shops = [(Shop Sheets) Requested Reference].Shops);

Any guidance or help on this is greatly appreciated I may be going to wrong route with this and is why I am asking the question.

Either query may contain a couple different entries but I need to show all equal ones and all different ones in the same ending query.

Thanks

 

[plog]

The two SQL codes you've pasted are identical, I don't see how they can return different results. I think you copied the same one in twice. So I can't breakdown the SQL and form just one query to achieve what you want. However, without knowing the SQL in both queries I would suggest a union query based on the two queries you have.

Suppose the first is called 'A' and the second is called 'B', this would be the SQL to return all unique results:

SELECT * FROM A
UNION
SELECT * FROM B;

 

[thechazm]

My bad I accidently posted the same one twice :/ Anyway I'll take a look at your recommendations and see if that will achive what I am looking for.

 

[thechazm]

Thank you a Union query was just what I needed.

 

[MSAccessRookie]

Thank you a Union query was just what I needed.

Another tip for you would be to reconsider your Table Naming Conventions. Using Special Characters like "(", ")", and Spaces, and having overly Long Names makes it more difficult to maintain the code over time. If you need to (or just want to) continue these Names, you can always use Aliases for your Table Names to provide more control over maintaining the code.

FOR INSTANCE:

[FONT=Times New Roman][SIZE=3]SELECT [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   [(Shop Sheets) Shop Commits AWeek Flat].Shops,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   [(Shop Sheets) Shop Commits AWeek Flat].[Project Name],[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   [(Shop Sheets) Shop Commits AWeek Flat].TSD,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   nz([CountOfTSD],0)-nz([P Week],0) AS Difference[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]FROM [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   [(Shop Sheets) Shop Commits AWeek Flat] LEFT JOIN [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   [(Shop Sheets) Requested Reference] ON [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       ([(Shop Sheets) Shop Commits AWeek Flat].TSD = [(Shop Sheets) Requested Reference].TSD) AND [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       ([(Shop Sheets) Shop Commits AWeek Flat].[Project Name] = [(Shop Sheets) Requested Reference].[Project Name]) AND [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       ([(Shop Sheets) Shop Commits AWeek Flat].Shops = [(Shop Sheets) Requested Reference].Shops);[/FONT][/SIZE]

BECOMES:

[SIZE=3][FONT=Times New Roman]SELECT [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   SSSC.Shops,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   SSSC.[Project Name],[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   SSSC.TSD,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   nz([CountOfTSD],0)-nz([P Week],0) AS Difference[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]FROM [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   [(Shop Sheets) Shop Commits AWeek Flat] AS SSSC LEFT JOIN [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   [(Shop Sheets) Requested Reference] AS SSRR ON [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       (SSSC.TSD = SSRR.TSD) AND [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       (SSSC.[Project Name] = SSRR.[Project Name]) AND [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       (SSSC.Shops = SSRR.Shops);[/FONT][/SIZE]