Complex formula regarding 1 field

[CMooreJr]

Hey all! I have a report that breaks down the total number of items sold during the month. It is broken down by week.

The user inputs the start and end date, and it pulls all items sold each week, and the cost. Now what they want is this.

From week 1, say we have "Item1" that sold 200 units.
Week 2, "Item1" sold 350 units. The client wants the percentage of change from those two figures.

My problem is, how do I do this since the report really only has the one field labeled "Items" and one labeled "Total Sold". The query pulls all the data and sorts it accordingly. In case I'm not making myself clear, the report looks something like this...

Week of ITEM Description Total Sold % Change
10/12/03 ATP3 3 piece lifestyle 209
10/19/03 ATP3 3 piece lifestyle 300 NEED THIS

How would I do this?

Thanks everyone!

 

[dhookom]

You might want to try add the previous Total Sold into the report's record source. Your query would be something like:
SELECT tblA.*,
(SELECT TOP 1 A.[TotalSold]
FROM tblA A
WHERE A.ITEM = tblA.ITEM
AND A.[Week Of]<tblA.[Week Of]
ORDER BY A.[Week Of] DESC) As PrevTotSold
FROM tblA;

You would need to substitute your table/query/field names.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[CMooreJr]

Hey Duane! The report is already running from an SQL query, so how can I add this? I completly see where you are coming from. I have made a new field on the report for &quot;PreviousSold&quot;...is there a simple control I can put on that text box to pull the data from the previous Item number? If it will help, I can send you the dbase, it's only about a meg. I appreachate anything you can help me with, these guys really want this....

(This was my code that failed)

SELECT tblInventoryTable.*,
(SELECT TOP 1 A.[UnitsSold]
FROM tblInventoryTable A
WHERE A.SKU = tblInventoryTable.SKU
AND A.[Week Of]<tblCompany.[Week Of]
ORDER BY A.[Week Of] DESC) As PrevTotSold
FROM tblInventoryTable;

 

[dhookom]

Modify the sql of the report's recordsource. I would not attempt to do this using only the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[CMooreJr]

Thanks Duane...how would I add that into this existing SQL? (As you can tell, I'm not a programmer...)

SELECT DISTINCT tblCompany.WeekBeginning, tblCompany.WeekEnding, tblInventoryTable.SKU, tblInventoryTable.Description, Sum(tblInventoryTable.UnitsSold) AS SumOfUnitsSold
FROM tblCompany INNER JOIN tblInventoryTable ON tblCompany.Company = tblInventoryTable.Company
GROUP BY tblCompany.WeekBeginning, tblCompany.WeekEnding, tblInventoryTable.SKU, tblInventoryTable.Description
HAVING (((tblCompany.WeekBeginning) Between [Enter Starting Date] And [Enter Ending Date]));

 

[dhookom]

I would save your above sql as a saved query if not already done. Assuming the name of this query is &quot;qtotInvWk&quot;. Then, create a new query based on qtotInvWk.

SELECT qtotInvWk.*,
(SELECT TOP 1 A.[SumOfUnitsSold]
FROM qtotInvWk A
WHERE A.SKU = qtotInvWk.SKU
AND A.[WeekBeginning]<qtotInvWk.[WeekBeginning]
ORDER BY A.[WeekBeginning] DESC) As PrevTotSold
FROM qtotInvWk;

I think I incorporated all your correct field names.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[CMooreJr]

OK...I think we almost have it...here is the code

------------
SELECT qryInventoryMonthEnd.*, (SELECT TOP 1 A.[SumOfUnitsSold]
FROM qryInventoryMonthEnd A
WHERE A.SKU = qryInventoryMonthEnd.SKU
AND A.[WeekBeginning]<qryInventoryMonthEnd.[WeekBeginning]
ORDER BY A.[WeekBeginning] DESC) AS PrevTotSold
FROM qryInventoryMonthEnd;

------------------

However when I run it, I get a &quot;Multi-Level GROUP BY Clause not allowed in Subquery&quot;

Any suggestions?

 

[CMooreJr]

OK...it works if I just run the query...it DOESNT work if I run the report....it gives me the error...shere should I look for this &quot;Group By&quot; clause?

 

[dhookom]

Are you sorting at all by the calculated column? I was worried that you might need to replace some part of this subquery with a domain aggregate function.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.