Select query - only one-to-many records?

[Guest]

hi all

I have two tables related one-to-many. Can I write a query which will only
return those records with *more than one* related record, and ignore those
with only one? Example:

tblMeter tblConnection
Meter Connection Meter
111 456 111
222 777 222
222 888 222

I only want to select those records for meter 222, ignore the record for
meter 111. What's the easiest way of doing this? Will I need to write some
code?

Thanks for reading and for any assistance!

 

[Guest]

hi Tom thanks for responding.

Meter *is* the primary key of tblMeter, and is held as a foreign key in
tblConnection. I was trying to show the query output in the example, ie
there aren't two tblMeter records with the same primary key (222 in this
case), but there are two tblConnection records that both have the same
foreign key of tblMeter.

In other words if I run this:

SELECT tblMeter.*, tblConnection.*
FROM tblMeter INNER JOIN tblConnection ON tblMeter.Meter =
tblConnection.Meter;

I get this query output:

tblMeter tblConnection
Meter Connection Meter
111 456 111
222 777 222
222 888 222

The two additional examples you gave could not occur (as Meter is the
primary key and referential integrity is ticked).

Hopefully this is a bit clearer - can you advise? Thanks again for your time!

 

[Guest]

Hi Andy,

Okay, I thought you were showing the data from the tables, as opposed to the
query recordset. You can use a sub query to filter out records in
tblConnection that have only one meter. Does this work for you?

SELECT tblMeter.Meter, tblConnection.Connection
FROM tblMeter
INNER JOIN tblConnection
ON tblMeter.Meter = tblConnection.Meter
WHERE (((tblMeter.Meter)
In (SELECT Meter FROM tblConnection
GROUP BY Meter HAVING Count(Meter)>1));

It should return this:

Meter Connection
222 777
222 888


If you wish to return only one record for meter 222, then you need to decide
if you want an earlier connection number or a later connection number.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Aha! Yes exactly what I was after - thank you very much.

rgrds

 

[Guest]

You're welcome. Glad I could help.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________