This is a followup question to my august 3rd question that dealt with
union queries.

I have a table with 4 different date fields (Dept1 Date, Dept2 Date,
Dept3 Date, Actual Ship Date):
Each dept have their own due date (when the job needs to leave the
dept).  If a part has to go thru more than 1 dept, each dept will have
their own due dates.  The Actual Ship Date is when it finally shipped to
the customer.

job#,  dept1,       dept2,       dept3,        actualshipdate
1                                          7/25/99       7/25/99
2                                          7/26/99       7/26/99
3        7/25/99     7/26/99     7/30/99       7/29/99
4        7/29/99                      8/1/99         8/4/99
5                         7/26/99                        7/30/99

I am looking for a late and early shipping date report.  so any job that
was not shipped on time or was early should be on the report. the due
date of the last dept should be the header.

like below:

7/26/99
job#,  dept1,       dept2,       dept3,        actualshipdate,      days
late or early
5                         7/26/99
7/30/99                 4

7/30/99
job#,  dept1,       dept2,       dept3,        actualshipdate,      days
late or early

3        7/25/99     7/26/99     7/30/99       7/29/99                1

8/1/99
job#,  dept1,       dept2,       dept3,        actualshipdate,     days
late or early
4        7/29/99                      8/1/99
8/4/99                 3

Below is a copy of my first question and the answer

Thanks!
Ngan

Subject:  Re: Dates
Date: Tue, 3 Aug 1999 09:27:24 +0200

Organization: Telenor Online Public Access
 Newsgroups: microsoft.public.access.modulesdaovba
 References:  1

Hi,
You need to make a Union Query to obtain all the dates as:
qryGetDates:
Select dept1 as alldept From JobDates Where dept1 is not null
Union
select dept2 From JobDates where dept2 is not null
Union
select dept3 From JobDates where dept3 is not null

and then in your rptquery:

select distinct alldept, jobno, dept1,dept2,dept3 from JobDates JD,
qryGetDates GD
Where (AllDept = dept1 OR AllDept = dept2 OR AllDept = dept3)
Order by AllDept, JobNo
--
Regards
Pieter Wijnen

Everybody should believe in something - I belive I'll have another beer
Recommended Sites:
Dev's site   http://www.*-*-*.com/ ~dashish
Paradox   http://www.*-*-*.com/

Hi,

It seems nice, in this case, to separate the way it is presented and the way
it is stored. The presentation has to fit the satisfaction of the reader, a
human, so

is acceptable. But for the tool at hand, a relational database, it is
preferable to have:

JobID, TheDate, TypeOfDate
1, 7/25/99, dept3
1, 7/25/99, actualShip
2, 7/26/99, dept3
2, 7/26/99, actualShip
3, 7/25/99, dept1
3, 7/26/99, dept2
3, 7/30/99, dept3
3, 7/29/99, actualShip
....

and now, all the values you need to work with are values, not fields name.
That's help a little bit. Sure, the TypeOfDate can be numerical and a simple
INNER JOIN or a DLookup will convert it to string, but for clarity, I'll
keep a string representation.

-You can now write a query to find the Max value of TheDate, GroupBy JobID,
WHERE TypeOfDate <> actualShip.
-You can also quite simply subtract that found Max from the TheDate if the
corresponding JobID WHERE TypeOfDate = actualShip.
-You can write a crosstab query to pass from the data "as stored" to the
"view agreeable to the reader".

Every step is relatively simple, keeping a simple query one at a time, and
making them working in cascade or together through some JOINs, assuming you
get data in the second presentation.

Maybe the biggest problem would be to link the TypeOfDate to the Max value.
It is easy to get that the max value for theDate, excluding TypeOfDate =
actualShip, is the 7/30/99:

    New query, bring your table, push the summation button on the toolbar.
Bring JobID, keep the GroupBy; Bring TheDate, change GroupBy to Max; Bring
TypeOfDate, change GroupBy to Where and add a criteria: <> actualShip. Save
it (Q1).

but harder, a little bit, to get its associated value, here like dept3. But
just a little bit, as I said:

    New query, bring your table and Q1. JOIN JobID of your table and
Q1.JobID if Access doesn't do it already. JOIN Table.TheDate and
Q1.MaxOfTheDate too. Bring JobID (table or query, your choice), TheDate,
TypeOfDate, adding the criteria <> actualShip for the TypeOfDate. Observe
that if the same max occurs at two different department at the same time,
they are both listed.

Hoping it may help,
Vanderghast, Access MVP.

was wondering if i can somehow use what I have now, in terms of how the table is
currently set up?  or do i need to restructure the table to make it work?

The reason I ask is that the table already contain a couple thousand records and
if I have to restructure the table, it could be time consuming.  Just wondering
if I could work with what I have now for the short term, because we are in the
middle of evaluating a packaged software that would do our job tracking for us.
We're trying to stretch out our existing db as much as we can (even tho it's not
really relational).

Thanks.

Actually, I found a easier way to narrow down the results.
I assumed that if dept1 has a date and the other depts dont, then dept1 is the
max date.
If dept2 has a date and the dept3 doesnt, then dept2 is the max date (dept1
doesnt matter because if there was a date, it would/should always be earlier
than the date of dept2).
If dept3 has a date, that means it's the max because the other depts
would/should have earlier dates.

With this assumption and the extra assumption of the max date <>
actualshipdate,  I was able to get the correct results.  I just have to figure
out how to layout the results in a report.

Thanks.

below is the sample code if anyone wants to know:

SELECT [Job #], ActualShipDate, Dept1, Dept2, Dept3
FROM [table1]
WHERE ActualShipDate Is Not Null And ActualShipDate Between [Some Date] And
[Some Date]
AND (
(Dept1 <> ActualShipDate and Dept2 Is Null and Dept3 Is Null)
or
(Dept2 <> ActualShipDate and Dept3 Is Null)
or
(Dept3 <> ActualShipDate)
);