# Assigning a position based on quantity

1. Novice
Windows XP Access 2002
Join Date
Feb 2020
Posts
3

## Assigning a position based on quantity

Hello, Forgive me for my lack of knowledge.
I'm looking for a solution in access 2000. What I'm ultimately trying to accomplish is giving each candle a position in a box so it can be packed in cases of 12.

I have a table of products (Candles) with their quantities...
Name Qty

Spring 5

Summer 5

Fall 4

Winter 6

I want to list them like this...

1 Spring 1

2 Spring 1

3 Spring 1

4 Spring 1

5 Spring 1

6 Summer 1

7 Summer 1

8 Summer 1

9 Summer 1

10 Summer 1

11 Fall 1

12 Fall 1

13 Fall 1

14 Fall 1

15 Winter 1

16 Winter 1

17 Winter 1

18 Winter 1

19 Winter 1

20 Winter 1

We need to print a report like this:

Box 1
Spring 5
Summer 5
Fall 2

Box 2
Fall 2
Winter 6

My idea was to use IIF statements in a query to assign them a position. ??? Any ideas??

Dave

2. VIP
Windows 10 Access 2010 32bit
Join Date
Mar 2015
Posts
8,016
I fear your example is too simplistic so I'm not going to attempt a suggested solution. Suggest you explain what it is you are trying to do in reality

Best I can do is suggest

1. you need to use something to define order perhaps 1=spring, 2=summer etc
2. look at using the mod function (12 mod 3 would return 0 - the remainder) and the \ function (12\3 will return 4 - the number of 3's in 12)

3. VIP
Windows 7 64bit Access 2007
Join Date
Nov 2015
Posts
1,344
Can you give better example of what you have now and what you want. Explain the process from inventory you get, how you put it in the table and if you get an order, how do you fill it, etc. Here are some questions:

1. So you have 1 table that has the Name and Qty? Is that the only table and you just add to the quantity as you get more inventory?
2. What does the number 1 after each candle type mean in your example?
3. When you say position in a box, does it actually have a "position" within the box or you just need to know a box had 4 summer, 2 Winter, etc.? If you need to know how they are arranged in each box, what are the rules for that?
4. Is each order/box for a different customer? So you could have multiple Box1, Box2 per vendor?

4. Novice
Windows XP Access 2002
Join Date
Feb 2020
Posts
3
Hard to explain but I’ll give it a shot….

We do fundraising with candles. Each seller will submit an order. No two orders are the same. We box the order per seller, 12 candles to a box. To make it easy for the customer to check the order, we fill the boxes in the sequence they are ordered. We print a sticker to go on the box listing what’s in the box. Currently we do this by hand.

I want to input the order in a form as below:

Spring 5
Summer 5
Fall 4
Winter 6
Total 20

I want the program to figure out what position each candle should be in like below

My idea: If I can list the candles as a Qty of 1 for as many times as it’s quantity and give it a new sequential ID, the ID would be the position.

Spring 5
Summer 5
Fall 2
Winter 6

ID Name Qty
1 Spring 1
2 Spring 1
3 Spring 1
4 Spring 1
5 Spring 1
6 Summer 1
7 Summer 1
8 Summer 1
9 Summer 1
10 Summer 1
11 Fall 1
12 Fall 1
13 Fall 1
14 Fall 1
15 Winter 1
16 Winter 1
17 Winter 1
18 Winter 1
19 Winter 1
20 Winter 1

I then need a report that I can print and attach to the box like this:

Box 1

Seller Name:
School:
Order: Spring 5, Summer 5, Fall 2 Box Total: 12 Order total: 20

Box 2

Seller Name:
School:
Order: Fall 2, Winter 6 Box Total: 8 Order total: 20

5. Novice
Windows XP Access 2002
Join Date
Feb 2020
Posts
3
Hard to explain but I’ll give it a shot….

We do fundraising with candles. Each seller will submit an order. No two orders are the same. We box the order per seller, 12 candles to a box. To make it easy for the customer to check the order, we fill the boxes in the sequence they are ordered. We print a sticker to go on the box listing what’s in the box. Currently we do this by hand.

I want to input the order in a form as below:

Spring 5
Summer 5
Fall 4
Winter 6
Total 20

I want the program to figure out what position each candle should be in like below

My idea: If I can list the candles as a Qty of 1 for as many times as it’s quantity and give it a new sequential ID, the ID would be the position.

Spring 5
Summer 5
Fall 2
Winter 6

ID Name Qty
1 Spring 1
2 Spring 1
3 Spring 1
4 Spring 1
5 Spring 1
6 Summer 1
7 Summer 1
8 Summer 1
9 Summer 1
10 Summer 1
11 Fall 1
12 Fall 1
13 Fall 1
14 Fall 1
15 Winter 1
16 Winter 1
17 Winter 1
18 Winter 1
19 Winter 1
20 Winter 1

I then need a report that I can print and attach to the box like this:

Box 1

Seller Name:
School:
Order: Spring 5, Summer 5, Fall 2 Box Total: 12 Order total: 20

Box 2

Seller Name:
School:
Order: Fall 2, Winter 6 Box Total: 8 Order total: 20

6. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
6,877
Your stated report requirements don't match anything you've shown regarding the ID list nor the 'table' of positions. If the report needs to show the ID, you'll have to use a report textbox with running sum property set to 'over all'. You cannot use a table with autonumber ID because this value will continue to increase. Beyond that I can't say much, partly because I have no idea what your data looks like. Do you have a table or query with 5 records if there are 5 spring candles on an order? No table or query at all? Also, that 20 item list doesn't look anything like your table of positions or what you show as being needed for the report.

7. VIP
Windows 10 Access 2010 32bit
Join Date
Mar 2015
Posts
8,016
I suspect your data is perhaps simplified but do the following will give you the box and position for each candle.

1. create a table called admCounter, populated like this - datatype is a long number

ctr
0
1
2
3
4
5
6
7
8
9

2. copy and paste this sql into a new query and call it admCount
Code:
```SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
3. Make sure your table looks like this (I've called mine Table1, but you might call it tblOrders)
Season Qty SeasonOrder
spring 5 1
summer 5 2
autumn 4 3
winter 6 4

4. Copy this sql to a new query - change table name if required
Code:
```SELECT Table1.Season, Table1.Qty AS SeasonTtl, [Counter]+Nz((SELECT sum(qty) FROM Table1 T where seasonorder<table1.seasonorder),0) AS ID, (([id]-1)\12)+1 AS Box, (([id]-1) Mod 12)+1 AS [Position]FROM admCount, Table1
which produces this result
Season SeasonTtl ID Box Position
spring 5 1 1 1
spring 5 2 1 2
spring 5 3 1 3
spring 5 4 1 4
spring 5 5 1 5
summer 5 6 1 6
summer 5 7 1 7
summer 5 8 1 8
summer 5 9 1 9
summer 5 10 1 10
autumn 4 11 1 11
autumn 4 12 1 12
autumn 4 13 2 1
autumn 4 14 2 2
winter 6 15 2 3
winter 6 16 2 4
winter 6 17 2 5
winter 6 18 2 6
winter 6 19 2 7
winter 6 20 2 8

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Tech Forums: Microsoft Office Forums