append distinct records

[slimjen1]

Gm. I changed the table to narrow down the dates so I would not need the max date. All the records in the table have a current and the same date. I thought I had this problem solved. But when I applied this code to another table with the same concept; it returned data that matched the data in both tables and a lot of duplicates. Please review my code. I've compared it to the previous table that works and only changed field names and I am getting less than desirable results.

SELECT qryMed.SSN, qryMed.CoverageLevel, qryMed.PPeriod, qryMed.CliRate, qryMed.ERate, qryMed.TotRate, qryMed.SnapShotDte
FROM qryMed INNER JOIN tblEECoverage ON qryMed.SSN = tblEECoverage.SSN
WHERE (((Nz([qryMed].[CovLevel]))<>Nz([tblEECoverage].[CovLevel]))) OR (((Nz([qryMed].[PayPeriod]))<>Nz([tblEECoverage].[PPeriod]))) OR (((Nz([qryMed].[CliRate]))<>Nz([tblEECoverage].[CliRate]))) OR (((Nz([qryMed].[ERate]))<>Nz([tblEECoverage].[ERate]))) OR (((Nz([qryMed].[TotRate]))<>Nz([tblEECoverage].[TotRate])));

Thanks

 

[CJ_London]

I've had a look at your code and on the face of it, cannot see a problem so suspect the issue is with the data.

If all your data now has the same date, it will return all of those records - the fact you are not limiting it to the max date is immaterial - what you need to do is eliminate from the table all the duplicate records.

You can try SELECT DISTINCT rather than SELECT which will eliminate some duplicates - perhaps all, but without seeing your data I cannot confirm this will work to your satisfaction

 

[slimjen1]

Thanks. I did put distinct and eliminated the dups in the qry table. But I am still getting records that are in both tables(all fields are matching) with no unique data in any of the fields. I pulled the records from each table and reviewed the data and they are identical; field to field. so the query should not be pulling any of the records because I have <> as the criteria. Don't know what is wrong!.

 

[slimjen1]

Ok. I think I might have found the problem. The Rate fields(totRate, ERate) on the tbl side are not fixed in the query(tbl totRate is 61.344444 qry totRate is 61.34) In the table it is fixed but when I run the query it's different. I cannot format the field in the query. Is there a way that I'm missing? Thanks

 

[CJ_London]

SELECT qryMed.SSN, qryMed.CoverageLevel, qryMed.PPeriod, qryMed.CliRate, qryMed.ERate, qryMed.TotRate, qryMed.SnapShotDte
FROM qryMed INNER JOIN tblEECoverage ON qryMed.SSN = tblEECoverage.SSN
WHERE (((Nz([qryMed].[CovLevel]))<>Nz([tblEECoverage].[CovLevel]))) OR (((Nz([qryMed].[PayPeriod]))<>Nz([tblEECoverage].[PPeriod]))) OR (((Nz([qryMed].[CliRate]))<>Nz([tblEECoverage].[CliRate]))) OR (((Nz([qryMed].[ERate]))<>Nz([tblEECoverage].[ERate]))) OR (((Nz([qryMed].[TotRate]))<>Nz([tblEECoverage].[TotRate])));

Not sure if this is relevant but your select is different from your criteria i.e.
qryMed.CoverageLevel v [qryMed].[CovLevel]
qryMed.PPeriod v [qryMed].[PayPeriod]

 

[CJ_London]

use the round function to round each value to say 2dp

 

[slimjen1]

Having difficulty to how to do this with nz. Before or after the nz: format(round(<>Nz([tblEECoverage].[CliRate], 2))) Do I do this with both fields or just the criteria?

 

[CJ_London]

nz always goes immediately round the variable so you need

round(Nz([qryMed].[CliRate]),2)<>round(Nz([tblEECoverage].[CliRate]),2)

not sure if you need it on both sides but have included just in case

 

[slimjen1]

The rounding worked but it wasn't what is wrong with the data. This is so frustrating. I am going to try rebuilding on field at a time. I don't know what else to do!

 

[slimjen1]

Got it! It was looking at the earlier date in the tbl fields. I switched the dmax to lookup the latest date in the tbl field to compare to the date in the qry and it works as expected! Thanks for all your help! Awesome forum!!!