Printing Each Month in a Quarter on a Separate Line

  • Thread starter Thread starter sara
  • Start date Start date
S

sara

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
 
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
 
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


.
Click to expand...
.
Click to expand...
 
Wow that is confusing. You are right, it is not quite
arranged in the manner ideal for data bases.
Any way. If in the final query you currently have you
would include the FY and FQuarter, you would be able to
write one more query resulting in comparative adjacent
numbers on your report.
You could group by fquarter, sort by fmonth.
Let me know if you have these data elements available in
your current final query.
Fons
-----Original Message-----
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


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
This is it! You know, sometimes the most difficult step
is the obvious/easiest. All I had to do was Group and
Sort (I only had sort by Store in the report). Just
to "redeem" myself, I want you to know that I am the only
IT/software person in the company, so I have no one to
bounce ideas off of, and to look at these "knee
slappers". (I am training a new person - she took Access
Intro and is doing ok, but can't review and debug for me
yet)

I really appreciate your help, and this newsgroup overall.

Have a great weekend. Thanks again.
Sara
-----Original Message-----
Wow that is confusing. You are right, it is not quite
arranged in the manner ideal for data bases.
Any way. If in the final query you currently have you
would include the FY and FQuarter, you would be able to
write one more query resulting in comparative adjacent
numbers on your report.
You could group by fquarter, sort by fmonth.
Let me know if you have these data elements available in
your current final query.
Fons
-----Original Message-----
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
Click to expand...
([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


.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Glad it worked for you, we both learned some more.
Fons
-----Original Message-----
This is it! You know, sometimes the most difficult step
is the obvious/easiest. All I had to do was Group and
Sort (I only had sort by Store in the report). Just
to "redeem" myself, I want you to know that I am the only
IT/software person in the company, so I have no one to
bounce ideas off of, and to look at these "knee
slappers". (I am training a new person - she took Access
Intro and is doing ok, but can't review and debug for me
yet)

I really appreciate your help, and this newsgroup overall.

Have a great weekend. Thanks again.
Sara
-----Original Message-----
Wow that is confusing. You are right, it is not quite
arranged in the manner ideal for data bases.
Any way. If in the final query you currently have you
would include the FY and FQuarter, you would be able to
write one more query resulting in comparative adjacent
numbers on your report.
You could group by fquarter, sort by fmonth.
Let me know if you have these data elements available in
your current final query.
Fons
-----Original Message-----
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".
Click to expand...
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 -
Click to expand...
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
Click to expand...
have
a
class coming up in early July, but I can't wait for then
to get this report out.
Thanks,
Sara


.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top