Query Dates for 12 months

[Guest]

Help!! I've got a database table that has monthly sales, as well as utility
and rental payment information for 150 stores. I want to create a query that
will show monthly sales for each store for a 12 month period based on a lease
start date field in the table. Example:

Lease start date is 04/01/05, I would like to show sales for 12 months
beginning 04/01/05. Also, since we have data that encompasses several years,
I'd like to show only the more recent 12 month sales period. Does this make
sense to anyone but myself? Can this be done?

I am learning Access by doing, so step by step instructions would be
appreciated. Thank you.

 

[Duane Hookom]

I am somewhat confused sinse you want the calculations based on both the
start date and the most recent 12 months? We don't know your table or field
names, what your actual data looks like, or what you want your final output
to look like.

 

[Guest]

Sorry for the confusion. One of the tables in the database is a summary of
monthly payments made per store. The fields are: Store Number, Payment Date
(the 1st of every month), Sales, Rent, Property Tax, Utilities, etc.

The form I've created is a "recap" of all of the lease information. I'm
trying to create a subform that will show just the sales information for the
most recent lease year. Some of the stores pay rent based on a percentage of
their monthly, quarterly or yearly sales. But each of these years is
determined by the Lease start date. So in order to calculate the percentage
rent, I need to show the sales for the current year based on the Lease start.
Also, since the leases usually span 5 or more years, I collect all monthly
sales, but on the subform, I need to show only the most recent years' sales.
So if the lease starts in April, I need to show sales from April to March of
the current 12 month period.

I hope this clears up some of the confusion.

 

[Duane Hookom]

So, then "for 12 months" doesn't make sense because you are only wanting to
display values from the most recent lease date anniversary.
What kind of details do you want to display? Monthly values or totals?

 

[Guest]

I just want to show the month and the monthly values.

 

[Duane Hookom]

You provided some field names but not anything that looked like a lease
start date.

Sorry I have so many questions but I am not willing to make WAGs regarding
your structures.

 

[Guest]

Here are the fields:

The fields are: Store Number, Lease start date (mm/dd/yy formatted to only
show mmmm in datasheet view), monthly sales, payment date, rent, rent
adjustment, property tax, utilities, other, total, GST, Chq. Total, Chq.
Number, Comments.

I'm not sure what other information I can provide. Basically, it's a
datasheet view of all monthly payments made on each store. All I want to do
is look at the current 12 months payments based on the lease start date. So
instead of viewing ALL payments made and ALL monthly sales, I just want to
see data for the last 12 months based on the lease start date: If a lease
starts in April, I want to see data from April through June, or if the lease
starts in October, I want to see data from October to September.

I hope this clarifies it. What I want is very clear in my head, but putting
it down in writing isn't so easy.

Thanks for taking the time to help me try and figure it out!

 

[Duane Hookom]

So [Lease Start Date] is a field in the same table as a field [Payment
Date]. If there are 20 records for [Store Number] = "123" they all have the
same [Lease Start Date].
"If a lease starts in April, I want to see data from April through June"
that is either 3 months or 15 months.

All I would really like to see is some sample records with just the
significant fields like StoreNumber, LeaseStartDate, MonthlySales,
PaymentDate. I don't think any others pertain to your question.

Then, provide exactly how these sample records would appear in the
form/subform.

 

[Guest]

Sorry, I mis-typed the dates. I meant sales from April through March.

It would be easier if you could look at the database structure yourself.
But, in the meantime, I'll try to explain further. Yes, the data I'm trying
to query is NOW in one table. (Note, I've pulled this information together
from many different Excel spreadsheets that were given to me.) I moved the
LeaseStartDate to this table thinking it would be easier to manipulate this
data. The Store Number is the primary key in all of the tables (the main
table being the Stores table with store addresses, managers, etc.).

On the main form, you navigate through the records by store number. In this
table, the store number column is hidden because all of the information on
the main form pertains to the current store number. Make sense? The Lease
Start Date will be shown in another section of the form, but is integral to
the viewing of the data in this subform.

The records shown in this subform would be something like this:

[Payment Date], [Monthly Sales], [Basic Rent], [Property Taxes], [Utilities],
[SubTotal], [GST], [Chq Total], [Chq No].

Again, I'm sorry this is so confusing. My frustration with this database is
beginning to show.

Thanks again,

Debbie

So [Lease Start Date] is a field in the same table as a field [Payment
Date]. If there are 20 records for [Store Number] = "123" they all have the
same [Lease Start Date].
"If a lease starts in April, I want to see data from April through June"
that is either 3 months or 15 months.

All I would really like to see is some sample records with just the
significant fields like StoreNumber, LeaseStartDate, MonthlySales,
PaymentDate. I don't think any others pertain to your question.

Then, provide exactly how these sample records would appear in the
form/subform.
--
Duane Hookom
MS Access MVP
--

Here are the fields:

The fields are: Store Number, Lease start date (mm/dd/yy formatted to
only
show mmmm in datasheet view), monthly sales, payment date, rent, rent
adjustment, property tax, utilities, other, total, GST, Chq. Total, Chq.
Number, Comments.

I'm not sure what other information I can provide. Basically, it's a
datasheet view of all monthly payments made on each store. All I want to
do
is look at the current 12 months payments based on the lease start date.
So
instead of viewing ALL payments made and ALL monthly sales, I just want to
see data for the last 12 months based on the lease start date: If a lease
starts in April, I want to see data from April through June, or if the
lease
starts in October, I want to see data from October to September.

