I have a table with 3 fields
Item;Qty;ReceiveID
ReceiveID is a foreign key to the one side of the relationship.

Is there a way using queries only, no code, to return a recordset
having one record for each item * the qty?
In other words - I have a record in the table, say Item - Widget, Qty
- 10, ReceiveID - 9999.
I need to create a recordset that has 10 records in it. 
Example records:
Widget,1,999
Widget,1,999
Widget,1,999 ect...

Again I want to do this using only a query.

Is this possible?

Thanks,

Brian

PS - Hammer if you're out there I would love to hear from you :->

Brian,

Good news!

I found a technique from Allen Browne that will work for you. Here is
the link to the newsgroup posting in which it appears:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=utf8&selm=Hnp4a.18128%24863.50425%40news-server.bigpond.net.au

Here is the post text itself:
**** Begin Quote ****

From: Allen Browne (abrowne1_SpamTrap@bigpond.net.au)
Subject: Re: Duplicating records
View: Complete Thread (10 articles)
Original Format
Newsgroups: microsoft.public.access.queries
Date: 2003-02-18 04:10:12 PST

Simon, you can do this without any code if you have a counting table
(i.e. a
table that has a record for each record from 1 to the highest number
you
will need). I'll assume this table is named tblCount, and has one
field
named CountID, of type Number (size Long Integer).

1. Create a query that has both your table and tblCount.

2. If you see any line joining the 2 queries, delete it: it is the
lack of a
join that gives you one record for each combination.

3. Drag the fields you want from your table into the query design
grid. Also
trag tblCount.CountID into the grid.

4. In the Criteria row beneath the tblCount.Count field, enter:
    <= [NameOfYourTableHere].[Qty]
This causes Access to limit the query to just the right number of
rows,
depending on the value in the Qty field.


Rather than enter all the records in tblCount by hand, this function
will
enter 1000 records for you. If you need more than that, increase the
constant before running it.

Function MakeData()
    'Purpose:   Create the records for a counter table.
    Dim db As Database                  'Current database.
    Dim lng As Long                     'Loop controller.
    Dim rs As DAO.Recordset             'Table to append to.
    Const conMaxRecords As Long = 1000  'Number of records you want.

    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("tblCount", dbOpenDynaset, dbAppendOnly)
    With rs
        For lng = 1 To conMaxRecords
            .AddNew
                !CountID = lng
            .Update
        Next
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    MakeData = "Records created."
End Function

-- 
Allen Browne - Microsoft MVP (Most Valuable Professional)
Allen Browne's Database And Training - Perth, Western Australia.
Tips for MS Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

**** End Quote ****

Let me know how this works out for you.

- Hammer

As always Hammer went out of her way and found me an answer. The
information she provided was perfect and worked out great for me.
Thanks again Hammer you're the best :->

I'm out here, but I don't know of a way to do this with only queries
without being unspeakably kludgy and inflexible. Maybe Mathtalk-ga has
a trick up his SQL sleeve...?

Why are you trying to do this? What are you trying to accomplish? Why
is using code not an option? Perhaps we can suggest a better way.

- Hammer

Hey Hammer,

Good to hear from you again. I've been neglecting my coding due to
some other obligations lately but I'm finally getting back into the
swing of things.

I thought there might be a little query trick that I was just not
seeing. I can accomplish this in code but it would have been better
for me to have a query automatically take care of it for me.

Thanks for the reply and if anyone else has a solution plase let me
know :->

Brian