Crosstab query value

[Guest]

I am trying to generate a crosstab query, but I am having trouble with the
"Value" field.

I have data broken out by day updated YTD. I want to generate CSR Name as a
row heading, Month as a column heading, and the Quality Pct as the value. The
Quality pct is calculated from NUmber A/Number B.

The problem is I can calculate the Total Number A per month in a crosstab,
by selecting "Number A", "value", and "sum".....but I can't seem to calculate
the percentage. It won't allow me to do "Group by" in a "value", and I tried:
[Number A]/[Number B] grouping by "Sum" for the "value" and that gave me
inflated (wrong) data.

Any suggestions? I'd like to do this in one query if possible. I guess I
could do three crosstabs and match on CSR name, but that seems kind of silly.
Thanks!!

 

[Duane Hookom]

You haven't provided SQL views, field names, sample data, expect results
display,...

The only WAG I have is to set your value to
TheVal: Count([Number A]) / Count([Number b])
set the Totals to Expression

I doubt this is correct.

 

[Guest]

Ok, sorry about that. No, that didn't work. The crosstab is populated by two
queries....here is query 1:

SELECT MastrPhoneStatsTbl.DATE, MastrPhoneStatsTbl.USERID,
MastrPhoneStatsTbl.LASTNAME, MastrPhoneStatsTbl.FIRSTNAME,
MastrPhoneStatsTbl.TEAM_NAME, MastrPhoneStatsTbl.STAFF_TIME,
Nz([RESEARCH_HRS_SCH],0) AS RESEARCH_HRS,
(MastrPhoneStatsTbl!STAFF_TIME*0.4)+([RESEARCH_HRS]*4) AS ACCT_ITEMS
FROM MastrPhoneStatsTbl LEFT JOIN MastrScheduledResearchTbl ON
(MastrPhoneStatsTbl.DATE = MastrScheduledResearchTbl.DATE) AND
(MastrPhoneStatsTbl.USERID = MastrScheduledResearchTbl.USERID);

Here is query 2:

SELECT MastrCompletedResearchTbl.DATE, MastrCompletedResearchTbl.USERID,
MastrCompletedResearchTbl.LASTNAME, MastrCompletedResearchTbl.FIRSTNAME,
MastrCompletedResearchTbl.TEAM_NAME, Sum(MastrCompletedResearchTbl.STAUS_CNT)
AS SumOfSTAUS_CNT
FROM MastrCompletedResearchTbl
GROUP BY MastrCompletedResearchTbl.DATE, MastrCompletedResearchTbl.USERID,
MastrCompletedResearchTbl.LASTNAME, MastrCompletedResearchTbl.FIRSTNAME,
MastrCompletedResearchTbl.TEAM_NAME;

In the desired crosstab query, I am looking to show LAST_NAME, FIRSTNAME, by
Month(Left(DATE,2)) in the column, and the SUM(STATUS_CNT)/SUM(Acct_Items) as
the value. Here are some expected results

In Query 1 I have:

date last name first name acct_items
10/1 Smith John 5
10/2 Smith John 6

Query 2:
date last name first name Status_cnt
10/1 Smith John 4
10/2 Smith John 6

I Want:

last name first name Jan feb march april may june july aug
sept oct
smith john 0% 0 0 0 0 0
0 0 0 90.9%

Hope this helps, I don't know how to explain it any better. Thank you so much!

You haven't provided SQL views, field names, sample data, expect results
display,...

The only WAG I have is to set your value to
TheVal: Count([Number A]) / Count([Number b])
set the Totals to Expression

I doubt this is correct.

--
Duane Hookom
MS Access MVP

I am trying to generate a crosstab query, but I am having trouble with the
"Value" field.

I have data broken out by day updated YTD. I want to generate CSR Name as
a
row heading, Month as a column heading, and the Quality Pct as the value.
The
Quality pct is calculated from NUmber A/Number B.

