Can not SUM on Sales field

[bhandarip]

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.
What am I doing wrong? Any help will be highly appreciated.
Thank you,
Prad

 

[Norie]

Why not just import the 'raw data' from Access into an Excel spreadsheet then create a pivot from there.

 

[bhandarip]

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?

Thank you very much!
Prad

 

[texasalynn]

Try posting it to an access forum

http://www.mrexcel.com/board2/viewforum.php?f=14

HTH . . . texasalynn

 

[Norie]

Are you trying to get the total profit?

If it's in a report you should be able to use an unbound textbox with =SUM([profit]).

Don't place it in the detail sectioon, put it in a header/group section.

 

[bhandarip]

No, I am trying to get Daily Sales. Here is my access query: Thanks!

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;

 

[Norie]

Try the Access forum.