weekly reports

[khurram7x]

I've learned quite a bit of VBA till now, but I've very limited knowledge about dealing with dates.

I've a table full of quantities installed in a field on daily basis. In other more familiar words, it is like record of sale for a specific product in a shop on daily basis.

Let's say, standard week is from Monday - Sunday. Now I want to generate a weekly report based on this daily sale table every Sunday, in such a way that product sales from a previous week will be shown in a Calculated Previous_Week_Sales field and Current_Week_Sales for current or, just finished week. Result of this query then should be added as a new record in a table.

It seems to me like a group query, but I'm not getting a respectable clue about how to do it exactly.

Thank you for any help.

K

 

[Minty]

Break this task down into small bits.
First create a query that gives you the last weeks data.
Then create another similar query that gives you the the previous weeks data.
You may be able to get your totals from the second query immediately. If not create a third query based on the second one that gives you the totals you require.
Finally create a summary query that pulls the others together.

 

[CJ_London]

you could try having a calculated column grouped in your query

Period:Iif(Invdate >Date()-7,"Current Week",iif(Invdate between Date-15 and Date-7,"Previous Week","Earlier"))

then filter would be <>"Earlier"

then you can crosstab it if required