divide one record (amount) into several

[ranfan]

I am trying to create a query that takes a customers delivery amount and break it into several records (labels) for a store. So if a store needs 120 papers and the bundle size is 25 then four labels will print out with 25 on each and one thats prints 20 (instead of one label of 120 which I can do). I am fairly new and don't know where to start. I can get a query to tell me how many labels are needed but not to create the individual records for each customer based on the total. HELP PLEASE. THANKS>

Current query returns this:
Nmae Date Total
Tesco 16/15/03 120

I need this:
Tesco 16/15/03 25
Tesco 16/15/03 25
Tesco 16/15/03 25
Tesco 16/15/03 25
Tesco 16/15/03 20

 

[blom0344]

I you want to tackle this without resorting to writing code (and using looping) you can do it just using SQL and an additional table (If Total is not very large):

First query:

SELECT Table1.nmae, Table1.date, Int([total]/25) AS twentyfive_int, [total]-(Int([total]/25)*25) AS twentyfive_rest
FROM Table1;

Create a table2 as follows:

1
2
2
3
3
3
4
4
4
4
..
..
..

Now run second query on Query1 and additional table:

SELECT Query1.nmae, Query1.date, 25 AS Sub
FROM Query1 INNER JOIN Table2 ON Query1.twentyfive_int = Table2.Multiplier;
UNION ALL
SELECT Query1.nmae, Query1.date, Query1.Twentyfive_rest
FROM Query1

The output is not sorted in any way, but that can be done in a third step.

The actual work is in creating the additional table with multipliers and knowing the max value of Total.

If you want example database , mail me directly....

(Someone better in using VBA code will probably offer a solution through a piece of code)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ranfan]

Thanks for the quick reply. I have the first query finished. I am confused about the table config. It should have name, date, twentyfive_int and twnetyfive_rest fields, right?

 

[blom0344]

Well no,

The additional table should store as many records per integer as its value:

So it can contain just two fields:

Autonumber Multiplier
1 1
2 2
3 2
4 3
5 3
6 3
etc etc

You only need the autonumber to get an unique key, otherwise the SQL engine cannot join tables / queries.
Anyway, I would sent you an example database if I would have your email adress...........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ranfan]

Thanks, I changed the table. Also, I e-mailed you for an example.

 

[ranfan]

Thanks for all your help blom0344! Everything is working now!

 

[ranfan]

One more Question: The bundles are working great. But we want to limited the minimum size of the last bundle (Twentyfive_rest) to 10. So if its 9 or less it is added to the previous bundle of 25 (twentyfive_int)? Any ideas? thanks.

 

[blom0344]

Yes, with a modification to the additional table this is doable, but it really amounts to things usually done by usings cursors and code. (I am misusing SQL a bit in the next approach!)

Add a column "Counter" to the table we added:

1 1 1
2 2 1
3 2 2
4 3 1
5 3 2
6 3 3
7 4 1
8 4 2
8 4 3
8 4 4
.. .. ..

I will add another3 queries (make-table,update and delete) to the procedings and wrap everything in a macro. I'll send you the example database..........

T. Blom
Information analyst
tbl@shimano-eu.com