Struggling to understand the link.

[Bopsgtir]

Hi all, what im trying to do is link some tables used to order a fuel card,

So in my Employees Tbl (TblTechs) i have a field saying FuelCardRequired, when you use a form to raise a new starter the FuelCardRequired being TRUE when submitted creates an email to order the card, now my issue is how to i store the card ordered data.

i have three table a fuel card and tech table and a joining assignment table.

My first thought was to raise a record in the fuel card table when you submit the new started request but i was advised against it as that record would really have any data other than the order date. Which wasnt very efficient.

Also a new starter request wouldnt be the only way of ordering cards as techs could require replacements.

What would be peoples suggestions on this, please note im very very new so my understanding is limited.

TblTechs

ID
TechID
FirstName
Surname
EmployeeNumber
DateOfBirth
NationalInsuranceNumber
DrivingLicenseNumber
Town
Address
PostCode
HomeNumber
PersonalMobile
WorkMobile
UserID
VirginEmailAddress
UserGroupID
ComexEmailAddress
StartDate
EndDate
Salary
LastReviewDate
FuelCardRequired
RegionID
FieldStoreID
ReasonForLeaving
Photo
ReEmployee


TblFuelCardAssignment

ID
TechID
FuelCardID
AssignmentDate
UnassignedDate
CardActive

TblFuelCard

ID
FuelCardID
DateReceived
CardNumber
PinNumber
DateCancelled
ReasonForCancellation
ReplacementCard

 

[gemma-the-husky]

you have two basic choices

in the tech table, you could store details of the current card, and overwrite it as the card changes. this is simple, but that way, you don't have a card history.

so the alternative is to store a card history, and have a number of cards all linked to this tech. if you do this, then you need some mechanism to determine the current active card, although maybe more than 1 card can be active at a time.

now, let's assume that a card can only belong to a single tech. if you define this in a relationship, you get a 1 to many relationship - 1 tech, can have several cards -

[note that if a card can be used by more than 1 tech at the same time, it is slightly more complex, but I assume this is not the case]

now the thing is, that defining a relationship from tech TO card, and including referential integrity enables a tech to have 0, 1 or more cards. It also says that a card cannot be "floating" and unattached, as this would break your RI rule.

so you might be ending up in a sort of catch 22 - you can't set up the tech, because you can't pick the card - but you can't pick the card until you set the tech up.

the answer is probably to treat the "required" flag as a sort of check - set the tech up first, without a card. at a later stage (or immediately after), you can create his card.

and you can run a query to find any techs with no card assigned.

 

[Bopsgtir]

Thanks Dave.

I decided to take the following approach, on my new starter forms there is the fuel card required tick box, if that is ticked then it generate an email to order a card and uses a slq insert into to add a record in the fuel card table stating the date ordered. then i added a missing data query to capture all the fuel cards that have been ordered but no details filled in, then i have an assignments table that i use to link the tech to driver and a tick box to show which card is active.

 

[gemma-the-husky]

sounds good.

glad you got it working.