Seeking advice to attempt Vehicle Database Tables (template based) Access2007 (1 Viewer)

Proverbs · Oct 5, 2009

Made a valiant attempt and floundered at amending, to our business requirements, the ‘Vehicle maintenance’ template
Made the attached Tables to start at building from scratch.
The attached shows, I hope, the concept of the information required to store and query.
I would be grateful for the following:
Is the attached normalised properly, if not how should it be corrected? Are the relationships properly in place?
Or, once this attachment has been viewed would you recommend persevering with the template? Would that still be a simpler way forward?
The database is for use in a small(ish) business where there are two companies running cars and light commercials and ‘family’ owned cars.
(The thought also occurs at this time of posting fields would be needed to query when MOT and other commercial testing would be required so I will need to add these later)

Database (Access 2007_Vista) are new to me. I am starting with this project with a view to learning before starting on a more important project of a stock control database (with all its implications!)
Thank you for considering and hopefully your guidance.

Simon_MT · Oct 5, 2009

To m operationally it is the Vehicle and what happens to that vehicle that is important. Who it is financed and ownership is secondary. I don't really understand the difference between Supplier and Purchaser.

Simon

dbDamo · Oct 5, 2009

For a start it looks like you need an invoice table with invoice number as the primary key. At the moment you are saying that Suppliers and Purchasers should only have one invoice number, I can't imagine this being the case!!

You say there are 2 companies, do you not require the ability to query against those separately?

Your finance method table contains a finance company field, I would create a new table for finance companies.

You also appear to have notes fields in all of your tables but it may well be that you wish to store different notes in each table?

It is difficult to say if your design is correct without really knowing what you are trying to achieve but I would say you definitely have some more normalisation to go through.

Proverbs · Oct 5, 2009

