Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13

    Database Design Mechanical Contractor

    Hi,
    I am an Access novice designing a database for our small company. We are a plumbing and mechanical company. My objective is to track Purchase Orders (PO) by customer and determine our cost vs. our margin.


    We have many customers. When a customer hires our company we create a PO. That PO is our mechanism for capturing time and materials. Our work could last for one day, weeks, months or years....PO remains "open" until we complete the job.
    Time is hours worked by our employees. Multiple employees could work under this PO. Their hourly and billable (to client) rates vary by PO...Each employee might have up to 4 Labor Cost Rates and 4 Billable Rates assigned to them. Materials are supplies we purchase from our wholesalers/distributors. We have many Suppliers and purchase many items from each supplier. A PO could have many items purchased from the same supplier or many items purchased from multiple suppliers.

    In a perfect world I will be able to track a PO by customer and all activities associated with that PO from date opened to date closed, calculating cost and profit. I have been working on my design for some time and keep running into challenges around duplication errors. I have one-to-many and many-to-may relationship tables and junction tables.

    All assistance is appreciated and welcomed.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,981
    so guessing, you have:
    tPO table
    tCustomer table
    tEmployee tbl
    tEmpHrs
    tMaterials
    tSuppliers
    tProjects
    tTasks(?)

    this is not a trivial database.

  3. #3
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Not trivial indeed...
    I currently have:
    tblCustomer
    tblPurchaseOrder
    tblSupplier
    tblEmployee
    tblMaterials
    tblBillRate
    tblLaborCost

    Then junction tables
    tblPOBillRateDetails
    tblPOLaborRateDetails
    tblPOMaterialsDetails
    tblPOEmployeeDetails

    I have created several queries and forms too...I was making great progress up to this point. My "win" would be to have a Tabbed Form with all data broken out

  4. #4
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    The junction tables have become challenging. I am not sure I have established the correct relationships on the ONE and MANY sides or the correct MANY to MANY relationships. I was making great progress and now am running into challenges around duplicates.

    Click image for larger version. 

