Duplicate responses to query

[sar68]

I'm using Access 2007 on windows vista. Intel T7400 with 2.16 GHz and 2 gigs of ram. 64 bit os.

If I run a query that goes across multiple tables, I get sensible results. However, if I run a query in only one table, I get duplicate results. In fact I get over 300 repetitions of each result. This only occurs when I use multiple fields. The database I'm using is a large collection of info on boating accidents. I was trying to get a list of all prop injuries and their ages so I used field1=accident report number, field 2=prop injury with criteria="yes" and field 3 as age in my query. I got over 300 repetitions of each accident(duplicates of the accident report number) in my results. 368 to be exact

SELECT DECSEDCGAll.[Accident Report No#], DECSEDCGAll_1.[Prop Injury], DECSEDCGAll_2.[Age of Victim]
FROM DECSEDCGAll, DECSEDCGAll AS DECSEDCGAll_1, DECSEDCGAll AS DECSEDCGAll_2
WHERE (((DECSEDCGAll_1.[Prop Injury])="yes"));

I really have no idea why this happens.

As I said, this doesn't seem to be a problem if I query accross tables. For example if I use field 1) prop injuries with criteria=-1 from the deceased table and field 2) vessel length from the vessel table, I get a nice table of all the different vessel lengths with no duplicates!

help?

Stephen




Help?

Thanks,
Stephen

[MMcCarthy]

Hi Stephen,

You are getting multiple results because you haven't created any join relationship between your instances of the table. Therefore you are getting every combination of possible results.

Why do you need three instances of the table?

Does this not give you the result you want, if not why?

 
SELECT [Accident Report No#], [Prop Injury], [Age of Victim]
FROM DECSEDCGAll
WHERE ((([Prop Injury])="yes"));

[sar68]

Thanks, I realized my error after a couple decent nights of sleep!

[MMcCarthy]

Thanks, I realized my error after a couple decent nights of sleep!

Isn't that always the way :)