INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help with a query

Need help with a query

(OP)
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



CODE -->

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)); 

RE: Need help with a query

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

CODE

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.

RE: Need help with a query

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

And you want to create a table like:
Color
Red
Red
Red
Blue
Blue
Blue
Blue
Orange 

You can create a table of numbers like:
Num
1
2
3
4
5
 

Then create a query like:

CODE --> vba

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

RE: Need help with a query

(OP)

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


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.


RE: Need help with a query

When you run just your Select statement (which is your original Select without 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;


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.

RE: Need help with a query

(OP)

Quote (When you run just your Select statement (which is your original Select without 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; 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:

CODE --> Quantity

, 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]));] 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close