Creating multi Column Date Ranges using Now/Date ??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BruceG - 24 Mar 2005 21:41 GMT

Total Newbie ..

At new job, we have an Access97 databse that lists appts. by locations. I am
looking for a way in a query to create columns that will list a result if
there is an appt within the date ranges based on a now formula.

Example Ranges:

Now 3/23/05
Col 1 3/23/05 + 1-2 days (3/24/05 - 3/25/05)
col 2 3/23/05 + 3-4 days (3/26/05 - 3/27/05)
col 3 3/23/05 + 5-6 days (3/28/05 - 3/29/05)

This would be dynamic rolling as it based on current date .

TIA

Bruce

Reply to this Message

Duane Hookom - 25 Mar 2005 03:33 GMT

Probably possible. Does your table have a structure you could share with us?
What would you expect to see in the columns? How does Location relate to
your question?

Signature

Duane Hookom
MS Access MVP

> Total Newbie ..
>
[quoted text clipped - 15 lines]
>
> Bruce

Reply to this Message

BruceG - 25 Mar 2005 15:39 GMT

Basic table structure & field I would be picking are as follows:

Loc Code Cust # Appt Date
Hou 1 3/25/05
Aus 2 3/25/05
Aus 10 3/26/05
Aus 11 3/28/05
hou 21 3/30/05
Hou 30 4/1/05

What my desired result is loc based on Now calc. as follows: Now = 3/25/05.
Count Count Count

Loc appt Range #1 Range #2 Range #3
3/25 - 3/27/05 3/28-30/05 3/31-4/02/05

Hou 1 1 1

Aus 2 1 0

etc...

TIA for any help.

Bruce

> Probably possible. Does your table have a structure you could share with us?
> What would you expect to see in the columns? How does Location relate to
[quoted text clipped - 19 lines]
> >
> > Bruce

Reply to this Message

Duane Hookom - 25 Mar 2005 23:13 GMT

Play with this SQL of a crosstab query:

TRANSFORM Nz(Count([Cust #]),0) AS Expr2
SELECT tblBruceG.[Loc Code]
FROM tblBruceG
GROUP BY tblBruceG.[Loc Code]
PIVOT "Range" & DateDiff("d",Date(),[Appt Date]) Mod 3;

Signature

Duane Hookom
MS Access MVP
--

> Basic table structure & field I would be picking are as follows:
>
[quoted text clipped - 50 lines]
>> >
>> > Bruce

Reply to this Message