Proper Query Construction 2

[SanAntonioSpurFan]

I have 4 tables (Products,Dates,Stores,Sales). The products table contains the primary key UPC along with other fields. The Dates table contains primary Key WeekID along with other fields. The Stores table contains StoreID primary Key along with other fields. And the Sales table contains UPC, WeekID, StoreID, Dollars. Each primary key is related back to each individual table. The relationships look ok to me.

In the Dates table I have WeekID layed out until 2015 by incrementing numbers in the WeekID field. As each weeks data becomes available, I change the value of the DateSwitch Field from "N" to "Y" thus signaling this weeks data is now available.

I want to create a query that will do the following: Look up the highest value for WeekID in Date table where DateSwitch is equal to "Y". Otherwise I think it will pick a number that is equal to "N" and I don't care about those weeks since they have not occured. Once I know that value, say it's 2500 then I want it to prompt me for the number of weeks to sum up together. If I enter 12, It should use the between or something like between 2500 and 2488 to give me the sum of 12 weeks. I also want it to get the sales from last years 12 week period also on the same record.

So it would look like this:
UPC, StoreID, Current Sales, Last Year Sales

I'm not sure how to tell it to get all last year sales and all this year sales so my numbers all add up.

Each week I get data in the following layout
StoreID,WeekID,UPC,Dollars

I need the values on the same line to compute %Change vs LY on all items and other calculations.

Does anyone have any advise/directions I should take and how to best construct this query?

 

[lespaul]

You're probably going to have to create your SQL dynamically and get into VBA in order to accomplish what you're looking for.

Leslie

 

[scriverb]

This is a little tricky but let's give it try. My solution requires three(3) saved queries. You will only have to run the last one to get your results:

Save this SQL as [red]qryMaxWeek[/red]

SELECT Max(Dates.WeekID) AS MaxOfWeekID
FROM Dates
WHERE (((Dates.DateSwitch)="Y"));

Save this SQL as [red]qryDatesAndSales[/red]

SELECT Sales.UPC, Sales.StoreID, Sum(IIf([Sales]![WeekID] Between ([MaxOfWeekID]-[Enter # Weeks: ])-52 And [MaxOfWeekID]-52,[Sales]![Sales],0)) AS Sales_LastYear, Sum(IIf([Sales]![WeekID] Between [MaxOfWeekID]-[Enter # Weeks: ] And [MaxOfWeekID],[Sales]![Sales],0)) AS Sales_Current, (Sum(IIf([Sales]![WeekID] Between [MaxOfWeekID]-[Enter # Weeks: ] And [MaxOfWeekID],[Sales]![Sales],0)))-(Sum(IIf([Sales]![WeekID] Between ([MaxOfWeekID]-[Enter # Weeks: ])-52 And [MaxOfWeekID]-52,[Sales]![Sales],0))) AS Sales_Change
FROM Sales, qryMaxDate
WHERE (((Sales.WeekID) Between [MaxOfWeekID]-[Enter # Weeks: ] And [MaxOfWeekID] Or (Sales.WeekID) Between ([MaxOfWeekID]-[Enter # Weeks: ])-52 And [MaxOfWeekID]-52))
GROUP BY Sales.UPC, Sales.StoreID
ORDER BY Sales.UPC, Sales.StoreID;

Save this SQL as [red]qrySalesRollup[/red]

SELECT qryDatesAndSales.UPC, qryDatesAndSales.StoreID, qryDatesAndSales.Sales_LastYear, qryDatesAndSales.Sales_Current, qryDatesAndSales.Sales_Change, Switch([qryDatesAndSales]![Sales_LastYear]=0,"N/A",[qryDatesAndSales]![Sales_Change]=0,"-100",True,[qryDatesAndSales]![Sales_Change]/IIf([qryDatesAndSales]![Sales_LastYear]=0,1,[qryDatesAndSales]![Sales_LastYear])*100) AS Percent_Chng
FROM qryDatesAndSales;

Now just run qrySalesRollup. I have tested this a small amount of data and it looks okay. Please test it against your stuff and let me know.

Post back with any questions.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[lespaul]

Even if that doesn't work, it deserves a STAR! I'm impressed that you could get all that in a few queries!

Leslie

 

[scriverb]

lespaul: Thanks for the star and the kind words. I had fun with this one. Hope it works as advertised. On my limited tables it seems to do the job.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SanAntonioSpurFan]

Bob,

I just tried the SQL and it works perfect! Thanks for taking the time to help!

 

[scriverb]

Great!!! Thanks for the Star. It is really appreciated. This one was really fun for me.

By the way, Go Pistons!!!!!

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]