Displaying running counts 1 of X, 2 of X, 3 of X

[LeLe]

I am creating work tickets for based on quantities order. So if I have an
order for 2 pillows and 1 blanket, my application creates 3 tickets, numbered
as follows:
1 of 2 pillows, 2 of 2 pillows, 1 of 1 blankets.

I would also like my tickets to include the following info:
1 of 3 items, 2 of 3 items, 3 of 3 items.

Can I do this?
Thanks so much for any suggestions.

 

[Allen Browne]

The answer will depend on how you have your tables set up.

Since several things can be sold in one order, you need 2 tables:
- the Orders header table, with fields such as:
OrderID unique number for this order (AutoNumber, p.key)
OrderDate date of the order
ClientID relates to Client.ClientID
- the OrderDetail table, with fields such as:
OrderID relates to Orders.OrderID (which order this row belongs
to)
ProductID relates to Product.ProductID (what was ordered)
Quantity how many were ordered
PriceEach Currency
If that's not how you are set up, open the Northwind sample database that
installs with Access, go to the Relationships window (Tools menu), and see
how their tables are connected.

If you have a strucuture like that, you can easily print out the tickets you
need, using a query that generates a record for each item in the Quantity
field. See:
Print a Quantity of a Label
at:
http://allenbrowne.com/ser-39.html

 

[LeLe]

Thanks so much for the info. I do use the structure suggested and am also
using the count ID to generate the number of labels I need. All that works
great. I quess my problem is that I want to add a second numbering system on
each label;

Currently I have a control which tells me how many of a specific item (1 of
2 pillows, 2 of 2 pillows, 1 of 2 blankets). Again this works great! What I
want to add (on the same ticket) is a second control which tells me which
item of the entire order I am processing. So the first label would read 1 of
2 pillows and 1 OF 3 ITEMS on the order, etc. This will help insure when we
consolidate the entire order, we gather all the peices.

Thanks again.

 

[Allen Browne]

Since you already have the records you need, you should be able to do this
with a running sum on the report.

1. If you don't already have it set up this way, open the Sorting'n'Grouping
box (View menu), and create a group header on the OrderID.

2. In the OrderID group header add text box like this:
Control Source =Count("*")
Format General Number
Visible No
Name txtItemInOrderCount

3. In the Detail section, add a text box with these properties:
Control Source =1
Format General Number
Running Sum Over Group

4. Alongside that, add another text box with Control Source of:
="of " & [txtItemInOrderCount]