Union Query - Query 1

[Binnit]

I have built a Union query to comapre the data in two identicle tables.I have deliberately not done a UNION ALL so as to exclude duplicate records.

It appears to work OK and extracts 2 instances of each record that has changed the "before" and "after" and also includes "New" records.

However, it also appears to then include a single instance of records that have not changed from one of the tables. How can I prevent this?

my code is:-

SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblPreDecFolioDetails

UNION SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblFolioDetails;

Should I add a WHERE clause somewhere?


If IT ain’t working Binnit and Reboot

 

[hermanlaksko]

SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblPreDecFolioDetails WHERE .....

UNION SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblFolioDetails WHERE ....


Herman
Say no to macros

 

[Binnit]

Hermanlaksko

Thanks but
WHERE.... what?

If IT ain’t working Binnit and Reboot

 

[PHV]

you may try this:
SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM (
SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblPreDecFolioDetails
UNION ALL
SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblFolioDetails
) AS U
GROUP BY FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
HAVING COUNT(*) = 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hermanlaksko]

What are you looking for?

Where FolioPolicyNo = 34

SELECT ServicerapportVare.VareNR, Sum(ServicerapportVare.Antal) AS SumOfAntal, ServicerapportVare.VareTekst, ServicerapportVare.VareDato, Sum(ServicerapportVare.VarePris) AS SumOfVarePris, Mid([VareDato],4,2) AS Md, Right([VareDato],4) AS Yr FROM ServicerapportVare GROUP BY ServicerapportVare.VareNR, ServicerapportVare.VareTekst, ServicerapportVare.VareDato HAVING (ServicerapportVare.VareNR Is Not Null) And Right([VareDato], 4) = Me!Ar

UNION SELECT ServicerapportVare.VareNR, Sum(ServicerapportVare.Antal) AS SumOfAntal2, ServicerapportVare.VareTekst, ServicerapportVare.VareDato, Sum(ServicerapportVare.VarePris) AS SumOfVarePris2, Mid([VareDato],4,2) AS Md, Right([VareDato],4) AS Yr FROM ServicerapportVare GROUP BY ServicerapportVare.VareNR, ServicerapportVare.VareTekst, ServicerapportVare.VareDato HAVING (ServicerapportVare.VareNR Is Not Null) And Right([VareDato], 4) = Me!Ar -1
ORDER BY ServicerapportVare.VareNR

Herman
Say no to macros

 

[Binnit]

PHV
Having copied & pasted your suggestion, I am getting a syntax error in thr FROM clause, are you able to identify why?

If IT ain’t working Binnit and Reboot

 

[PHV]

Which version of access (hopefully 2k or above) ?
If still ac97 then save the union ALL query as, say, qryAllFolioDetails:
SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblPreDecFolioDetails
UNION ALL
SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM tblFolioDetails

And now your query:
SELECT FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
FROM qryAllFolioDetails
GROUP BY FolioRef, FolioPolicyNo, FolioCoverFrom, FolioCoverTo, FolioRenewalDate, FolioCanxDate, FolioOccupation, FolioInsured, FolioAddress, FolioBuildingsDV, FolioBuildingsUplift, FolioContentsSI, FolioRentSI, FolioRentMIP, FolioPremium, FolioPostCode, FolioTerrorismPremium, FolioTerrorismEffDate
HAVING COUNT(*) = 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Binnit]

PHV
Apologies for the delay in reply...I am using both 97 & 2003 but at present this is being done in 97.

Your solution appears to have worked, I just need to verify exactly what data it has returned but it looks very promising.

I think I can follow your logic behind the 2 query method but will spend a bit of time to study it closer. Once again a different approach appears to have made the solution. I presume from your version question that this would be done differently in A2003?

All the same, a great bit of work - here's a well earned star!

If IT ain’t working Binnit and Reboot

 

[Binnit]

PHV
OK I think I get it,
Union query picks up all records inc duplicates
2nd Query groups and restricts record count to 1 instance

This method of Count(*) as an expression is new to me but what really puzzles me is why the 2nd query does not work by setting the Unique Values property. Clearly its not the same because yours works and Unique Values does not!

Any chance of an explanation for this?

If IT ain’t working Binnit and Reboot