Thank you both. I'll try to be more expansive to both replies.
Three seperate identies; Partnership; Ltd Co. plus vehicle purchases made as personal items by the partners and directors,(Ownership).
Vehicles may be bought outright or financed (Aquisition Method) by Hire Purchase; Finace Lease; Operating Lease (Type of Agreement).
Queries to consider:
Who supplied any vehicle. Who bought a vehicle on disposal. How many on this or that type of agreement. When do agreements end (possible disposal date). Total of current repayment for those funded. When are the MOT and testing dates, Licence renewal dates. Who do we/did we deal with at this supplier or purchaser.
Running costs of a vehicle, Next service date.
Know doubt a good few others will occur. These a few samples which come to mind.
Attempting to have a central core covering all vehicles (as assets) and all actions that can happen on those vehicles which can be queried for information.
dbDamo - That would be the ownership field with Co 'x'; Partnership 'Y' and 'Private'.
Finance Co's - ah, think I see your point. One Finance Co may fund more than one vehicle (one to many) - I got that bit but split it into Co's (as they occur) and have the terms & type of agreement in seperate table.
'Notes' - the answer would be yes, so comments on any supplier or purchaser (e.g. pays good price - not! type of thing, Finace Co's e.g. good rates and Expenses, missed this or that part.
The mountain looks hard to climb at the moment from what you have just thrown in! Keep climbing as it has to be done for future project.

dbDamo · Oct 7, 2009

Having had another look it looks like there should be a few more tables:-

1. Invoices
2. FinanceCompanies
3. ServiceProviders

Also, you appear to be storing duplicate data across tables, i.e. you are storing:-

1. Model in the FinanceMethod table which is already stored in the VehicleDetails table
2. Vehicle Expenses in the VehicleDetails table which is already stored in the Expenses table
3. Registration Number in the Expenses table which is alreay stored in the VehicleDetails table

Other than a bit more normalisation it looks like you are headed in the right direction, so keep going!!

Proverbs · Oct 7, 2009

Thank you dbDamo
Following your previous suggestions I have done some more work on tables. I haven't got the outcome on this pc to attach the revision with this post. I will tomorrow look again taking into account your above comments (the Model I certainly picked up) and made some other changes.
I will try and post with attachment from work tomorrow though I have experienced inability to do this (maybe firewall issues - I don't know as not looked to sys admin people yet). If not will from home tomorrow evening after emailing the pdf to myself.

Many thanks for your encouragement.

dbDamo · Oct 8, 2009

Just upload when you can and we'll check for you. Like I said you have made a good start, just a few changes needed. Its extremely important to get this right now as it will save you major headaches in the future!

Proverbs · Oct 8, 2009

Okay dbDamo and any others.
Radical rethink. There is 'Plant' (e.g. Manitu and Lancer Boss) which the business(s) historically lump together in their minds under vehicles so I have decided to include them (bear with me I do think necessary).
I have:-
Table for Ownership - Look up of Co X; Co Y and Private (one to many as each can have this)
Table for Asset Type - Look up of Vehicle or Plant (one to many as each can have this)
Table for the VehicleDetails (record for one item and containing field for only that item - excluding other child keys)
Table for Finance Co's with one record per company (one to many as any record could finance more than one vehicle)
Table for Finance Method (uncertain here as only one agreement can be for one vehicle so One to One comes to mind but can't see/feel the concept so 'help' or guidance)
Tables for Suppliers and Purchasers and each of these would/could be a one to many.
Table for Expenses incured of one vehicle to many expense entries.
Can't see now a duplication of Fields but 'needle and haystack' come to mind when you've looked at it so long.
No gaps in Table titles and I've removed gaps in field names (use Caption later in Table design?). Still to set any detaild properties for the fields except ensuring Primary & Childs match with Auto Number (though did think of Reg Number or VIN as always been different but Private number plates come to mind and other things.
That's where I am. (Feels strange using 'Primary' & 'Child'. Sounds like I know what I'm talking about but still a real novice!)
Hope I'm getting somewhere and thanks in advance for the time and trouble.

dbDamo · Oct 8, 2009

I see where you're struggling with the FinanceMethod relationship. I would have thought the FinanceMethod should be linked to the FinanceCompany table, not the VehicleDetails table?

You still need to do something about the invoices. Currently a supplier or purchaser can have only one invoice. While this is true per transaction, I would imagine a suppliers and purchasers will have many transactions.

Other than that it looks like you're nearly there.

Proverbs · Oct 8, 2009

Thank you. Yes, the Finance Company can finance many vehicles with many agreements but agreements are per vehicle so the link should go via the Finance Company. Hhmmm.
Yes can work this out I think. From my page skipping; Chapter flicking; help text searching something in the back of my mind remembers linking Tables. Don't know why this might be relevent yet (might not be) but I'll sit a while at weekend, if not before, and solve it (hhmmm again).
Take care and thank you for prompting.

dbDamo · Oct 9, 2009

I think you may have misunderstood what I meant, but that is my fault as I didn't word it correctly.

What I should have said is that I believe your FinanceMethod table should be related to your FinanceCompany table and not your VehicleDetails table, i.e. a Finance Company can have many Finance Methods.

Proverbs · Oct 11, 2009

dbDamo, thank you. I believe you were clear, my muddled mind read too quickly and is so again! Couple of hours looking this afternoon before writing this.
To move on and review if I may?
Any one vehicle/plant which is acquired is the main item we want to know (query) about but queries could be run on each other table (so to speak) to see how many items they affected (badly put). So each item will have:
Each Supplier (who could supply many more) One to Many Vehicle Details
Each Purchaser (who could buy many more) One to Many Vehicle Details
Each item will be an Asset Type of either a Vehicle (and have One to Many Vehicle Details) or Plant (have One to Many Vehicle Details)
Each Item will be owned (Ownership) by one of three parties. Each party can own many (One to Many Vehicle Details )
Each item will have many Expenses so One Vehicle to Many Expense.
Now Finance!
Each item can be acquired one of two ways 1) Bought (from capital/cash) 2) Financed (through a Finance Co or Bankers) So AquisitionMethod will be One to Many.
Now I struggle!
If the acquisition method for an item was ‘Finance’ I want a query to go away and look for which Finance Company and the type and terms of agreement that Finance Company provided.
As can be seen I have the Finance Co table and Finance Method table but dammed if I can get them joined.
Or, would it be better to have the Finance Method fields in the Vehicle Table as they can only be for one item? Though that breaks down if you get two vehicles on one agreement (which they have one instance of)!
Thank you.

gemma-the-husky · Oct 11, 2009

this needs to be based around the vehicle

you will then have related tables for many other things, such as

vehiclemake
leasingcompanies
serviceplans
servicingcompanies
companydepartments
fueltypes
fuelusage
tyretypes
etc etc

things like acquisitioncost, acquisitiondate, motdate, rfldate, etc are attributes of the central vehicle table

