Multiple mail labels per record

  • Thread starter Thread starter Piers Chivers
  • Start date Start date
P

Piers Chivers

Hi,
I have a table that records a customer's journeys where a journey is made up
of

Start Place
Start Pickup Time
Return Place
Number of Tickets.

I would like to print out a label that contains "Start Place, Pickup Time,
Return Place" BUT I need to print out "Number Of Tickets" number for each
record. That is, if I have a record

"London"
"10:00 am"
"Paris"
"5"

I would like 5 identical labels to be printed as "London, 10:00am, Paris".

I'm not very experienced with SQL but is there some clever query I can write
to feed into the Label Wizard to get this? If not, how do I go about this?

Many thanks,
Piers
 
Piers Chivers said:
Hi,
I have a table that records a customer's journeys where a journey is made up
of

Start Place
Start Pickup Time
Return Place
Number of Tickets.

I would like to print out a label that contains "Start Place, Pickup Time,
Return Place" BUT I need to print out "Number Of Tickets" number for each
record. That is, if I have a record

"London"
"10:00 am"
"Paris"
"5"

I would like 5 identical labels to be printed as "London, 10:00am, Paris".

I'm not very experienced with SQL but is there some clever query I can write
to feed into the Label Wizard to get this? If not, how do I go about this?
Click to expand...
If it's always five there is a simple SQL way to do this.
Create a table with one field and five records 1, 2, 3, 4, 5 for values (not
really important)
Create a query with your journey table and the number table.
Do not join them and you will get a Cartesian set with five records per
journey record.

There are several routines for doing what you want with code and The Access
Developers handbook has one, along with the code on the CD.
It's worth having and almost anyone of the routines in the book is worth the
price.
www.bookpool.com for the best price.

How come programmers who work by the hour spend money to buy books that let
them spend less time on a project?
 
If it's always five there is a simple SQL way to do this.
Create a table with one field and five records 1, 2, 3, 4, 5 for values (not
really important)
Create a query with your journey table and the number table.
Do not join them and you will get a Cartesian set with five records per
journey record.
Click to expand...

For any number of tickets, you can do the same thing with another
twist: use a table (I call mine Num) with a field, N, with integer
values 1 through 10000. Put a criterion on N of

<= [Number of Labels]

and that's how many you'll get for each record.
 
Sco,
Many thanks. This is almost what I need! However, the sample gets the
number to print by prompting the user. I already have this number in my
table but I'm at a loss of how to communicate my value to the VB code
(LabelSetup in the example).

My general question is : how do I pass a value from a table into a general
module when referring to the function from an event procedure? I've tried
LabelSetup([Journeys]![Number of Tickets]) but no luck :(

I also tried creating a hidden text box on the label containing the Number
Of Tickets and then referring to the control by Me![Number of Tickets] but
this didn't work either (and seemed too clumsy).

Any ideas?

Thanks,
Piers
 
Sco,
Many thanks. This is almost what I need! However, the sample gets the
number to print by prompting the user. I already have this number in my
table but I'm at a loss of how to communicate my value to the VB code
(LabelSetup in the example).

My general question is : how do I pass a value from a table into a general
module when referring to the function from an event procedure? I've tried
LabelSetup([Journeys]![Number of Tickets]) but no luck :(

I also tried creating a hidden text box on the label containing the Number
Of Tickets and then referring to the control by Me![Number of Tickets] but
this didn't work either (and seemed too clumsy).
Click to expand...

For any number of tickets, you can do the same thing with another
twist: use a table (I call mine Num) with a field, N, with integer
values 1 through 10000. Include Num in the Query upon which the label
report is based, with NO join line - this gives you a "Cartesian
join", normally a bad idea but handy here. Put a criterion on N of

<= [Number of Labels]

and that's how many you'll get for each record.
 
Back
Top