Monthly Totals Plus YTD by Item

[Guest]

I have a sales records table with the following fields - FiscMonth, CustNo,
Product, SalesQuan, SalesVal, ShipDate.

One of our customers would like their sales data summarised as follows -
Product, Tot Jan Sales, Tot Feb Sales, Total Mar Sales etc., YTD Sales,
Price Per (I was going to use the SalesVal / SalesQuan from most recent
transaction to calculate this)

Any help would be appreciated

 

[Duane Hookom]

Have you looked into using a crosstab query?

 

[Guest]

Yes, the crosstab query gets me most of the way -

TRANSFORM Sum(['05 Sales].M2Shipped) AS SumOfM2Shipped
SELECT ['05 Sales].CustNo, ['05 Sales].Product, ['05 Sales].ItemDescription,
['05 Sales].ProdCls, Sum(['05 Sales].M2Shipped) AS [Total Of M2Shipped]
FROM ['05 Sales]
WHERE (((['05 Sales].CustNo)=345511)) OR (((['05 Sales].CustNo)=258808))
GROUP BY ['05 Sales].CustNo, ['05 Sales].Product, ['05
Sales].ItemDescription, ['05 Sales].ProdCls
ORDER BY ['05 Sales].Product
PIVOT ['05 Sales].FisMon;

It's the 'last price charged' info that I am struggling with.

 

[Duane Hookom]

The simplest method would be to create a totals query that finds the most
recent sale record by product. You can combine this query with a query of
products and all dates to find out the last price charged.

--
Duane Hookom
MS Access MVP

Yes, the crosstab query gets me most of the way -

TRANSFORM Sum(['05 Sales].M2Shipped) AS SumOfM2Shipped
SELECT ['05 Sales].CustNo, ['05 Sales].Product, ['05
Sales].ItemDescription,
['05 Sales].ProdCls, Sum(['05 Sales].M2Shipped) AS [Total Of M2Shipped]
FROM ['05 Sales]
WHERE (((['05 Sales].CustNo)=345511)) OR (((['05 Sales].CustNo)=258808))
GROUP BY ['05 Sales].CustNo, ['05 Sales].Product, ['05
Sales].ItemDescription, ['05 Sales].ProdCls
ORDER BY ['05 Sales].Product
PIVOT ['05 Sales].FisMon;

It's the 'last price charged' info that I am struggling with.

Have you looked into using a crosstab query?