I hope this clarifies it. What I want is very clear in my head, but
putting
it down in writing isn't so easy.

Thanks for taking the time to help me try and figure it out!

 

[Duane Hookom]

I'm not sure what is so difficult with providing sample records. Even the
table name would have helped.

However I would create a function as noted below that would return the date
of the most recent anniversary. You can then use this in a query
Consider this sql to return payments since the most recent anniversary:
SELECT [One Table].*
FROM [One Table]
WHERE [Payment Date]>=GetLastAnniv([LeaseStartDate]);


Function GetLastAnniv(datAnniv As Date) As Date
'function to get the most recent anniversary date
If Format(datAnniv, "mmdd") > _
Format(Date, "mmdd") Then
GetLastAnniv = DateSerial(Year(Date) - 1, _
Month(datAnniv), Day(datAnniv))
Else
GetLastAnniv = DateSerial(Year(Date), _
Month(datAnniv), Day(datAnniv))
End If
End Function


--
Duane Hookom
MS Access MVP
--

Sorry, I mis-typed the dates. I meant sales from April through March.

It would be easier if you could look at the database structure yourself.
But, in the meantime, I'll try to explain further. Yes, the data I'm
trying
to query is NOW in one table. (Note, I've pulled this information
together
from many different Excel spreadsheets that were given to me.) I moved
the
LeaseStartDate to this table thinking it would be easier to manipulate
this
data. The Store Number is the primary key in all of the tables (the main
table being the Stores table with store addresses, managers, etc.).

On the main form, you navigate through the records by store number. In
this
table, the store number column is hidden because all of the information on
the main form pertains to the current store number. Make sense? The
Lease
Start Date will be shown in another section of the form, but is integral
to
the viewing of the data in this subform.

The records shown in this subform would be something like this:

[Payment Date], [Monthly Sales], [Basic Rent], [Property Taxes],
[Utilities],
[SubTotal], [GST], [Chq Total], [Chq No].

Again, I'm sorry this is so confusing. My frustration with this database
is
beginning to show.

Thanks again,

Debbie

So [Lease Start Date] is a field in the same table as a field [Payment
Date]. If there are 20 records for [Store Number] = "123" they all have
the
same [Lease Start Date].
"If a lease starts in April, I want to see data from April through June"
that is either 3 months or 15 months.

All I would really like to see is some sample records with just the
significant fields like StoreNumber, LeaseStartDate, MonthlySales,
PaymentDate. I don't think any others pertain to your question.

Then, provide exactly how these sample records would appear in the
form/subform.
--
Duane Hookom
MS Access MVP
--

Here are the fields:

The fields are: Store Number, Lease start date (mm/dd/yy formatted to
only
show mmmm in datasheet view), monthly sales, payment date, rent, rent
adjustment, property tax, utilities, other, total, GST, Chq. Total,
Chq.
Number, Comments.

I'm not sure what other information I can provide. Basically, it's a
datasheet view of all monthly payments made on each store. All I want
to
do
is look at the current 12 months payments based on the lease start
date.
So
instead of viewing ALL payments made and ALL monthly sales, I just want
to
see data for the last 12 months based on the lease start date: If a
lease
starts in April, I want to see data from April through June, or if the
lease
starts in October, I want to see data from October to September.

I hope this clarifies it. What I want is very clear in my head, but
putting
it down in writing isn't so easy.

Thanks for taking the time to help me try and figure it out!

:

You provided some field names but not anything that looked like a
lease
start date.

Sorry I have so many questions but I am not willing to make WAGs
regarding
your structures.

--
Duane Hookom
MS Access MVP
--

I just want to show the month and the monthly values.

:

So, then "for 12 months" doesn't make sense because you are only
wanting
to
display values from the most recent lease date anniversary.
What kind of details do you want to display? Monthly values or
totals?

--
Duane Hookom
MS Access MVP
--

Sorry for the confusion. One of the tables in the database is a
summary
of
monthly payments made per store. The fields are: Store Number,
Payment
Date
(the 1st of every month), Sales, Rent, Property Tax, Utilities,
etc.

The form I've created is a "recap" of all of the lease
information.
I'm
trying to create a subform that will show just the sales
information
for
the
most recent lease year. Some of the stores pay rent based on a
percentage
of
their monthly, quarterly or yearly sales. But each of these
years
is
determined by the Lease start date. So in order to calculate the
percentage
rent, I need to show the sales for the current year based on the
Lease
start.
Also, since the leases usually span 5 or more years, I collect
all
monthly
sales, but on the subform, I need to show only the most recent
years'
sales.
So if the lease starts in April, I need to show sales from April
to
March
of
the current 12 month period.

I hope this clears up some of the confusion.

:

I am somewhat confused sinse you want the calculations based on
both
the
start date and the most recent 12 months? We don't know your
table
or
field
names, what your actual data looks like, or what you want your
final
output
to look like.
--
Duane Hookom
MS Access MVP
--

Help!! I've got a database table that has monthly sales, as
well
as
utility
and rental payment information for 150 stores. I want to
create
a
query
that
will show monthly sales for each store for a 12 month period
based
on a
lease
start date field in the table. Example:

Lease start date is 04/01/05, I would like to show sales for
12
months
beginning 04/01/05. Also, since we have data that encompasses
several
years,
I'd like to show only the more recent 12 month sales period.
Does
this
make
sense to anyone but myself? Can this be done?

I am learning Access by doing, so step by step instructions
would
be
appreciated. Thank you.