Google Answers Logo
View Question
Q: Access Query ( Answered 5 out of 5 stars,   2 Comments )
Subject: Access Query
Category: Computers > Programming
Asked by: bselltiz-ga
List Price: $5.00
Posted: 18 Jul 2003 14:41 PDT
Expires: 17 Aug 2003 14:41 PDT
Question ID: 232593
I have a table with 3 fields
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 ect...

Again I want to do this using only a query.

Is this possible?



PS - Hammer if you're out there I would love to hear from you :->
Subject: Re: Access Query
Answered By: hammer-ga on 18 Jul 2003 16:04 PDT
Rated:5 out of 5 stars

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:

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

From: Allen Browne (
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
will need). I'll assume this table is named tblCount, and has one
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
depending on the value in the Qty field.

Rather than enter all the records in tblCount by hand, this function
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
                !CountID = lng
    End With
    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 -
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

**** End Quote ****

Let me know how this works out for you.

- Hammer
bselltiz-ga rated this answer:5 out of 5 stars and gave an additional tip of: $5.00
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 :->

Subject: Re: Access Query
From: hammer-ga on 18 Jul 2003 15:48 PDT
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
Subject: Re: Access Query
From: bselltiz-ga on 18 Jul 2003 15:57 PDT
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 :->


Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy