Solved

Query criteria:  Count number of distinct months in date field

Posted on 2016-11-05
38
35 Views
Last Modified: 2016-11-07
I'm trying to use query criteria to count the number of distinct months (including distinct years) in a date field stored as a short date in the underlying table.  I think I have to use Select, Count, and Distinct, but I can't figure out how to extract the month and year from the date field.  The statements I've tried throw syntax errors or tell me I can't do aggregate functions on that field.

This gives a syntax error:
=select count(*) from (select distinct(DateSerial(Year(*),Month(*),Day()));

As does this:
=select count(*) from (select distinct(DateSerial(Year([AsnmDate]),Month(AsnmDate),Day()));

Thanks in advance for any help.
0
Comment
Question by:Cindy Aitken
  • 17
  • 10
  • 6
  • +1
38 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Use this !!

SELECT COUNT(*) AS DistinctYearCount
FROM
(SELECT DISTINCT Year(DateSerial) FROM yourTableName) AS T;

Open in new window

0
 

Author Comment

by:Cindy Aitken
Comment Utility
Thanks for your reply, Pawan.

Here's my edited version of your suggested code, which is still giving a syntax error:

=SELECT COUNT(*) AS DistinctYearCount
FROM (SELECT DISTINCT Year(DateSerial) FROM tblAssignments.[AsnmDate]) AS T;

I tried it with parentheses around the table.field, but still get the syntax error.

I need to count the distinct months in the date field.  The distinct year is necessary because January 2015 is not the same as January 2016.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
What is the column Name - DateSerial correct ?

What is this - [AsnmDate]?

Try below- If the column is AsnmDate then for bold(DateSerial) use AsnmDate

SELECT COUNT(*) AS DistinctYearCount
FROM (SELECT DISTINCT Year(DateSerial) FROM tblAssignments) AS T;
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Hi Cindy,

Is it done?
0
 

Author Comment

by:Cindy Aitken
Comment Utility
Pawan,

The table is tblAssignments and the field is AsnmDate.  I'm still getting syntax errors:

SELECT COUNT(*) AS DistinctYearCount
FROM (SELECT DISTINCT Year(AsnmDate) FROM tblAssignments) AS T;
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Are you using MSACCESS ? Which Version ?

Can you provide me the syntax error you are getting ? Screen shot / error message ?
0
 

Author Comment

by:Cindy Aitken
Comment Utility
I'm using MS Access 2007.  This is the error message:

"The syntax of the subquery in this expression is incorrect.  Check the subquery's syntax and enclose the subquery in parentheses."
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Ok try..

SELECT COUNT(AsnmDate) AS DistinctYearCount
FROM (SELECT DISTINCT Year(AsnmDate) FROM tblAssignments) AS T

Also try below-

SELECT Count(Distinct YEAR(AsnmDate)) AS DistinctYearCount FROM tblAssignments

Pls let me know in any case - fail/Success
0
 

Author Comment

by:Cindy Aitken
Comment Utility
Pawan,

I tried both and got that same syntax error both times. :(  I really appreciate your help with this!
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Complete Table schema pls ?
0
 

Author Comment

by:Cindy Aitken
Comment Utility
I'm sorry, I'm not sure what a table schema is.  Please explain.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Table Name and column Names ? Is this your homework  ?
0
 

Author Comment

by:Cindy Aitken
Comment Utility
No, this is a database I'm working on for my business, trying to automate some paperwork to reduce human error.  

The table name is tblAssignments.  There are a lot of fields, but the ones that matter here are ClAcctNum (client account number, text field, lookup column from tblClients) and AsnmDate (the date of the assigned job, short date field).
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Ok, First try this..and let me know..

SELECT DISTINCT Year(AsnmDate) AS DistinctYears FROM tblAssignments

try2..

SELECT DISTINCT Year(AsnmDate) AS DistinctYears FROM tblAssignments;
0
 

Author Comment

by:Cindy Aitken
Comment Utility
Pawan,

Thank you again for your help.  Unfortunately, the syntax error still comes up with both those statements.  Am I right that the only difference between the two is the semi-colon at the end?
0
 

Author Comment

by:Cindy Aitken
Comment Utility
I will have to put this aside for a few hours to get some other things done.  Thanks in advance for any suggestions!  I will try them as soon as I finish my other chores.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Yes only semicolon. try very basic.

1.

SELECT [AsnmDate] FROM tblAssignments

2.

SELECT AsnmDate FROM tblAssignments
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Try to upload a sample database demonstrating the issue.
count the number of distinct months (including distinct years) in a date field
What does that mean. Give example of few records and expected output.
0
 

Author Comment

by:Cindy Aitken
Comment Utility
hnasr,

This is an example of the data:

ClAcctNum     AsnmDate
CH0002           4/12/2015
CH0002           4/06/2016
CH0003           8/3/2016
CH0003           8/10/2016
CH0003           10/30/2016

The invoice number, which is what I'm trying to create here, will be a combination of client account number and number of months in which the client received services, like this:  CH0003-2.  Notice there are two distinct months for each client in the above example.

It'll be easy to concatenate the client account number and number of months to create the invoice number, once I have the month count, but the month count is proving difficult.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Cindy Aitken
Comment Utility
I forgot to mention that the expected output is a query with two columns -- the client account number and the number of months in which that client received services.  

Thanks in advance!
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
monthCount= DATEDIFF ("m", d1, d2)

d1 is the minimum date for each client
d2 is the maximum date for each client

More help? A demo database is needed.
0
 

Author Comment

by:Cindy Aitken
Comment Utility
hnasr,

Thanks for your suggestion, but finding the minimum date for each client would probably take more code than this is worth.  I appreciate that it would help to look at my database, but extracting the pertinent data to a demo database didn't work out very well.  The client account number is a lookup column, so it only shows PK values, and the date field has an applied format (mm/dd/yy) which caused errors in the demo.  I was unable to save the demo table due to the errors.  

Sorry, I'm not that experienced with Access.  That's why I entered the sample data above.  Uploading my entire database with confidential records from my company wouldn't be wise, but I don't know how to put a demo together for you.

Really, it's just a text field with the client account number (like CH0003) and a date/time field with a short date input mask and format of mm/dd/yy.  I was hoping to avoid writing a lot of code for this, but there may be no way around it.  It appears a Select statement isn't the right tool for the job.

I'll leave this question open in case someone has a flash of inspiration...
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this query

Select A.ClAcctNum, Count(A.Mnth) As CountOfMonths
From
(
SELECT tblAssignments.ClAcctNum, Month([AsnmDate]) AS Mnth
FROM tblAssignments
GROUP BY tblAssignments.ClAcctNum, Month([AsnmDate])
) As A
Group By A.ClAcctNum
0
 

Author Comment

by:Cindy Aitken
Comment Utility
Thank you, Rey.  I tried it exactly as you wrote it, but it's throwing the same syntax error as before.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
do  a compact and repair of your db
0
 

Author Comment

by:Cindy Aitken
Comment Utility
I did the compact and repair, closed and reopened the database, and tried the query again with your code exactly as written.  Still getting the same error. :(
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
look at your references from the vba window if you have MISSING reference, uncheck and look for the correct reference

do a debug>Compile and correct any  errors.

try the query again ( the query runs here with no error)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
SELECT tbl.f1, DateDiff("m",d1,d2) AS monthCount
FROM (SELECT a.f1, Min(a.dd) AS d1, Max(a.[dd]) AS d2
FROM a GROUP BY a.f1)  AS tbl;

Replace with your object names:
a=tableName
fi = client
dd=date
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Here a quick replacement of names:
SELECT tbl.ClAcctNum, DateDiff("m",d1,d2) AS monthCount
FROM (SELECT tableName.ClAcctNum, Min(tableName.AsnmDate) AS d1, Max(tableName.[AsnmDate]) AS d2
FROM tableName GROUP BY tableName.AsnmDate)  AS tbl;

Check and feedback.
0
 

Author Comment

by:Cindy Aitken
Comment Utility
Thanks, Rey and hnasr.

Rey, I'm not sure what you mean about the VBA window.  I'm trying to add the code to the criteria of a query in the query design window and have been building the statements in the Expression Builder window.  Is there another place I can find any missing references?  I'll try putting the code into the VBA code editor and see if it runs from there.  Will report back shortly...

hnasr, I'm still getting the syntax error, same as before.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
@cindy

copy the sql statement that I posted
create a new query, do NOT select any  table
go to SQL view of the query and paste the query I posted.

is this how you are dong your query?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
see this sample db, see query Q1
db_28981260.accdb
0
 

Author Comment

by:Cindy Aitken
Comment Utility
OMG!  THANK YOU, REY!!!!  Yes, it works perfectly when I paste your code into the SQL view.  Apparently, my mistake was trying to enter it as query criteria in the query design window.  Please let me know where to send a big box of chocolates. :)

hnasr, thank you also for your efforts.  The fault was entirely mine for not knowing I couldn't do this in the query design window.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Now can you try mine query also :) below one.

SELECT COUNT(*) AS DistinctYearCount
FROM (SELECT DISTINCT Year(AsnmDate) FROM tblAssignments) AS T;
1
 

Author Closing Comment

by:Cindy Aitken
Comment Utility
I'm so grateful to have access to experienced database managers here!  What would we noobs do without you???!!  Many thanks!
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Check my understanding!
monthCount.accdb
1
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Are you sure you got the right answer?
f1      dd
2      12/04/2015
2      06/04/2016
3      03/08/2016
3      10/08/2016
3      30/10/2016
Query1
SELECT tbl.f1, DateDiff("m",d1,d2) AS monthCount
FROM (SELECT a.f1, Min(a.dd) AS d1, Max(a.[dd]) AS d2 FROM a GROUP BY a.f1)  AS tbl
ORDER BY tbl.f1;

f1      monthCount
2      12
3      2

Query2_accepted
SELECT tbl.f1, Count(A.f1) AS CountOfMonths
FROM (SELECT a.f1, Month([dd]) AS Mnth FROM a GROUP BY a.f1, a.dd)  AS tbl
GROUP BY tbl.A.f1;

f1      CountOfMonths
2      2
3      3

Other query in thread tried:
SELECT COUNT(*) AS DistinctYearCount
FROM (SELECT DISTINCT Year(dd) FROM a) AS T;
DistinctYearCount
2
1
 

Author Comment

by:Cindy Aitken
Comment Utility
hnasr, you're right, the other one doesn't count distinct years.  I neglected to check that.  

Pawan, thank you for the SQL to get the distinct year!  

I'll see if I can manage to put the statements together without problems and will report back.  Many thanks for all the help!

Much later...  No, I can't figure out how to get the month count when the years are different.  I tried combining Pawan's and Rey's statements, but it's not working out no matter how I manipulate it.  Since I've already chosen a solution for this question, I think I'm stuck writing VBA code to work it out.  Too bad, because a query would have been so much tighter.  Such is the life of a noob. :(
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now