Label Printing Part 2 3

[proximity]

I am repeating this question again, as I don't think I was specific enough on the previous version!

In Access 97, I have a query that has two fields: Item and Qty. The first field has an Item Number such as 1234567 and the second field contains the quantity of that number, eg 10.

I have set up a label report to print these two fields onto labels (mini address type so that I can label all my stock) and all works fine, but only prints one label of every item, regardless of the figure in the quantity field. The report screen in design view is 3cm by 2.5cm - a slightly customized version of the label wizard. The question, however, is how do I get Access to print 10 labels of 1234567 and not just one?

In other words, it should automatically look at the quantity field and print as many labels as specified and not 1!

Hopefully, it is something dead simple and I just haven't spotted it yet! Any help would be greatly appreciated

 

[Ouch]

have you tried creating a query to produce the correct number of records first then printing them as opposed to tring to do it on the report?

 

[proximity]

Hi Ouch,

Thanks for your suggestion, though I must confess to being unsure of how to proceed with the actual query! Most Access problems I have been able to solve, but this one seems to have stumped everyone!

To make it even clearer, the data table looks like this:

ID Item Qty
1 12345 10
2 12346 5
3 12347 25
4 12348 16
5 12349 18
6 12350 2
7 12351 1

And so on and so forth!

In the example above, I would only get 7 labels and not 87!

I'm sure that such a task must be possible - it's just a question of how!

--
Steven

 

[CosmoKramer]

I don't have Access in front of me, but this should work. Add a table to your database that only has one number field. Populate this table to contain sequential numbers 1 to whatever the maximum number of labels you might print.

Add this table to the query that is the record source for your label report. Don't join it.
Add the field that contains the numbers to the query grid and set the criteria for this field to:

Between 1 And [Qty]

This should generate the [Qty] number of labels for each [Item]........

Let me know if this works for you....

 

[proximity]

Cosmo,

Many, many thanks! A beautifully simple solution and it works a treat! You are a star...

Thanks again,

--
Steven

 

[SBendBuckeye]

When I try that with Access2000, I get a Cartesian Product. What am I doing wrong? Below is the Query SQL. I have 4 records in my test Item table and 25 records in my number table.

SELECT tblItem.ItemNumber, tblItem.QtyOnHand
FROM tblItem, tblNumbersInSequence
WHERE (((tblItem.QtyOnHand) Between 1 And [QtyOnHand]));

Item Table - ItemID is autonumber primary key
ItemID ItemNumber QtyOnHand
1 1100 2
2 1200 1
3 1300 4
4 1400 6

Number Table - on key
ANumber
1
2
3
4
5
.
.
.
23
24
25

Thanks!


Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[CosmoKramer]

SBendBuckeye,

You have to reference the sequential number field. Try this:

SELECT tblItem.ItemNumber, tblItem.QtyOnHand, tblNumbersInSequence.ANumber
FROM tblItem, tblNumbersInSequence
WHERE (((tblNumbersInSequence.ANumber) Between 1 And [QtyOnHand]));

 

[SBendBuckeye]

Thanks much! I really appreciate it! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[CJTyo]

Here's a related, if a little varied, thread that Cosmo and Paul helped me with:

thread 703-371336

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[rookiedev]

CosmoKramer-
I took your advice and the solution worked on my home computer but when I installed it at work my reports gave me a message =Trim. Any idea why or how I can fix it? The solution was exactly what I was looking for but now I can't figure this out. I am using Access 97.

Thanks-
RookieDev