Re: Counting days in a crosstab query

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: Counting days in a crosstab query
    ... > How would those two fields fir into the sql statement you created. ... >> The SQL view of the query should be: ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: Counting days in a crosstab query
    ... FROM tblDates ... which type of query are you using? ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: Another sequential query field problem... are you out the Duan
    ... You seem to have seen some solutions that involve a subquery. ... attempted in terms of your SQL statement and results? ... >> Duane Hookom ... >>> What I need to do is creat a field in my query that creates a sequence ...
    (microsoft.public.access.queries)
  • Re: query
    ... You take the sql statement that I provided in my first post and enter it ... > I'm sorry i don't understand what a union query is. ... >> Duane Hookom ... >>> Event Date Time Slot Reception Hall Quidi Vidi Room Gibbett Hill Room ...
    (microsoft.public.access.queries)
  • Re: How to summarize records on one page?
    ... The query that I am suggesting doesn't have a join between the query and ... Duane Hookom ... MS Access MVP ... I have to create a link between the tblDates and the query so I ...
    (microsoft.public.access.reports)