Format report, fixed height for entries that must shrink?

[Guest]

Please forgive the length, I will try to condense this explanation as much as
possible while still conveying the essentials. I am trying to create a
report which prints 2-to-a-page "receipts," essentially, to hand out to
customers when they donate items to our organization. I have been struggling
for several weeks to find a way to format this report so that each entry is
exactly 1/2-page, so I can print out a daily report (~20-30 pages) and cut
the stack in half with a paper cutter. The report pulls from a query which
pulls from 2 tables, customer info and donation info (detail section.)

Formatting the detail section was the first problem. When the order is
taken from a list of about 55 items, a quantity is typed in for which items
they are donating, and I only want those entries with a quantity to show up
on the report, the other ones to disappear and the whole section to collapse
so there's not empty space. Ideally, the list would be arranged in 4 columns
for a cleaner layout, but I couldn't figure out how to make items from all 4
columns align up to the top, squeezing out the empty space left by the items
not showing. I solved this by designing the report with all 55 items in one
long column to the left, aligning them all to the top, and setting everything
to Grow/Shrink, then using "Me!item_Label.Visible = Not IsNull(Me!item) on
format (one for each item in the list).

Problem is, this solution allowed each entry to shrink up and not take up
extra space, but I can't set the section to a fixed height because the
single-column list of items in the design view cannot fit within that height.
Between the header and footer for each entry and all the donation info, I
absolutely cannot get the design of the report to fit within a 5.5" height,
even though each receipt in print view will always be less because most of
that column disappears (there's usually only quantities entered for a handful
of items on the list.)

I even tried setting the report to landscape so the 2-to-a-page are side by
side, hoping some kind of column formatting would work, but I can't get that
to go either.

Is it possible to either set a fixed height for the print layout only, or
modify my 55-item list into 4 columns so that they tidy themselves up
properly when I'm only printing a receipt with a few items? (The horizontal
shrink seems to be the hangup.) I built this project because I thought I had
a decent knowledge of access, but unfortunately I don't know VB and did not
think I would need it... so please forgive me in advance but I must request
that if your advice involves coding that you are very explicit in your
instructions. (The code above that I used, I found on these forums and used
trial and error to figure out how to make it work for myself and where to put
it.)

Thank you so, so much and, again, my apoligies for the length.

 

[John Spencer]

Your first problem seems to be that your data structure is wrong.
You should really have a table that records the item donated, the person
donating, and the quantity.

With that structure it would straight forward to list the number of items
and the amounts donated without hiding/showing a lot of fields. Plus if you
add another donation category you won't have to rewrite all your forms,
codes, queries, and reports.

You need a table for
Donors
ItemsDonatedByDonor
DonationItemType

If you have to live with the current structure then you would need to use
some vba to pull the information into one field. Would you care to post
your data structure. I am guessing you have something like
DonorId
DonorName
Cups
Plates
Saucers
Apples

And then you enter the number of cups in the Cups field, the number of
Apples in the apples field. IF that is the case, you could copy and paste
the following expression (with the needed changes) into a VBA module and
then call it from your query.

Field: ListOfItems: strListItems([DonationID])

Assumptions:
-- Number field that identifies the donation
-- Field names are the names of the items to be donated
-- The fields are number fields and contain

Public Function strListItems(DonationID) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

strSQL = "SELECT Cups, Plates, Saucers, Apples " & _
" FROM YourDonationTable " & _
" WHERE DonationID= " & DonationID

Set rstAny = CurrentDb().OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & rstAny.Fields(I).Name & ": " &
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If
strListItems = strReturn
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks so much for your help. I chose the 2 linked tables ("donors" and
"transactions") for a number of reasons; primarily because many of our
customers donate regularly, and I only want 1 entry per donor, even if they
make 12 donations in a year. The donor list is more like a mailing list, with
different physical and mailing addresses, etc. Then, too, when we schedule
donations for repeat donors the donor info is already entered and only the
transaction details have to be typed in (date of donation, items, special
instructions, etc.) Then we can always pull up a donation history for any
one person, do just a large thank-you mass mailing to everyone on the list,
or even just count how many bags of clothing are donated in a year.

I'm building this to replace our old system, which was an old Fox program
written specifically for us to route our home pick-ups. We got a fancy new
GPS routing program to route our 60-80 stops per day, but it's not able to
store any donor or donation info in a permanent database, hence my brilliant
plan to use Access. (These donor receipts are only my final of about 8
steps; I've created other reports and queries I'll use to print for each
truck driver, and import into the routing program to make maps.) We also plan
to purge this about once per year, and keep only current or repeat donors. I
managed to dig into the old program's databases and import 1 year's worth of
donor info to start with, which is about 9,000 names.

DONOR table fields:

DonorID
First
Last
Company
Address
Zip
…etc.

TRANSACTIONS table fields:

PU# (pickup number; just an auto-number to make it unique)
DonorID (pulled from above)
PickupDate
CallDate
BoxesofMisc
Sofa
Loveseat
Rocker
…etc… (54 different items; all number fields just meant to contain 1-2 digit
quantity)
Notes (for special driver instructions)
Truck (see below)
Order (see below)

Our receptionist uses an intake form when someone calls to schedule a
pick-up; it's a form for donor information with a sub-form with transaction
details. With a quick search by address they can easily populate the donor
fields if someone is a repeat. Every day when the route fills up they just
go through a routine run the queries, routing import and the report printing.
The Truck and Order fields remain empty until after the daily route query is
mapped in our mapping program, giving us the info for which truck (1, 2 or 3)
gets which addresses, and then their order on the route. They have to be
manually entered as the last step, unfortunately. (When I started this
project it did not seem that it would end up this complex; I'm sure my setup
is messier than if I had been more seasoned to begin with.)

Does this make sense, and does it seem that it will jive with the
recommendation you made to me? And if so, might you be able to clarify for
me the "modifications" I will need to make in the code, and where exactly to
post it? And, can you tell me exactly what this will do? (As in, is this
meant to solve my problem leaving the current report design as-is, with one
long column of items in the detail section?)

Thank you, thank you so much for your help.
Jennifer

Your first problem seems to be that your data structure is wrong.
You should really have a table that records the item donated, the person
donating, and the quantity.

With that structure it would straight forward to list the number of items
and the amounts donated without hiding/showing a lot of fields. Plus if you
add another donation category you won't have to rewrite all your forms,
codes, queries, and reports.

You need a table for
Donors
ItemsDonatedByDonor
DonationItemType

If you have to live with the current structure then you would need to use
some vba to pull the information into one field. Would you care to post
your data structure. I am guessing you have something like
DonorId
DonorName
Cups
Plates
Saucers
Apples

And then you enter the number of cups in the Cups field, the number of
Apples in the apples field. IF that is the case, you could copy and paste
the following expression (with the needed changes) into a VBA module and
then call it from your query.

Field: ListOfItems: strListItems([DonationID])

Assumptions:
-- Number field that identifies the donation
-- Field names are the names of the items to be donated
-- The fields are number fields and contain

Public Function strListItems(DonationID) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

strSQL = "SELECT Cups, Plates, Saucers, Apples " & _
" FROM YourDonationTable " & _
" WHERE DonationID= " & DonationID

Set rstAny = CurrentDb().OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & rstAny.Fields(I).Name & ": " &
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If
strListItems = strReturn
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please forgive the length, I will try to condense this explanation as much
as
possible while still conveying the essentials. I am trying to create a
report which prints 2-to-a-page "receipts," essentially, to hand out to
customers when they donate items to our organization. I have been
struggling
for several weeks to find a way to format this report so that each entry
is
exactly 1/2-page, so I can print out a daily report (~20-30 pages) and cut
the stack in half with a paper cutter. The report pulls from a query
which
pulls from 2 tables, customer info and donation info (detail section.)

Formatting the detail section was the first problem. When the order is
taken from a list of about 55 items, a quantity is typed in for which
items
they are donating, and I only want those entries with a quantity to show
up
on the report, the other ones to disappear and the whole section to
collapse
so there's not empty space. Ideally, the list would be arranged in 4
columns
for a cleaner layout, but I couldn't figure out how to make items from all
4
columns align up to the top, squeezing out the empty space left by the
items
not showing. I solved this by designing the report with all 55 items in
one
long column to the left, aligning them all to the top, and setting
everything
to Grow/Shrink, then using "Me!item_Label.Visible = Not IsNull(Me!item) on
format (one for each item in the list).

Problem is, this solution allowed each entry to shrink up and not take up
extra space, but I can't set the section to a fixed height because the
single-column list of items in the design view cannot fit within that
height.
Between the header and footer for each entry and all the donation info, I
absolutely cannot get the design of the report to fit within a 5.5"
height,
even though each receipt in print view will always be less because most of
that column disappears (there's usually only quantities entered for a
handful
of items on the list.)

