Rotational Wrecker List 1

[dortoh]

There was a post which was talked about a couple years back...ironically it still helped me in developing the following database in access. I too work for a police department and we did not have a means to track tow companies and the infamous "Rotational Tows". I want to be able to share that database with anyone who may need it.

I need help keeping track of wreckers being called and arriving on scene, but then law enforcement officer canceling on scene. This would then permit the tow company to be put "Back on Top" of the rotation list.

For instance...
.1. Tow One called/dispatched.
.2. Tow One arrives on Scene One.
.3. Tow Two is called from the rotation list and "Used" at Scene Two.
.4. Tow One is "Canceled".
.5. Tow One needs to be put on the top of the rotation list, before say, Tow Three is used.

At the moment we are able to just re-select Tow One from the bottom of the dropdown list, which makes it prone to user error. It would be nice to have the "Status" (Canceled, No Answer, Refused, Used) when selected as canceled, to have the Tow One placed back on top of the list...

Tables:
- Status (ID, Title, Type)
- WreckerLog (LogID, Title, Date, Requester, Location, Vehicle, Notes, Status, fkWreckerID)
- Wreckers (WreckerID, Title, WreckerName, PhoneNumber, AreaType)

Forms:
- frmLogNorth
- frmLogSouth
- frmMain
- subWreckerLogNorth
- subWreckerLogSouth

Queries:
qryAllRecsNorth:
SELECT qryLastAssign.WreckerName, qryLastAssign.LastDate, WreckerLog.fkWreckerID, qryLastAssign.AreaType
FROM WreckerLog LEFT JOIN qryLastAssign ON WreckerLog.fkWreckerID = qryLastAssign.fkWreckerID
WHERE (((qryLastAssign.AreaType)="Northern"))
ORDER BY qryLastAssign.LastDate, WreckerLog.fkWreckerID;

qryLastAssign:
SELECT Max(WreckerLog.Date) AS LastDate, WreckerLog.fkWreckerID, Wreckers.WreckerName, Wreckers.AreaType
FROM Wreckers INNER JOIN WreckerLog ON Wreckers.WreckerID = WreckerLog.fkWreckerID
GROUP BY WreckerLog.fkWreckerID, Wreckers.WreckerName, Wreckers.AreaType
ORDER BY Max(WreckerLog.Date), WreckerLog.fkWreckerID;

qryNorth:
SELECT WreckerLog.*, Wreckers.AreaType
FROM Wreckers INNER JOIN WreckerLog ON Wreckers.WreckerID = WreckerLog.fkWreckerID
WHERE (((Wreckers.AreaType)="Northern"));

qryRotationList:
SELECT WreckerLog.*, Wreckers.AreaType
FROM Wreckers INNER JOIN WreckerLog ON Wreckers.WreckerID = WreckerLog.fkWreckerID
WHERE (((Wreckers.AreaType)="Northern"));

Thank you in advance.

Database:

http://files.engineering.com/getfil...3d2eb90a83e1&file=RotationList_-_Tables.accdb

Database Schema Map:

http://files.engineering.com/getfile.aspx?folder=bf3f9299-0b8d-434a-98ce-82878ede70de&file=db.pdf

 

[missinglinq]

Not knowing your business, I'm not going to even try to wade through your data scheme, but I will suggest a general approach.

Looking at qryLastAssign, I assume that the Field in the Table WreckerLog named Date represent the last date, or presumably date/time, that a given wrecker answered a call. I also assume that this is what you're using to determine who is 'next up.' If these assumptions are correct, I would
[ul]
[li]Create a new Field, and for convenience we'll call it PrevCallDateTime.[/li]
[li]As a new date/time is entered in Date, assign the the old date/time (the one currently in Date) to the PrevCallDateTime Field.[/li]
[li]If the Status Field is a marked as anything except Used, assign the Value in PrevCallDateTime back to the Date Field.[/li]

[/ul]When you next run your Query (qryLastAssign, I think) the wrecker whose call was Cancelled/Not Answered/Refused should then be back at the top of the list.

As I said, this is only a general approach, but should guide you in obtaining your stated goal.

I would strongly suggest that you change the name of the Field currently call Date to something else! Reserved Words, such as Date, should never be used as the name of a Field; it tends to confuse the Access Gnomes! And of all of the Reserved Words, the word Date is the possibly the worst to use in this manner!



The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[dortoh]

Thank you Missinglinq for your assistance. I will have to research how to exactly have the dates swap back and forth programmically... If you have a suggestion for that it would be appreciated.

Thanks,
Dortoh