Results 1 to 6 of 6
  1. #1
    r0nP is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    3

    Setting the number of records to return based on the results of another query

    Let me start by saying I'm not a VB person, but I think the only way for me to do this is through code module.



    I have one report that is combining the results from two queries.

    the first query is the result of a user scanning inventory into the database. It is returning information such as the part number, description, UPC and a sum field for Qty. So if he scans 5 pieces the report returns one line of 5.

    the second query is showing any inventory in a specified location (location x) that matches the scanned results.

    So my user scans 5 pieces of part A and my queries find 7 pieces of part A in location x.

    Currently my report will return all 7 records when I only need the oldest 5 that matches the qty of what was scanned.

    Is there a way to set the number of return records based on the recordset of another query?

    any suggestions will be greatly appreciated.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Afraid your example is a bit confusing. Can you rephrase with actual examples of what you are getting as opposed to what you want.

    David

  3. #3
    r0nP is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    3
    OK I will try:

    Query6

    Does a summary of one table: Giving me the following:

    Storage Location
    Part Number
    UPC
    CountOfscannedParts - Summary of parts scanned

    I perform an INNER JOIN (on Part Number) to a listing of inventory of location DCUNPICK01

    I am trying to determine of the records found in Query6 are there any matching inventory for that part in this location.


    Now what I want to do is:

    I return a part in Query 6 that had CountOfscannedParts = 4

    In my table DCUNPICK01 I have 7 individual records for this part, all parts having Date Added date...

    I want to return only the 4 oldest not all 7.

    Since I do my join just on part number I am returning all 7 records.

    Does that help?

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    What is it about the other 3 records that you don't want? I think somewhere your joins are wrong. You can use the SELECT TOP n syntax in your query and by sorting the results descending by date you would get the last n, but as you say this would involve VBA to require the sql for the query prior to launching.

    David

  5. #5
    r0nP is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    3
    The matching inventory is returning a bar code to print on my report, that my putaway associate will use to scan and use to put the inventory away...so I only need a matching number of records to be return, anymore and it is just adding to the length of my report.

    If I did use a SELECT TOP wouldnt that apply to the whole query no matter the number of parts scanned that match the number of records found in the unpick location table?

    I kinda figured that it would take a some sort of VB to complete this.

    Thank you for your help though

  6. #6
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Can you provide a cut down copy of your mdb to look at? I am stillnot clear on your objective.


    From what I can see your query6 is based on an unknown table that groups by Location by part number by ucpwith a count of the number of parts. In you example 4

    Then in a second query you bring in the above query and create a join between the location fields. This is where it gets confusing. Your location table has 7 parts based on a particular location, each with a different date added date. Now obviously you want to reconcile only the number of parts that appear in the first query (4) so using a LIFO system you choose the last four items. Without looking at your tables it is hard to visualise what you have. It may be that your structure is not normalised.

    David

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to Add New Records based upon Query?
    By SteveAb in forum Database Design
    Replies: 0
    Last Post: 08-06-2009, 10:24 PM
  2. Replies: 0
    Last Post: 08-04-2009, 08:51 PM
  3. Replies: 1
    Last Post: 01-31-2009, 10:43 AM
  4. Replies: 4
    Last Post: 01-29-2009, 02:43 AM
  5. Return records not updated by update query
    By ars80 in forum Queries
    Replies: 2
    Last Post: 05-01-2006, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums