B
BruceM
I have used the method described in Microsoft KB article 304458 to combine
two Crosstab queries into a single Select (Totals) query in order to view
more than one column. I have a Jobs table and a Rework table relate
one-to-many. A Job record includes the Quantity of parts processed, and the
PartNumber (there is only one PartNumber per job). The PartNumber links to
a Parts table that includes the PartCategory (Gear, Spindle, etc.). The
Rework table lists the number of parts that had problems during processing.
Since Rework could occur at any of several points during processing, there
can be several Rework records per Job.
I have created a Crosstab query from the Job table to list the PartCategory
(row), the month (Column), and the Sum of Quantity:
Category Jan Feb
--------- --- ---
Gear 50 70
Spindle 20 40
In similar fashion I have built a query from the Rework table, except the
month totals are just the quantity reworked. I combine these into a single
Select query as explained in the KB article. It works as it should (except
for the math problem, which I will get to shortly).
First, the naming strategy question. Jan, Feb, etc. are defined Column
Headings (which enables them to appear in the correct order). The formula I
use for the Column Heading in the crosstab query is Format(JobDate,"mmm").
However, I prefer that all fields have unique names. If both crosstab
queries use Format(JobDate,"mmm") I have to go to a lot of extra trouble to
specify the query name when I use Jan, Feb, etc. in calculations. One way
to avoid this is to use Format(JobDate, "m") in one of the crosstab queries,
but then I end up with column headings of [1], [2], etc. It makes me uneasy
to use numbers in that way, but I don't know if the unease is justified. If
it is, I am having trouble coming up with an alternative.
Now the math/expression question. The Month headings in the crosstab query
built on the Job table are Jan, Feb, etc. In the Rework crosstab query they
are 1, 2, etc.
These are alias fields for the Column Headings:
Jan_P: Nz([Jan],0)
Jan_R: Nz([1],0)
JanAvg: Format(Nz(1-([Jan_R]/[Jan_P]),1),"0.0%")
Fields are constructed the same way for Feb, etc. (just through Feb so far).
This leads to rows something like:
Category Jan_P Jan_R JanAvg
--------- ------ ------ --------
Gear 50 10 80.0%
This works as it should, including showing 100% when Jan_R (total parts
reworked in January) = 0 and Jan_P (total parts processed in January) > 0.
The difficulty comes when there were no parts processed (e.g. Jan_P = 0), in
which case the percentage field shows #Error. I finally came up with a
rather cumbersome expression:
JanRTY: Format(IIf(Val([Jan_P])=0,0,Nz(1-([Jan_R]/[Jan_P]),1)),"0.0%")
but I wonder if I am missing an easier way.
By the way, an alias field with the expression:
Nz([Jan_P],1)
or
Nz(Val([Jan_P]),1)
never evaluates to 1, even when Jan_P = 0. I don't understand this.
two Crosstab queries into a single Select (Totals) query in order to view
more than one column. I have a Jobs table and a Rework table relate
one-to-many. A Job record includes the Quantity of parts processed, and the
PartNumber (there is only one PartNumber per job). The PartNumber links to
a Parts table that includes the PartCategory (Gear, Spindle, etc.). The
Rework table lists the number of parts that had problems during processing.
Since Rework could occur at any of several points during processing, there
can be several Rework records per Job.
I have created a Crosstab query from the Job table to list the PartCategory
(row), the month (Column), and the Sum of Quantity:
Category Jan Feb
--------- --- ---
Gear 50 70
Spindle 20 40
In similar fashion I have built a query from the Rework table, except the
month totals are just the quantity reworked. I combine these into a single
Select query as explained in the KB article. It works as it should (except
for the math problem, which I will get to shortly).
First, the naming strategy question. Jan, Feb, etc. are defined Column
Headings (which enables them to appear in the correct order). The formula I
use for the Column Heading in the crosstab query is Format(JobDate,"mmm").
However, I prefer that all fields have unique names. If both crosstab
queries use Format(JobDate,"mmm") I have to go to a lot of extra trouble to
specify the query name when I use Jan, Feb, etc. in calculations. One way
to avoid this is to use Format(JobDate, "m") in one of the crosstab queries,
but then I end up with column headings of [1], [2], etc. It makes me uneasy
to use numbers in that way, but I don't know if the unease is justified. If
it is, I am having trouble coming up with an alternative.
Now the math/expression question. The Month headings in the crosstab query
built on the Job table are Jan, Feb, etc. In the Rework crosstab query they
are 1, 2, etc.
These are alias fields for the Column Headings:
Jan_P: Nz([Jan],0)
Jan_R: Nz([1],0)
JanAvg: Format(Nz(1-([Jan_R]/[Jan_P]),1),"0.0%")
Fields are constructed the same way for Feb, etc. (just through Feb so far).
This leads to rows something like:
Category Jan_P Jan_R JanAvg
--------- ------ ------ --------
Gear 50 10 80.0%
This works as it should, including showing 100% when Jan_R (total parts
reworked in January) = 0 and Jan_P (total parts processed in January) > 0.
The difficulty comes when there were no parts processed (e.g. Jan_P = 0), in
which case the percentage field shows #Error. I finally came up with a
rather cumbersome expression:
JanRTY: Format(IIf(Val([Jan_P])=0,0,Nz(1-([Jan_R]/[Jan_P]),1)),"0.0%")
but I wonder if I am missing an easier way.
By the way, an alias field with the expression:
Nz([Jan_P],1)
or
Nz(Val([Jan_P]),1)
never evaluates to 1, even when Jan_P = 0. I don't understand this.