06-28-04, 02:39 #1Registered User
- Join Date
- Nov 2003
Unanswered: SQL: "... where related records from column x are >= n"?
Need a fairly standard SQL trick:
I have a union query that gives a list where a group of 2-5 records are related to the same main key (field/column1), the second key and a general date field is what sorts the records within the main key number. Example
1, 20.01.2004 11:53, 5032, etc.
1, 20.01.2004 13:05, 5033, etc
1, 02.02.2004 09:38, 7389, etc
2, 16.01.2004 14.11, 5158, etc.
2, 23.01.2004 16:00, 5267, etc.
colunm 1 and 3 are the main keys.
I want to make a query that takes this as a source, and where I can specify that I want only those main keys that have more than x sub records. In the example above , if I want those with 3 or more sub records, it should only return (the same type of listing) the 3 records for main key number 1, but not number 2, as it only contain 2 related subrecords.
I am not sure how to specify that in a query.
"... where related records from colunm 3 are >= 3"... (within the same main id/key in column 1)
Last edited by kedaniel; 06-28-04 at 03:41.Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.
06-28-04, 02:45 #2Registered User
- Join Date
- Sep 2003
- The extremely Royal borough of Kensington, London
from tableA ta
(select key, count(*) As COUNT
group by key
having count(*) > X) V ON
ta.key = V.keyBessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.