The problem is I can calculate the Total Number A per month in a crosstab,
by selecting "Number A", "value", and "sum".....but I can't seem to
calculate
the percentage. It won't allow me to do "Group by" in a "value", and I
tried:
[Number A]/[Number B] grouping by "Sum" for the "value" and that gave me
inflated (wrong) data.

Any suggestions? I'd like to do this in one query if possible. I guess I
could do three crosstabs and match on CSR name, but that seems kind of
silly.
Thanks!!

 

[Guest]

Two suggestions --
Use another field name instead od 'Date' as that is a reserved word in Access.
Use a DateTime field for your date instead of a text field.

Try a query on your Month(Left(DATE,2)) of a data sample in a temp table.
You will not get what you are wanting.

Ok, sorry about that. No, that didn't work. The crosstab is populated by two
queries....here is query 1:

SELECT MastrPhoneStatsTbl.DATE, MastrPhoneStatsTbl.USERID,
MastrPhoneStatsTbl.LASTNAME, MastrPhoneStatsTbl.FIRSTNAME,
MastrPhoneStatsTbl.TEAM_NAME, MastrPhoneStatsTbl.STAFF_TIME,
Nz([RESEARCH_HRS_SCH],0) AS RESEARCH_HRS,
(MastrPhoneStatsTbl!STAFF_TIME*0.4)+([RESEARCH_HRS]*4) AS ACCT_ITEMS
FROM MastrPhoneStatsTbl LEFT JOIN MastrScheduledResearchTbl ON
(MastrPhoneStatsTbl.DATE = MastrScheduledResearchTbl.DATE) AND
(MastrPhoneStatsTbl.USERID = MastrScheduledResearchTbl.USERID);

Here is query 2:

SELECT MastrCompletedResearchTbl.DATE, MastrCompletedResearchTbl.USERID,
MastrCompletedResearchTbl.LASTNAME, MastrCompletedResearchTbl.FIRSTNAME,
MastrCompletedResearchTbl.TEAM_NAME, Sum(MastrCompletedResearchTbl.STAUS_CNT)
AS SumOfSTAUS_CNT
FROM MastrCompletedResearchTbl
GROUP BY MastrCompletedResearchTbl.DATE, MastrCompletedResearchTbl.USERID,
MastrCompletedResearchTbl.LASTNAME, MastrCompletedResearchTbl.FIRSTNAME,
MastrCompletedResearchTbl.TEAM_NAME;

In the desired crosstab query, I am looking to show LAST_NAME, FIRSTNAME, by
Month(Left(DATE,2)) in the column, and the SUM(STATUS_CNT)/SUM(Acct_Items) as
the value. Here are some expected results

In Query 1 I have:

date last name first name acct_items
10/1 Smith John 5
10/2 Smith John 6

Query 2:
date last name first name Status_cnt
10/1 Smith John 4
10/2 Smith John 6

I Want:

last name first name Jan feb march april may june july aug
sept oct
smith john 0% 0 0 0 0 0
0 0 0 90.9%

Hope this helps, I don't know how to explain it any better. Thank you so much!

You haven't provided SQL views, field names, sample data, expect results
display,...

The only WAG I have is to set your value to
TheVal: Count([Number A]) / Count([Number b])
set the Totals to Expression

I doubt this is correct.

--
Duane Hookom
MS Access MVP

I am trying to generate a crosstab query, but I am having trouble with the
"Value" field.

I have data broken out by day updated YTD. I want to generate CSR Name as
a
row heading, Month as a column heading, and the Quality Pct as the value.
The
Quality pct is calculated from NUmber A/Number B.

The problem is I can calculate the Total Number A per month in a crosstab,
by selecting "Number A", "value", and "sum".....but I can't seem to
calculate
the percentage. It won't allow me to do "Group by" in a "value", and I
tried:
[Number A]/[Number B] grouping by "Sum" for the "value" and that gave me
inflated (wrong) data.

Any suggestions? I'd like to do this in one query if possible. I guess I
could do three crosstabs and match on CSR name, but that seems kind of
silly.
Thanks!!

 

[Duane Hookom]

