How to list only the current reassign in a query

[Lance A. via AccessMonster.com]

I have a issue tracking database that list users when an item is assigned and
then reassigned to a user group. Well the issue can be reassigned several
times. I only want to show the current reassignment. I have a query that
has many of the same id number but the date and timestamp makes the record
unique. right now it showing all the same idno's and listing them in
decending order (most current first).

How do i run the query to only list the current reassignment.

Thanks,
Lance

 

[Tom Ellison]

Dear Lance:

As your post is only a word description of your issue, I cannot provide any
specifics, which would be based on factors not given by you.

You can use an aggregate function, MAX, to determine the timestamp value
desired for the "current reassignment." Constructing your query with this
value returned by a subquery, you can filter the query to show only this
row.

If you need more specific assistance, please post the query you have now.
If the details are clear from this, I should be able to give you code to do
specifically what you need.

Tom Ellison

 

[Lance A. via AccessMonster.com]

Hi Tom, Here's my query code.

Thanks,
Lance

SELECT tblDataEntry.CompanyName, Max(tblReAssign.[Re-AssignedDate]) AS
[MaxOfRe-AssignedDate], Max(tblReAssign.[Re-AssignedTime]) AS [MaxOfRe-
AssignedTime], tblDataEntry.TrackingNo, tblReAssign.TrackingNo, tblDataEntry.
AssignedTo, tblDataEntry.AssignedDate, tblReAssign.[Re-AssignedTo],
tblDataEntry.ReassignDueDate, tblReAssign.[Re-AssignedDept], tblDataEntry.
FromDate, tblDataEntry.ToDate, tblDataEntry.IssueType, tblDataEntry.[Issue-
ActionNotes], tblDataEntry.Status, tblDataEntry.JOAmount, tblDataEntry.
JobOrder, tblDataEntry.[WE-Date], tblDataEntry.MAIdentifier, tblDataEntry.
CustomerNo, tblDataEntry.[IssueNotes-New], tblReAssign.[Re-AssignedDate]

FROM tblDataEntry INNER JOIN tblReAssign ON tblDataEntry.TrackingNo =
tblReAssign.TrackingNo

GROUP BY tblDataEntry.CompanyName, tblDataEntry.TrackingNo, tblReAssign.
TrackingNo, tblDataEntry.AssignedTo, tblDataEntry.AssignedDate, tblReAssign.
[Re-AssignedTo], tblDataEntry.ReassignDueDate, tblReAssign.[Re-AssignedDept],
tblDataEntry.FromDate, tblDataEntry.ToDate, tblDataEntry.IssueType,
tblDataEntry.[Issue-ActionNotes], tblDataEntry.Status, tblDataEntry.JOAmount,
tblDataEntry.JobOrder, tblDataEntry.[WE-Date], tblDataEntry.MAIdentifier,
tblDataEntry.CustomerNo, tblDataEntry.[IssueNotes-New], tblReAssign.[Re-
AssignedDate]

HAVING (((tblReAssign.[Re-AssignedTo])=[Forms]![frmReportPanel]![ReAssignedTo]
) AND ((tblDataEntry.Status)="ReAssigned"));

Dear Lance:

As your post is only a word description of your issue, I cannot provide any
specifics, which would be based on factors not given by you.

You can use an aggregate function, MAX, to determine the timestamp value
desired for the "current reassignment." Constructing your query with this
value returned by a subquery, you can filter the query to show only this
row.

If you need more specific assistance, please post the query you have now.
If the details are clear from this, I should be able to give you code to do
specifically what you need.

Tom Ellison

I have a issue tracking database that list users when an item is assigned
and

[quoted text clipped - 8 lines]

Thanks,
Lance

 

[Tom Ellison]

Dear Lance:

OK, now I'll have a go.

WHERE tblReAssign.[Re-AssignedDate] =
(SELECT MAX(T1.Re-AssignedDate)
FROM tblReAssign T1
WHERE T1.TrackingNo = tblDataEntry.TrackingNo)

It looks like this may work if you place the above code just before your
GROUP BY clause.

As you query code becomes larger and more complex, there are some things you
can do to improve its readability (not that what you present here is all
that terrible, but it's lengthy).

I recommend that this query would be a lot nicer with the addition of some
aliasing. If you have to come back to this code (or if someone else, like
me, must try to read it, a couple of short aliases not only shortens it but
makes it easier to digest.

Also, I usually prefer to place all the columns extracted from the same
table together in the SELECT list. This is a sytlistic thing, and has
nothing to do with anything but readability. If you have several tables
joined, you can even leave a blank line between the tables referenced in the
SELECT clause. No essential here, just a stylistic suggestion.

Hope this helps. Let me know how it works out, OK?

Tom Ellison

Hi Tom, Here's my query code.

Thanks,
Lance

SELECT tblDataEntry.CompanyName, Max(tblReAssign.[Re-AssignedDate]) AS
[MaxOfRe-AssignedDate], Max(tblReAssign.[Re-AssignedTime]) AS [MaxOfRe-
AssignedTime], tblDataEntry.TrackingNo, tblReAssign.TrackingNo,
tblDataEntry.
AssignedTo, tblDataEntry.AssignedDate, tblReAssign.[Re-AssignedTo],
tblDataEntry.ReassignDueDate, tblReAssign.[Re-AssignedDept], tblDataEntry.
FromDate, tblDataEntry.ToDate, tblDataEntry.IssueType,
tblDataEntry.[Issue-
ActionNotes], tblDataEntry.Status, tblDataEntry.JOAmount, tblDataEntry.
JobOrder, tblDataEntry.[WE-Date], tblDataEntry.MAIdentifier, tblDataEntry.
CustomerNo, tblDataEntry.[IssueNotes-New], tblReAssign.[Re-AssignedDate]

FROM tblDataEntry INNER JOIN tblReAssign ON tblDataEntry.TrackingNo =
tblReAssign.TrackingNo

GROUP BY tblDataEntry.CompanyName, tblDataEntry.TrackingNo, tblReAssign.
TrackingNo, tblDataEntry.AssignedTo, tblDataEntry.AssignedDate,
tblReAssign.
[Re-AssignedTo], tblDataEntry.ReassignDueDate,
tblReAssign.[Re-AssignedDept],
tblDataEntry.FromDate, tblDataEntry.ToDate, tblDataEntry.IssueType,
tblDataEntry.[Issue-ActionNotes], tblDataEntry.Status,
tblDataEntry.JOAmount,
tblDataEntry.JobOrder, tblDataEntry.[WE-Date], tblDataEntry.MAIdentifier,
tblDataEntry.CustomerNo, tblDataEntry.[IssueNotes-New], tblReAssign.[Re-
AssignedDate]

HAVING
(((tblReAssign.[Re-AssignedTo])=[Forms]![frmReportPanel]![ReAssignedTo]
) AND ((tblDataEntry.Status)="ReAssigned"));

Dear Lance:

As your post is only a word description of your issue, I cannot provide
any
specifics, which would be based on factors not given by you.

You can use an aggregate function, MAX, to determine the timestamp value
desired for the "current reassignment." Constructing your query with this
value returned by a subquery, you can filter the query to show only this
row.

If you need more specific assistance, please post the query you have now.
If the details are clear from this, I should be able to give you code to
do
specifically what you need.

Tom Ellison

I have a issue tracking database that list users when an item is assigned
and

[quoted text clipped - 8 lines]

Thanks,
Lance