Creating query to batch print reports

[Guest]

Hello,

I'm not sure if this is the correct place to post this, but here goes:

I created a lease management database to house lease/rent payment
information for over 200 stores. Every month, we have to print a rent
remittance report to send with each rent payment.

I created the report based on a parameter query. The remittance shows a
breakdown of the current payment, and, if we pay percentage rent for a
location, we need to show the monthly sales for the current year. Here's the
problem: We
need to show the sales for the lease year (if a lease was signed June 1st, we
need to show sales for June through May of the current year). The parameter
query solves this problem, as we manually input the beginning and end
months. But it's very tedious printing more than 60 reports this way.

What I need to figure out is, can we somehow batch print the reports? The
criteria is:
1) is there percentage rent, if yes, show sales
2) look at lease start date and determine lease year months
3) print ALL reports

Can this be done (I have NO experience with code)?

Any help would be appreciated. Thanks in advance.

 

[Allen Browne]

So you need to print a year's worth of records, from the first of the month
the lease was signed?

Subtract the month of the lease from the starting date, and you can get the
year the lease started by typing this into a fresh column in the Field row
in query design:
TheYear: Year(DateAdd("m", -Month([LeaseStartDate])+1, 1))
replacing LeaseStartDate with the name of your field.

You can now Group By this field (totals query) to get each year's income, or
use critiera to limit it to a particular year number.

 

[Guest]

Thanks Allen, but I wasn't clear, what I need to show the sales as follows:

January $1,000,000
February $2,000,000
March $3,000,000

The Sales table fields are: StoreID, SalesDate, Sales

I hope that clarifies my question.

Thanks again,
D

So you need to print a year's worth of records, from the first of the month
the lease was signed?

Subtract the month of the lease from the starting date, and you can get the
year the lease started by typing this into a fresh column in the Field row
in query design:
TheYear: Year(DateAdd("m", -Month([LeaseStartDate])+1, 1))
replacing LeaseStartDate with the name of your field.

You can now Group By this field (totals query) to get each year's income, or
use critiera to limit it to a particular year number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I'm not sure if this is the correct place to post this, but here goes:

I created a lease management database to house lease/rent payment
information for over 200 stores. Every month, we have to print a rent
remittance report to send with each rent payment.

I created the report based on a parameter query. The remittance shows a
breakdown of the current payment, and, if we pay percentage rent for a
location, we need to show the monthly sales for the current year. Here's
the
problem: We
need to show the sales for the lease year (if a lease was signed June 1st,
we
need to show sales for June through May of the current year). The
parameter
query solves this problem, as we manually input the beginning and end
months. But it's very tedious printing more than 60 reports this way.

What I need to figure out is, can we somehow batch print the reports? The
criteria is:
1) is there percentage rent, if yes, show sales
2) look at lease start date and determine lease year months
3) print ALL reports

Can this be done (I have NO experience with code)?

Any help would be appreciated. Thanks in advance.

 

[Guest]

Basically, I need Access to determine the start month, end month, then show
the sales for those months for the current year.

Thanks

So you need to print a year's worth of records, from the first of the month
the lease was signed?

Subtract the month of the lease from the starting date, and you can get the
year the lease started by typing this into a fresh column in the Field row
in query design:
TheYear: Year(DateAdd("m", -Month([LeaseStartDate])+1, 1))
replacing LeaseStartDate with the name of your field.

You can now Group By this field (totals query) to get each year's income, or
use critiera to limit it to a particular year number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I'm not sure if this is the correct place to post this, but here goes:

I created a lease management database to house lease/rent payment
information for over 200 stores. Every month, we have to print a rent
remittance report to send with each rent payment.

I created the report based on a parameter query. The remittance shows a
breakdown of the current payment, and, if we pay percentage rent for a
location, we need to show the monthly sales for the current year. Here's
the
problem: We
need to show the sales for the lease year (if a lease was signed June 1st,
we
need to show sales for June through May of the current year). The
parameter
query solves this problem, as we manually input the beginning and end
months. But it's very tedious printing more than 60 reports this way.

What I need to figure out is, can we somehow batch print the reports? The
criteria is:
1) is there percentage rent, if yes, show sales
2) look at lease start date and determine lease year months
3) print ALL reports

Can this be done (I have NO experience with code)?

Any help would be appreciated. Thanks in advance.

 

[Allen Browne]

So you have a PaymentDate field, and you want all payments by *month* of the
lease year?

You could do that by typind an expression like this into the Field row:
MonthNum: DateDiff("m", [LeaseStartDate], [PaymentDate]) + 1

Then GroupBy that field, and Sum the Amount field to get the total for the
calendar month. The first month of the lease will come out as month 1, the
next month as month 2, etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Basically, I need Access to determine the start month, end month, then
show
the sales for those months for the current year.

Thanks

So you need to print a year's worth of records, from the first of the
month
the lease was signed?

Subtract the month of the lease from the starting date, and you can get
the
year the lease started by typing this into a fresh column in the Field
row
in query design:
TheYear: Year(DateAdd("m", -Month([LeaseStartDate])+1, 1))
replacing LeaseStartDate with the name of your field.

You can now Group By this field (totals query) to get each year's income,
or
use critiera to limit it to a particular year number.

Hello,

I'm not sure if this is the correct place to post this, but here goes:

I created a lease management database to house lease/rent payment
information for over 200 stores. Every month, we have to print a rent
remittance report to send with each rent payment.

I created the report based on a parameter query. The remittance shows a
breakdown of the current payment, and, if we pay percentage rent for a
location, we need to show the monthly sales for the current year.
Here's
the
problem: We
need to show the sales for the lease year (if a lease was signed June
1st,
we
need to show sales for June through May of the current year). The
parameter
query solves this problem, as we manually input the beginning and end
months. But it's very tedious printing more than 60 reports this way.

What I need to figure out is, can we somehow batch print the reports?
The
criteria is:
1) is there percentage rent, if yes, show sales
2) look at lease start date and determine lease year months
3) print ALL reports

Can this be done (I have NO experience with code)?

Any help would be appreciated. Thanks in advance.

 

[John W. Vinson]

Thanks Allen, but I wasn't clear, what I need to show the sales as follows:

January $1,000,000
February $2,000,000
March $3,000,000

The Sales table fields are: StoreID, SalesDate, Sales

I hope that clarifies my question.

You'll need a totals query summing sales over the month and grouping by month:

SELECT Format([SaleDate], "mmmm"), Sum([Sales])
FROM yourtable
GROUP BY Year([SaleDate]), Month([SaleDate]), Format([SaleDate], "mmmm");


John W. Vinson [MVP]