One record becomes two in query

[RobTsintas]

This one has got me completely stumped. I have already tried repairing the database.

A query called CurrentGP has one record for a particular ID number. The query has 1787 records in total.

If I create a new query containing ONLY the CurrentGP query, and show the ID field, there are two records for this particular ID. This does not happen for any of the other records; the total number of records is 1788.

Please, does anyone have ANY idea how something like this could happen??!

 

[RobTsintas]

By the way, I'm (unfortunately) using Access97, if that makes a difference.

 

[DanChard]

I don't know if it would help but you might try using the distinct keyword to select only unique records in the 2nd query.

 

[RobTsintas]

Thanks. This would be a workaround, but we may want authentic duplicates in the first query to be preserved in the 2nd.

The main thing is trying to understand why this could happen in the first place, as it could have been very costly if I hadn't noticed it.

 

[DanChard]

I can't remember the exact circumstances but I've noticed the same thing (not exactly, but similar) in the past due to grouping. If you're using any group bys then you may want to consider them and see if they are making this happen.

 

[RobTsintas]

Nope, nothing complicated at all, just a straight select query of a query, which returns one extra record.

 

[DanChard]

Well if they're not too long and complicated then maybe you could post the SQL from the two queries and let everyone have a look at what's going on. That's usually the best way to get results!

 

[RobTsintas]

The SQL is below. However, the problem has now gone (although it may return and I still don't know the cause).

The current GP - prequery, shown in the SQL for current GP contained a table called current address. This had two entries for the ID in question, but one of these entries was excluded from current GP by the end date criteria in the SQL.

The older address has now been removed, and even though the duplicate was never included in current GP, the extra record has now disappeared from the second query.

Anyway, here's the SQL. I'm still interested to track down the cause:
For current GP:

SELECT [current GP - prequery].[Hospital Number], [current GP - prequery].[Take-on From], [current GP - prequery].[Take-on To], [current GP - prequery].[GP name], [current GP - prequery].[National GP code], [current GP - prequery].[GP Organisation], [current GP - prequery].[GP addr line 1], [current GP - prequery].[GP addr line 2], [current GP - prequery].[GP addr line 3], [current GP - prequery].[GP addr line 4], [current GP - prequery].[GP Postcode], [current GP - prequery].[GPFH/PCG Code], [current GP - prequery].[PCG Name], [current GP - prequery].[GP HA Code], [current GP - prequery].[DHA Name], [current GP - prequery].[HA Code], IIf(([GP HA Code]="QAA" Or [GP HA Code]="QAR" Or [GP HA Code]="QAT" Or [GP HA Code]="QAV" Or [GP HA Code]="QA4" Or [GP HA Code]="QAP" Or [GP HA Code]="QAJ"),[GPFH/PCG Code],[GP HA Code]) AS [GP Notification Code], IIf(([GP HA Code]="QAA" Or [GP HA Code]="QAR" Or [GP HA Code]="QAT" Or [GP HA Code]="QAV" Or [GP HA Code]="QA4" Or [GP HA Code]="QAP" Or [GP HA Code]="QAJ"),[PCG Name],[DHA Name]) AS [GP Notification Name]
FROM [current GP - prequery], [current month]
WHERE ((([current GP - prequery].[Take-on From])<=[end date]) AND (([current GP - prequery].[Take-on To]) Is Null Or ([current GP - prequery].[Take-on To])>=[end date]));

For the 2nd query:

SELECT [current GP].[Hospital Number]
FROM [current GP];