Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-05-2009, 10:14 AM   #1
Proverbs
Newly Registered User
 
Join Date: Sep 2009
Location: Harrogate, N Yorks, UK
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Proverbs is on a distinguished road
Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
Attached Files
File Type: pdf 091001 Vehicle Table Relationships.pdf (94.5 KB, 555 views)

Proverbs is offline   Reply With Quote
Old 10-05-2009, 10:33 AM   #2
Simon_MT
Newly Registered User
 
Join Date: Feb 2007
Location: United Kingdom
Posts: 2,177
Thanks: 2
Thanked 162 Times in 158 Posts
Simon_MT has a spectacular aura about Simon_MT has a spectacular aura about
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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
Simon_MT is offline   Reply With Quote
Old 10-05-2009, 10:41 AM   #3
dbDamo
Registered User
 
Join Date: May 2009
Location: Bristol
Posts: 395
Thanks: 0
Thanked 7 Times in 3 Posts
dbDamo is an unknown quantity at this point
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.

dbDamo is offline   Reply With Quote
Old 10-05-2009, 12:23 PM   #4
Proverbs
Newly Registered User
 
Join Date: Sep 2009
Location: Harrogate, N Yorks, UK
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Proverbs is on a distinguished road
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
Proverbs is offline   Reply With Quote
Old 10-07-2009, 11:33 AM   #5
dbDamo
Registered User
 
Join Date: May 2009
Location: Bristol
Posts: 395
Thanks: 0
Thanked 7 Times in 3 Posts
dbDamo is an unknown quantity at this point
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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!!

Last edited by dbDamo; 10-07-2009 at 11:39 AM.
dbDamo is offline   Reply With Quote
Old 10-07-2009, 12:51 PM   #6
Proverbs
Newly Registered User
 
Join Date: Sep 2009
Location: Harrogate, N Yorks, UK
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Proverbs is on a distinguished road
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
Proverbs is offline   Reply With Quote
Old 10-07-2009, 11:12 PM   #7
dbDamo
Registered User
 
Join Date: May 2009
Location: Bristol
Posts: 395
Thanks: 0
Thanked 7 Times in 3 Posts
dbDamo is an unknown quantity at this point
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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!

dbDamo is offline   Reply With Quote
Old 10-08-2009, 11:09 AM   #8
Proverbs
Newly Registered User
 
Join Date: Sep 2009
Location: Harrogate, N Yorks, UK
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Proverbs is on a distinguished road
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
Attached Files
File Type: pdf Relationships for JSL Vehicle Database.pdf (96.0 KB, 216 views)
Proverbs is offline   Reply With Quote
Old 10-08-2009, 11:35 AM   #9
dbDamo
Registered User
 
Join Date: May 2009
Location: Bristol
Posts: 395
Thanks: 0
Thanked 7 Times in 3 Posts
dbDamo is an unknown quantity at this point
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
dbDamo is offline   Reply With Quote
Old 10-08-2009, 01:35 PM   #10
Proverbs
Newly Registered User
 
Join Date: Sep 2009
Location: Harrogate, N Yorks, UK
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Proverbs is on a distinguished road
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
Proverbs is offline   Reply With Quote
Old 10-09-2009, 09:43 AM   #11
dbDamo
Registered User
 
Join Date: May 2009
Location: Bristol
Posts: 395
Thanks: 0
Thanked 7 Times in 3 Posts
dbDamo is an unknown quantity at this point
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
dbDamo is offline   Reply With Quote
Old 10-11-2009, 06:16 AM   #12
Proverbs
Newly Registered User
 
Join Date: Sep 2009
Location: Harrogate, N Yorks, UK
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Proverbs is on a distinguished road
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
Attached Files
File Type: pdf Relationships for JSL Vehicle Database.pdf (95.7 KB, 179 views)
Proverbs is offline   Reply With Quote
Old 10-11-2009, 09:04 AM   #13
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,463
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.

Last edited by gemma-the-husky; 10-11-2009 at 03:04 PM.
gemma-the-husky is offline   Reply With Quote
Old 10-11-2009, 10:40 AM   #14
Proverbs
Newly Registered User
 
Join Date: Sep 2009
Location: Harrogate, N Yorks, UK
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Proverbs is on a distinguished road
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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.
Proverbs is offline   Reply With Quote
Old 10-11-2009, 12:17 PM   #15
Simon_MT
Newly Registered User
 
Join Date: Feb 2007
Location: United Kingdom
Posts: 2,177
Thanks: 2
Thanked 162 Times in 158 Posts
Simon_MT has a spectacular aura about Simon_MT has a spectacular aura about
Re: Seeking advice to attempt Vehicle Database Tables (template based) Access2007

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

Simon_MT is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Update Randomblink Modules & VBA 6 02-16-2004 08:24 AM
Problem duplicating Access 97 database that has linked tables jimwei General 5 07-17-2003 04:32 AM
Connect to all tables in a database greaseman General 6 05-21-2003 12:43 PM
Exporting tables to a password-protected Database through code R2D2 Modules & VBA 4 02-05-2003 12:30 PM




All times are GMT -8. The time now is 12:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World