What happens when you use SUM(STATUS_CNT)/SUM(Acct_Items) as the value?

I would never use a derived column as an expression in another calc. I would
replace
Nz([RESEARCH_HRS_SCH],0) AS RESEARCH_HRS,
(MastrPhoneStatsTbl!STAFF_TIME*0.4)+([RESEARCH_HRS]*4) AS ACCT_ITEMS

with
(MastrPhoneStatsTbl!STAFF_TIME*0.4)+(Nz([RESEARCH_HRS_SCH],0)*4) AS
ACCT_ITEMS

You might want to group your select queries by month and USERID so you have
values to join on in the crosstab.

--
Duane Hookom
MS Access MVP

Ok, sorry about that. No, that didn't work. The crosstab is populated by
two
queries....here is query 1:

SELECT MastrPhoneStatsTbl.DATE, MastrPhoneStatsTbl.USERID,
MastrPhoneStatsTbl.LASTNAME, MastrPhoneStatsTbl.FIRSTNAME,
MastrPhoneStatsTbl.TEAM_NAME, MastrPhoneStatsTbl.STAFF_TIME,
Nz([RESEARCH_HRS_SCH],0) AS RESEARCH_HRS,
(MastrPhoneStatsTbl!STAFF_TIME*0.4)+([RESEARCH_HRS]*4) AS ACCT_ITEMS
FROM MastrPhoneStatsTbl LEFT JOIN MastrScheduledResearchTbl ON
(MastrPhoneStatsTbl.DATE = MastrScheduledResearchTbl.DATE) AND
(MastrPhoneStatsTbl.USERID = MastrScheduledResearchTbl.USERID);

Here is query 2:

SELECT MastrCompletedResearchTbl.DATE, MastrCompletedResearchTbl.USERID,
MastrCompletedResearchTbl.LASTNAME, MastrCompletedResearchTbl.FIRSTNAME,
MastrCompletedResearchTbl.TEAM_NAME,
Sum(MastrCompletedResearchTbl.STAUS_CNT)
AS SumOfSTAUS_CNT
FROM MastrCompletedResearchTbl
GROUP BY MastrCompletedResearchTbl.DATE, MastrCompletedResearchTbl.USERID,
MastrCompletedResearchTbl.LASTNAME, MastrCompletedResearchTbl.FIRSTNAME,
MastrCompletedResearchTbl.TEAM_NAME;

In the desired crosstab query, I am looking to show LAST_NAME, FIRSTNAME,
by
Month(Left(DATE,2)) in the column, and the SUM(STATUS_CNT)/SUM(Acct_Items)
as
the value. Here are some expected results

In Query 1 I have:

date last name first name acct_items
10/1 Smith John 5
10/2 Smith John 6

Query 2:
date last name first name Status_cnt
10/1 Smith John 4
10/2 Smith John 6

I Want:

last name first name Jan feb march april may june july aug
sept oct
smith john 0% 0 0 0 0 0
0 0 0 90.9%

Hope this helps, I don't know how to explain it any better. Thank you so
much!

You haven't provided SQL views, field names, sample data, expect results
display,...

The only WAG I have is to set your value to
TheVal: Count([Number A]) / Count([Number b])
set the Totals to Expression

I doubt this is correct.

--
Duane Hookom
MS Access MVP

I am trying to generate a crosstab query, but I am having trouble with
the
"Value" field.

I have data broken out by day updated YTD. I want to generate CSR Name
as
a
row heading, Month as a column heading, and the Quality Pct as the
value.
The
Quality pct is calculated from NUmber A/Number B.

The problem is I can calculate the Total Number A per month in a
crosstab,
by selecting "Number A", "value", and "sum".....but I can't seem to
calculate
the percentage. It won't allow me to do "Group by" in a "value", and I
tried:
[Number A]/[Number B] grouping by "Sum" for the "value" and that gave
me
inflated (wrong) data.

Any suggestions? I'd like to do this in one query if possible. I guess
I
could do three crosstabs and match on CSR name, but that seems kind of
silly.
Thanks!!