Access97 query

[bethbob]

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]

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]

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]

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]

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.

 

[OBP]

I will post back tomorrow.