Duplicate Records in Access

[GregC]

I'm running a query in access. The query draws from several tables and queries. The tables and queries in the this query use a master table with 7800 records. The relationship to all the other tables and queries are to show all the records from the master table and only those records that match from the other tables and queries. The first time I ran the querry it came back with 7800 records. Just what I looking for. I run the query again and now I'm getting 9700 records. The 1900 extra records are dups. Any ideas why. I'd be happy to include more info as needed.

[ZirconBlue]

I'm running a query in access. The query draws from several tables and queries. The tables and queries in the this query use a master table with 7800 records. The relationship to all the other tables and queries are to show all the records from the master table and only those records that match from the other tables and queries. The first time I ran the querry it came back with 7800 records. Just what I looking for. I run the query again and now I'm getting 9700 records. The 1900 extra records are dups. Any ideas why. I'd be happy to include more info as needed.

In my experience, this is a really common problem with Access queries, and can have different causes. Did you make any changes to any of the queries between then and now?

[GregC]

In my experience, this is a really common problem with Access queries, and can have different causes. Did you make any changes to any of the queries between then and now?

I added two more fields from a query with an existing relationship. I've had this happen to me in the past and I've just rewritten the query and the problem goes away. This one has 88 fields in it (most of them the field name being changed for import into another program.) I really don't feel like starting over.

[kevin]

hmmm, what happens if you view the query in SQL mode instead of the grid, select all the text and copy it. Then create a new query and paste in the copied SQL statement.

Ugly hack but might be the fastest way to create a new query with the same info.

[GregC]

I'll give it a try on Monday.

[ZirconBlue]

I added two more fields from a query with an existing relationship. I've had this happen to me in the past and I've just rewritten the query and the problem goes away. This one has 88 fields in it (most of them the field name being changed for import into another program.) I really don't feel like starting over.

You added the new fields in the 'final' query, or in one of the source queries?

[GregC]

final.

[ZirconBlue]

final.

Hmm. If you make a new query that only contains your current final query, with all fields "grouped", does that consolidate the table down to 7800 records?

[kittykatkarma]

It sounds like one or more of the "new" fields added can have more than one value. If that's the case then try selecting the MAX value in the new field.

Example: In an Issues Log MDB, an issue could have multiple notes. In the where clause I would do this to pull all unique issues and only the most current note.


WHERE ((
(Notes.Note_ID)=(SELECT MAX(note_id)
FROM NOTES
WHERE [Notes]![Issue_ID] = [ISSUES]![Issue_ID] )
))



Otherwise, check your joins. Access has a knack for confusing joins, particularly when you have a good working query then add to it.

[GregC]

Thanks everyone, I started fresh and I'm coming up with the 7800 records now. I've compared the two and I'll be danged if I can figure where I went wrong. I'm sure if I look at it fresh in a week or so I'll see my error.

The only thing I can figure is I did some minor reformating of some data in the query, maybe that had some effect. I had a field where info comes in like 12345/6789 and I had to break it down into two fields, 12345 and 6789.

[Ripley Twenty-Nine]

Thanks everyone, I started fresh and I'm coming up with the 7800 records now. I've compared the two and I'll be danged if I can figure where I went wrong. I'm sure if I look at it fresh in a week or so I'll see my error.

The only thing I can figure is I did some minor reformating of some data in the query, maybe that had some effect. I had a field where info comes in like 12345/6789 and I had to break it down into two fields, 12345 and 6789.
I would suggest to check your 'AND's and 'OR's in your old query. I've been tripped up in the past by putting brackets in the wrong spot. This can completely change the meaning of the query; Often this will return extra records unexpectedly.
Consider the following example:

SELECT *
FROM Table1, Table2
WHERE Table1.Field1 = Table2.Field1
AND Table1.Field3 > 3
OR Table2.Field3 > 10;

In this case, you will get more results than expected, because it will only be joining Table1.Field1 to Table2.Field1 IF Table1.Field3 > 3. For the second condition, it will not do the join at all, and it will match all records from Table1 to Table2. However, the meaning completely changes with the following change:


SELECT *
FROM Table1, Table2
WHERE Table1.Field1 = Table2.Field1
AND (Table1.Field3 > 3
OR Table2.Field3 > 10);


It might have been something like that.

[ZirconBlue]

Thanks everyone, I started fresh and I'm coming up with the 7800 records now. I've compared the two and I'll be danged if I can figure where I went wrong. I'm sure if I look at it fresh in a week or so I'll see my error.

The only thing I can figure is I did some minor reformating of some data in the query, maybe that had some effect. I had a field where info comes in like 12345/6789 and I had to break it down into two fields, 12345 and 6789.

That may have been the problem. Duplicate records in which a field has to be "calculated" will often not get consilidated in a particular query. You usually need to do a pre-query that breaks the fields down, then another queary based on that queary will consolidate the records for you.

[GregC]

It turns out I forgot on one of the sub-queries to set the criteria value for one of the fields. Once again I appreciate all the help.