UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query To Find Only Form Records That Have Multiple Subform Values Related To Them, Access 2007    
 
   
mussy
post Apr 8 2020, 06:16 PM
Post#1



Posts: 41
Joined: 4-May 05



Hi!

I have records related in a one-to-many relationship with an AID (One) related to BID (Many) records. So, as you would expect, one AID can have many BID records related to it (or, of course, just one BID associated with it). Is there a query I can use to show only those AID records that have more than one BID associated with it? I tried using the Query builder and the Criteria field with a value of >1 but that simple-headed solution returns every AID (or BID) whose number is greater than 1. I'm assuming I have to do some thermonuclear VBA here but not sure how to even get started...

Anyone ever have to tackle something like this?

Thanks in advance for any advice!

Bill

PS: I should probably note that those fields are Autonumber fields.
This post has been edited by mussy: Apr 8 2020, 06:20 PM
Go to the top of the page
 
vtd
post Apr 8 2020, 06:52 PM
Post#2


Retired Moderator
Posts: 19,777
Joined: 14-July 05
From: Sydney NSW Australia


Should be something like:
CODE
SELECT A.AID, A.FieldA, A.FieldB, ...
FROM tblA AS A LEFT JOIN tblB AS B
  ON A.AID = B.frg_AID
GROUP BY A.AID, A.FieldA, A.FieldB, ...
HAVING COUNT(B.BID) > 1


or if you want to use SubQuery:
CODE
SELECT A.AID, A.FieldA, A.FieldB, ...
FROM tblA AS A
WHERE
( SELECT COUNT(B.BID)
  FROM tblB AS B
  WHERE B.frg_AID = A.AID ) > 1


This type of query may take some time to process if there are large number of records in 1 or both Tables so you should restrict the returned data set to the minimum possible, i.e. a WHERE clause in the SQL to restrict filter the records from tblA to the minimum required.

Go to the top of the page
 
GroverParkGeorge
post Apr 8 2020, 06:54 PM
Post#3


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


Try an aggregate query similar to this.

SQL
SELECT AID, Count(AID) AS AIDCount FROM tblBID WHERE Count(AID) >1 Group BY AID

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Apr 8 2020, 07:03 PM
Post#4



Posts: 3,704
Joined: 27-February 09



Umm...
HAVING COUNT(*) > 1
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 10:25 PM