Results 1 to 9 of 9
  1. #1
    faca is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    52

    Connecting tables

    Hello!

    I'm designing a database for my mechanic.


    Problem is that currently he has all the information stored in notebook. It's painful when he needs to search trough notebook to get to the service that he done for particular car so that's why i am going to create ms access app for him.

    Currently i have the problem with how to connect these tables:



    Code:
    tblParts:
    PartID
    PartNumber
    PartName
    Unit
    PurchasePrice
    SellingPrice
    NetPrice
    TaxRate
    DateOfEntry
    Code:
    tblDeliveryNoteParts:
    DeliveryNotePartsID
    DeliveryNoteID
    PartID
    Quantity
    PurchasePrice
    Code:
    tblDeliveryNote:
    DeliveryNoteID
    DeliveryNoteNumber
    DateOfPurchase
    Store
    Delivery (Yes/No)
    Every part that he needs for current service on car is purchased from a store (vendor) for certain price.

    Each purchase like that has a delivery note (number of delivery note, date of purchase, delivery or pickup, quantity, purchase price for each part, store (vendor)).

    What he wants is to have a history of purchase price and under which delivery note it was purchased for each a part.

    Are my tables even correct or would anyone of you do it other way and how?

    Thanks

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    tables look OK to me in principle for this specific task. You might want to include a table for vehicle models and another to link parts to vehicles so your mechanic can put in a vehicle model and find all parts purchased for that vehicle model regardless of the owner/registration. May also need to consider 'consumables' such as oil, brake fluid and the like which are not vehicle specific

  3. #3
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by faca View Post
    Every part that he needs for current service on car is purchased from a store (vendor) for certain price.

    Each purchase like that has a delivery note (number of delivery note, date of purchase, delivery or pickup, quantity, purchase price for each part, store (vendor)).

    What he wants is to have a history of purchase price and under which delivery note it was purchased for each a part.
    We need to see the whole picture. For example: how is a deliverynote related to a "current service"? If he needs the same part for 2 cars, will there be 1 delivery? He will never hold stock for parts?

    You do not need purchaseprice in the parts table. The current price can be derived from the deliveries.

    You might need to introduce a vendors table.

  4. #4
    faca is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2018
    Posts
    52
    Image below shows all my tables and relations.

    Click image for larger version. 

Name:	Screenshot_1.png 
Views:	26 
Size:	28.2 KB 
ID:	48146

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you know your business better than we do, but on the face of it looks OK except perhaps for the parts table which I don't quite understand.

    parts can come from many vendors and the price may well be different. You have that available via the delivery note so not sure why you have it in the parts table.
    Also what is the difference between selling price and net price since one can be calculated from the other when combined with tax rate.
    what is the relationship between unit and instock?
    as with deliveries - selling price is duplicated in tblserviceparts


    You might want some lookup tables for make, model and bodystyle in tblVehicles and maybe include a field for service type in tblservices. And as XPS says perhaps a lookup table or detailed (i.e. phone/fax/email etc) table for store if that is the vendor.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    I'd add a table, where all transactions with all parts, materials, etc., will be registered (all arrivals of purchased parts, all outgivings of parts needed for working on service order for specific car, returning faulty parts to supplier or scrapping them, returning parts given out and unused for given service back into storage, etc.). This table will provide you with inventory of any parts available at any given time moment. Something like
    tblPartTransactions: TransactionID, TransactionType, TransactionDate, PartID, PartUnit, PartQuantity, SourceDocument, TargetDocument, ... You can also merge SourceDocument and TargetDocument into single Document field.
    In this/those document field(s) you store either DeliveryNotePartID (I'd rename it something like DeliveryNoteRowID) or ServicePartID (another set of tables I'd advice will be tblServiceOrders - general info about service for certain car at certain time -, tblServiceParts - all parts needed for this service-, and tblServiceOperations - all operations needed to do, as there must be some reason to ask some money for work made too), depending on transaction type. And some transactions (like scrapping) don't have any document listed.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @faca,

    For your own benefit and others, try writing in point form each step in the process. Undoubtedly there will be several processes because of vehicle, part(s) required, type of service/repair, vendor...., but take a few typical service requests and identify what is involved, then what is the next step, then the next... until job is complete.
    You will learn more about what has to be supported by the proposed database and therefore what must be included in the database.

    Some thing to be considered in any repair/service work. Customer comes to you and says--I don't know what is wrong. It makes a noise.
    So could be anything from brakes, fan belt, loose exhaust.... and when mechanic checks, it may be thee are 4 things needing correction. Is this 1 service, 4 services to 1 vehicle? A vehicle could require multiple parts for a variety of services in 1 "appointment". How this would/could be recorded depends a lot on what the mechanic needs and whether this database is involved in invoicing/payments etc (scope of the proposed database).

    Just a few things to consider and reconcile before getting into physical database.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    2 comments

    1) in table tblVehicles, "Year" is a reserved word and a built in function. Plus it is not very descriptive. "Year" of what? Maybe use "ModelYear"?


    2) tblParts.PartID is connected to the wrong field in tblDeliveryNotesParts. It is connected to field tblDeliveryNotesParts.DeliveryNoteID when it should be connected to tblDeliveryNotesParts.PartID

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    tblParts.PartID is connected to the wrong field in tblDeliveryNotesParts.
    good spot

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help! Connecting Tables
    By MHWH23 in forum Access
    Replies: 1
    Last Post: 01-12-2016, 11:23 AM
  2. connecting tables in a database
    By maligauss in forum Access
    Replies: 10
    Last Post: 05-20-2014, 05:20 AM
  3. Connecting three tables together
    By Lori944 in forum Access
    Replies: 3
    Last Post: 01-10-2014, 11:49 AM
  4. Connecting tables
    By FrankGrinds in forum Database Design
    Replies: 2
    Last Post: 05-05-2013, 07:36 PM
  5. connecting two tables?
    By imintrouble in forum Access
    Replies: 1
    Last Post: 01-24-2012, 02:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums