Crosstab query with date problem

  • Thread starter Thread starter John Viescas
  • Start date Start date
J

John Viescas

You can calculate "weeks ago" like this:

WeeksAgo: CLng(Date() - [DateSent]) \ 7

You can then use that expression as a column or row heading in a crosstab.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John's expression can be used in a query. You can also use
WeeksAgo: DateDiff("ww", [DateSent], Date())
To see examples of crosstab reports, check the download at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

--
Duane Hookom
MS Access MVP


Some Guy said:
Yeah but can I do it directly in my SQL query? Your example uses VBA (the
cast to long I mean)... does access allow VBA mixed with SQL in queries?

If so could you please show me an example...? Otherwise are you suggesting I
preprocess the data and then use those results to build the final query?

I haven't used Access for many years (prefer proper SQL Server environment
with VB, VB.Net or C# over Access) which is why I am not sure how to
approach this. I've been stuck with the job of expanding a poorly designed
and clunky old Access database. Personally I'd rather rewrite using .Net and
SQL or MSDE but don't have that luxury right now. So, I have to do it the
hard way (hard because my knowledge of Access is very outdated).

Cheers for the info


John Viescas said:
You can calculate "weeks ago" like this:

WeeksAgo: CLng(Date() - [DateSent]) \ 7

You can then use that expression as a column or row heading in a crosstab.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Some Guy said:
Hi there,

I have a table with these fields:

Location, DateSent, Case Number

This records case files that have been sent to a location and the date sent.
I am trying to do a report showing case files sent a certain number of weeks
ago... kind of like the 30 day, 60 day, 90 day overdue section on some
statements.

I want to create a crosstab (if poss but not necessary) query for a report
which does the following:

Lists Locations (if crosstab as column headings)

List CaseNumber (if crosstab as the column value)

Lists DateSent (if crosstab as row headers) BUT not as the actual date...
instead I want rows showing 1 week, 3 weeks, 5 weeks, 7 weeks, and 9
weeks...

Any idea how I can achieve this? I could do this in an SQL stored procedure
in a snap but I think Access will require me to do it all in the SELECT
statement. Buggered if I can figure out how though...

The format for the crosstab query I am going for is something like:

Location A Location B Location C
1 week Case1 Case2 Case3
3 weeks
5 weeks
...

Failing that I'd be happy with:

Location A
1 week Case1
3 weeks Case2
Case3
5 weeks Case 4
LocationB
1 week etc etc etc

I hope this formats ok when you guys view it....



Cheers in advance for any suggestions...
Click to expand...
Click to expand...
Click to expand...
 
I wouldn't have posted the suggestion if you couldn't use it in an Access
query. You already said you're thinking of using a Crosstab - something
unique to Access, so I assumed you want to use the Access environment -
which includes the ability to use almost all of the VB functions. The JET
expression service has hooks to all the VB functions, so you can use any of
the convert or date/time functions in an Access query. Access doesn't
support the ANSI Standard CAST or SQL Server Convert - but it gets the
equivalent functionality with VB. I expect your SQL will look something
like:

TRANSFORM First([CaseNumber])
SELECT Location
FROM MyTable
GROUP BY Location
PIVOT CLng(Date() - [DateSent]) \ 7

Or

SELECT Location, CaseNumber, (CLng(Date() - [DateSent]) \ 7) As WeeksAgo
FROM MyTable
ORDER BY Location, CLng(Date() - [DateSent]) \ 7

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Some Guy said:
Yeah but can I do it directly in my SQL query? Your example uses VBA (the
cast to long I mean)... does access allow VBA mixed with SQL in queries?

If so could you please show me an example...? Otherwise are you suggesting I
preprocess the data and then use those results to build the final query?

I haven't used Access for many years (prefer proper SQL Server environment
with VB, VB.Net or C# over Access) which is why I am not sure how to
approach this. I've been stuck with the job of expanding a poorly designed
and clunky old Access database. Personally I'd rather rewrite using .Net and
SQL or MSDE but don't have that luxury right now. So, I have to do it the
hard way (hard because my knowledge of Access is very outdated).

Cheers for the info


John Viescas said:
You can calculate "weeks ago" like this:

WeeksAgo: CLng(Date() - [DateSent]) \ 7

You can then use that expression as a column or row heading in a crosstab.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Some Guy said:
Hi there,

I have a table with these fields:

Location, DateSent, Case Number

This records case files that have been sent to a location and the date sent.
I am trying to do a report showing case files sent a certain number of weeks
ago... kind of like the 30 day, 60 day, 90 day overdue section on some
statements.

I want to create a crosstab (if poss but not necessary) query for a report
which does the following:

Lists Locations (if crosstab as column headings)

List CaseNumber (if crosstab as the column value)

Lists DateSent (if crosstab as row headers) BUT not as the actual date...
instead I want rows showing 1 week, 3 weeks, 5 weeks, 7 weeks, and 9
weeks...

Any idea how I can achieve this? I could do this in an SQL stored procedure
in a snap but I think Access will require me to do it all in the SELECT
statement. Buggered if I can figure out how though...

The format for the crosstab query I am going for is something like:

Location A Location B Location C
1 week Case1 Case2 Case3
3 weeks
5 weeks
...

Failing that I'd be happy with:

Location A
1 week Case1
3 weeks Case2
Case3
5 weeks Case 4
LocationB
1 week etc etc etc

I hope this formats ok when you guys view it....



Cheers in advance for any suggestions...
Click to expand...
Click to expand...
Click to expand...
 
Hi there,

I have a table with these fields:

Location, DateSent, Case Number

This records case files that have been sent to a location and the date sent.
I am trying to do a report showing case files sent a certain number of weeks
ago... kind of like the 30 day, 60 day, 90 day overdue section on some
statements.

I want to create a crosstab (if poss but not necessary) query for a report
which does the following:

Lists Locations (if crosstab as column headings)

List CaseNumber (if crosstab as the column value)

Lists DateSent (if crosstab as row headers) BUT not as the actual date...
instead I want rows showing 1 week, 3 weeks, 5 weeks, 7 weeks, and 9
weeks...

Any idea how I can achieve this? I could do this in an SQL stored procedure
in a snap but I think Access will require me to do it all in the SELECT
statement. Buggered if I can figure out how though...

The format for the crosstab query I am going for is something like:

Location A Location B Location C
1 week Case1 Case2 Case3
3 weeks
5 weeks
....

Failing that I'd be happy with:

Location A
1 week Case1
3 weeks Case2
Case3
5 weeks Case 4
LocationB
1 week etc etc etc

I hope this formats ok when you guys view it....



Cheers in advance for any suggestions...
 
Yeah but can I do it directly in my SQL query? Your example uses VBA (the
cast to long I mean)... does access allow VBA mixed with SQL in queries?

If so could you please show me an example...? Otherwise are you suggesting I
preprocess the data and then use those results to build the final query?

I haven't used Access for many years (prefer proper SQL Server environment
with VB, VB.Net or C# over Access) which is why I am not sure how to
approach this. I've been stuck with the job of expanding a poorly designed
and clunky old Access database. Personally I'd rather rewrite using .Net and
SQL or MSDE but don't have that luxury right now. So, I have to do it the
hard way (hard because my knowledge of Access is very outdated).

Cheers for the info


John Viescas said:
You can calculate "weeks ago" like this:

WeeksAgo: CLng(Date() - [DateSent]) \ 7

You can then use that expression as a column or row heading in a crosstab.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Some Guy said:
Hi there,

I have a table with these fields:

Location, DateSent, Case Number

This records case files that have been sent to a location and the date sent.
I am trying to do a report showing case files sent a certain number of weeks
ago... kind of like the 30 day, 60 day, 90 day overdue section on some
statements.

I want to create a crosstab (if poss but not necessary) query for a report
which does the following:

Lists Locations (if crosstab as column headings)

List CaseNumber (if crosstab as the column value)

Lists DateSent (if crosstab as row headers) BUT not as the actual date...
instead I want rows showing 1 week, 3 weeks, 5 weeks, 7 weeks, and 9
weeks...

Any idea how I can achieve this? I could do this in an SQL stored procedure
in a snap but I think Access will require me to do it all in the SELECT
statement. Buggered if I can figure out how though...

The format for the crosstab query I am going for is something like:

Location A Location B Location C
1 week Case1 Case2 Case3
3 weeks
5 weeks
...

Failing that I'd be happy with:

Location A
1 week Case1
3 weeks Case2
Case3
5 weeks Case 4
LocationB
1 week etc etc etc

I hope this formats ok when you guys view it....



Cheers in advance for any suggestions...
Click to expand...
Click to expand...
 
Back
Top