How to Crosstab with several periods of time

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hello,

We operate some waste dump site and each entry of trucks making disposal at
our facility is recorded.

Our record structure is like this:

Date Time In Time Out Vehicle No Source Material etc.

My question is:

How to do a crosstab query to show Sources as row headers and different
periods of time such as Week1, Week2, Week3, Week4 as column headers and
counts as the content?

Right now, I'm making different individual queries to get the counts for
separate time periods.


Another question:

My data is like this: each month is stored in separate tables (it's bad
database design, but it's the current situation.)

I sometimes want to count the number of disposal of different materials over
different months. Now I have to first UNION ALL the tables selecting only
the material column, and then do count on this query result. Any way to do
it in one query?

Thanks.

Herbert
 
I would definitely place all records in a single table. I would use a text
box on a form to store the report ending date/week. Then use a column
heading expression of:
ColHead:"Week" & DateDiff("ww",[YourDate], Forms!frmA!txtEndDate)
Add "Week1","Week2",..."Week5" in the Column Headings property.
You will also need to select Query|Parameters and enter
Forms!frmA!txtEndDate Date/Time
 
I've found out the answer exists actually right within the crosstab query
wizard. It doesn't directly have the option of displaying the data in weeks,
but it has options of displaying the data in months. So it's easy to just
adapt it into weeks.

Thanks.

Herbert

Duane Hookom said:
I would definitely place all records in a single table. I would use a text
box on a form to store the report ending date/week. Then use a column
heading expression of:
ColHead:"Week" & DateDiff("ww",[YourDate], Forms!frmA!txtEndDate)
Add "Week1","Week2",..."Week5" in the Column Headings property.
You will also need to select Query|Parameters and enter
Forms!frmA!txtEndDate Date/Time

--
Duane Hookom
MS Access MVP


Herbert Chan said:
Hello,

We operate some waste dump site and each entry of trucks making disposal at
our facility is recorded.

Our record structure is like this:

Date Time In Time Out Vehicle No Source Material etc.

My question is:

How to do a crosstab query to show Sources as row headers and different
periods of time such as Week1, Week2, Week3, Week4 as column headers and
counts as the content?

Right now, I'm making different individual queries to get the counts for
separate time periods.


Another question:

My data is like this: each month is stored in separate tables (it's bad
database design, but it's the current situation.)

I sometimes want to count the number of disposal of different materials over
different months. Now I have to first UNION ALL the tables selecting only
the material column, and then do count on this query result. Any way to do
it in one query?

Thanks.

Herbert
Click to expand...
Click to expand...
 
Back
Top