Multiply records (1 Viewer)

[Gismo]

Hi All,

Please could you assist?

I need to create a query to multiply records in a new table

After I have ordered a product that requires an expiry date and a batch number, i need to receive the product
If 2 was ordered, I need to receive 1 and 1 to be able to add the batch number and the expiry date

if 4 was ordered, i need to receive 1+1+1+1

How will I accomplish this?
Or am I approaching this from an incorrect angle?

 

[Josef P.]

Use an auxiliary table with numbers (1..n).

select
    ...
from
    YourTable as T
    inner join
    NumberTable as N ON N.Num <= T.Quantity

Or am I approaching this from an incorrect angle?

This could also be - keyword: normalization.

 

[Gismo]

Use an auxiliary table with numbers (1..n).

select
    ...
from
    YourTable as T
    inner join
    NumberTable as N ON N.Num <= T.Quantity

This could also be - keyword: normalization.

Not sure I understand

This is my attempt

SELECT [Transactions - Procurement].[Document Number], [Transactions - Procurement].[Product ID], [Transactions - Procurement].Qty
FROM [Transactions - Procurement]
Inner join
[Transactions - Procurement] as N ON N.Num <= [Transactions - Procurement].Qty
WHERE ((([Transactions - Procurement].[Document Number])=49));

 

[Josef P.]

The number table requires only one data field:
Num
1
2
3
4
5
...
99999999

 

[Gismo]

The number table requires only one data field:
Num
1
2
3
4
5
...
99999999

I am getting everything except 1 2 3

below is my sample data

The output should be 1+1 for product id = 71
and 1+1 for product id 72

This is the output I get from the query

SELECT [Transactions - Procurement].[Document Number], [Transactions - Procurement].[Product ID], [Transactions - Procurement].Qty, N.Qty
FROM [Transactions - Procurement], [Transactions - Procurement] AS N
WHERE ((([Transactions - Procurement].[Document Number])=49));

 

[Josef P.]

Join is missing. But you can write this in the where part, then the query editor will handle it.
You need an extra number table.

SELECT 
   [Transactions - Procurement].[Document Number], [Transactions - Procurement].[Product ID]
   , [Transactions - Procurement].Qty
   , N.Num
FROM
    [Transactions - Procurement], [ExtraTableWithNumbers] AS N
WHERE
   [Transactions - Procurement].[Document Number]=49
   and
   N.Num <=  [Transactions - Procurement].Qty

 

[Gismo]

Join is missing. But you can write this in the where part, then the query editor will handle it.
You need an extra number table.

SELECT
   [Transactions - Procurement].[Document Number], [Transactions - Procurement].[Product ID]
   , [Transactions - Procurement].Qty
   , N.Num
FROM
    [Transactions - Procurement], [ExtraTableWithNumbers] AS N
WHERE
   [Transactions - Procurement].[Document Number]=49
   and
   N.Num <=  [Transactions - Procurement].Qty[/CODE
[/QUOTE]
Thank you very much

 

[Pat Hartman]

Here is a working sample. That might help. Some people call the "number" table a tally table. This solution also works for the problem of generating dates between x and y so you have a record for each day even if you don't have any transactions for that day so you can make the report/chart complete. Use dates in the "number" table instead of numbers.

 

[Gismo]

Here is a working sample. That might help. Some people call the "number" table a tally table. This solution also works for the problem of generating dates between x and y so you have a record for each day even if you don't have any transactions for that day so you can make the report/chart complete. Use dates in the "number" table instead of numbers.

Thank you,

This in not quite what i need

If product 1 requires quantity 3 on an order
I need to receive
Qty 1 - Expiry date - Batch Number
Qty 1 - Expiry date - Batch Number
Qty 1 - Expiry date - Batch Number

Each qty received requires a different batch number

I dont want to tally as per your sample as such

Hope my explanation make sense

 

[Josef P.]

Can you create a new table?

 

[Pat Hartman]

The example doesn't "tally" anything. The example is generating some number of child records based on the number requested. The objective of the example is to produce labels. If you want 5 labels for an order, it generates 5 records numbered 1-5. Sure sounds like what you are doing. You are just adding more data to the "label"
1 of 5
2 of 5
3 of 5
4 of 5
5 of 5

 

[Gismo]

Can you create a new table?

I have created the table yes

 

[Josef P.]

Is it already working?
In #7 is only a quote, so I did not read the last sentence before.

 

[Gismo]

Is it already working?
In #7 is only a quote, so I did not read the last sentence before.

Yes it is working perfect
Thank you for the advice


below my order

Below the outcome