Manage Trackable Parts (1 Viewer)

kobiashi

Registered User.
Local time
Today, 12:30
Joined
May 11, 2018
Messages
258
Hi all,

im trying to create a model to manage parts from one location to another, two locations are either on a vehicle or in stores, im currntly trying to manage it like this

TblEventPartsLocation
EventTrackableParts_ID
EventTracker_IDFK
Vehicle_IDFK
Part_IDFK
PartLocation_IDFK
TimeStamp

TblPart
Part_ID
SerialNumber
Quantiy
TimeStamp

TblVehicle
Vehicle_ID
VehicleName
TimeStamp

TblPartLocation
PartLocation_ID
PartLocation
TimeStamp

TblPart.Part_ID is linked to TblEventTrackablePart.Part_IDFK
TblVehicle.Vehicle_ID is linked to Tbl.EventTrackablePart.Vehicle_IDFK
TblPartLocation.PartLocation_ID is linked to Tbl.EventTrackablePart.PartLocation_IDFK


So the idea is a part is either on a vehicle or in a part location in this case its stores, i can do this by adding new events to the TblEventTrackablePart Table, but what im struggling to do is return the most recent entry, be it either on a vehicle or in a part location (Stores).

i have two forms that manage the location of the part, Form add part which adds the part to a vehicle, and a remove part which removes the part from a vehicle and adds it back to stores, in both cases i use an append query to add a new entry in to the table and i use DoCmd.GoTorecord,, AcLast to access the relevant record, this works fine, but what im struggling at is on the add and remove form i hava a combo box with a query, on the add part form, the combo box should return all parts that are assigned to stores, and this is where im struggling, i can return the latest entry for that part, so if all the parts are assigned to vehicles then the query should return nothing, but i cant get it to do that, hence the question, of whether this is the correct model.

Im not sure if this is the best way to manage the part location, if any body has a better idea to manage them i would love to hear.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:30
Joined
May 7, 2009
Messages
19,169
on my opinion, you must add each part on separate record.
say, common, alternator.
if there are 5, you enter each on it's own record.

tblpartlocation is not needed (opinion).

you can have another column on tblPart (UsedIn, text), it can be Null (not used therefore in store,
reg-number, meaning it is already attached to that vehicle).

to put back in inventory, just Null this field.
to put to vehicle, just saved the reg-no (plate no, etc).
 

kobiashi

Registered User.
Local time
Today, 12:30
Joined
May 11, 2018
Messages
258
thank you for the reply

the reason for the TblPartLocation is for traceability of the part, so i can track where the part has been, ie what vehicle,
 

Users who are viewing this thread

Top Bottom