473,289 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Counting Records by Date where there are several groups of dates

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!
Nov 12 '05 #1
2 2275
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!

Nov 12 '05 #2
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!

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Bambero | last post by:
Hello all Problem like in subject. There is no problem when I want to count days between two dates. Problem is when I want to count years becouse of leap years. For ex. between 2002-11-19...
6
by: edwardfredriks | last post by:
I'm looking for a script that, instead of counting down, can "count up" from a given date. So the output should be something like "(xx) days since (date/event)" or "(date/event) was (xx) days ago"....
1
by: Steve Claflin | last post by:
I have a database with a moderate number of records in several tables (the biggest table at the moment is about 800 records). In development it got moved between 2K and XP repeatedly. Several...
2
by: Jason | last post by:
I have a table of 650,000 records associated to contracts. Some of the contracts have multiple records but each contract only has one Active Record (there might be several inactive records). ...
1
by: Gee | last post by:
Hi Folks, I have a small database that contains test dates. We can only sit 9 people per test. I need to count the dates that are the same and NOT go over 9. I read MSAcess help on the DCount...
14
by: jpr | last post by:
Friends, I have a form with four fields, date1, date2, date3 and date4. All these have all a mm/dd/yyyy format and have their source to a table. I need to add an unbound control (I will name...
1
by: RussCRM | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for...
8
by: Dr Al | last post by:
I have a table with four date fields, some of which may not be filled in based on our data entry needs. I have a criteria set as <date()-180 which is supposed to pull dates older than 180 days ago....
5
by: sara | last post by:
Hi - I have had this problem MANY times and I just don't think I have the best solution. I am running a parameter query to retrieve records where work was completed between 2 dates. The...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.