I even tried setting the report to landscape so the 2-to-a-page are side
by
side, hoping some kind of column formatting would work, but I can't get
that
to go either.

Is it possible to either set a fixed height for the print layout only, or
modify my 55-item list into 4 columns so that they tidy themselves up
properly when I'm only printing a receipt with a few items? (The
horizontal
shrink seems to be the hangup.) I built this project because I thought I
had
a decent knowledge of access, but unfortunately I don't know VB and did
not
think I would need it... so please forgive me in advance but I must
request
that if your advice involves coding that you are very explicit in your
instructions. (The code above that I used, I found on these forums and
used
trial and error to figure out how to make it work for myself and where to
put
it.)

Thank you so, so much and, again, my apoligies for the length.

 

[John Spencer]

In the long run, you should have another table where you store the items
picked up and a table of items.


Donor table looks good

Tranactions table should be pared down to
PUNum
PickupDate
CallDate
Truck
Notes
Order

Donations table
DonationId (autonumber)
ItemTypeID
Amount

ItemTypes Table
ItemTypeId
ItemName - This stores the Name of all the item fields

With this structure you select an ItemType (from a drop down) and enter
an amount. The data entry form would contain a continuous subform
displaying a combobox to choose the item type and a control to enter the
amount (or count).


The proposed interim solution would build a "field" in the query that
would have all the items and the count associated with the item in one
field with each item on a new line. ONLY the items where the number was
more than zero would show. So your report would have one field that
showed all the items in the list that were donated.

Public Function strListItems(PickupNumber) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

'You need to modify the select to list all the item types
strSQL = "SELECT BoxesOfMisc, Sofa, LoveSeat, Rocker, ... " & _
" FROM Transactions " & _
" WHERE [PU#]= " & PickupNumber

Set rstAny = CurrentDb().OpenRecordset(strSQL)

'This will add a line for each item that has a count of
'one or more in the field, along with the name of the field
If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & _
rstAny.Fields(I).Name & ": " & _
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If

strListItems = strReturn
End Function

