Query based on Date

  • Thread starter Thread starter Marvin Ettlin
  • Start date Start date
M

Marvin Ettlin

Currently the Business I work for runs cbts for on-line
training. The day you complete the cbt it records a date
in their data base, which they post in an excel
spreadsheet. I take that spread sheet and import it into
Access. I want to run a query that will total those
fields that have dates in them. When I set up my query,
it returns the number of instances that date is used per
person... but I don't care what date it is, just that
there is a date in the field so when I do my training
matrix, it will count the number of dates and divide that
number by the number of required classes... I have the
queiry set up, but just need to know how to combine those
dates, because if I use <> " " or "##/##/####" I get the
dates and a total of how many were completed on that
day. Any suggestions?????

Marv
 
I think you would need to use GROUP BY.

Select TestDate, Count(TestID) from tblTests
GROUP By TestDate

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I do sort my column by the GROUP BY... and your right, it
does work. The spreadsheet I take this information from
gives me a date if you complete your training
requirement. The sort gives me 5 people completed this
training from this divsion on 1/28/2004 and then also 5
people from the same divsion completed the training on
2/5/2004 - what I am looking for is some way to say 10
people completed the training - which date they completed
it on is really unimportant to me. Is there some way to
even write an expression or some other formula to combine
the two totals?

Marv

EXAMPLE:
Division POSH CBT Total
software 1/22/2004 3
software 1/26/2004 6
hardware 1/22/2004 8
hardware 1/26/2004 4
 
SELECT Division, Count(DateField) as NumberTests
FROM SomeTable
WHERE DateField Between #1/1/03# AND #1/31/03#
GROUP BY Division
 
Back
Top