Multiply records

[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

 

[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?

 

[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