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? The SQL code for the query looks like this as of now and all works great except for getting the % Change to work...

-------code starts---------

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]));

Thanks all!

Thanks everyone!

 

[Blorf]

I don't know if you can do this by simply using the report. I have always had to use VBA to do these calculations into a table, and then run the report against that table. If no one is able to help you with just using the report, I will be happy to help you with that.

ChaZ

 

[CMooreJr]

Hey ChaZ!

Yeah, I see it's gonna take VB to do it, and I'm not up to speed on my programming skills. I have tried and this is what I have come up with....I have the main query that looks like this....

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

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]));

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

and then another query that runs from that query that is bound to the report. However, I get a "Multi Level GROUP BY error" when I try to run it. (I was trying to pull in the previous figure) If you know a better way, please let me know!

----------Code for Report Query-----------

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;

 

[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?

 

[Blorf]

I think the real problem is that in a query, you cant pull from the previous record, at least if you can, I have never figured out how.

Here is what I suggest, since you are not up to speed on vba.

Write a make table query that has all the fields you need for the report, and a blank field for the previous pecentage. like the following:

SELECT Alias.PartID, Alias.AliasID, Alias.Type, 1.11 AS BlankPct INTO RptTable
FROM Alias;

Use 1.11 so it forces a type double field in the new table. Make sure you sort the query in the same manner the report will print.

When you are done, send me your field names, and I will write the vb code you need. The vb code will open your table, and move from one record to the next and put the correct percentage in each record.

It is not very complex, you will see, after we are done.

 

[JonFer]

You can calculate the percentage change by pulling in two instances of the summary query and putting a criteria that the date from the first instance is 7 days before the date from the second instance. With just this criteria, it will not show the first week because it won't find a previous week's record but you can fix that by starting a week earlier than what the user requests.