Saving Pivot Table view of Query

K

Katie

I have a Query that I run each day in Access 2003.
Once the Query completes, I convert it to a Pivot table.

Each time I do this, 2 of the fields in my Detail area
always are there ( It remembers the layout). The other 2,
I have to drag into the table each time. After I bring
the 2 additional fields in, I do say YES to Save
Changes?, however, it doesn't seem to hold. The next time
I run into Pivot Table view, I have to pull these two
fields in again.

I don't know if this would make the difference, but the
two fields that won't "stay" are both fields where I have
an expression in the Access Query.

Any suggestions?
 
D

Dale Fye

Can you post the SQL of your query? along with what you are getting for
results and what you want to get for results (just a short example)

Dale
 
K

Katie

Here is the SQL:
SELECT SWB_SW_CASE.SWCASEID, SWB_SW_CUSTOMER.AMNEMONIC,
SWB_SW_CASE.SWTYPE, Team_Type.Team_Type,
SWB_SW_CASE.SWSTATUS, SWB_SW_CASE.AISSUETYPE,
SWB_SW_CASE.SWDATECREATED, SWB_SW_PROD_RELEASE.SWNAME,
Employee_Team.SWLOGIN, Employee_Team.TEAM,
SWB_SW_CASE.SWNOTE, SWB_SW_CASE.SWRESPONDBY, IIf
([SWRESPONDBY]<Date() Or IsNull([SWRESPONDBY]),1,0) AS
COOKED, GetWorkDays(SWB_SW_CASE!SWDATECREATED,Now()) AS
AGE, SWB_SW_CASE.ALASTEDITEDDATE, GetWorkDays(SWB_SW_CASE!
ALASTEDITEDDATE,Now()) AS [LAST EDIT], [Employee_Team
Query].ALASTCOMMAFIRSTNAME, SWB_SW_CASE.APROBLEMTYPE,
SWB_SW_CASE.AECOMMERCETYPE, SWB_SW_CASE.APAYER
FROM ((SWB_SW_PROD_RELEASE INNER JOIN SWB_SW_INST_PRODUCT
ON SWB_SW_PROD_RELEASE.SWPRODRELEASEID =
SWB_SW_INST_PRODUCT.SWPRODRELEASEID) INNER JOIN
(((SWB_SW_CASE INNER JOIN SWB_SW_CUSTOMER ON
SWB_SW_CASE.SWCUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID)
LEFT JOIN Team_Type ON SWB_SW_CASE.SWTYPE =
Team_Type.Type) LEFT JOIN Employee_Team ON
SWB_SW_CASE.AIDXCASEOWNERID = Employee_Team.SWPERSONID)
ON SWB_SW_INST_PRODUCT.SWINSTPRODID =
SWB_SW_CASE.SWINSTPRODID) LEFT JOIN [Employee_Team Query]
ON SWB_SW_CASE.SWASSIGNEDTO = [Employee_Team
Query].SWPERSONID
WHERE (((SWB_SW_CUSTOMER.AMNEMONIC)<>"PRO" And
(SWB_SW_CUSTOMER.AMNEMONIC) Is Not Null) AND
((SWB_SW_CASE.SWTYPE) Is Not Null) AND
((SWB_SW_CASE.SWSTATUS)<>"Closed" And
(SWB_SW_CASE.SWSTATUS)<>"Deleted") AND
((SWB_SW_CASE.SWDATECREATED)>=#1/7/2004#) AND
((Employee_Team.SWLOGIN)<>"mmcguire") AND
((SWB_SW_CUSTOMER.ASD)=1));


It is a backlog report, so I get the results out and then
I put it into pivot table to tell me:
by SWLOGIN (or person),
How many Cases they have (Count SWCASEID),
The # of Cooked cases (Sum of COOKED),
and then I want the average age of these cases
(AGE) - instead it comes out as 'Count of AGE'.
 
K

Katie

Sorry... I hit send a little early on that last one.

So... anyway, there are 4 fields I want to see in the
data area:
Count of SWCASEID
Avg of AGE
Avg of Last Edit and
Sum of COOKED

by default (i guess), I get "Count" of SWCASEID and AGE
and "SUM" of Last Edit and COOKED

I have to export these to Excel in order to change the
Field Settings and make the Age and Last Edit fields into
Averages. I am looking for a way that this could happen
right in Access.

I originally stated that my problem was that these 2
fields would not save as part of my pivot table. I have
realized since that they will save, but they just don't
save in a format that I need them.. so I had been
exporting to Excel to pull those 2 fields in and set the
field settings to Average.

Does that make sense?
-----Original Message-----
Here is the SQL:
SELECT SWB_SW_CASE.SWCASEID, SWB_SW_CUSTOMER.AMNEMONIC,
SWB_SW_CASE.SWTYPE, Team_Type.Team_Type,
SWB_SW_CASE.SWSTATUS, SWB_SW_CASE.AISSUETYPE,
SWB_SW_CASE.SWDATECREATED, SWB_SW_PROD_RELEASE.SWNAME,
Employee_Team.SWLOGIN, Employee_Team.TEAM,
SWB_SW_CASE.SWNOTE, SWB_SW_CASE.SWRESPONDBY, IIf
([SWRESPONDBY]<Date() Or IsNull([SWRESPONDBY]),1,0) AS
COOKED, GetWorkDays(SWB_SW_CASE!SWDATECREATED,Now()) AS
AGE, SWB_SW_CASE.ALASTEDITEDDATE, GetWorkDays (SWB_SW_CASE!
ALASTEDITEDDATE,Now()) AS [LAST EDIT], [Employee_Team
Query].ALASTCOMMAFIRSTNAME, SWB_SW_CASE.APROBLEMTYPE,
SWB_SW_CASE.AECOMMERCETYPE, SWB_SW_CASE.APAYER
FROM ((SWB_SW_PROD_RELEASE INNER JOIN SWB_SW_INST_PRODUCT
ON SWB_SW_PROD_RELEASE.SWPRODRELEASEID =
SWB_SW_INST_PRODUCT.SWPRODRELEASEID) INNER JOIN
(((SWB_SW_CASE INNER JOIN SWB_SW_CUSTOMER ON
SWB_SW_CASE.SWCUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID)
LEFT JOIN Team_Type ON SWB_SW_CASE.SWTYPE =
Team_Type.Type) LEFT JOIN Employee_Team ON
SWB_SW_CASE.AIDXCASEOWNERID = Employee_Team.SWPERSONID)
ON SWB_SW_INST_PRODUCT.SWINSTPRODID =
SWB_SW_CASE.SWINSTPRODID) LEFT JOIN [Employee_Team Query]
ON SWB_SW_CASE.SWASSIGNEDTO = [Employee_Team
Query].SWPERSONID
WHERE (((SWB_SW_CUSTOMER.AMNEMONIC)<>"PRO" And
(SWB_SW_CUSTOMER.AMNEMONIC) Is Not Null) AND
((SWB_SW_CASE.SWTYPE) Is Not Null) AND
((SWB_SW_CASE.SWSTATUS)<>"Closed" And
(SWB_SW_CASE.SWSTATUS)<>"Deleted") AND
((SWB_SW_CASE.SWDATECREATED)>=#1/7/2004#) AND
((Employee_Team.SWLOGIN)<>"mmcguire") AND
((SWB_SW_CUSTOMER.ASD)=1));


It is a backlog report, so I get the results out and then
I put it into pivot table to tell me:
by SWLOGIN (or person),
How many Cases they have (Count SWCASEID),
The # of Cooked cases (Sum of COOKED),
and then I want the average age of these cases
(AGE) - instead it comes out as 'Count of AGE'.
-----Original Message-----
Can you post the SQL of your query? along with what you are getting for
results and what you want to get for results (just a short example)

Dale
Click to expand...
other
2,


.
Click to expand...
.
Click to expand...
 
Top