So in the query design view you would have your Donors table and your
Transactions table
-- Select the donor fields you want on the report
-- Select any of the non-item fields you want from the transactions table
-- In a blank field area enter
Field: DonationList: strListItems([Pu#])

If I've coded the above function correctly you should get something back
for the Donation list that could look like the following. Anything
that was blank or had zero or a negative number would not show up in the
list.
BoxesOfMisc 3
LoveSeat 1
Piano 1
Television 2






'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks so much for your help. I chose the 2 linked tables ("donors" and
"transactions") for a number of reasons; primarily because many of our
customers donate regularly, and I only want 1 entry per donor, even if they
make 12 donations in a year. The donor list is more like a mailing list, with
different physical and mailing addresses, etc. Then, too, when we schedule
donations for repeat donors the donor info is already entered and only the
transaction details have to be typed in (date of donation, items, special
instructions, etc.) Then we can always pull up a donation history for any
one person, do just a large thank-you mass mailing to everyone on the list,
or even just count how many bags of clothing are donated in a year.

I'm building this to replace our old system, which was an old Fox program
written specifically for us to route our home pick-ups. We got a fancy new
GPS routing program to route our 60-80 stops per day, but it's not able to
store any donor or donation info in a permanent database, hence my brilliant
plan to use Access. (These donor receipts are only my final of about 8
steps; I've created other reports and queries I'll use to print for each
truck driver, and import into the routing program to make maps.) We also plan
to purge this about once per year, and keep only current or repeat donors. I
managed to dig into the old program's databases and import 1 year's worth of
donor info to start with, which is about 9,000 names.

DONOR table fields:

DonorID
First
Last
Company
Address
Zip
…etc.

TRANSACTIONS table fields:

PU# (pickup number; just an auto-number to make it unique)
DonorID (pulled from above)
PickupDate
CallDate
BoxesofMisc
Sofa
Loveseat
Rocker
…etc… (54 different items; all number fields just meant to contain 1-2 digit
quantity)
Notes (for special driver instructions)
Truck (see below)
Order (see below)

Our receptionist uses an intake form when someone calls to schedule a
pick-up; it's a form for donor information with a sub-form with transaction
details. With a quick search by address they can easily populate the donor
fields if someone is a repeat. Every day when the route fills up they just
go through a routine run the queries, routing import and the report printing.
The Truck and Order fields remain empty until after the daily route query is
mapped in our mapping program, giving us the info for which truck (1, 2 or 3)
gets which addresses, and then their order on the route. They have to be
manually entered as the last step, unfortunately. (When I started this
project it did not seem that it would end up this complex; I'm sure my setup
is messier than if I had been more seasoned to begin with.)

Does this make sense, and does it seem that it will jive with the
recommendation you made to me? And if so, might you be able to clarify for
me the "modifications" I will need to make in the code, and where exactly to
post it? And, can you tell me exactly what this will do? (As in, is this
meant to solve my problem leaving the current report design as-is, with one
long column of items in the detail section?)

Thank you, thank you so much for your help.
Jennifer

Your first problem seems to be that your data structure is wrong.
You should really have a table that records the item donated, the person
donating, and the quantity.

With that structure it would straight forward to list the number of items
and the amounts donated without hiding/showing a lot of fields. Plus if you
add another donation category you won't have to rewrite all your forms,
codes, queries, and reports.

You need a table for
Donors
ItemsDonatedByDonor
DonationItemType

If you have to live with the current structure then you would need to use
some vba to pull the information into one field. Would you care to post
your data structure. I am guessing you have something like
DonorId
DonorName
Cups
Plates
Saucers
Apples

And then you enter the number of cups in the Cups field, the number of
Apples in the apples field. IF that is the case, you could copy and paste
the following expression (with the needed changes) into a VBA module and
then call it from your query.

Field: ListOfItems: strListItems([DonationID])

Assumptions:
-- Number field that identifies the donation
-- Field names are the names of the items to be donated
-- The fields are number fields and contain

Public Function strListItems(DonationID) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

strSQL = "SELECT Cups, Plates, Saucers, Apples " & _
" FROM YourDonationTable " & _
" WHERE DonationID= " & DonationID

Set rstAny = CurrentDb().OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & rstAny.Fields(I).Name & ": " &
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If
strListItems = strReturn
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please forgive the length, I will try to condense this explanation as much
as
possible while still conveying the essentials. I am trying to create a
report which prints 2-to-a-page "receipts," essentially, to hand out to
customers when they donate items to our organization. I have been
struggling
for several weeks to find a way to format this report so that each entry
is
exactly 1/2-page, so I can print out a daily report (~20-30 pages) and cut
the stack in half with a paper cutter. The report pulls from a query
which
pulls from 2 tables, customer info and donation info (detail section.)

Formatting the detail section was the first problem. When the order is
taken from a list of about 55 items, a quantity is typed in for which
items
they are donating, and I only want those entries with a quantity to show
up
on the report, the other ones to disappear and the whole section to
collapse
so there's not empty space. Ideally, the list would be arranged in 4
columns
for a cleaner layout, but I couldn't figure out how to make items from all
4
columns align up to the top, squeezing out the empty space left by the
items
not showing. I solved this by designing the report with all 55 items in
one
long column to the left, aligning them all to the top, and setting
everything
to Grow/Shrink, then using "Me!item_Label.Visible = Not IsNull(Me!item) on
format (one for each item in the list).

Problem is, this solution allowed each entry to shrink up and not take up
extra space, but I can't set the section to a fixed height because the
single-column list of items in the design view cannot fit within that
height.
Between the header and footer for each entry and all the donation info, I
absolutely cannot get the design of the report to fit within a 5.5"
height,
even though each receipt in print view will always be less because most of
that column disappears (there's usually only quantities entered for a
handful
of items on the list.)

I even tried setting the report to landscape so the 2-to-a-page are side
by
side, hoping some kind of column formatting would work, but I can't get
that
to go either.

Is it possible to either set a fixed height for the print layout only, or
modify my 55-item list into 4 columns so that they tidy themselves up
properly when I'm only printing a receipt with a few items? (The
horizontal
shrink seems to be the hangup.) I built this project because I thought I
had
a decent knowledge of access, but unfortunately I don't know VB and did
not
think I would need it... so please forgive me in advance but I must
request
that if your advice involves coding that you are very explicit in your
instructions. (The code above that I used, I found on these forums and
used
trial and error to figure out how to make it work for myself and where to
put
it.)

Thank you so, so much and, again, my apoligies for the length.

 

[Guest]

Thank you very much for your help. It will take me some time to redesign my
database using your suggestions, but I think it will be worth the time if it
all goes as planned. I may have to re-post with some follow up questions
once I get further along.

Jennifer

In the long run, you should have another table where you store the items
picked up and a table of items.


Donor table looks good

Tranactions table should be pared down to
PUNum
PickupDate
CallDate
Truck
Notes
Order

Donations table
DonationId (autonumber)
ItemTypeID
Amount

ItemTypes Table
ItemTypeId
ItemName - This stores the Name of all the item fields

With this structure you select an ItemType (from a drop down) and enter
an amount. The data entry form would contain a continuous subform
displaying a combobox to choose the item type and a control to enter the
amount (or count).


The proposed interim solution would build a "field" in the query that
would have all the items and the count associated with the item in one
field with each item on a new line. ONLY the items where the number was
more than zero would show. So your report would have one field that
showed all the items in the list that were donated.

Public Function strListItems(PickupNumber) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

'You need to modify the select to list all the item types
strSQL = "SELECT BoxesOfMisc, Sofa, LoveSeat, Rocker, ... " & _
" FROM Transactions " & _
" WHERE [PU#]= " & PickupNumber

Set rstAny = CurrentDb().OpenRecordset(strSQL)

'This will add a line for each item that has a count of
'one or more in the field, along with the name of the field
If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & _
rstAny.Fields(I).Name & ": " & _
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If

strListItems = strReturn
End Function

So in the query design view you would have your Donors table and your
Transactions table
-- Select the donor fields you want on the report
-- Select any of the non-item fields you want from the transactions table
-- In a blank field area enter
Field: DonationList: strListItems([Pu#])

If I've coded the above function correctly you should get something back
for the Donation list that could look like the following. Anything
that was blank or had zero or a negative number would not show up in the
list.
BoxesOfMisc 3
LoveSeat 1
Piano 1
Television 2






'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks so much for your help. I chose the 2 linked tables ("donors" and
"transactions") for a number of reasons; primarily because many of our
customers donate regularly, and I only want 1 entry per donor, even if they
make 12 donations in a year. The donor list is more like a mailing list, with
different physical and mailing addresses, etc. Then, too, when we schedule
donations for repeat donors the donor info is already entered and only the
transaction details have to be typed in (date of donation, items, special
instructions, etc.) Then we can always pull up a donation history for any
one person, do just a large thank-you mass mailing to everyone on the list,
or even just count how many bags of clothing are donated in a year.

I'm building this to replace our old system, which was an old Fox program
written specifically for us to route our home pick-ups. We got a fancy new
GPS routing program to route our 60-80 stops per day, but it's not able to
store any donor or donation info in a permanent database, hence my brilliant
plan to use Access. (These donor receipts are only my final of about 8
steps; I've created other reports and queries I'll use to print for each
truck driver, and import into the routing program to make maps.) We also plan
to purge this about once per year, and keep only current or repeat donors. I
managed to dig into the old program's databases and import 1 year's worth of
donor info to start with, which is about 9,000 names.

DONOR table fields:

DonorID
First
Last
Company
Address
Zip
…etc.

TRANSACTIONS table fields:

PU# (pickup number; just an auto-number to make it unique)
DonorID (pulled from above)
PickupDate
CallDate
BoxesofMisc
Sofa
Loveseat
Rocker
…etc… (54 different items; all number fields just meant to contain 1-2 digit
quantity)
Notes (for special driver instructions)
Truck (see below)
Order (see below)

Our receptionist uses an intake form when someone calls to schedule a
pick-up; it's a form for donor information with a sub-form with transaction
details. With a quick search by address they can easily populate the donor
fields if someone is a repeat. Every day when the route fills up they just
go through a routine run the queries, routing import and the report printing.
The Truck and Order fields remain empty until after the daily route query is
mapped in our mapping program, giving us the info for which truck (1, 2 or 3)
gets which addresses, and then their order on the route. They have to be
manually entered as the last step, unfortunately. (When I started this
project it did not seem that it would end up this complex; I'm sure my setup
is messier than if I had been more seasoned to begin with.)

Does this make sense, and does it seem that it will jive with the
recommendation you made to me? And if so, might you be able to clarify for
me the "modifications" I will need to make in the code, and where exactly to
post it? And, can you tell me exactly what this will do? (As in, is this
meant to solve my problem leaving the current report design as-is, with one
long column of items in the detail section?)

Thank you, thank you so much for your help.
Jennifer

Your first problem seems to be that your data structure is wrong.
You should really have a table that records the item donated, the person
donating, and the quantity.

With that structure it would straight forward to list the number of items
and the amounts donated without hiding/showing a lot of fields. Plus if you
add another donation category you won't have to rewrite all your forms,
codes, queries, and reports.

You need a table for
Donors
ItemsDonatedByDonor
DonationItemType

If you have to live with the current structure then you would need to use
some vba to pull the information into one field. Would you care to post
your data structure. I am guessing you have something like
DonorId
DonorName
Cups
Plates
Saucers
Apples

And then you enter the number of cups in the Cups field, the number of
Apples in the apples field. IF that is the case, you could copy and paste
the following expression (with the needed changes) into a VBA module and
then call it from your query.

Field: ListOfItems: strListItems([DonationID])

Assumptions:
-- Number field that identifies the donation
-- Field names are the names of the items to be donated
-- The fields are number fields and contain

Public Function strListItems(DonationID) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

strSQL = "SELECT Cups, Plates, Saucers, Apples " & _
" FROM YourDonationTable " & _
" WHERE DonationID= " & DonationID

Set rstAny = CurrentDb().OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & rstAny.Fields(I).Name & ": " &
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If
strListItems = strReturn
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please forgive the length, I will try to condense this explanation as much
as
possible while still conveying the essentials. I am trying to create a
report which prints 2-to-a-page "receipts," essentially, to hand out to
customers when they donate items to our organization. I have been
struggling
for several weeks to find a way to format this report so that each entry
is
exactly 1/2-page, so I can print out a daily report (~20-30 pages) and cut
the stack in half with a paper cutter. The report pulls from a query
which
pulls from 2 tables, customer info and donation info (detail section.)

Formatting the detail section was the first problem. When the order is
taken from a list of about 55 items, a quantity is typed in for which
items
they are donating, and I only want those entries with a quantity to show
up
on the report, the other ones to disappear and the whole section to
collapse
so there's not empty space. Ideally, the list would be arranged in 4
columns
for a cleaner layout, but I couldn't figure out how to make items from all
4
columns align up to the top, squeezing out the empty space left by the
items
not showing. I solved this by designing the report with all 55 items in
one
long column to the left, aligning them all to the top, and setting
everything
to Grow/Shrink, then using "Me!item_Label.Visible = Not IsNull(Me!item) on
format (one for each item in the list).

Problem is, this solution allowed each entry to shrink up and not take up
extra space, but I can't set the section to a fixed height because the
single-column list of items in the design view cannot fit within that
height.
Between the header and footer for each entry and all the donation info, I
absolutely cannot get the design of the report to fit within a 5.5"
height,
even though each receipt in print view will always be less because most of
that column disappears (there's usually only quantities entered for a
handful
of items on the list.)

I even tried setting the report to landscape so the 2-to-a-page are side
by
side, hoping some kind of column formatting would work, but I can't get
that
to go either.

Is it possible to either set a fixed height for the print layout only, or
modify my 55-item list into 4 columns so that they tidy themselves up
properly when I'm only printing a receipt with a few items? (The
horizontal
shrink seems to be the hangup.) I built this project because I thought I
had
a decent knowledge of access, but unfortunately I don't know VB and did
not
think I would need it... so please forgive me in advance but I must
request

 

[John Spencer]

Good luck.

Getting the structure correct is the single most important step in
designing a good database.

It takes practice and skill to do so. One thing you might try is to get
a book on designing relational databases. One I can recommend is
Rebecca Riordan's but there are many out there.

If you have access to a bookstore with a good computer book section, you
might go there and browse some of the books to see if any appeals to you.

And once you get your design worked out, you can post back here and ask
for advice. Most of us really do like to help out.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you very much for your help. It will take me some time to redesign my
database using your suggestions, but I think it will be worth the time if it
all goes as planned. I may have to re-post with some follow up questions
once I get further along.

Jennifer

In the long run, you should have another table where you store the items
picked up and a table of items.


Donor table looks good

Tranactions table should be pared down to
PUNum
PickupDate
CallDate
Truck
Notes
Order

Donations table
DonationId (autonumber)
ItemTypeID
Amount

ItemTypes Table
ItemTypeId
ItemName - This stores the Name of all the item fields

With this structure you select an ItemType (from a drop down) and enter
an amount. The data entry form would contain a continuous subform
displaying a combobox to choose the item type and a control to enter the
amount (or count).


The proposed interim solution would build a "field" in the query that
would have all the items and the count associated with the item in one
field with each item on a new line. ONLY the items where the number was
more than zero would show. So your report would have one field that
showed all the items in the list that were donated.

Public Function strListItems(PickupNumber) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

'You need to modify the select to list all the item types
strSQL = "SELECT BoxesOfMisc, Sofa, LoveSeat, Rocker, ... " & _
" FROM Transactions " & _
" WHERE [PU#]= " & PickupNumber

