I am trying to get total Sales by Item, Category etc. I am using Access database(linking with Oracle DB) and trying to create Pivot table by linking to Access query/table. I get Zeros in total sales field. However, if I export the query into excel and back to access in a separate Access database it works.
Sample Data:
date Category invoice cust Sales Profit
6/9/04 dairy 8556 9999 $100 $10
6/9/04 meat 4552 1111 $200 $15
However, I am able to get total profit. My access query has sum(sales),
GP: (item * other fileds)
Group by date, category, cust, invoice.
The reason I do not want to do it from Excel is because my other queries, tables are stored in Access and it makes it easy to put everything in the database so I can link to Oracle, AS400 etc and launch multiple reports from Access database. My boss is wanting to install this application on his desktop so he can run the report himself on a daily basis.
The SALES field is datatype NUMBER so are some of my other fields in the query. It just baffles me why the pivot can handle calculation in other fields but not Sales.
Can you think of any other way I can run this from Access?
Query:
SELECT EIS_OPNBB1PF.TSDT AS [Date], EIS_OPNBB1PF.LCCT AS Catrgory, EIS_OPNBB1PF.ITEM AS Item, EIS_OPNBB1PF.CUNO AS [Customer No], EIS_OPNBB1PF.VNBR AS [Vendor No], EIS_OPNBB1PF.ORIN AS Invoice, EIS_MSEAA3PF.SMAN AS Salesperson, Sum(EIS_OPNBB1PF.IEPR) AS DailySales, Sum((([NPRC]-[ACGS])*(IIf([ITCI]='Y',[TTCW],[QNTY])))) AS [GPT$], Sum(EIS_OPNBB1PF.QNTY) AS Pieces, Count(EIS_OPNBB1PF.LN03) AS Lines
FROM (EIS_MSEAA3PF INNER JOIN EIS_OPNBB1PF ON EIS_MSEAA3PF.CUNO = EIS_OPNBB1PF.CUNO) INNER JOIN EIS_OPNAAYPF ON (EIS_OPNBB1PF.CUNO = EIS_OPNAAYPF.CUNO) AND (EIS_OPNBB1PF.ORIN = EIS_OPNAAYPF.ORIN)
WHERE (((EIS_OPNBB1PF.ACCP) Not In ('NON','VND')) AND ((EIS_OPNBB1PF.TSDT)=20040609) AND ((EIS_OPNBB1PF.ORTY) Not In ('SAM','VRT')) AND ((EIS_OPNBB1PF.SUCA)<>0) AND ((EIS_OPNAAYPF.OBSC) Not In ('MB','MS','RE')))
GROUP BY EIS_OPNBB1PF.TSDT, EIS_OPNBB1PF.LCCT, EIS_OPNBB1PF.ITEM, EIS_OPNBB1PF.CUNO, EIS_OPNBB1PF.VNBR, EIS_OPNBB1PF.ORIN, EIS_MSEAA3PF.SMAN;
Sample Data:
date Category invoice cust Sales Profit
6/9/04 dairy 8556 9999 $100 $10
6/9/04 meat 4552 1111 $200 $15
However, I am able to get total profit. My access query has sum(sales),
GP: (item * other fileds)
Group by date, category, cust, invoice.
The reason I do not want to do it from Excel is because my other queries, tables are stored in Access and it makes it easy to put everything in the database so I can link to Oracle, AS400 etc and launch multiple reports from Access database. My boss is wanting to install this application on his desktop so he can run the report himself on a daily basis.
The SALES field is datatype NUMBER so are some of my other fields in the query. It just baffles me why the pivot can handle calculation in other fields but not Sales.
Can you think of any other way I can run this from Access?
Query:
SELECT EIS_OPNBB1PF.TSDT AS [Date], EIS_OPNBB1PF.LCCT AS Catrgory, EIS_OPNBB1PF.ITEM AS Item, EIS_OPNBB1PF.CUNO AS [Customer No], EIS_OPNBB1PF.VNBR AS [Vendor No], EIS_OPNBB1PF.ORIN AS Invoice, EIS_MSEAA3PF.SMAN AS Salesperson, Sum(EIS_OPNBB1PF.IEPR) AS DailySales, Sum((([NPRC]-[ACGS])*(IIf([ITCI]='Y',[TTCW],[QNTY])))) AS [GPT$], Sum(EIS_OPNBB1PF.QNTY) AS Pieces, Count(EIS_OPNBB1PF.LN03) AS Lines
FROM (EIS_MSEAA3PF INNER JOIN EIS_OPNBB1PF ON EIS_MSEAA3PF.CUNO = EIS_OPNBB1PF.CUNO) INNER JOIN EIS_OPNAAYPF ON (EIS_OPNBB1PF.CUNO = EIS_OPNAAYPF.CUNO) AND (EIS_OPNBB1PF.ORIN = EIS_OPNAAYPF.ORIN)
WHERE (((EIS_OPNBB1PF.ACCP) Not In ('NON','VND')) AND ((EIS_OPNBB1PF.TSDT)=20040609) AND ((EIS_OPNBB1PF.ORTY) Not In ('SAM','VRT')) AND ((EIS_OPNBB1PF.SUCA)<>0) AND ((EIS_OPNAAYPF.OBSC) Not In ('MB','MS','RE')))
GROUP BY EIS_OPNBB1PF.TSDT, EIS_OPNBB1PF.LCCT, EIS_OPNBB1PF.ITEM, EIS_OPNBB1PF.CUNO, EIS_OPNBB1PF.VNBR, EIS_OPNBB1PF.ORIN, EIS_MSEAA3PF.SMAN;
