Results 1 to 13 of 13
  1. #1
    HVAC is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    7

    Seeking help. Building database from ground up (overview and ERD). Notebook and pen to database

    Hello, I'm new here. I'm setting up a database to keep track of my service business. I have been using a notebook and pen to do this. Unfortunately, it's not very effective and I find myself missing potential customer sales and key information.



    Many will ask, well why not subscribe to an already existing program. Simple answer is, most of the programs don't do exactly what I want, and the ones that do cost around $300+/monthly subscription. I'm looking to have my laptop in my service truck to take the place of the notebook and pen. I could use excel, but a database seems to be the right solution.

    I'm willing to do everything myself and ask for help when something doesn't co-operate. I'm using office 2016. If anyone is willing to guide me, I will post my Database overview I've typed out and ER-Diagram in Visio that I've used as a starting point.


    I apologize in advance if asking for help on my first post is against forum rules.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It IS the right solution, so i hope youve done some study on relational database.
    The basics, you want to fill lookup tables,like:
    like tState table (usa) would hold the StateCode and StateName. (no ID needed since stateCode is unique)
    or tCountry table. 1 country code for each country.

    tClient table. holds only client info but DOES have an ID (autonum) because no person can have any unique identifier.
    and
    parent/child relationships, like a web shopping cart. You have 1 parent (tOrders) and many child records (tOrderDetails)
    the tOrder table would have a single item for the order:
    OrderID
    ClientID
    OrderDate
    BillToID
    ShipToID

    this would link to the child table with many records
    OrderID, ItemID, Qty, Price

  3. #3
    HVAC is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    7
    Overview and ERD.zip.zipThanks for the reply. I've done a fair bit of reading and research regarding the matter. Familiarized myself with tables, queries, forms, reports, structure, relationships, and normalization; not to any expert level of course, but enough to move forward. I'll attach the database overview to give insight on the flow and my take on representing the data in an ER-Diagram. I would appreciate feed back. Also feel free to edit the documents and re-upload them with what you think it should be. Thank you.

  4. #4
    HVAC is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    7
    Below is an overview of my service procedures:

    HVAC Database Overview
    Customers are identified by their first name, last name and phone number.
    Each customer may have more than one address associated with them.
    Address details contain street name and number (address), city, province, and postal code.
    Each address contains 3 equipment; furnace, air conditioner, and water heater.
    Each equipment is identified by make, model, and serial number.
    Service calls are tracked to each customer and their associated address by the date of service, which of the 3-equipment got serviced (could be multiple equipment serviced in one service call), the service carried out on the equipment, total cost of the service and whether or not the bill was paid (simple yes or no).

    A tabbed form will be used to search, view, update, delete, or add new customer details on one tab. Equipment details on another tab and service history on another tab.
    Another form, will be used to create new service calls where a new customer (with their address and equipment) can be added, or an existing customer can be selected or searched for service.

    I've also made an ER Diagram. It may or may not be correct. I want to get this stage right before moving forward.

  5. #5
    HVAC is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    7
    Here is my ERD

    Click image for larger version. 

