No, not exactly!
From: sara (anonymous_at_discussions.microsoft.com)
Date: 05/13/04
- Next message: Marty: "Print one record only from Form to Report"
- Previous message: Marshall Barton: "Re: Columns in the detail section"
- In reply to: Fons Ponsioen: "Printing Each Month in a Quarter on a Separate Line"
- Next in thread: Fons Ponsioen: "No, not exactly!"
- Reply: Fons Ponsioen: "No, not exactly!"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 12 May 2004 17:10:22 -0700
No, what you've got sounds like it would work, but it
won't - allow me to provide the details that clarify.
First, let me explain the data:
1 table is AllDates. There are 365 rows in the table for
each year. In each row, is the DOW, DOY, Date, WeekNum,
MonthNum, QtrNum, YearNum, Start and EndDate for Week,
Month, Quarter and Year - and all the corresponding dates
for Last year. (Just to be more confusing, FY05 is Feb 1
04 thru Jan 29 2005. The months rarely start or end on
the first or last day of the calendar month; they always
start on a Sunday and end on a Saturday. So, calculating
the Fiscal month is impossible: May 2, 2004 is FY05,
Month 03 - that's why the separate table is needed. It is
so confusing, too, that the table (though maybe not the
best database design), guarantees getting the correct date
(s) and minimizes confusion for anyone who wants to look
at the query or report. Also, we always display ThisYear
and LastYear on all reports, including the corresponding
dates. There's no way to calculate that, that I know of.
For example, April 30, 2004 has LY date of May 2, 2003).
That's the DATES data.
The sales data is sent to us from a third party and I
import it into tables for our use. So changes are not
allowed (or if they are absolutely necessary, are very
costly - almost $1000 to change a field heading
from "Over/-Short" to "OverShort"!)
The data is SaleDate, Store#, Hour, #Cust, Sales$,
Avg#Txn, Avg$perTxn, %Cust (etc.)
When HOUR = 23:59, that means "Totals for the Day". SO, I
have a query that pulls the "23:59" record for each day
for This Year, based on pulling the TY and LY dates from
the ALLDates table, based on the user typing in Year and
FiscalQuarterNumber. Then I use that query (pulling all
its data fields in) and Year -1 for LY data in another
query, and end up with:
(Fields in Parens are in the query, not in the output)
MonthName MonthNum Store (Hour) #Cust $Sales Avg#Txn
Avg$perTxn
where The #Cust, $Sales is the SUM of all the 23:59
records for the month, and the Avg#Txn is the Average of
all the 23:59 records for the month.
I get ONE record per month per store with ALL the data - I
know what is from LY field name as I rename it in the
final query (all begin with LY):
February 1 15 4530 23456.33 450.5 45.65
(the above is an example for Store 15)
I want my report to print:
Hourly comparison for Q1 2005 (Q1 and 2005 are parameters
from the user)
Store 15 TotCust TY/LY Sales$ TY/LY AvgTxn/Cust TY/LY
February 4530 4420 23456.33 12335.02 450.5 440.6
March 2621 2088 12456.82 19699.77 130.3 104.1
April 3067 4109 20345.98 22337.56 226.2 320.8
Qtr TOT ### (whatever the totals would be)
I can't figure out how to get the data out of my query
(columns are month, store and all the data fields with one
row per month per store) into the report as above. I
figure that if I did 3 separate queries and then pulled
them together in a fourth it might work, but I was hoping
to use what I had done here already.
Hope this clarifies. It is NOT as simple as it first
sounds - those dates and the way we have our data really
hinder us.
Thanks so very much,
Sara
>-----Original Message-----
>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
>>
>>
>>.
>>
>.
>
- Next message: Marty: "Print one record only from Form to Report"
- Previous message: Marshall Barton: "Re: Columns in the detail section"
- In reply to: Fons Ponsioen: "Printing Each Month in a Quarter on a Separate Line"
- Next in thread: Fons Ponsioen: "No, not exactly!"
- Reply: Fons Ponsioen: "No, not exactly!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
