Results 1 to 7 of 7
  1. #1
    chestnuthill is offline 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. #2
    Ajax is offline 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. #3
    Bulzie is offline 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. #4
    chestnuthill is offline 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


    Click image for larger version. 

Name:	box.jpg 
Views:	15 
Size:	79.8 KB 
ID:	40851



    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. #5
    chestnuthill is offline 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. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    Ajax is offline 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]
    FROM admCounter AS singles, admCounter AS tens, admCounter AS hundreds, admCounter AS thousands;
    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
    WHERE (((admCount.Counter)>0 And (admCount.Counter)<=[qty]))
    ORDER BY Table1.SeasonOrder, admCount.Counter;
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Assigning #'s based on two criteria
    By flea in forum Access
    Replies: 1
    Last Post: 06-28-2019, 10:10 AM
  2. Replies: 5
    Last Post: 12-02-2018, 11:43 PM
  3. Replies: 14
    Last Post: 10-24-2017, 11:07 AM
  4. Replies: 7
    Last Post: 11-18-2016, 12:14 PM
  5. Allocate quantity per team based on member count
    By critusodem in forum Access
    Replies: 4
    Last Post: 07-25-2016, 11:44 AM

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