Crosstab query value

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!
 
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.
 
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!

Duane Hookom said:
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


Nick I said:
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!!
Click to expand...
Click to expand...
 
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.

Nick I said:
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!

Duane Hookom said:
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


Nick I said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
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


Nick I said:
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!

Duane Hookom said:
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


Nick I said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top