Carolyn-
It looks like the relationships are:
People --> [CARO Dogs] --> [Score Table]
That should return one row per score, not one row per dog. After you add
criteria to filter data from the Score Table to the year and month you want, you
should get the scores only for dogs who had entries in the year and month you
want. You still may get multiple rows per dog if a dog entered more than one
competition in a given year and month.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Carolyn Homonko
Sent: Thursday, May 19, 2011 3:42 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Easy query gone wrong
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-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: May-18-11 11:27 PM
To: ms_access_professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Easy query gone wrong
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
To: MS_Access_Professionals@yahoogroups.com
From: homonko@tbaytel.net
Date: Wed, 18 May 2011 23:03:05 -0400
Subject: [MS_AccessPros] Easy query gone wrong
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]
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar