Is Crosstab query the way?

[Guest]

Hello wise ones,
I am using Access 2003, but working in an Access 2000 database.
I am trying to create a subform that will show a delivery schedule for the
next 7 days.
Relevant Data in table tbl_Pickups is [Date Scheduled], Truck_Name and Item.

The twist is that the [Date Scheduled] and Truck_Name are not constant and
will change everyday. I would also like to be able to requery this output
every 5 min or so.

Desired Output if viewed 4/10 (for just 3 days.. extend to 7 days). The
count if items for each Truck is displayed in ( ).

4/10 4/11 4/12
Truck1 (2) Truck1 (3) Truck2 (1)
Truck2 (1) Truck2 (5) Truck4 (3)
Truck3 (4) Truck3 (2)


Any direction or hints you can provide is greatly appreciated.

 

[Michel Walsh]

Hi,


Well, maybe with more time, but in a "rush", I would use more than one
query. I assume your table

Deliveries
id, Date, Truck ' field name


Your final result miss a column, the "group" which, is nothing else that the
"line number" , or rank, into which we display the truck, under a given
column.

So,

SELECT DISTINCT truck, date FROM deliveries


as SavedQuery1, in order to simplify the typing for the second query:



SELECT a.Date, a.Truck, COUNT(*) As rank
FROM savedQuery1 As a INNER JOIN savedQuery1 As b
ON a.date=b.date and a.truck >= b.truck


as savedQuery2.


To save some computing time, and break the statements into smaller ones, I
also use:


SELECT truck, date, truck & " (" & COUNT(*) & ")" as theShow
FROM deliveries
GROUP BY truck, date


as savedQuery3, which produces "what" is to be displayed, in a cell, by our
final crosstab.


We are ready for the pre-assemblage:

SELECT Q2.date, Q2.rank, Q3.theShow
FROM savedQuery2 As Q2 INNER JOIN savedQuery3 AS Q3
ON Q2.truck=Q3.truck AND Q2.date=Q3.date


as useThis saved query.

eh.. and yes, use a crosstab on useThis query, with the help of the wizard.
Rank produces the lines (groups), date produces the pivot (new columns),
LAST(theShow) produces the content of each cell.



You were right about the use of a crosstab (to create NEW columns OUT of
data, the dates), were just missing the "group", which were "hidden" by the
fact they were nothing else than a "simple line" on which we write the
information, which can be represented as the "rank" of ordered data (per
date), which has been computed in savedQuery2.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michael,
WOW, Thank you. Everything looks great, with one small glitch. Can you
look at Query2? On a side note, I had a minor difference in field names, but
hopefully that is not causing the error.

Query1 works fine
Query2 errors out
Query3 works fine

Error I receive on Query2 is "You tried to execute a query that does not
include the specified expression 'date' as part of an aggregate function."

Unfortunately, this is so far beyond me that I can't tweak your code to get
it to work.

Hi,


Well, maybe with more time, but in a "rush", I would use more than one
query. I assume your table

Deliveries
id, Date, Truck ' field name


Your final result miss a column, the "group" which, is nothing else that the
"line number" , or rank, into which we display the truck, under a given
column.

So,

SELECT DISTINCT truck, date FROM deliveries


as SavedQuery1, in order to simplify the typing for the second query:



SELECT a.Date, a.Truck, COUNT(*) As rank
FROM savedQuery1 As a INNER JOIN savedQuery1 As b
ON a.date=b.date and a.truck >= b.truck


as savedQuery2.


To save some computing time, and break the statements into smaller ones, I
also use:


SELECT truck, date, truck & " (" & COUNT(*) & ")" as theShow
FROM deliveries
GROUP BY truck, date


as savedQuery3, which produces "what" is to be displayed, in a cell, by our
final crosstab.


We are ready for the pre-assemblage:

SELECT Q2.date, Q2.rank, Q3.theShow
FROM savedQuery2 As Q2 INNER JOIN savedQuery3 AS Q3
ON Q2.truck=Q3.truck AND Q2.date=Q3.date


as useThis saved query.

eh.. and yes, use a crosstab on useThis query, with the help of the wizard.
Rank produces the lines (groups), date produces the pivot (new columns),
LAST(theShow) produces the content of each cell.



You were right about the use of a crosstab (to create NEW columns OUT of
data, the dates), were just missing the "group", which were "hidden" by the
fact they were nothing else than a "simple line" on which we write the
information, which can be represented as the "rank" of ordered data (per
date), which has been computed in savedQuery2.




Hoping it may help,
Vanderghast, Access MVP

Hello wise ones,
I am using Access 2003, but working in an Access 2000 database.
I am trying to create a subform that will show a delivery schedule for the
next 7 days.
Relevant Data in table tbl_Pickups is [Date Scheduled], Truck_Name and
Item.

The twist is that the [Date Scheduled] and Truck_Name are not constant and
will change everyday. I would also like to be able to requery this output
every 5 min or so.

Desired Output if viewed 4/10 (for just 3 days.. extend to 7 days). The
count if items for each Truck is displayed in ( ).

4/10 4/11 4/12
Truck1 (2) Truck1 (3) Truck2 (1)
Truck2 (1) Truck2 (5) Truck4 (3)
Truck3 (4) Truck3 (2)


Any direction or hints you can provide is greatly appreciated.

 

[Michel Walsh]

Hi,


very true, missing the GROUP BY clause completely:

SELECT a.Date, a.Truck, COUNT(*) As rank
FROM savedQuery1 As a INNER JOIN savedQuery1 As b
ON a.date=b.date and a.truck >= b.truck
GROUP BY a.Date, a.Truck



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michael,
Works perfectly, thank you.

How do I display this info as a subform? I was thinking about a continuous
form based on the crosstab query from the useThis saved query , but the field
names are the dates in the query. Since the dates will change daily, this
approach isn't working for me.

Any thoughts about how to get this data in my form?

 

[Michel Walsh]

Hi,


You can


PIVOT "D" & DateDiff("d" , Date(), myquery.date)


The DateDiff produces values such as 0, 1, 2, 3, ... and the expression
produces constant fields D0, D1, D2, D3, .... In fact, you can obliged
some of these fields to be present (even if that means they are all empty)
using:



PIVOT "D" & DateDiff("d" , Date(), myquery.date) IN( "D0", "D1", "D2",
"D3", "D4")


but in this case, if there is data for D5, as example, it won't be in the
result.



Having constants fields name, you can easily bind them inside a form, to the
form's controls.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Unfortunately, this doesn't make sense to me.
I tried to paste this into an SQL statement as a query, no such luck.

The Usethis query works well using the Form's Pivot Table Wizard, but the
style doesn't match my application. I would rather use your approach of
creating constants of D0, D1, D2, D3, D4, D5... then I can use these
constants in a continuous form. But I don't know where to start with your
statements.

If you have time, can you help me understand what I should do to create the
constants?

 

[Michel Walsh]

Hi,


If you edit the crosstab query produced by the wizard, in SQL view, you
should spot, near the end of the SQL statement, the word PIVOT followed by
some expression, likely something like

.... PIVOT myQuery.Date;

you change it to

.... PIVOT "D" & DateDiff("d" , Date(), myQuery.Date) IN( "D0", "D1", "D2",
"D3", "D4")


Indeed, what follows PIVOT is what will create the new fields NAME. With

PIVOT myQuery.Date


the NEW fields created by the crosstab query will get the ... date ... as
name. But we can use an expression, we are not limited to bare field name.
So

.... PIVOT "D" & DateDiff("d" , Date(), myQuery.Date)


should now created new fields name like D0, D1, D2, .... the number
following the letter D coming from the DateDiff evaluation.


The IN( ) list that follows is not really fully logical, but an extension to
the logic: it forces the new fields name to be in the IN-list (which is the
logical part) but ALSO, it will create a new field for each and every
element of that list, so, the new fields will ALWAYS be present, which marks
your crosstab as a "constant crosstab", a crosstab what supplies a well
known set of fields, with constant names, wonderful to produce forms, or
graphs, as examples. The IN-list also appears in the crosstab query property
under the property name "Column Headings", so, in theory, you are not
obliged to edit the SQL statement directly, if your pivot expression is
already in the grid and if you have the query properties sheet visible.
Overall, I prefer to type directly those kinds of modifications directly in
the SQL view, that is a matter of personal preference



Hoping it may help,
Vanderghast, Access MVP