Printing Each Month in a Quarter on a Separate Line

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Fons Ponsioen (anonymous_at_discussions.microsoft.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 09:36:26 -0700

Hi Sara.
I am a little confused by your descriptions.
I assume you have a table somewhat like the following.
Store#, SalesDate, Transactions#, Sales$. Where the
SalesDate is the real Date.
The SalesDate is is in the short date format.
So:
 Month([SalesDate]-1) is the FiscalMonth I'll use FMonth
 Year([SalesDate]-31) is the FiscalYear I'll use FYear
So in the query for your report;
FMonth: IIf(Month([SalesDate])-1=0,12,Month([SalesDate])-1)
FYear: Year([SalesDate]-31)

Now you can group your report by store# and sort based on
the FYear and FMonth.
I hope this helps to get you started.
FOns
>-----Original Message-----
>I'm in Retail, so we have funky quarters and fiscal
>years. Each quarter is 90 days, and fiscal year is Feb-
>Jan, so I have a table called "AllDates" where each row
>has all the date info one could need - saleDate, Week#,
>FiscalMonthNum, FiscalQtrNum, start and end dates for
each
>(week, month, qtr, year) and also has all the same for LY
>(everything in retail is about "how does this compare
>against last year for the same sale date?)
>
>I have a query where the user enters FiscalYear and Qtr
as
>parameters, and I pull all the data needed for the
report -
> it's sales$ and transactions (and averages...). The raw
>data is by hour; I'm just summing/averaging for the month.
>
>I want my report to print:
>
>Store #1 (Group Header)
>February $$ ### $$$ (etc)
>March $$ ### $$$
>April $$ ### $$$
>QTR TOTAL $$$ #### $$$$
>
>Where the Feb, Mar, April lines are detail.
>
>I can't figure out how to do it! My query pulls the
right
>data, I have month number (1,2,3) and all the totals, but
>I don't know how to print the 3 months in a row in
>detail. I tried an "If" statement, but I ended up with
>loads of rows with no data, though my data did show up
>under the store:
>Feb
>Mar data
>April
>Feb
>Mar
>April Data
>Feb
>Mar
>April
>Feb data
>or something like that.
>
>I hope this is clear and I appreciate the help. I have a
>class coming up in early July, but I can't wait for then
>to get this report out.
>Thanks,
>Sara
>
>
>.
>

Relevant Pages