Access97 query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

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?
 

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?
 

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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top