Kamis, 19 Mei 2011

RE: [MS_AccessPros] Easy query gone wrong

 


Does every dog have one and only one owner?

If a dog has multiple records in the Score Table, it will display multiple times in your list. You have the Score Table in the query but don't display any fields.

You could try change to a totals query to remove duplicates:

SELECT [CARO Dogs].Dog, [CARO Dogs].[Call Name],
[CARO Dogs].Breed, [CARO Dogs].[Year/Month of Participation],
[CARO Dogs].[Jump Height]
FROM People
INNER JOIN ([CARO Dogs]
INNER JOIN [Score Table]
ON [CARO Dogs].Dog = [Score Table].Dog)
ON People.[Owner ID] = [CARO Dogs].[Owner ID]
GROUP BY [CARO Dogs].Dog, [CARO Dogs].[Call Name],
[CARO Dogs].Breed, [CARO Dogs].[Year/Month of Participation],
[CARO Dogs].[Jump Height];

Duane Hookom
MS Access MVP


From: homonko@tbaytel.net


Thanks for suggesting the SQL view, Duane. I haven't used it before. There
was a reference to a table I had been playing with and thought I had
deleted. By getting rid of that, the following query only produces a list of
388 dogs instead of over 12,000. (Still would like it to be around 30!)

SELECT [CARO Dogs].Dog, [CARO Dogs].[Call Name], [CARO Dogs].Breed, [CARO
Dogs].[Year/Month of Participation], [CARO Dogs].[Jump Height]
FROM People INNER JOIN ([CARO Dogs] INNER JOIN [Score Table] ON [CARO
Dogs].Dog = [Score Table].Dog) ON People.[Owner ID] = [CARO Dogs].[Owner
ID];

I have a table of People. The key field is an automatically assigned Owner
ID number. These People may own multiple dogs who I keep in the CARO Dogs
table. The unique key field is Dog. Owner Id in this table links back to
Owner Id in the People table. Dogs may compete in multiple classes in
multiple events during the year. Score Table will hold these
entries/results. Dog is the field linking this table to CARO Dogs. There is
no key field in this table.

The query I was trying to eventually create would choose all the dogs
entered in a particular year and month and list with their associated
information from the Score Table.

Have I given you enough information to show me the error of my ways? Thanks
for any help you can give.
Carolyn

-----Original Message-----
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom

Can you provide the SQL view of the query? Have you joined the key fields?
How are the tables related?

Duane Hookom
MS Access MVP

From: homonko@tbaytel.net

I was having trouble with getting the records I expected from a query
using

3 tables, so I went to the most simple scenario - a straightforward listing

of 1 table. My plan was to then expand my criteria one step at a time until

I found where I went wrong. The original table only has 48 records. I was

only displaying the key field plus 3 other fields from the table. I expected

to see 48 records displayed. Instead my query - with absolutely no criteria

applied - produced a list of 12,416. What in the world have I screwed up?

Carolyn




[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar