To Crosstab or not to crosstab

O

OMS

Hi,

I am trying to make a query where columns are months and rows are periods of
time during the day. Expr1,2,3, etc. are table column headers with that data
I want. My problem is how do I get the value to properly populate their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
 
O

OMS

Thanks but that didn't answer the question. By the way this is what I'm
using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


Jeff C said:
See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


OMS said:
Hi,

I am trying to make a query where columns are months and rows are periods
of
time during the day. Expr1,2,3, etc. are table column headers with that
data
I want. My problem is how do I get the value to properly populate their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
 
D

Duane Hookom

Is your TRANSACTION_DATE a date/time field or is it text. When you use
Mid(), it suggests text. You shouldn't need a big, ugly Switch() function.
Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1, Expr2,...?

--
Duane Hookom
MS Access MVP


OMS said:
Thanks but that didn't answer the question. By the way this is what I'm
using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


Jeff C said:
See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


OMS said:
Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers with that
data
I want. My problem is how do I get the value to properly populate their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

OMS

TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence 0700-1000
(7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



Duane Hookom said:
Is your TRANSACTION_DATE a date/time field or is it text. When you use
Mid(), it suggests text. You shouldn't need a big, ugly Switch() function.
Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1, Expr2,...?

--
Duane Hookom
MS Access MVP


OMS said:
Thanks but that didn't answer the question. By the way this is what I'm
using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


Jeff C said:
See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers with that
data
I want. My problem is how do I get the value to properly populate their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a union
query.

Do you have a table with fields like TIME_INTERVAL15, TIME_INTERVAL16,
TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


OMS said:
TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



Duane Hookom said:
Is your TRANSACTION_DATE a date/time field or is it text. When you use
Mid(), it suggests text. You shouldn't need a big, ugly Switch()
function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1, Expr2,...?

--
Duane Hookom
MS Access MVP


OMS said:
Thanks but that didn't answer the question. By the way this is what I'm
using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers with
that data
I want. My problem is how do I get the value to properly populate
their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

OMS

HI DH,

Format(Format(Transaction_Date,"0000-00-00"),"mmmm") worked. Thanks. :)

Yes, TIME_INTERVAL_xx come from a table. All it does is count an instance of
a service. That's why I was adding 6 T_Intervals to get 3 hour periods.

OMS

Duane Hookom said:
To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a union
query.

Do you have a table with fields like TIME_INTERVAL15, TIME_INTERVAL16,
TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


OMS said:
TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



Duane Hookom said:
Is your TRANSACTION_DATE a date/time field or is it text. When you use
Mid(), it suggests text. You shouldn't need a big, ugly Switch()
function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1, Expr2,...?

--
Duane Hookom
MS Access MVP


Thanks but that didn't answer the question. By the way this is what I'm
using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers with
that data
I want. My problem is how do I get the value to properly populate
their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

How about a reply regarding my statement "If you want columns to be Row
Headings, you might need to create a union query"?

A union query can normalize an un-normalized table structure so that it can
be more flexibly reported.
--
Duane Hookom
MS Access MVP


OMS said:
HI DH,

Format(Format(Transaction_Date,"0000-00-00"),"mmmm") worked. Thanks. :)

Yes, TIME_INTERVAL_xx come from a table. All it does is count an instance
of a service. That's why I was adding 6 T_Intervals to get 3 hour periods.

OMS

Duane Hookom said:
To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a union
query.

Do you have a table with fields like TIME_INTERVAL15, TIME_INTERVAL16,
TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


OMS said:
TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Is your TRANSACTION_DATE a date/time field or is it text. When you use
Mid(), it suggests text. You shouldn't need a big, ugly Switch()
function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1,
Expr2,...?

--
Duane Hookom
MS Access MVP


Thanks but that didn't answer the question. By the way this is what
I'm using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers with
that data
I want. My problem is how do I get the value to properly populate
their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

OMS

Oh sorry, I'm fine with that. Never made one but more than willing to try.

OMS

Duane Hookom said:
How about a reply regarding my statement "If you want columns to be Row
Headings, you might need to create a union query"?

A union query can normalize an un-normalized table structure so that it
can be more flexibly reported.
--
Duane Hookom
MS Access MVP


OMS said:
HI DH,

Format(Format(Transaction_Date,"0000-00-00"),"mmmm") worked. Thanks. :)

Yes, TIME_INTERVAL_xx come from a table. All it does is count an instance
of a service. That's why I was adding 6 T_Intervals to get 3 hour
periods.

OMS

Duane Hookom said:
To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a union
query.

Do you have a table with fields like TIME_INTERVAL15, TIME_INTERVAL16,
TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Is your TRANSACTION_DATE a date/time field or is it text. When you use
Mid(), it suggests text. You shouldn't need a big, ugly Switch()
function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1,
Expr2,...?

--
Duane Hookom
MS Access MVP


Thanks but that didn't answer the question. By the way this is what
I'm using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers with
that data
I want. My problem is how do I get the value to properly populate
their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If you need further assistance, you might want to reply with the significant
table and field names from your application.

--
Duane Hookom
MS Access MVP

OMS said:
Oh sorry, I'm fine with that. Never made one but more than willing to try.

OMS

Duane Hookom said:
How about a reply regarding my statement "If you want columns to be Row
Headings, you might need to create a union query"?

A union query can normalize an un-normalized table structure so that it
can be more flexibly reported.
--
Duane Hookom
MS Access MVP


OMS said:
HI DH,

Format(Format(Transaction_Date,"0000-00-00"),"mmmm") worked. Thanks. :)

Yes, TIME_INTERVAL_xx come from a table. All it does is count an
instance of a service. That's why I was adding 6 T_Intervals to get 3
hour periods.

OMS

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a
union query.

Do you have a table with fields like TIME_INTERVAL15, TIME_INTERVAL16,
TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Is your TRANSACTION_DATE a date/time field or is it text. When you
use Mid(), it suggests text. You shouldn't need a big, ugly Switch()
function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1,
Expr2,...?

--
Duane Hookom
MS Access MVP


Thanks but that didn't answer the question. By the way this is what
I'm using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers with
that data
I want. My problem is how do I get the value to properly populate
their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

OMS

Ok Duane,
Boy do I still need help. I know the Sum part of the query is probably wrong
because I don't really want the sum of all Expr1,Expr2... As you see
multiple daily entries. Over 8,000 total. I just want it to populate the
correct area of month/time and can't quite wrap my head around this. I'm
thinking my logic is whacked.
TRANSACTION_DATE SITE Expr1 Expr2 Expr3 Expr4 Expr5 Expr6
20050922 3 29 1 17 23 90 4
20050922 3 2 0 0 2 2 0
20050922 3 9 32 20 7 49 5
20050922 3 4 6 8 7 3 1
20050922 3 12 17 24 10 24 3

20051031 3 2 10 13 1 0 0
20051031 3 34 107 40 100 107 3
20051031 3 11 41 12 19 17 2
20051031 3 22 29 19 0 3 0
20051101 3 65 21 61 31 115 3
20051101 3 4 9 13 14 25 0


Example of what I would like it to look like below:
Campus: MMS
September October November
0700-1000 56 69 69
1000-1300 46 187 84
1300-1600 69 30 74
etc.

Sample of Query:

TRANSFORM
Sum([tblWL_MMS_Only]![Expr1]+[tblWL_MMS_Only]![Expr2]+[tblWL_MMS_Only]![Expr3]+[tblWL_MMS_Only]![Expr4]+[tblWL_MMS_Only]![Expr5]+[tblWL_MMS_Only]![Expr6])
AS Expr9
SELECT Switch([SITE]="1","LOC",[SITE]="3","MMS",[SITE]="4","IRHS") AS
Campus,
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")
AS Expr8
FROM tblWL_MMS_Only
GROUP BY Switch([SITE]="1","LOC",[SITE]="3","MMS",[SITE]="4","IRHS"),
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")
PIVOT Format(Format([Transaction_Date],"0000-00-00"),"mmmm");

OMS

Duane Hookom said:
If you need further assistance, you might want to reply with the
significant table and field names from your application.

--
Duane Hookom
MS Access MVP

OMS said:
Oh sorry, I'm fine with that. Never made one but more than willing to
try.

OMS

Duane Hookom said:
How about a reply regarding my statement "If you want columns to be Row
Headings, you might need to create a union query"?

A union query can normalize an un-normalized table structure so that it
can be more flexibly reported.
--
Duane Hookom
MS Access MVP


HI DH,

Format(Format(Transaction_Date,"0000-00-00"),"mmmm") worked. Thanks.
:)

Yes, TIME_INTERVAL_xx come from a table. All it does is count an
instance of a service. That's why I was adding 6 T_Intervals to get 3
hour periods.

OMS

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a
union query.

Do you have a table with fields like TIME_INTERVAL15, TIME_INTERVAL16,
TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Is your TRANSACTION_DATE a date/time field or is it text. When you
use Mid(), it suggests text. You shouldn't need a big, ugly Switch()
function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1,
Expr2,...?

--
Duane Hookom
MS Access MVP


Thanks but that didn't answer the question. By the way this is what
I'm using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows are
periods of
time during the day. Expr1,2,3, etc. are table column headers
with that data
I want. My problem is how do I get the value to properly populate
their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Start by creating a union query like:

SELECT TRANSACTION_DATE, SITE, Expr1 as TheValue, "0700-1000" as TimeSlot
FROM tblWL_MMS_Only
UNION ALL
SELECT TRANSACTION_DATE, SITE, Expr2, "1000-1300"
FROM tblWL_MMS_Only
UNION ALL
SELECT TRANSACTION_DATE, SITE, Expr3, "1300-1600"
FROM tblWL_MMS_Only
UNION ALL
--etc-- for all expr values.

You can then create a crosstab query with the TimeSlot and SITE as the Row
Headings, the month expression as the column heading, and Sum of TheValue as
the Value.


--
Duane Hookom
MS Access MVP


OMS said:
Ok Duane,
Boy do I still need help. I know the Sum part of the query is probably
wrong because I don't really want the sum of all Expr1,Expr2... As you see
multiple daily entries. Over 8,000 total. I just want it to populate the
correct area of month/time and can't quite wrap my head around this. I'm
thinking my logic is whacked.
TRANSACTION_DATE SITE Expr1 Expr2 Expr3 Expr4 Expr5 Expr6
20050922 3 29 1 17 23 90 4
20050922 3 2 0 0 2 2 0
20050922 3 9 32 20 7 49 5
20050922 3 4 6 8 7 3 1
20050922 3 12 17 24 10 24 3

20051031 3 2 10 13 1 0 0
20051031 3 34 107 40 100 107 3
20051031 3 11 41 12 19 17 2
20051031 3 22 29 19 0 3 0
20051101 3 65 21 61 31 115 3
20051101 3 4 9 13 14 25 0


Example of what I would like it to look like below:
Campus: MMS
September October November
0700-1000 56 69 69
1000-1300 46 187 84
1300-1600 69 30 74
etc.

Sample of Query:

TRANSFORM
Sum([tblWL_MMS_Only]![Expr1]+[tblWL_MMS_Only]![Expr2]+[tblWL_MMS_Only]![Expr3]+[tblWL_MMS_Only]![Expr4]+[tblWL_MMS_Only]![Expr5]+[tblWL_MMS_Only]![Expr6])
AS Expr9
SELECT Switch([SITE]="1","LOC",[SITE]="3","MMS",[SITE]="4","IRHS") AS
Campus,
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")
AS Expr8
FROM tblWL_MMS_Only
GROUP BY Switch([SITE]="1","LOC",[SITE]="3","MMS",[SITE]="4","IRHS"),
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")
PIVOT Format(Format([Transaction_Date],"0000-00-00"),"mmmm");

OMS

Duane Hookom said:
If you need further assistance, you might want to reply with the
significant table and field names from your application.

--
Duane Hookom
MS Access MVP

OMS said:
Oh sorry, I'm fine with that. Never made one but more than willing to
try.

OMS

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
How about a reply regarding my statement "If you want columns to be Row
Headings, you might need to create a union query"?

A union query can normalize an un-normalized table structure so that it
can be more flexibly reported.
--
Duane Hookom
MS Access MVP


HI DH,

Format(Format(Transaction_Date,"0000-00-00"),"mmmm") worked. Thanks.
:)

Yes, TIME_INTERVAL_xx come from a table. All it does is count an
instance of a service. That's why I was adding 6 T_Intervals to get 3
hour periods.

OMS

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a
union query.

Do you have a table with fields like TIME_INTERVAL15,
TIME_INTERVAL16, TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Is your TRANSACTION_DATE a date/time field or is it text. When you
use Mid(), it suggests text. You shouldn't need a big, ugly
Switch() function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1,
Expr2,...?

--
Duane Hookom
MS Access MVP


Thanks but that didn't answer the question. By the way this is
what I'm using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows
are periods of
time during the day. Expr1,2,3, etc. are table column headers
with that data
I want. My problem is how do I get the value to properly
populate their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

OMS

Duane,
I was getting "Syntax error in query. Incomplete query clause." So I added a
semi-colon to the end and got "Invalid SQL statement; expected 'DELETE',
'INSERT', PROCEDURE',SELECT', or 'UPDATE'.

So I removed the last UNION ALL (and the ";" I added) and it worked
perfectly. Thank you so much for taking the time to help me. You show great
patience.

Thank you again so much,

OMS




Duane Hookom said:
Start by creating a union query like:

SELECT TRANSACTION_DATE, SITE, Expr1 as TheValue, "0700-1000" as TimeSlot
FROM tblWL_MMS_Only
UNION ALL
SELECT TRANSACTION_DATE, SITE, Expr2, "1000-1300"
FROM tblWL_MMS_Only
UNION ALL
SELECT TRANSACTION_DATE, SITE, Expr3, "1300-1600"
FROM tblWL_MMS_Only
UNION ALL
--etc-- for all expr values.

You can then create a crosstab query with the TimeSlot and SITE as the Row
Headings, the month expression as the column heading, and Sum of TheValue
as the Value.


--
Duane Hookom
MS Access MVP


OMS said:
Ok Duane,
Boy do I still need help. I know the Sum part of the query is probably
wrong because I don't really want the sum of all Expr1,Expr2... As you
see multiple daily entries. Over 8,000 total. I just want it to populate
the correct area of month/time and can't quite wrap my head around this.
I'm thinking my logic is whacked.
TRANSACTION_DATE SITE Expr1 Expr2 Expr3 Expr4 Expr5 Expr6
20050922 3 29 1 17 23 90 4
20050922 3 2 0 0 2 2 0
20050922 3 9 32 20 7 49 5
20050922 3 4 6 8 7 3 1
20050922 3 12 17 24 10 24 3

20051031 3 2 10 13 1 0 0
20051031 3 34 107 40 100 107 3
20051031 3 11 41 12 19 17 2
20051031 3 22 29 19 0 3 0
20051101 3 65 21 61 31 115 3
20051101 3 4 9 13 14 25 0


Example of what I would like it to look like below:
Campus: MMS
September October November
0700-1000 56 69 69
1000-1300 46 187 84
1300-1600 69 30 74
etc.

Sample of Query:

TRANSFORM
Sum([tblWL_MMS_Only]![Expr1]+[tblWL_MMS_Only]![Expr2]+[tblWL_MMS_Only]![Expr3]+[tblWL_MMS_Only]![Expr4]+[tblWL_MMS_Only]![Expr5]+[tblWL_MMS_Only]![Expr6])
AS Expr9
SELECT Switch([SITE]="1","LOC",[SITE]="3","MMS",[SITE]="4","IRHS") AS
Campus,
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")
AS Expr8
FROM tblWL_MMS_Only
GROUP BY Switch([SITE]="1","LOC",[SITE]="3","MMS",[SITE]="4","IRHS"),
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")
PIVOT Format(Format([Transaction_Date],"0000-00-00"),"mmmm");

OMS

Duane Hookom said:
If you need further assistance, you might want to reply with the
significant table and field names from your application.

--
Duane Hookom
MS Access MVP

Oh sorry, I'm fine with that. Never made one but more than willing to
try.

OMS

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
How about a reply regarding my statement "If you want columns to be
Row Headings, you might need to create a union query"?

A union query can normalize an un-normalized table structure so that
it can be more flexibly reported.
--
Duane Hookom
MS Access MVP


HI DH,

Format(Format(Transaction_Date,"0000-00-00"),"mmmm") worked. Thanks.
:)

Yes, TIME_INTERVAL_xx come from a table. All it does is count an
instance of a service. That's why I was adding 6 T_Intervals to get 3
hour periods.

OMS

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
To get the month name, consider getting the month name with:
Format( datevalue(Format(Transaction_Date,"0000-00-00")),"mmmm")
or
Format(Format(Transaction_Date,"0000-00-00"),"mmmm")

If you want columns to be Row Headings, you might need to create a
union query.

Do you have a table with fields like TIME_INTERVAL15,
TIME_INTERVAL16, TIME_INTERVAL16,...?


--
Duane Hookom
MS Access MVP


TRANSACTION_DATE is text like 20050930
Format(Transaction_Date,"mmmm") gave me Overflow error

Expr1, Expr2,... are sums of TIME_INTERVALS (1/2 hour each) hence
0700-1000 (7am-10am)

Expr1:
[TIME_INTERVAL15]+[TIME_INTERVAL16]+[TIME_INTERVAL17]+[TIME_INTERVAL18]+[TIME_INTERVAL19]+[TIME_INTERVAL20]

Expr1 data is in number form ie. 13

Remember I want Expr1, Expr2... to be values in the crosstab query

Example: April May June
0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Example: April May June
[Expr1Total] 6 1 3
[Expr2Total] 5 4 7
[Expr3Total] 3 4 2



"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Is your TRANSACTION_DATE a date/time field or is it text. When you
use Mid(), it suggests text. You shouldn't need a big, ugly
Switch() function. Try use Format(Transaction_Date,"mmmm").

What does your actual data look like? What exactly are Expr1,
Expr2,...?

--
Duane Hookom
MS Access MVP


Thanks but that didn't answer the question. By the way this is
what I'm using for the date. I'm converting from numeric.
Month:
Expr7:
Switch(Mid([TRANSACTION_DATE],5,2)="01","January",Mid([TRANSACTION_DATE],5,2)="02","February",Mid([TRANSACTION_DATE],5,2)="03","March",Mid([TRANSACTION_DATE],5,2)="04","April",Mid([TRANSACTION_DATE],5,2)="05","May",Mid([TRANSACTION_DATE],5,2)="06","June",Mid([TRANSACTION_DATE],5,2)="07","July",Mid([TRANSACTION_DATE],5,2)="08","August",Mid([TRANSACTION_DATE],5,2)="09","September",Mid([TRANSACTION_DATE],5,2)="10","October",Mid([TRANSACTION_DATE],5,2)="11","November",Mid([TRANSACTION_DATE],5,2)="12","December")


See Duane Hookom's article "Dynamic Monthly Crosstab Report"

http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
--
Jeff C
Live Well .. Be Happy In All You Do


:

Hi,

I am trying to make a query where columns are months and rows
are periods of
time during the day. Expr1,2,3, etc. are table column headers
with that data
I want. My problem is how do I get the value to properly
populate their
respective fields?

I am using Expression:
Switch([Expr1],"0700-1000",[Expr2],"1000-1300",[Expr3],"1300-1600",[Expr4],"1600-1900",[Expr5],"1900-2200",[Expr6],"2200-0100")

Example: April May June

0700-1000 6 1 3
1000-1300 5 4 7
1300-1600 3 4 2

Hope you can help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

you tried to assign the null value to a variable that is not avariant data type - MsAccess 2007 4
Ambiguous outer join vs. multiple duplicative records 1
Why does this Nested IIF statement add AS fields 11
Alignment 6
Very lost 5
Closing Spaces on Report 1
Execute multiple crosstab queries with one parameter 2
To Query or not to Query 0

Top