other stuff will go in one of the other tables. There may be some data that needs a third table, as it relates to one of the related tables, if you like. (eg vehicle model/make - a modeo is a ford, and a fiesta is a ford - so you probably need a models table to link to the vehicles table and a makes table to link to the models

but for a start, just have a vehicle table and gradually add all the fields you need to manage. But if you find you need to enter (primarily TEXT) data in this table, and for which you have multiple similar examples (eg vehicle make or model) then you need a separate lookup table. Text is generally the key to this - you wouldnt have a lookup table just because several MOTs are due on 1st November - but you would have a lookup table for all the cars using 65SR250VR tyres (say)

if you are used to normalisation you will be used to getting to the required structure.

Proverbs · Oct 11, 2009

Dave, thank you.
New to Databases, Access & Normalisation (and forums to a degree).
What you have posted seems to have blown my first attempts off the screen. It will take this old man some time to assimilate your posted suggestions for I have no ‘picture’ of it in my head at all yet.
I have to listen, consider and respect a VIP. I’m the lowest in the food chain here, so please bear with me. I’ll go back to my books and paper where I started and come back with something, well sometime.
(thinks bubble – I’ll live off my pension and give up this part time office job, seems easier).
In confusion at the moment but willing to learn.

Simon_MT · Oct 11, 2009

The way to look at it is from the bottom up. The car is the most important peice of information everything "hangs" off the vehicle.

Simon

DannySeager · Oct 12, 2009

Just some quick notes

1.) A company is a company - whether it's a finance company or a supplier.... so they belong in 1 table - you either need 2 tick boxes or better yet a junction table for company types.

Code:

[B]TblCompanyType[/B]
CompanyTypeID
CompanyType

Code:

[B]TblCompanyTypeJunction[/B]
CompanyTypeID
CompanyID

2.) To be honest I would put purchasers in the same table as well... whilst they may not be companies they can be treated the same way

3.) I think you need a junction table between the purchasers and Vehicle details. Purchasers could own multiple vehicles

Code:

TblVehiclePurchases
VehiclePurchaseID
VehicleID
CompanyID
PurchaseDate

in fact you need to move most of the data from the Vehicles table into this one... Leave only static information (Registration number, colour, make, model etc) in the vehicle table.

I'll try and post some more details later as I think there might be other changes to improve the structure

gemma-the-husky · Oct 12, 2009

having a single company table .. hmmm

you see, i wouldnt do that ... i find it harder that way

just because i have three fields that refer to companies, i wouldnt necessarily put all three of them into one table - to me that just adds complexity

i would have separate tables for leasing compaines, vehicle suppliers, servicing agents etc etc. if you put them in one table, you then have to separate them again for combo boxes, and such like - easier to keep them separate all the while i think.

DannySeager · Oct 12, 2009

having seperate tables for the same information means that you either need seperate forms to edit that information of that you need to start editing your forms recordsource at runtime.... these are both unnecessary things that add complexity where it really isn't needed.

You also have the issue of what if a company is a supplier and a customer and a servicing agent... then you have to maintain the same address 3 times... and storing duplicate data - which is a no no with a correctly normalised system (i'm not saying that a database has to be 110% normalised but there has to be a good reason for each deviation from the normalisation path as far as I'm concerned)

speakers_86 · Oct 12, 2009

Simon_MT said:
everything "hangs" off the vehicle.

Simon

Perhaps your vehicles need maintenance?

speakers_86 · Oct 12, 2009

gemma-the-husky said:
having a single company table .. hmmm

you see, i wouldnt do that ... i find it harder that way

just because i have three fields that refer to companies, i wouldnt necessarily put all three of them into one table - to me that just adds complexity

i would have separate tables for leasing compaines, vehicle suppliers, servicing agents etc etc. if you put them in one table, you then have to separate them again for combo boxes, and such like - easier to keep them separate all the while i think.

I feel like I need to post something useful now!

I think gemma may be right for 2 reasons. The first gemma already mentioned. The second is that you may find out that you want to store different information for each type of company. Either way, I don't think this is a major issue. I don't believe it matters which way you do it, but gemma's answer is likely to be easier to code, and less resource intensive.

If you really need a list of all companies, regardless of type, then you can make a union query.

edit-Danny, I didn't read your last post. That looks like a good reason to only use one table. You have me second guessing myself!

Seeking advice to attempt Vehicle Database Tables (template based) Access2007 (1 Viewer)

New member

Attachments

Registered User.

Registered User.

New member

Registered User.

New member

Registered User.

New member

Attachments

Registered User.

New member

Registered User.

New member

Attachments

Super Moderator

New member

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread