Creating multi Column Date Ranges using Now/Date ??

[Guest]

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

 

[Duane Hookom]

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?

 

[Guest]

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

 

[Duane Hookom]

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;