Blank Fields in a query

[Chris66]

Hi, I am trying to get a query which will compare a table created in the morning and then a table created in the afternoon. I am createing a field in each table which will become the primary key. The problem I am having is the fields which come from the table created in the afternoon are blank. I am using the join properties which will give me everything from the afternoon file and only those files from the morning file which have had a change made to them. For some reason, I am missing data from the afternoon file, It is a one to one relationship.

 

[Jon K]

Please post the SQL statement of your query (i.e. the Select statement in SQL View.)

 

[Chris66]

SELECT [POAMMOD.PEQTC]/[POPMMOD.PEQTC] AS Expr2, POAMMOD.PEPN, POAMMOD.PENO, POAMMOD.PEQTC AS [Old qty], POPMMOD.PEQTC AS [New qty], POAMMOD.PEVEND, POAMMOD.Expr1, POPMMOD.Expr1 INTO [Changed PO]
FROM POAMMOD LEFT JOIN POPMMOD ON POAMMOD.Expr1 = POPMMOD.Expr1
WHERE ((([POAMMOD.PEQTC]/[POPMMOD.PEQTC])<>1 Or ([POAMMOD.PEQTC]/[POPMMOD.PEQTC]) Is Null));


Thanks again for any help. I also attached the file if anyone would care to look at that.

 

[Jon K]

The fields returned are empty because the AM Expr1s returned do not have a corresponding PM Expr1. They are returned by the LEFT JOIN.

(Note. The rest of the AM Expr1s in the table all have a corresponding PM Expr1 in the PM table and their AM and PM quantities have remained the same, so these records are not returned. If you change the PEQTC of one of these Expr1s in one of the tables, that Expr1 will be returned. So your query works so long as none of the PM PEQTC is 0.)

However, if you want to show also the PM Expr1s that do not have a corresponding AM Expr1, you can use a Union Query to combine the LEFT JOIN with a RIGHT JOIN.

qryUnionLeftRightJoins:-
SELECT [POAMMOD.PEQTC]/[POPMMOD.PEQTC] AS Expr2, [POAMMOD].[PEPN], [POAMMOD].[PENO], [POAMMOD].[PEQTC] AS [Old qty], [POPMMOD].[PEQTC] AS [New qty], [POAMMOD].[PEVEND], [POAMMOD].[Expr1], [POPMMOD].[Expr1]
FROM POAMMOD LEFT JOIN POPMMOD ON [POAMMOD].[Expr1]=[POPMMOD].[Expr1]
WHERE ((([POAMMOD.PEQTC]/[POPMMOD.PEQTC])<>1 Or ([POAMMOD.PEQTC]/[POPMMOD.PEQTC]) Is Null))
UNION
SELECT [POAMMOD.PEQTC]/[POPMMOD.PEQTC] AS Expr2, [POAMMOD].[PEPN], [POAMMOD].[PENO], [POAMMOD].[PEQTC] AS [Old qty], [POPMMOD].[PEQTC] AS [New qty], [POAMMOD].[PEVEND], [POAMMOD].[Expr1], [POPMMOD].[Expr1]
FROM POAMMOD right JOIN POPMMOD ON [POAMMOD].[Expr1]=[POPMMOD].[Expr1]
WHERE ((([POAMMOD.PEQTC]/[POPMMOD.PEQTC])<>1 Or ([POAMMOD.PEQTC]/[POPMMOD.PEQTC]) Is Null));


As a Union Query cannot be converted into a Make-Table Query, you will need a Make-Table query to create the table:-

SELECT * INTO [Changed PO]
FROM qryUnionLeftRightJoins;