Query for groups of records

[Greg]

I would like to know if it is possible to write a select query based upon
the number of records within a group.
I have a table with a primary key made up from 2 fields. The first field is
Billing Account Number and the second field is Invoice Date. I have several
thousand records in this table.
An example of the table and values follows:

Billing Account Number, Invoice Date, A charges, B charges, C charges,,,,,
7791123123 , 06/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 07/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 08/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 09/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 10/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 11/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 12/01/2006, $1234.99, $135.88, $ 54321.87,,,,
1172223333 , 06/01/2006, $12345.99,$ 243.55, $458.00,,,,,
1172223333 , 07/01/2006, $12345.99,$ 243.55, $458.00,,,,,
1172223333 , 08/01/2006, $12345.99,$ 243.55, $458.00,,,,,

I would like to select all 7791123123 records because there is a month 12 in
the Invoice Date field but no records for 1172223333 because that group does
not have a month 12.
Is this possible through the Query Wizard? I know what SQL is but would not
consider myself at all proficient.

Does anyone have any suggestions on how to do this?

Thank You in advance for you attention!

Greg

 

[John W. Vinson]

I would like to know if it is possible to write a select query based upon
the number of records within a group.

Billing Account Number, Invoice Date, A charges, B charges, C charges,,,,,
7791123123 , 06/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 07/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 08/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 09/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 10/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 11/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 12/01/2006, $1234.99, $135.88, $ 54321.87,,,,
1172223333 , 06/01/2006, $12345.99,$ 243.55, $458.00,,,,,
1172223333 , 07/01/2006, $12345.99,$ 243.55, $458.00,,,,,
1172223333 , 08/01/2006, $12345.99,$ 243.55, $458.00,,,,,

I would like to select all 7791123123 records because there is a month 12 in
the Invoice Date field but no records for 1172223333 because that group does
not have a month 12.

I'm not understaning these two requirements. First you say your
criterion depends on the number of records in the group; but then you
say you need to determine based on the existance of a particular value
somewhere in the group - with no reference to the number of records.

Is this possible through the Query Wizard? I know what SQL is but would not
consider myself at all proficient.

The Wizard probably won't be clever enough, but the query can
certainly be done. I REALLY have to question your table design though.
Do you (I hope!!!) have a separate Billing Accounts table, with one
record per billing account number? Are you aware that you are
"committing spreadsheet", embedding a one Invoice Date to many Charges
relationship in each record? If you have up to Z charges, what will
you do when you need to go On Beyond Zebra?

AS WRITTEN, you can do this with a single subquery:

SELECT * FROM yourtable
WHERE [Billing Account Number IN
(SELECT DISTINCT [Billing Account Number]
FROM tablename AS X
WHERE Month(X.[Invoice Date]) = 12

John W. Vinson [MVP]

 

[Greg]

John,

You are correct I did not correctly state what I am trying to do.
I did want to select the group based on a value not a record count.

I do not completely understand your next comment concerning "committing
spreadsheet".
My situation is that I receive several CD's each month. Each CD Equates to
an account by our service provider(communications). Within that account or
CD are hundreds of wireless units(Blackberry, PDA, Voice only, air
cards,etc).
Each of these units have dozens of associated usage and charge fields. I
have a Billing Account table for each CD (account) but not for each unit.
The "billing account number" field I used in my example was an attempt to
simplify my example, it should really be "Unit Number" Do you feel that I
am painting myself into a corner? Do you see something wrong?

Thank you for your help! I will study you SQL and implement as soon as
possible.

Greg

I would like to know if it is possible to write a select query based upon
the number of records within a group.

Billing Account Number, Invoice Date, A charges, B charges, C charges,,,,,
7791123123 , 06/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 07/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 08/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 09/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 10/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 11/01/2006, $1234.99, $135.88, $ 54321.87,,,,
7791123123 , 12/01/2006, $1234.99, $135.88, $ 54321.87,,,,
1172223333 , 06/01/2006, $12345.99,$ 243.55, $458.00,,,,,
1172223333 , 07/01/2006, $12345.99,$ 243.55, $458.00,,,,,
1172223333 , 08/01/2006, $12345.99,$ 243.55, $458.00,,,,,

I would like to select all 7791123123 records because there is a month 12 in
the Invoice Date field but no records for 1172223333 because that group does
not have a month 12.

I'm not understaning these two requirements. First you say your
criterion depends on the number of records in the group; but then you
say you need to determine based on the existance of a particular value
somewhere in the group - with no reference to the number of records.

Is this possible through the Query Wizard? I know what SQL is but would not
consider myself at all proficient.

The Wizard probably won't be clever enough, but the query can
certainly be done. I REALLY have to question your table design though.
Do you (I hope!!!) have a separate Billing Accounts table, with one
record per billing account number? Are you aware that you are
"committing spreadsheet", embedding a one Invoice Date to many Charges
relationship in each record? If you have up to Z charges, what will
you do when you need to go On Beyond Zebra?

AS WRITTEN, you can do this with a single subquery:

SELECT * FROM yourtable
WHERE [Billing Account Number IN
(SELECT DISTINCT [Billing Account Number]
FROM tablename AS X
WHERE Month(X.[Invoice Date]) = 12

John W. Vinson [MVP]