Need help with a query 2

[irethedo]

This is a simple one but for some reason I don't remember how to do this...

I have an query that makes a table but I want it to make one record for each quantity of
PC_frm_tbl.QtyLeft so that if the value of PC_frm_tbl.QtyLeft = 12 then I want the qry to create 12 new revords in the table

Thanks

SELECT PC_frm_tbl.ID, First(PC_frm_tbl.Part_No) AS FirstOfPart_No, PC_frm_tbl.Sys_PC, PC_frm_tbl.type, PC_frm_tbl.[Ordered Quantity], PC_frm_tbl.[Line Number], PC_frm_tbl.QtyLeft INTO Build_tbl
FROM PC_frm_tbl
GROUP BY PC_frm_tbl.ID, PC_frm_tbl.Sys_PC, PC_frm_tbl.type, PC_frm_tbl.[Ordered Quantity], PC_frm_tbl.[Line Number], PC_frm_tbl.QtyLeft, PC_frm_tbl.Checked
HAVING (((PC_frm_tbl.Checked)=True));

 

[Andrzejek]

If your [tt]Build_tbl[/tt] table have corresponding fields to your Select statement (number of fields, order of fields, type of fields, etc.) try:

INSERT INTO Build_tbl
SELECT ID, First(Part_No) AS FirstOfPart_No, Sys_PC, type, 
[Ordered Quantity], [Line Number], QtyLeft 
FROM PC_frm_tbl
GROUP BY ID, Sys_PC, type, [Ordered Quantity], 
[Line Number], QtyLeft, Checked
HAVING Checked = True;

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

If I understand correctly and you have a table like:
[pre]Color Qty
Red 3
Blue 4
Orange 1[/pre]

And you want to create a table like:
[pre]Color
Red
Red
Red
Blue
Blue
Blue
Blue
Orange[/pre]

You can create a table of numbers like:
[pre]Num
1
2
3
4
5
[/pre]

Then create a query like:

SELECT tblOrderQty.Color, tblNums.Num INTO tblNewTable
FROM tblNums, tblOrderQty
WHERE (((tblNums.Num)<=[Qty]));

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

Thanks Andy-

When I tried the code you posted I got a message that stated:

Circular reference caused by alias 'Ordered Quantity' in query definitions's SELECT list.

 

[Andrzejek]

When you run just your Select statement (which is your original Select without [tt]INTO Build_tbl[/tt]):
[tt]
SELECT ID, First(Part_No) AS FirstOfPart_No, Sys_PC, type,
[Ordered Quantity], [Line Number], QtyLeft
FROM PC_frm_tbl
GROUP BY ID, Sys_PC, type, [Ordered Quantity],
[Line Number], QtyLeft, Checked
HAVING Checked = True; [/tt]

What do you get? Any errors? Do you get the records you are trying to INSERT into Build_tbl ?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

, [Line Number], QtyLeft FROM PC_frm_tbl GROUP BY ID, Sys_PC, type, [Ordered Quantity], [Line Number], QtyLeft, Checked HAVING Checked = True; What do you get? Any errors? Do you get the records you are trying to INSERT into Build_tbl ? Have fun. ---- Andy]

Thanks Andy

When I run just my Select statement I get one entry so I modified this like Duane suggested and now I get a record for each of the quantity which was what I was looking for:

, PC_frm_tbl.[Line Number], PC_frm_tbl.QtyLeft, Count_tbl.Count FROM PC_frm_tbl, Count_tbl GROUP BY PC_frm_tbl.ID, PC_frm_tbl.Sys_PC, PC_frm_tbl.type, PC_frm_tbl.[Ordered Quantity], PC_frm_tbl.[Line Number], PC_frm_tbl.QtyLeft, PC_frm_tbl.Checked, Count_tbl.Count HAVING (((PC_frm_tbl.[Checked])=True) AND ((Count_tbl.Count)<=[PC_frm_tbl].[QtyLeft]));]