Re: Counting days in a crosstab query

This is a totals query. I noticed now that I missed a "]" following Thedate.
The SQL view of the query should be:

SELECT Month([TheDate]) as Mth, Count(*) as NumOf
FROM tblDates
WHERE TheDate Between #1/15/2005# and #3/23/2005#
GROUP BY Month([TheDate]);

--
Duane Hookom
MS Access MVP


"Scooter" <Scooter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9552890-FEEF-466E-A9CF-D09FB210CC7C@xxxxxxxxxxxxxxxx
> Please forgive my lack of knowledge, are you using a SQL statement to
> create
> that query? If it is, which type of query are you using?
>
>
>
> "Duane Hookom" wrote:
>
>> The simple method is to create a table of all dates:
>> tblDates
>> ===========
>> TheDate date/time
>>
>> Then create a query like:
>> SELECT Month([TheDate) as Mth, Count(*) as NumOf
>> FROM tblDates
>> WHERE TheDate Between #1/15/2005# and #3/23/2005#
>> GROUP BY Month([TheDate);
>>
>> --
>> Duane Hookom
>> MS Access MVP
>> --
>>
>> "Scooter" <Scooter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:0701E61F-33C7-4EEB-AAAE-7641FB66853D@xxxxxxxxxxxxxxxx
>> >I am writing this to assist a friend in counting the number of days per
>> >month
>> > between the start date and the end date. Due to the nature of the
>> > report,
>> > the total number of days between wont work. I need the number of days
>> > in
>> > Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a
>> > crosstab query might break them down easier.
>> >
>> > - Scotty
>> >
>> > "Duane Hookom" wrote:
>> >
>> >> Your question isn't real clear. The number of days betwee Jan 15 and
>> >> March
>> >> 23 is DateDiff("d",#1/15/2005#, #3/23/2005#).
>> >>
>> >> I'm not sure what any of this has to do with a crosstab query.
>> >>
>> >> --
>> >> Duane Hookom
>> >> MS Access MVP
>> >> --
>> >>
>> >> "Scooter" <Scooter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:7E66C4E0-BEB8-4C71-AD05-452717F64285@xxxxxxxxxxxxxxxx
>> >> >I need to count the number of days each month and quarter between
>> >> >two
>> >> >dates.
>> >> > For example Jan 15 and March 23. I need to count the total days for
>> >> > Jan,
>> >> > Feb and March. I'm having a terrible time trying to figure to the
>> >> > calculations.
>> >> >
>> >> > Any ideas?
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Beginner query help please
    ... MS Access MVP ... Duane Hookom wrote: ... I've noticed that if you select all the query output (in this ... Controls in most query solutions would not be ignored. ...
    (microsoft.public.access.queries)
  • Re: Beginner query help please
    ... MS Access MVP ... I've noticed that if you select all the query output (in this case ... Duane Hookom wrote: ... Controls in most query solutions would not be ignored. ...
    (microsoft.public.access.queries)
  • Re: Tricky query question
    ... Microsoft Access MVP ... "Duane Hookom" wrote: ... dynamic monthly crosstab report solution found at ... July data minus June data) in a crosstab query? ...
    (microsoft.public.access.queries)
  • Re: Crosstab Report
    ... MS Access MVP ... I try to write the report my headings/totals crash. ... "Duane Hookom" wrote: ... some of the information you posted to the query and found it very ...
    (microsoft.public.access.reports)
  • Re: Formula will not work with mail merge
    ... There is code that does this in the Query By Form ... > How do I push the records to a separate Word Merge format file? ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.queries)
Loading