Counting Records by Date where there are several groups of dates

[BlackFireNova]

I have an Access 2003 mdb which contains software records. I need to
sort on a particular type of software, and then identify and count how
many copies there are per each group of that type purchased on the same
date.

I have no trouble doing a query to extract the type (say MS Excel 2002,
for example). The trouble is, there could be 50 copies purchased on
date "x", 80 copies purchased on date "y", and 250 copies purchased on
date "z", etc.

Is there any way I can use a function, or is there a code example that
would scan the date field, and count the number of records which exist
for each separate date of purchase?

Any help is appreciated!

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

I believe you're going to find this easier than you anticipated.

Create a query in the Query Grid, including the Date and some other field
which will always be present. In design view, on the menu, choose View |
Totals. Under the Date, select GroupBy, and under the other field, select
Count.

If your "dates" are actually date and time, then extract the Date using the
DateValue function (in the query itself) and GroupBy the extracted date
value.

Larry Linson
Microsoft Access MVP
"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:40*********************@news.frii.net...

I have an Access 2003 mdb which contains software records. I need to
sort on a particular type of software, and then identify and count how
many copies there are per each group of that type purchased on the same
date.

I have no trouble doing a query to extract the type (say MS Excel 2002,
for example). The trouble is, there could be 50 copies purchased on
date "x", 80 copies purchased on date "y", and 250 copies purchased on
date "z", etc.

Is there any way I can use a function, or is there a code example that
would scan the date field, and count the number of records which exist
for each separate date of purchase?

Any help is appreciated!

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[BlackFireNova]

Larry:

Your solution gives me only two elements of the information. No
matter what I try, if I try to acquire the additional date info it
skews the results.

I can, as described, do a query that groups by Software Type, and
counts the date groups, however I also need to retrieve the actual
DATE of each group counted. This is where I'm having trouble.

As you described, I can generate an output that shows me (Software
[grouped]) and the count of how many per date purchased. When I try
and add the additional field to show the ACTUAL DATE of each date
group counted, it skews the query output.

How can I achieve this additional output?

ie: Say there were 200 Excel Licenses purchased in four groups, on
four dates

Software # of License per Date of Purchase Date
Purchased

Excel 25
xx/xx/xxxx
Excel 100
yy/yy/yyyy
Excel 75
zz/zz/zzzz

As I said, the query to group by type, and count the # of licenses per
each date group is great. How can I derive the date per each group?

Thanks,

BlackFireNova

"Larry Linson" <bo*****@localhost.not> wrote in message news:<tF*******************@nwrddc01.gnilink.net>. ..

I believe you're going to find this easier than you anticipated.

Create a query in the Query Grid, including the Date and some other field
which will always be present. In design view, on the menu, choose View |
Totals. Under the Date, select GroupBy, and under the other field, select
Count.

If your "dates" are actually date and time, then extract the Date using the
DateValue function (in the query itself) and GroupBy the extracted date
value.

Larry Linson
Microsoft Access MVP
"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:40*********************@news.frii.net...

I have an Access 2003 mdb which contains software records. I need to
sort on a particular type of software, and then identify and count how
many copies there are per each group of that type purchased on the same
date.

I have no trouble doing a query to extract the type (say MS Excel 2002,
for example). The trouble is, there could be 50 copies purchased on
date "x", 80 copies purchased on date "y", and 250 copies purchased on
date "z", etc.

Is there any way I can use a function, or is there a code example that
would scan the date field, and count the number of records which exist
for each separate date of purchase?

Any help is appreciated!

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!