Name:	image003.jpg 
Views:	33 
Size:	147.9 KB 
ID:	40470

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,981
    instead of creating N subforms and binding data to them, (you can run out of memory)
    create 1 subform , then swap out the subform source depending on the tab the user clicks...

    Code:
    Private Sub TabCtl_Change()
    Select Case TabCtl.Value
       Case 0
          subfrm.SourceObject = "fPaymentsSub"
       Case 1
          subfrm.SourceObject = "fPurchasesSub"
       Case 2
          subfrm.SourceObject = "fInventorySub"
    End Select
    End Sub
    

  6. #6
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Thank you...appreciated. I have never coded...this code would be entered into the Properties of the Subform?

    If an employee can be associated with many Billable Rates and Labor Cost Rates how would I represent that in the appropriate tables? I am not sure how to create many-to-many relationships with my tblEmployee and tbl BillRate and tblLaborRate. I am willing to make any modification necessary and appreciate all assistance.

  7. #7
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    259
    Hi Michael
    Can you upload a zipped copy of your database?

  8. #8
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    I have sent you a compressed copy. I appreciate your assistance...please know I have deleted some of the queries and modified relationships as this is a challenging work in progress. Please contact me as needed. My contact information is in my email.
    Thank you Mike.
    Michael

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    You can take these comments with a grain of salt because little is known about the business or how the db is supposed to support it.

    - PO doesn't belong in employee table. A PO and an employee are 2 different entities, so that should be the first indicator of that.
    - nor does license, I suspect. If Joe gets a 4th license, you add another field? That's an indicator of license belonging in another table.
    - missing PO fields? (Creator, Status, StatusDate,,,)
    - missing PO materials field(s)? Unit (for qty - 15 in the field = 15 what?). Maybe others as well.
    - you can only have 1 supplier for a material
    - materials table needs units? Materials has a unit price but for what: each, 100 count, pound?
    - I guess you only supply one labor type (e.g. electrician) because I see no trades and associated costs
    - you probably need more supporting tables or fields around time sheets as a means of associating expenses. Do you charge the same rate for all trades (assuming there's more than one)? Do you charge the same for an apprentice vs journeyman?

    I have worked in this sort of thing before but the time sheet portion was its own app; perhaps because it was designed with the payor in mind and not the payee. It can get very complicated if you're going to split off things like setup time rates, time or rate allowances, equipment rental (by you) etc.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  10. #10
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    All comment welcomed...thank you.
    -PO and EE in one table....agreed I had removed that a while back...must have sent an old picture
    -The Licenses are personal and specific to that one Employee...your suggestion is to create a License Table? It would list all licenses but each Employee has a unique State issued license or multiple state issued licenses...their numbers are unique to that individual...thoughts?
    -I have a Qty field in the POMaterialsDetails Table...suggestion? Is this not the more efficient way to capture Quantity of Materials purchased from a Supplier for specific PO?
    -Why can I only have one Supplier for a material? We buy similar products from multiple Suppliers...i.e. black pipe or copper pipe, pumps...multiple Suppliers will sell us the same product...we mostly decided where to buy based on job location and efficiency of travel to Supply House....suggestions?
    -Materials Table is designed to capture Inventory at that Supplier...their Item Number or SKU for the supply and their Unit Price...I hope to use a calculated field to capture Unit Price * Qty in a Query...Best way? Suggestions?
    -Thank you for your help on Timesheets...I am have great difficulty with this dataset. We have multiple trades...Plumbers (Apprentices, Journeyman, Master); HVAC Techs (Apprentices, Journeyman, Master); Pipe Fitters and Sheet Metal Techs. Each of these trades can be attached to one PO...many times (for different amounts of time) over many days, weeks, months....each carries a different rate of Cost (incurred cost to us) and Billable Rate (to client)...I would greatly appreciate your thoughts on this topic...
    Thank you!!!

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    your suggestion is to create a License Table? It would list all licenses but each Employee has a unique State issued license or multiple state issued licenses...their numbers are unique to that individual...thoughts?
    It depends. IMHO often the right approach is 1 table for licenses. Need a new type (steam fitter, gas fitter, welder, etc.) you just add it as a new record in tblTradeTypes. Its PK becomes an FK in a linking table (tblEmplLicense), which is what you use to handle how the one (employee) can have many (the licenses) yet the licenses themselves are a one (you would not have the same license/trade in tblLicenses more than once, right?).
    Why can I only have one Supplier for a material?
    Not entirely true - if you don't mind repeating all the data for a material. I might be misinterpreting the image or making assumptions on how you use the db, but what I'm seeing is (e.g.)

    ID 55 is for a brass fitting that you get from supplier 10. Read across the record to see the rest of the material data. BUT you also get that fitting from supplier 25. From what I see, you need another record (ID 78) in the materials table that repeats all the fields for that fitting in order to have another material record for a different supplier. Maybe the materials table isn't what I think it's for, or you've posted an out of date picture. Thus I'll hold off commenting on your other questions in case that's true. What I suspect you ought to have is tblMaterials with all the material data. That data is all the attributes of the material - supplier is not an attribute of a material. Supplier is an entity thus gets its own table. Ditto materials. How to connect 1 material with many suppliers, then?? Already mentioned - a linking table - joins the 1 of a 1 when one of the 1's is also a many. Maybe that's poorly worded, I don't know.

    Look again at your structure and see if you can apply what I (maybe incorrectly) think of as the Entity / Attribute relationship. A thing (not necessarily a tangible object) is the entity. All of its characteristics are its attributes. YOU are a thing. Your attributes are height, gender, DOB, place of residence, etc. The school(s) you attended are not an attribute of you. For that my table would have 1 record for every school I cared to enter. To associate you with the many schools you attended, I'd need a linking table. If you attended 4 schools, there would be 4 records with your ID PK repeated 4x as a foreign key + the PK of schools table as a FK in this table - 1 for every school.

    I hope some of that helps rather than confuses. Apologize too if it's stuff you know well. If not, do research normalization and see if any of that makes sense.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  12. #12
    Join Date
    Apr 2017
    Posts
    1,071
    Classic structure will be:

    You have Purchase Order from customer. Based on this, you create your own Customer Order (it can be for 1 specific entity, several entities or same type, or for any number of entities od different types - depending on your needs). When you can have several different entities in Customer Order, you also need a table for Customer Order Rows;
    Based on Customer Order/Customer Order Row, you need to create (those may be created outside of DB, or be included into DB):
    1. Structured list of components/materials needed for 1 specific ordered entity (often named as BOM list) - along with component/material quantities;
    2. List of operations needed for every element of this specific BOM list - along with needed working hours, payment rates, equipment cost rates, etc.;
    Then you create Production Order Header and Production Order Details. The header has Customer Orderwhich include 1 or several operations listed for Customer Order, and are attatched to specific workstation, employee, or employee group. Production Order may be defined for all entities of same type (qty > 1), or e.g. for single entity;
    To start to work, you need components/materials. So next step is PurchaseOrders to suppliers (supplier ID, material ID, ordered quantity, etc.);
    When ordered material arrives, you need to register it. Usually is for this is used Storage Log, where all material movements (arrival, sent out for specific Production Order, returned from specific Production Order, Scrapped, etc.) are registered (material ID, movement date, movement type, quantity, etc.);
    Storage sends out materials for current Production Orders. After that the work starts.
    Now for every Production Order you need a Report with Report Details, where employee(s) reports made work and hours spent (reporting may be done stepwise - any number of rows in Report Details may be used for operation in Production Order;
    When for all Production Orders for Customer Order Row {number of ordered qty} = {number of reported qty}, Customer Order Row status will be set to to value indicating 'made';
    When Customer Order has several Customer Order Rows, and all Customer Order Rows have status to value indicating 'made', or when Customer Order has single row and all for all Production Orders for this row {number of ordered qty} = {number of reported qty}, Customer Order status will be set to to value indicating 'made';
    You can send invoices to customer at some shedule for work done at certain period, or at once when all work is done;
    When Customer Order status has value indicating 'made', the work is done.

  13. #13
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Micron,
    Thank you. You are making me think...in a very good way. I am going to modify my Tables and post an updated picture...
    Question:
    Licenses...each professional license is like a Driver's License...unique to the employee...are you suggestion I create a table with License ID (PK), list the licenses and then attached them to the EE? Below...

    tblLicenses
    LicenseID
    LicenseType
    LicenseNumber
    EmployeeID (FK)

    Question:
    Materials...each material has a unique SKU from the Supplier...are you suggesting remove the SupplierID (FK) from my materials table and connect materials and suppliers with a junction table?

    Any insights on Timecards...this is my most difficult challenge...maybe just the way I am thinking about it...Employees work under POs...different hours each day (sometimes multiple times per day). They record their time and materials on a daily Ticket which breaks down the POs they worked on for that day...thoughts?
    Thank you.
    Michael

  14. #14
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    My current tblMaterials
    Click image for larger version. 

Name:	tblMaterials.png 
Views:	22 
Size:	21.5 KB 
ID:	40473

  15. #15
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Thank you...very insightful.
    Yes...we create a Purchase Order based on securing work from a customer. That PO drives our entire process of capturing Time and Materials. I want to create a way of capturing all time and materials associated with that PO to determine our cost and our profit. I need to know what hours (time) where worked by which employee and what materials (BOM) where procured at what suppliers.
    I was thinking a multi-tabbed form as I would like the data to be entered as it occurs. Thoughts? suggested tbls?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  2. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  3. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  4. Mechanical Completion and Commissioning
    By masoud_sedighy in forum Sample Databases
    Replies: 1
    Last Post: 06-12-2013, 07:20 AM
  5. Replies: 2
    Last Post: 01-28-2013, 04:42 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
  •  
Tech Forums: Microsoft Office Forums