1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access97 query

Discussion in 'Business Applications' started by bethbob, Oct 6, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. bethbob

    bethbob Thread Starter

    Joined:
    Oct 6, 2008
    Messages:
    3
    Yes, I know Access97 is outdated and very old - but bear with me. We have two tables. One holds the sales order header file such as the sales order and the customer name. The other holds the sales order detail file - the individual line items on that sales order and information such as the part number, qty, date.ordered, & date.shipped.

    Example
    SOH_Header -
    so_number 12345

    SOH_Detail -
    so.nbr 12345
    line.nbr 1
    date.ordered 09-01-08
    date.shipped 09-02-08

    so.nbr 12345
    line.nbr 2
    date.ordered 09-02-08
    date.shipped 09-04-08

    so.nbr 12345
    line.nbr 3
    date.ordered 09-03-08
    date.shipped 09-03-08

    I need to create a query that will pull the earliest date.ordered field and the latest date.shipped field for this sales order and all other sales orders created this year. I'm thinking I may need to have a query running a subquery but I'm not sure of the joins.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Hello and welcome to the Forum, yes a simple 2 Table join query is what you need, but also a Criteria to limit the Records to this year.
    Can you post a zipped copy of the database with a couple of Dummy records in it, you will probably not be able to post it yet as you may not have enough posts.

    What differentiates between line 1, line 2 etc?
    Do you have an Order No?
     
  3. bethbob

    bethbob Thread Starter

    Joined:
    Oct 6, 2008
    Messages:
    3
    The line.nbr is the differentiating factor in the detail file. Unfortunately, the example I've described is not the exact tables I'm extracting information from. Its just an example I thought I could describe my tables best.

    I currently have a working query from the header file that limits my "sales orders" within a particular date range. But now I need to pull the earliest date.ordered field and the last date.shipped field from this sales orders multiple line items.

    Does this help?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    bethbob, it helps, but unfortunately it is not enough to really allow me to help you.
    When you say "pull the earliest date.ordered field and the last date.shipped field" do you mean to pull all of the records between those dates or just the records with those dates.
    To pull those Dates you would use "Totals" Group By and "Min" in the date.ordered field & "Max" in the date.ordered field.
    But I am not sure what kind of output you will get, so you may need more than one query.
     
  5. bethbob

    bethbob Thread Starter

    Joined:
    Oct 6, 2008
    Messages:
    3
    In the example given above with the 3 line items I would want the result to show:

    so.number 12345
    date.ordered 09-01-08 (earliest date came from line item 1)
    date.shipped 09-04-08 (latest date came from line item 2)

    Thing is I only want one result shown for each sales order - the earliest date.ordered and the last date.shipped field.

    Thanks.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I will post back tomorrow.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/756612

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice