Looks pretty good. Some column names suggest that their data should be stored in separate tables:
UserID (maybe)
UserGroup
Region
FieldStore
ReasonForLeaving (only if you want to establish domain integrity on this, not if it is to be freely entered)
And, it's just a matter of preference, and ease of coding, the primary key PKTechAN I would rename to just ID. In my shop, we give every table a primary key called ID, so once you've memorized the table names in our databases, you always know what the primary key is. Since all tables obviously have unique names in the DB, there's no need for a primary key column not to be abstract.
UserID (maybe)
UserGroup
Region
FieldStore
ReasonForLeaving (only if you want to establish domain integrity on this, not if it is to be freely entered)
And, it's just a matter of preference, and ease of coding, the primary key PKTechAN I would rename to just ID. In my shop, we give every table a primary key called ID, so once you've memorized the table names in our databases, you always know what the primary key is. Since all tables obviously have unique names in the DB, there's no need for a primary key column not to be abstract.
UserID is a letter and 7 digit number assigned to the tech for example R7592886
UserGroup is also the same APP-Outsourcer-LittleRedShed-All-Users-G
Region is now linked to another Table Regions.
FieldStore is a 3 digit location number for example 650, so would i still store the number 650 and inanother table that code would have all the details of that store.
UserID (maybe)
UserGroup
Region
FieldStore
ReasonForLeaving (only if you want to establish domain integrity on this, not if it is to be freely entered)
And, it's just a matter of preference, and ease of coding, the primary key PKTechAN I would rename to just ID. In my shop, we give every table a primary key called ID, so once you've memorized the table names in our databases, you always know what the primary key is. Since all tables obviously have unique names in the DB, there's no need for a primary key column not to be abstract.
UserID is a letter and 7 digit number assigned to the tech for example R7592886
UserGroup is also the same APP-Outsourcer-LittleRedShed-All-Users-G
Region is now linked to another Table Regions.
FieldStore is a 3 digit location number for example 650, so would i still store the number 650 and inanother table that code would have all the details of that store.
Ive just realised thats wrong, i was thinking the fuel card to tech relationship was one to one as a tech can only have one card, but if a tech lost his card he would get another one so that would be another record, so this should be a one to many, the tbltech being the one side correct??
Question now is, in the tech table the same fuel card will only be seen once so will be a unique and in the fuel card table the card number will be unique but if a set them both to no duplicates, it only lets me do a one to one relationship
Close. You should link on the ID column in the foreign tables. So, for example, change Region to RegionID, integer, and relate RegionID to Region.ID, and so on for each table.. JOINs in integer columns are much faster than on text fields.
If you need to keep historical data on fuel cards, put them in a separate table, and you will need a junction table between the Tech and FuelCard tables to keep assignment history. The junction table would be TechFuelCardLink, and look something like this: ID, TechID, FuelCardID, FuelCardNumber (Text), AssignedDate, UnAssignedDate, Active (bit). You could forego the Active column, and rely instead on the absence of a date in the UnAssignedDate column to identify the currently assigned card. Otherwise, if you don't need history, I think it's fine just to store the literal card value in the Tech table.
I didn't explain the JOINs completely. What we are going for here in atomicity. Take the Region example. The literal value for the region (East, West, whatever) should only exist in the database once, that being in the Region Table. Any other table that references a Region should store the ID of that Region's row in it's own RegionColumn. Another lesser advantage is that is
Another lesser advantage is that if a value changes in one of the lookup tables (Region again) it is automatically promulgated to all the Texh records that reference it.
Yes im getting there, i think my biggest problem is understand that the table view is not how you see your data. so what happens if i now change a techs fuel card, so the id will now change but will his old card still be associated to him, so if i did a fuel report it would show whats hes spent on any card he has had not just his current card.
So in the Fuel Card Junction table why would i have a field called FuelCardNumber would that not gets its value fromk the TblFuelCard?
Sorry and another question, would the TblFuelCard and TblFuelCardAssignment be a one to one join, and a single fuel card could only ever be assigned to a single tech??
Yes im getting there, i think my biggest problem is understand that the table view is not how you see your data. so what happens if i now change a techs fuel card, so the id will now change but will his old card still be associated to him, so if i did a fuel report it would show whats hes spent on any card he has had not just his current card.
His old card will remain assigned to him, but it will not be the current card. Only one card will be the current card. This design supports many fuel cards being assigned to the tech, and also the differentiation between the two classes of cards - current and assigned in the past but not now current.
So in the Fuel Card Junction table why would i have a field called FuelCardNumber would that not gets its value fromk the TblFuelCard?
Sorry and another question, would the TblFuelCard and TblFuelCardAssignment be a one to one join, and a single fuel card could only ever be assigned to a single tech??
It's one-to-many relationship between tech and card, because you need historical data on fuel card assignments, but only one card is current. So there is a row in the junction table for every fuel card ever assigned to the tech, and one row in the Fuel Card table for each card. I assume there are attributes of a fuel card (issuer, for example) and therefore there needs to be a Fuel Card table.
So the topic may not really be design based, but how can i create a record in the fuel card table based on just the fuel card required tick box in the TblTechs.
Basically on my new starters form, there is the tick box to say that they require a fuel card, ticking the box sends a new request to Esso to order a card. now i wont have all the info but i want that request to create a record in the TblFuelCard and generate an order date, and also assign the ordered card record to the tech in the TblFuelCardAssignment.
So the topic may not really be design based, but how can i create a record in the fuel card table based on just the fuel card required tick box in the TblTechs.
Basically on my new starters form, there is the tick box to say that they require a fuel card, ticking the box sends a new request to Esso to order a card. now i wont have all the info but i want that request to create a record in the TblFuelCard and generate an order date, and also assign the ordered card record to the tech in the TblFuelCardAssignment.
OK. I would do this is a class, because it keeps code loosely coupled away from forms and affords code reuse, but that's just how I architect this stuff.
You're going to need to run SQL INSERT statements against the database to insert a row in the FuelCard table, and in the link table. Since the link table contains both the FuelCardID and the TechID, both of those new records (Tech and FuelCard) need to be saved, and their ID's available to the code that executes the SQL that inserts the link table. Also, the FuelCard.CardNumber cannot be required if you want to insert the record without knowing the card number - you'll have to have a business process to update the card number after the card arrives. I'd argue that's inefficient, but I don't know any of your business rules.
After a Tech record is inserted, you'll need to evaluate the FuelCardRequired check box for true, and do all this only in that case.
Here's the simplest way I know of to execute a SQL INSERT Statement against an Access database:
Code:
Dim sSQL As String
sSQL = "INSERT INTO AthleteAttachment (AthleteID) VALUES ( " & Me.ID & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
This code assumes an Access database. You can also do it using an ADO connection object, which I do against my SQL Server DBs a lot to bypass the Jet DB engine.
You'll need to retrieve the ID of the newly added FuelCard record. I don't know a reliable way to do this in a multi-user Access back end, because I use SQL Server and we have a reliable method there. The best I can think of is, immediately after the new record is committed and you know it's in the database, issue this SQL:
Code:
SELECT TOP 1 ID FROM FuelCardTable ORDER BY ID DESC
to populate a recordset. The only problem is that I don't know how to prevent another user from having added a FuelCard record, and then you would get an ID one higher than your FuelCard record. This is probably a question to ask someone with a lot of experience using Access DB's in multi-user environments.
Ok well the multiuser bit isnt really a problem as there will only really be one person updating this.
i understand what your saying about adding a fuel card record BEFORE having all of the information, and you right its probably not the best way. the actual end goal, is to be able to know that a fuel card has been ordered for that tech and when it was ordered. the problem with esso is i can wait upto a month for a fuel card and i dont want to have to remember whos waiting for one.
So if there was a record for the order date and in the assignment table, i could atleast say ive got x amount on order and they have been on order for x amount of days.
Ok well the multiuser bit isnt really a problem as there will only really be one person updating this.
i understand what your saying about adding a fuel card record BEFORE having all of the information, and you right its probably not the best way. the actual end goal, is to be able to know that a fuel card has been ordered for that tech and when it was ordered. the problem with esso is i can wait upto a month for a fuel card and i dont want to have to remember whos waiting for one.
So if there was a record for the order date and in the assignment table, i could atleast say ive got x amount on order and they have been on order for x amount of days.
All true. If you add a DateAdded column to the Tech table, if it is the case that the card is ordered on the same day a new tech is added, the value of True in the FuelCardRequired bit column and the DateAdded column allow you to report on fuel card backorder.
Ok well that field is already there as the record would be added on the techs start date, as he comes into the office for the whole day to do computer course and filling this out will be part of that process, so if best not to add anything to the fuel card table until the fuel card is actually here then.
So i need to a form to add fuel cards and another to assign cards to techs, im guessing the easiest way would be two drop downs one that shows current cards not assigned and current techs without one.
Ok well that field is already there as the record would be added on the techs start date, as he comes into the office for the whole day to do computer course and filling this out will be part of that process, so if best not to add anything to the fuel card table until the fuel card is actually here then.
So i need to a form to add fuel cards and another to assign cards to techs, im guessing the easiest way would be two drop downs one that shows current cards not assigned and current techs without one.
Yes, as long as there aren't too many techs or cards. I try to limit data in drop downs to about 20 rows. Much more than that and it is unfair to the user, and accuracy goes way down.
When I have a lot of records for the user to choose from, I build a dedicated form tat they can use to filter the records down to a manageable number.
Ok well average turnover is around 5 techs a month so there shouldnt be to many in the list..
One thing i have just though though is this would only work with cards ive ordered for new starters and would take into account cards i order because someone has lost a card.