Set rstAny = CurrentDb().OpenRecordset(strSQL)

'This will add a line for each item that has a count of
'one or more in the field, along with the name of the field
If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & _
rstAny.Fields(I).Name & ": " & _
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If

strListItems = strReturn
End Function

So in the query design view you would have your Donors table and your
Transactions table
-- Select the donor fields you want on the report
-- Select any of the non-item fields you want from the transactions table
-- In a blank field area enter
Field: DonationList: strListItems([Pu#])

If I've coded the above function correctly you should get something back
for the Donation list that could look like the following. Anything
that was blank or had zero or a negative number would not show up in the
list.
BoxesOfMisc 3
LoveSeat 1
Piano 1
Television 2






'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks so much for your help. I chose the 2 linked tables ("donors" and
"transactions") for a number of reasons; primarily because many of our
customers donate regularly, and I only want 1 entry per donor, even if they
make 12 donations in a year. The donor list is more like a mailing list, with
different physical and mailing addresses, etc. Then, too, when we schedule
donations for repeat donors the donor info is already entered and only the
transaction details have to be typed in (date of donation, items, special
instructions, etc.) Then we can always pull up a donation history for any
one person, do just a large thank-you mass mailing to everyone on the list,
or even just count how many bags of clothing are donated in a year.

I'm building this to replace our old system, which was an old Fox program
written specifically for us to route our home pick-ups. We got a fancy new
GPS routing program to route our 60-80 stops per day, but it's not able to
store any donor or donation info in a permanent database, hence my brilliant
plan to use Access. (These donor receipts are only my final of about 8
steps; I've created other reports and queries I'll use to print for each
truck driver, and import into the routing program to make maps.) We also plan
to purge this about once per year, and keep only current or repeat donors. I
managed to dig into the old program's databases and import 1 year's worth of
donor info to start with, which is about 9,000 names.

DONOR table fields:

DonorID
First
Last
Company
Address
Zip
…etc.

TRANSACTIONS table fields:

PU# (pickup number; just an auto-number to make it unique)
DonorID (pulled from above)
PickupDate
CallDate
BoxesofMisc
Sofa
Loveseat
Rocker
…etc… (54 different items; all number fields just meant to contain 1-2 digit
quantity)
Notes (for special driver instructions)
Truck (see below)
Order (see below)

Our receptionist uses an intake form when someone calls to schedule a
pick-up; it's a form for donor information with a sub-form with transaction
details. With a quick search by address they can easily populate the donor
fields if someone is a repeat. Every day when the route fills up they just
go through a routine run the queries, routing import and the report printing.
The Truck and Order fields remain empty until after the daily route query is
mapped in our mapping program, giving us the info for which truck (1, 2 or 3)
gets which addresses, and then their order on the route. They have to be
manually entered as the last step, unfortunately. (When I started this
project it did not seem that it would end up this complex; I'm sure my setup
is messier than if I had been more seasoned to begin with.)

Does this make sense, and does it seem that it will jive with the
recommendation you made to me? And if so, might you be able to clarify for
me the "modifications" I will need to make in the code, and where exactly to
post it? And, can you tell me exactly what this will do? (As in, is this
meant to solve my problem leaving the current report design as-is, with one
long column of items in the detail section?)

Thank you, thank you so much for your help.
Jennifer


:

Your first problem seems to be that your data structure is wrong.
You should really have a table that records the item donated, the person
donating, and the quantity.

With that structure it would straight forward to list the number of items
and the amounts donated without hiding/showing a lot of fields. Plus if you
add another donation category you won't have to rewrite all your forms,
codes, queries, and reports.

You need a table for
Donors
ItemsDonatedByDonor
DonationItemType

If you have to live with the current structure then you would need to use
some vba to pull the information into one field. Would you care to post
your data structure. I am guessing you have something like
DonorId
DonorName
Cups
Plates
Saucers
Apples

And then you enter the number of cups in the Cups field, the number of
Apples in the apples field. IF that is the case, you could copy and paste
the following expression (with the needed changes) into a VBA module and
then call it from your query.

Field: ListOfItems: strListItems([DonationID])

Assumptions:
-- Number field that identifies the donation
-- Field names are the names of the items to be donated
-- The fields are number fields and contain

Public Function strListItems(DonationID) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String

strSQL = "SELECT Cups, Plates, Saucers, Apples " & _
" FROM YourDonationTable " & _
" WHERE DonationID= " & DonationID

Set rstAny = CurrentDb().OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & rstAny.Fields(I).Name & ": " &
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)

End If
strListItems = strReturn
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please forgive the length, I will try to condense this explanation as much
as
possible while still conveying the essentials. I am trying to create a
report which prints 2-to-a-page "receipts," essentially, to hand out to
customers when they donate items to our organization. I have been
struggling
for several weeks to find a way to format this report so that each entry
is
exactly 1/2-page, so I can print out a daily report (~20-30 pages) and cut
the stack in half with a paper cutter. The report pulls from a query
which
pulls from 2 tables, customer info and donation info (detail section.)

Formatting the detail section was the first problem. When the order is
taken from a list of about 55 items, a quantity is typed in for which
items
they are donating, and I only want those entries with a quantity to show
up
on the report, the other ones to disappear and the whole section to
collapse
so there's not empty space. Ideally, the list would be arranged in 4
columns
for a cleaner layout, but I couldn't figure out how to make items from all
4
columns align up to the top, squeezing out the empty space left by the
items
not showing. I solved this by designing the report with all 55 items in
one
long column to the left, aligning them all to the top, and setting
everything
to Grow/Shrink, then using "Me!item_Label.Visible = Not IsNull(Me!item) on
format (one for each item in the list).

Problem is, this solution allowed each entry to shrink up and not take up
extra space, but I can't set the section to a fixed height because the
single-column list of items in the design view cannot fit within that
height.
Between the header and footer for each entry and all the donation info, I
absolutely cannot get the design of the report to fit within a 5.5"
height,
even though each receipt in print view will always be less because most of
that column disappears (there's usually only quantities entered for a
handful
of items on the list.)

I even tried setting the report to landscape so the 2-to-a-page are side
by
side, hoping some kind of column formatting would work, but I can't get
that
to go either.

Is it possible to either set a fixed height for the print layout only, or
modify my 55-item list into 4 columns so that they tidy themselves up
properly when I'm only printing a receipt with a few items? (The
horizontal
shrink seems to be the hangup.) I built this project because I thought I
had
a decent knowledge of access, but unfortunately I don't know VB and did
not
think I would need it... so please forgive me in advance but I must
request

 

[Irene Sterling]

--
Gypsymega99

Please forgive the length, I will try to condense this explanation as much as
possible while still conveying the essentials. I am trying to create a
report which prints 2-to-a-page "receipts," essentially, to hand out to
customers when they donate items to our organization. I have been struggling
for several weeks to find a way to format this report so that each entry is
exactly 1/2-page, so I can print out a daily report (~20-30 pages) and cut
the stack in half with a paper cutter. The report pulls from a query which
pulls from 2 tables, customer info and donation info (detail section.)

Formatting the detail section was the first problem. When the order is
taken from a list of about 55 items, a quantity is typed in for which items
they are donating, and I only want those entries with a quantity to show up
on the report, the other ones to disappear and the whole section to collapse
so there's not empty space. Ideally, the list would be arranged in 4 columns
for a cleaner layout, but I couldn't figure out how to make items from all 4
columns align up to the top, squeezing out the empty space left by the items
not showing. I solved this by designing the report with all 55 items in one
long column to the left, aligning them all to the top, and setting everything
to Grow/Shrink, then using "Me!item_Label.Visible = Not IsNull(Me!item) on
format (one for each item in the list).

Problem is, this solution allowed each entry to shrink up and not take up
extra space, but I can't set the section to a fixed height because the
single-column list of items in the design view cannot fit within that height.
Between the header and footer for each entry and all the donation info, I
absolutely cannot get the design of the report to fit within a 5.5" height,
even though each receipt in print view will always be less because most of
that column disappears (there's usually only quantities entered for a handful
of items on the list.)

I even tried setting the report to landscape so the 2-to-a-page are side by
side, hoping some kind of column formatting would work, but I can't get that
to go either.

Is it possible to either set a fixed height for the print layout only, or
modify my 55-item list into 4 columns so that they tidy themselves up
properly when I'm only printing a receipt with a few items? (The horizontal
shrink seems to be the hangup.) I built this project because I thought I had
a decent knowledge of access, but unfortunately I don't know VB and did not
think I would need it... so please forgive me in advance but I must request
that if your advice involves coding that you are very explicit in your
instructions. (The code above that I used, I found on these forums and used
trial and error to figure out how to make it work for myself and where to put
it.)

Thank you so, so much and, again, my apoligies for the length.