Continue: Effective sequence

[Guest]

SELECT E.BranchID, Branch_TBL.Branch_Desc, Branch_TBL.BranchCategory,
Count(E.BranchID) AS CountOfBranchID, E.EffectiveDate, E1.EmployeeID,
E.Action_Abrv, E.Status, E.EffectiveSequence
FROM Branch_TBL INNER JOIN

(
History_Internal_Employment AS E INNER JOIN

(SELECT EmployeeID, Max(EffectiveDate) as MaxDate
FROM History_Internal_Employment
WHERE EffectiveDate <= [Enter Date]
GROUP BY EmployeeID) AS E1 ON

(E.EffectiveDate = E1.MaxDate) AND (E.EmployeeID = E1.EmployeeID)
)

ON Branch_TBL.BranchID = E.BranchID
WHERE (((E.Status)="Active") AND ((E.EffectiveSequence)=(SELECT
Max(E.EffectiveSequence) FROM History_Internal_Employment WHERE
E.EmployeeID=E1.EmployeeID )))
GROUP BY E.BranchID, Branch_TBL.Branch_Desc, Branch_TBL.BranchCategory,
E.EffectiveDate, E1.EmployeeID, E.Action_Abrv, E.Status, E.EffectiveSequence;

Hi these are the codes to extract a certain data. It does the following

1)Take those effective dates <= [Date Prompt]
2)Take maximum effective date
3)Did a JOIN from Branch_TBL.branchID with History Data BranchID
4) Only select those whose Status are Active

The problem is...

there's a record that has 2 event happen in 1 day, and it show up as 2
records.

For example in sequence:ID,Name,BranchID,EffectiveDate,EffectiveSequence,

0001 Mr A 3201 1-8-2000 0
0001 Mr A 3201 1-8-2000 2

I have problems fixing in the query and trying to put in the effective
sequence but it fails... Have tried for around a week but just give up. I
just need to adjust the codes in such a way that out of the two record in the
example, the results only show the max EffectiveSequence

Your help is deeply appreciated.

 

[Tom Ellison]

Dear Liz:

For my own reference, I'm going to rearrange your query a bit:

SELECT E.BranchID, B.Branch_Desc, B.BranchCategory,
Count(E.BranchID) AS CountOfBranchID,
E.EffectiveDate, E1.EmployeeID, E.Action_Abrv,
E.Status, E.EffectiveSequence
FROM Branch_TBL B
INNER JOIN (History_Internal_Employment AS E
INNER JOIN (SELECT EmployeeID,
Max(EffectiveDate) as MaxDate
FROM History_Internal_Employment
WHERE EffectiveDate <= [Enter Date]
GROUP BY EmployeeID) AS E1
ON E.EffectiveDate = E1.MaxDate
AND E.EmployeeID = E1.EmployeeID)
ON B.BranchID = E.BranchID
WHERE E.Status = "Active"
AND E.EffectiveSequence =
(SELECT Max(E.EffectiveSequence)
FROM History_Internal_Employment
WHERE E.EmployeeID = E1.EmployeeID)
GROUP BY E.BranchID, B.Branch_Desc, B.BranchCategory,
E.EffectiveDate, E1.EmployeeID, E.Action_Abrv, E.Status,
E.EffectiveSequence;

The next thing I did was to try to figure out in which table the two rows
exist. I take it this comes from History_Internal_Employment. Is this
correct?

Now, if you run your subquery by itself:

SELECT EmployeeID,
Max(EffectiveDate) as MaxDate
FROM History_Internal_Employment
WHERE EffectiveDate <= [Enter Date]
GROUP BY EmployeeID

You get only one value for each EmployeeID, right? And it is the one you
want, right?

Next, I see you have joined this to the same table
(History_Internal_Employment) on the EmployeeID and the MaxDate. Does this
portion work correctly?

SELECT E.EmployeeID, E.EffectiveDate
FROM History_Internal_Employment AS E
INNER JOIN (SELECT EmployeeID,
Max(EffectiveDate) as MaxDate
FROM History_Internal_Employment
WHERE EffectiveDate <= [Enter Date]
GROUP BY EmployeeID) AS E1
ON E.EffectiveDate = E1.MaxDate
AND E.EmployeeID = E1.EmployeeID

All I've done here is to add the first line to SELECT the two columns as a
test. The rest of the code is pretty much what you already had.

If this is working properly, I'm going to suggest you save this as a
separate query, then join that into your main query instead embedding it
inside. You'll need to add the other columns I omitted. When you JOIN to
this instead, what happens?

First I'm trying to do is deconstruct your original query. I want to see if
the pieces work independently. Also, to keep complexity down, when I work
with the Jet database, I break the thing into pieces with which I expect to
have less difficulty.

May I suggest that the complexity of the query work you're doing here will
be much easier if you do it with MSDE instead. At least, that was my
experience several years ago. This is a considerable shift, possibly not to
be taken in the middle of a project, but before starting another.

Tom Ellison

SELECT E.BranchID, Branch_TBL.Branch_Desc, Branch_TBL.BranchCategory,
Count(E.BranchID) AS CountOfBranchID, E.EffectiveDate, E1.EmployeeID,
E.Action_Abrv, E.Status, E.EffectiveSequence
FROM Branch_TBL INNER JOIN

(
History_Internal_Employment AS E INNER JOIN

(SELECT EmployeeID, Max(EffectiveDate) as MaxDate
FROM History_Internal_Employment
WHERE EffectiveDate <= [Enter Date]
GROUP BY EmployeeID) AS E1 ON

(E.EffectiveDate = E1.MaxDate) AND (E.EmployeeID = E1.EmployeeID)
)

ON Branch_TBL.BranchID = E.BranchID
WHERE (((E.Status)="Active") AND ((E.EffectiveSequence)=(SELECT
Max(E.EffectiveSequence) FROM History_Internal_Employment WHERE
E.EmployeeID=E1.EmployeeID )))
GROUP BY E.BranchID, Branch_TBL.Branch_Desc, Branch_TBL.BranchCategory,
E.EffectiveDate, E1.EmployeeID, E.Action_Abrv, E.Status,
E.EffectiveSequence;

Hi these are the codes to extract a certain data. It does the following

1)Take those effective dates <= [Date Prompt]
2)Take maximum effective date
3)Did a JOIN from Branch_TBL.branchID with History Data BranchID
4) Only select those whose Status are Active

The problem is...

there's a record that has 2 event happen in 1 day, and it show up as 2
records.

For example in sequence:ID,Name,BranchID,EffectiveDate,EffectiveSequence,

0001 Mr A 3201 1-8-2000 0
0001 Mr A 3201 1-8-2000 2

I have problems fixing in the query and trying to put in the effective
sequence but it fails... Have tried for around a week but just give up. I
just need to adjust the codes in such a way that out of the two record in
the
example, the results only show the max EffectiveSequence

Your help is deeply appreciated.