Grouping Query

[Jake]

I have a table containingg 4 fields: [Member ID] [Trans Date] [Code 1]
[Code 2]

I would like to create a query which indicates for each [Code 1] what
possible combinations of [Code 2] are there for any given [Trans Date]. For
[Code 1] there are 9 different possible values. There are about 400
different [Trans Date] and thousands of different [Member Id]. There are 203
combinations (1 to 1) of [Code 1] and [Code 2]. Please let me know if there
is any way possible to produce the results I am looking for.

Thanks

 

[John W. Vinson]

I have a table containingg 4 fields: [Member ID] [Trans Date] [Code 1]
[Code 2]

I would like to create a query which indicates for each [Code 1] what
possible combinations of [Code 2] are there for any given [Trans Date]. For
[Code 1] there are 9 different possible values. There are about 400
different [Trans Date] and thousands of different [Member Id]. There are 203
combinations (1 to 1) of [Code 1] and [Code 2]. Please let me know if there
is any way possible to produce the results I am looking for.

Thanks

Sure. A Totals query will do this for you.

Create a Query based on your table; select the [Trans Date] and the two code
fields, and the table's primary key (is that Member ID, or does the table have
a PK??). Change the query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M).

Group By the [Trans Date], [Code 1] and [Code 2] fields, and use the Count
operator on the totals line under the primary key field.

 

[Jake]

Hi John,
Thanks for the reply. The table currently does not have a primary key.
There are multiple records with the same Member ID. Should I create another
field, like an autonumber, and use that as the primary key in order to make
this work correctly?

thanks

I have a table containingg 4 fields: [Member ID] [Trans Date] [Code 1]
[Code 2]

I would like to create a query which indicates for each [Code 1] what
possible combinations of [Code 2] are there for any given [Trans Date]. For
[Code 1] there are 9 different possible values. There are about 400
different [Trans Date] and thousands of different [Member Id]. There are 203
combinations (1 to 1) of [Code 1] and [Code 2]. Please let me know if there
is any way possible to produce the results I am looking for.

Thanks

Sure. A Totals query will do this for you.

Create a Query based on your table; select the [Trans Date] and the two code
fields, and the table's primary key (is that Member ID, or does the table have
a PK??). Change the query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M).

Group By the [Trans Date], [Code 1] and [Code 2] fields, and use the Count
operator on the totals line under the primary key field.

 

[John W. Vinson]

Hi John,
Thanks for the reply. The table currently does not have a primary key.
There are multiple records with the same Member ID. Should I create another
field, like an autonumber, and use that as the primary key in order to make
this work correctly?

Every table should... must!... have a primary key. It needn't be an
autonumber, but if you don't have a PK then there is no way to distinguish one
record from another. As it is, there's nothing builtin to prevent having
eleven absolutely identical records; grouping these will give strange and very
likely erroneous results.