Missing Record

[DBFIN]

When I run a query in a macro it is missing one record. I have a very simple query that reads Table 1, groups on three fields, sums two fields and makes Table 2 without any filtering criteria. When I run the query separately it is not missing this record.

My research shows that this typically occurs from joins, database corruption, null values. None of these apply, there are no joins. Query SQL is as follows:


SELECT BOUND_TOTAL.[BROKER OWNER], BOUND_TOTAL.[BROKER NAME], BOUND_TOTAL.PROGRAM, Sum(BOUND_TOTAL.Count) AS [# BOUND], Sum(BOUND_TOTAL.PREMIUM) AS PREMIUM INTO [HIT RATIO_BOUND TEST]
FROM BOUND_TOTAL
GROUP BY BOUND_TOTAL.[BROKER OWNER], BOUND_TOTAL.[BROKER NAME], BOUND_TOTAL.PROGRAM;

 

[Minty]

Sounds like either a duplicate record or null value to me.
TBH we can't really guess without seeing the data - can you post the data and the query in a stripped down database

 

[CJ_London]

using characters like # in field names can also cause problems, if that is what you want to call it, use the caption property.

Also, does access advise how many records were created? - and if so any that weren't for some reason. If you have any error handling, turn off until the problem is resolved

 

[DBFIN]

I found the issue. The query was summing a field with a convaluted expression, which I replaced with a simple Sum function and the record is no longer missing. I don't know why this fixed it, but it worked.