ONE to MANY: Limiting Records for the ONE

[Guest]

I have a ONE to MANY relationship with Contacts and their multiple Addresses.

I know how to make it so that the ONE record shows up only once when I use a
Report/Form (and then of course multiple MANY records show up in the
SubReport/SubForm)

However, in a query, I would like a Contact to show only once--even if it
has many addresses.

The problem comes in when I need the Addresses Table as part of the query in
order to pick only certain address types. (I don't want to display the
address types, I just want to display any Contact with those types, but only
display the Contact once)

I've struggled with "Totals" queries (Group By), but am still not making
progress.

Any help is appreciated.

Is there a way to do this in a query?

 

[Jason Lepack]

Post back with the SQL of the query you're trying to build if this
doesn't work.

I think the only thing you need to do is un-check the "Show" check box
for the field from the addresses table.

I suspect you're trying to do something like this:

SELECT tbl_contacts.*
FROM tbl_contacts
INNER JOIN tbl_addresses
ON tbl_contacts.contact_id = tbl_addresses.contact_id
WHERE tbl_addresses.address_type = "Rural"

The difference between what the above SQL is and what you are actually
doing is all in the first line. What you are doing is probably:
SELECT tbl_contacts.*, tbl_addresses.address_type
This would show:
contact_id, address_type
1, home
1, office
2, home
2, office
etc.

Hope that helps,
Jason Lepack

 

[Guest]

Jason, thanks. You helped me get on the right road. What I did additionally
to make it work was either one of these:

SELECT DISTINCTROW
SELECT DISTINCT

So, the above solves the multiple MANY showing up. And I know I said I don't
want to display the various AddressTypes, but I would still like to have
access to these fields when I look at "that" query in VBA. You know, like
this:

While Not rst.EOF
rst.Fields(7) = X Then

Fields(7) being the field that is now "hidden" in the query in order to
insure only one of the ONE's shows up.