Re: Counting days in a crosstab query

You have to tell us how you want your table to fit into the result. Your
question was to count the number of days between two dates. Then you added
that you wanted these grouped by month. That is what you have.

Now you have mentioned a table and two fields. You need to provide several
sample records and how your friend would like to see a result displayed.

--
Duane Hookom
MS Access MVP
--

"Scooter" <Scooter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:43C0469B-6ED9-48D9-976E-82DDC8658E0D@xxxxxxxxxxxxxxxx
>I appreciate your patience with me.
>
> I currently have a table called applications and the two fields are
> transdate and reldate.
>
> How would those two fields fir into the sql statement you created. I
> chose
> to use two fields as thre will be many entries and all will have different
> trans and rel dates.
>
> "Duane Hookom" wrote:
>
>> 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: Scroll through records and get match
    ... Doug Steele, Microsoft Access MVP ... "User-defined type not defined" at Dim dbs As DAO.Database. ... that I posted is a select query. ... cannot use it to open an SQL statement that you generate via code. ...
    (microsoft.public.access.modulesdaovba)
  • 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: 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)