Saving Pivot Table view of Query

  • Thread starter Thread starter Katie
  • Start date Start date
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?
 
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
 
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'.
 
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...
 
Back
Top