Name:	ER-Diagram.jpg 
Views:	16 
Size:	76.4 KB 
ID:	27694

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Fellow Canuk, I say this with all due respect - I think you need to review normalization again. Maybe a different resource? The simplest way to explain normalization is to say that all the ATTRIBUTES of an ENTITY are in one table; one table for each entity. Your customer is one entity. Sales in another. Purchases in another. etc. So address goes in the customer table as it is an attribute of your customer. Then there are lookup tables (lookup and multi value fields should be avoided at all costs). I can provide what I think is a good link on normalization if any of this is new to you and you want to take me up on it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    HVAC is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    7
    Thank you for your input. I had it setup like that previously before coming here for help.

    Here's my problem with including the address as an attribute for customers. I have many many customers who have many many properties. For example:

    Joe is a property manager and is managing 10 properties. Each property is actually owned by 10 different people, however, those people are not my customers; Joe who manages them is my customer. When setting up Joe's account, his name and contact details will be repeated 10 times. Upon any updates to Joes contact details, I would have to seek and update it 10 times. This will be true for all the other customers I have that have more then one address associated to their name. This is why I've broken the address attribute into it's own entity.

    Please correct me if I'm looking at this all wrong, for that is why I've come

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is very rough... just sitting here spitballing...

    Consider:

    tblCustomers
    ----------------
    CustomerID_PK (Autonumber)
    CustFirstName (Text)
    CustLastName (Text)
    CustPhone (Text)
    BillAddress (Text)
    BillCity (Text)
    BillProvince (Text)
    BillPostalCode (Text)



    tblServiceAddresses
    -------------------
    ServiceAddressID_PK (Autonumber)
    CustomerID_FK (Long) (FK to tblCustomers)
    ServiceAddress (Text)
    ServiceCity (Text)
    ServiceProvince (Text)
    ServicePostalCode (Text)



    tblEquipment
    -------------------
    EquipmentID_PK (Autonumber)
    ServiceAddressID_FK (Long) (FK to tblServiceAddresses)
    EquipType (Text)
    EquipMake (Text)
    EquipModel (Text)
    EquipSerialNum (Text)



    tblServices
    ----------------
    ServiceID_PK (Autonumber)
    ServiceDesc (Text)
    ServiceCost (Double or currency)



    tblServiceCalls
    --------------------
    ServiceCallID_PK (Autonumber)
    ServiceAddress_FK (Long) (FK to tblServiceAddresses)
    ServiceID_FK (Long) (FK to tblServices)
    ServicedDate (Date)



    tblServiceCallDetails
    -----------------
    CallDetailsID_PK (Autonumber)
    Equipt_FK (Long) (FK to tblEquipment)
    ServiceCallDesc (Text)



    tblServiceNotes
    ----------------
    ServiceNoteID_PK (Autonumber)
    CallDetailsID_FK (Long) (FK to tblServiceCallDetails)
    ServiceNote (Text)

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Gotcha. I saw that in your post but figured 2 or 3 addresses at most, and updates can be cascading via relationships. I agree with your original notion.
    Steve; that's a whole lot of spit balls! Looks like it would allow for multiple visits to the same address for one service too.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link to a tutorial by Barry Williams. The model he discusses is much like the one I'm referencing below.
    I couldn't find the exact same model.

    Here is a data model from Barry Williams' site that may be helpful.
    It deals with Orders but you could rename that to Service Call etc.
    I'm suggesting it for the structure generally --entity/attribute/relationship
    It deals with multiple Addresses for Customers/Owners.
    The nice thing about these models is that they are generic. You can add to/remove from same as necessary,
    and change the names to meet your needs.

    I would normally create a model and post it, but I just found out the Community Edition (free) version of ErWin
    was dropped as of Feb 28. So I am diagram-less at the moment.

    Hope it helps.
    You're in good hands with Micron and Steve.

    Good luck.
    Last edited by orange; 03-01-2017 at 10:15 PM. Reason: update with video link

  11. #11
    HVAC is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    7
    Steve, I appreciate your time. I'll run a test to see what results I get. I wasn't expecting to break each and every "service" attribute into it's own entity. Is this for more versatility?

  12. #12
    HVAC is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    7
    Orange, Thank you for the link. I also looked at that site from reading other posts previously. I was flustered with the amount of examples provided that I couldn't zero in on anything specific. Glad you could provide a direct link.

    I use Visio pro that came with office for data modeling. Maybe give that a try.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I wouldn't spend too much time with Visio at this point. You can use pencil and paper and work from Steve's suggestion and anything you can glean from the Williams' model and build a quick picture of the tables and proposed relationships.
    Using some test data to represent records in the entities, test the model to ensure it handles what your require.
    Can you get to the data you need for an Invoice? Can you get the last Service call for Address X?
    How many service calls for Air Conditioners in the last 3 months?
    What ever questions are needed for your business --just mock up some test stuff and using the test data values, make sure you can get what you need.

    Tables and relationships are critical ---move on to forms etc later, but get the model designed and tested (it will give you a blueprint for design).
    See my stump the model post here.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-20-2016, 04:25 PM
  2. Seeking guidance with session database
    By melliott1712 in forum Access
    Replies: 5
    Last Post: 10-06-2014, 12:21 AM
  3. Replies: 6
    Last Post: 06-19-2014, 12:29 PM
  4. Seeking someone to help me build my own database
    By Meat4grinder in forum Access
    Replies: 7
    Last Post: 08-11-2012, 10:04 PM
  5. Database driven website....new ground for me
    By avarusbrightfyre in forum SQL Server
    Replies: 1
    Last Post: 10-09-2011, 08:19 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