Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    Twin Cities, MN
    Posts
    8

    Unhappy Unanswered: FindFirst? I'd settle for JustFindAnything! Help!!!

    I am trying to pull a REPORT from a query linked to a few tables. One of the tables is MAIN (has most info) and the other table is ITEMS.

    The ITEMS table contains an invoice description (each line item/cost is a record). These line items are then pulled and put into a form based on the PURCHAUTO linked to each item record. (PURCHAUTO is linked to all my tables)

    Example:

    PURCHAUTO NAME QTY TTL COST
    80 dog food 4 20.00
    80 dog collar 2 20.00
    80 dog treat 1 20.00
    81 cat food 1 16.00
    81 catnip 5 16.00
    82 horse feed 2 5.00
    83 chicken wire 3 15.00


    In ITEMS table, the PURCHAUTO column, has several items listed line by line for the same PURCHAUTO number. This prints out line items on my purchase requisition forms. I need to keep my tables as is for that reason.

    Problem is, now I want to create a REPORT showing what requisitions I have that have not been assigned an order number. I want a BRIEF description of the item ordered, but since the PURCHAUTO number is linked, I get multiple lines with each description and purchase.

    Example: I pull a report and I get these results:

    PURCHAUTO NAME QTY TTL COST
    80 dog food 4 20.00
    80 dog collar 2 20.00
    80 dog treat 1 20.00

    When what I WANT is this:

    PURCHAUTO NAME QTY TTL COST
    80 dog food 4 20.00

    It doesn't even have to be the first description listed. I just need ONE of the items listed under that PURCHAUTO to give me the jist of what was ordered.

    I created a QUERY to pull all the info I need for the report, and I have the report linked to that QUERY. My QUERY pulls from both MAIN table and ITEMS table (linked by that PURCHAUTO number).

    I'm not an expert at Access by any stretch of the imagination. I've been working on this for DAYS AND DAYS and I'm driving myself crazy. I can't find anything in the libraries here that refer to what I'm trying to do.

    In your explanation, don't assume I know what you're talking about if it's relatively complicated. I can look up just about anything to find answers but when you start abbreviating things and speaking French, well I'm just plain lost!

    I really need help.

    I'm even ready to start bribing people! One dollar? Two? Three?

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Here's one of several ways to do this. Quickest/easiest way I can think of...

    Go into the design of the query. Go to SQL view.

    It will say "SELECT..........."

    Make that line read "SELECT TOP 1......"

    I think that will solve your problem and it's free

  3. #3
    Join Date
    Apr 2004
    Location
    Twin Cities, MN
    Posts
    8

    Didn't work!

    Well, it SORT of did... it gave me just one line item, but with just ONE record. So I have a report showing only the first invoice basically. The rest of the invoices didn't show up.

    Help!!

  4. #4
    Join Date
    Apr 2004
    Location
    Twin Cities, MN
    Posts
    8

    Here's the code in SQL view...

    Maybe this will help? It's the code from the query:

    SELECT Main.[Requisition Date], Main.[AssetMgmt Date], Main.PurchAutoID, Main.[Cost Center], Main.Requestor, Vendor.VendName, Items.Name, Items.PurchAutoNumber, Main.[Inv Amount], Main.[Purch Ord No]
    FROM Vendor INNER JOIN (Main INNER JOIN Items ON Main.PurchAutoNumber = Items.PurchAutoNumber) ON Vendor.VendAutoNumber = Main.VendNumb
    WHERE (((Main.[Purch Ord No]) Is Null))
    ORDER BY Main.[Requisition Date], Main.[AssetMgmt Date], Main.PurchAutoID;

  5. #5
    Join Date
    Apr 2004
    Location
    Twin Cities, MN
    Posts
    8

    I almost have it.... I think!

    I've got code that SHOULD work, but I'm still getting the multiple line thing on my report.

    SELECT DISTINCT
    Main.PurchAutoID,
    Main.[Requisition Date], Main.[AssetMgmt Date], Main.PurchAutoID, Main.[Cost Center], Main.Requestor, Vendor.VendName, Items.Name, Items.PurchAutoNumber, Main.[Inv Amount], Main.[Purch Ord No]
    FROM Vendor INNER JOIN (Main INNER JOIN Items ON Main.PurchAutoNumber = Items.PurchAutoNumber) ON Vendor.VendAutoNumber = Main.VendNumb
    WHERE (((Main.[Purch Ord No]) Is Null))
    ORDER BY Main.[Requisition Date], Main.[AssetMgmt Date], Main.PurchAutoID;


    What am I doing wrong??

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I believe this article is what you want. It tells you how to get the Top N Values from a group. I think what you will need is the top 1 value from each PURCHAUTO.

    http://support.microsoft.com/default...47&Product=acc

  7. #7
    Join Date
    Apr 2004
    Location
    Twin Cities, MN
    Posts
    8

    Tried it...

    I just can't get it to work.

    I think I'm hopeless.

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Is there any way I could take a look at your database and see if I could get it doing something that you would like?

    I am not quite sure I completely understand what you are wanting...

    Do you want a report that has all the items ordered like the dog food and the collar and the bone, grouped under the 80 (purchauto) field?

    You can add grouping levels to reports if that is what you are wanting. Also if you are wanting to select only those records that dont have a purchauto number associated I think I could make that work to. I havent had much experience in SQL so I wouldnt know exactly how to write it all out for you. :-/

    Hope I can help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Re: Tried it...

    Originally posted by goldilox
    I just can't get it to work.

    I think I'm hopeless.
    I took a stab at this. (Worked for me with a simplistic db)

    I added the 2nd part of the Where Clause

    SELECT Main.[Requisition Date], Main.[AssetMgmt Date], Main.PurchAutoID, Main.[Cost Center], Main.Requestor, Vendor.VendName, Items.Name, Items.PurchAutoNumber, Main.[Inv Amount], Main.[Purch Ord No]
    FROM Vendor INNER JOIN (Main INNER JOIN Items ON Main.PurchAutoNumber = Items.PurchAutoNumber) ON Vendor.VendAutoNumber = Main.VendNumb
    WHERE ((Main.[Purch Ord No]) Is Null) And ((Items.Name) In (Select Top 1 [Name] FROM Items Where Main.PurchAutoNumber = Items.PurchAutoNumber))
    ORDER BY Main.[Requisition Date], Main.[AssetMgmt Date], Main.PurchAutoID;

  10. #10
    Join Date
    Apr 2004
    Location
    Twin Cities, MN
    Posts
    8

    Thank you thank you thank you!

    Your code worked PERFECT!

    Thank you sooooooooooooooooooo much!

    I owe you big time!!

  11. #11
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Re: Thank you thank you thank you!

    Originally posted by goldilox
    Your code worked PERFECT!

    Thank you sooooooooooooooooooo much!

    I owe you big time!!
    No prob, glad it worked

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •