Working with Consolidated Data - Working it backwards?

[Joycelet]

Hi

I've got a database that imports weekly sales figures by product by branch - so far so good.

Now been told these figures are cumalative figures week on week but they want to show the data by weekending data - so I need to work it backwards each week (I think this is the easiest way!)

I have created a table that imports the data and then created a query that gives me the Total By Product By Branch, I've then created another query that gives me the total for this weeks only and then a third query that takes the total away from this weeks but my problem is that the total includes this weeks cumlative figures as well so the calc isn't right

Any ideas on how best to do this greatly appreciated...

 

[lupins46]

You need to spell out what you are trying to achieve.

For example, to get this weeks values you need this weeks import and last weeks import and take one away from the other. That is a straightforward query so long as you keep the data identifiable by week.

But I got confused by the description of the queries cos there seemed to be too much there just to do what I have described.

 

[Joycelet]

Hi

Sorry I'm confusing myself - I'll try and explain it better.

Week 1 Figures
Week 2 Figures - are week 1 + week 2
Week 3 Figures - are week 1 + week 2 + week 3

What I need to do is extract the actual week 2 figures and append them to my actual week figures table so that it show the actual amount sold in week 1 and week 2 and week 3 etc.

For example:

Product X
16/10/2005
Qty 3

Product X
23/10/2005
Qty 4

Product X
29/10/2005
Qty 6

So first week is fine actually sold 3

Second week sold 1

Third week sold 2

Does this make sense?

Thanks

 

[PHV]

Something like this ?
SELECT A.branch, A.product, A.date, A.Qty, A.Qty-Nz(B.Qty,A.Qty) As Sold
FROM yourTable AS A LEFT JOIN yourTable AS B ON A.branch = B.branch AND A.product = B.product AND A.date > B.date
WHERE Nz(B.date,0)=Nz((SELECT Max([date]) FROM yourTable WHERE branch=A.branch AND product=A.product AND [date]<A.date),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Joycelet]

Hi PH

Thanks for that - but I'm not that good with Access SQL so not sure what your query is showing and how I'd get it to work. Would you be able to show in simpler terms? Apologies for being a numpty!

N