Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 48
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,737
    So my 2 cents since I suggested the overview...by no means all encapsulating and certainly untested. I would normally write these out on a sheet of paper, draw lines for the relationships, pencil in the field data types and think about whether or not it supports my goals. You certainly could embellish this but given that it's kind of a hobby and not a corporate thing as I first thought, I tried to balance simplicity with function. In case it's not known, PK is primary key (I'd use autonumber field) and FK is the foreign key value, which of course is the PK from the parent table. I might even leave out the equipment table since this isn't about corporate or personally owned assets. Again, not vetted....

    tblCustomer
    CustPk - autonumber PK
    FName
    LName
    Phone
    etc - attributes of the customer only

    tblEquipment
    EquipIdPk
    EquipType
    Make
    Model
    Serial
    etc

    tblJobs
    JobIdPK
    CustIDFk
    EquipmentIdFk
    Notes (operating complaint or customer service request)

    tblParts
    PartIdPK
    JobIdFk
    Qty
    PartNum
    Description
    Cost
    Price

    tblJobLabour


    LabourIdPk
    JobIdFk
    LbrHours
    LbrRate
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  2. #17
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    Quote Originally Posted by orange View Post
    You may find these links of interest.
    -see https://weingartz.com/expert-advice/...and-solutions/
    -see youtube donyboy snowblower
    -see youtube mustie1 snowblower
    -see youtube steve's small engine saloon

    I subscribe to all of those channels. Love those guys. Thanks.




    Quote Originally Posted by orange View Post
    No formal contract, work order, invoice (paper work generally)????


    Correct. I don't see any formal contracts now or in the near future.

  3. #18
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    Thank you Micron. I like that set up.

    The reason I was leaning toward an equipment table is that even though I'm small time friends and family right now, most of the people I do work for own multiple pieces of equipment. Sometimes up to 5 between blowers, trimmers, saws mowers, etc. (I should have pointed out it's not just snow blowers but I have snow blower on the brain right now here in New England).

    I like being able to have easily accessible machine info on hand for when I'm ordering parts and looking things up. It seemed like the best way I could think of to track it.

  4. #19
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    Seems you are on the right track. I often recommend this tutorial from RogersAccessLibrary to get experience with tables and relationships. If you work through the tutorial (~30-45 minutes) you will experience a process that will lead to a structure that supports the business. You can apply the same process to any database, and you can exercise that model with sample scenarios and sample data (good and bad) to ensure it meets your needs before getting too deep into physical database and code.

    There are lots of articles that you may find useful in your project in the Database Planning and Design link in my signature.

    Yes, I'm also subscribed to those and related links. Especially entertaining and useful in this covid-19 time.
    Last edited by orange; 02-17-2021 at 07:16 AM. Reason: spelling

  5. #20
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    I appreciate the info and will study it.

    On another note, during my research, I often see people using a combination of foreign keys as the primary key for a table. Is this a recommended practice?

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,737
    Better to use a compound index with a PK field rather than a compound PK. In a compound PK, no single field is unique which can cause issues. Easier to deal with an autonumber PK to uniquely identify a record. I can't say for certain, but it wouldn't surprise me if certain query types (e.g. subquery) would fail without a single record PK.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  7. #22
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    I agree with micron. The purpose of the PK is to uniquely identify records in a table, and with Access a meaningless autonumber does the job well. The PK is meaningful to the database software.
    Here is a good reference for composite unique index.

  8. #23
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    Got it. Thanks.

  9. #24
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    840
    not just snow blowers but I have snow blower on the brain right now here in New England
    Couple inches coming this week in western ma.
    I need some work on my antique john deere tractor. Where you at?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  10. #25
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    I'm in Wilmington, north of Boston along 93. What kind of tractor? Lawn tractor or tractor tractor?

  11. #26
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    840
    1982 JD lawn tractor. Got it a couple years ago for $300. It was used one season and the guy got sick and left it in his garage for 35 years.
    Needs its first tune up.
    I'm in the Berkshire Mountains. What do you charge for pick up?


    Click image for larger version. 

Name:	jd.jpg 
Views:	25 
Size:	187.0 KB 
ID:	44272
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #27
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    We're practically neighbors.

    That's in amazing condition. Look how proud your friend is in the background.

  13. #28
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    I guess this would be considered an illness. But I had to do it. My guess is, I've made this worse. But after talking with you fine folks, I took another run at this. Why limit myself? This is a partial design...I haven't yet developed the parts, inventory and invoicing components yet, but am I getting the idea?

    I figured that I may want to track other types of people...suppliers, employees, etc, so I made a contacts table instead of a customer table. Started also thinking about what Micron said...maybe it would be a good idea to be able to have multiple machines on a ticket for the purpose of partial completion / delivery. Did I make a mess out of this?

    Click image for larger version. 

Name:	New Relationships - Copy.png 
Views:	18 
Size:	57.3 KB 
ID:	44284

  14. #29
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,737
    think about what you're doing when you repeat data - it's often wrong to do so. The thinking part is for determining which table it really should be in when you have that situation. Why is there email and phone in company and contacts? My advice is to think of a thing being an entity and characteristics of the entity are attributes, hence an entity/attribute relationship. Simple example: Person is the entity, DOB, gender, height, weight - all attributes of a person. Does a piece of equipment have a phone number?

    You'd have to test what you have. I'd create queries that will support forms and see if I could enter all the data to make at least one scenario work. BUT first question the premises that the relationships suggest to you, e.g.
    can I/will I ever put more than one contact id for a ticket?
    Or is it really 1 contact per job (you'd have multiple jobs on a ticket)?
    Or is it really 1 contact per piece of equipment (would I really call Sam about the oil change but Joe about the spark plugs)?

    2 more cents - My advice might be to not make it more complicated than it needs to be for your purpose. Then when you've actually used it for some time, apply what you learn from that if you want to expand on its capabilities. If you get stuck on planning for something you don't really need now, you're depriving yourself of the journey. Don't be like that kid on the diving board who's stuck on planning the plunge.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  15. #30
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    What is a Contact vs a Client/Customer?
    Does a Customer (or whatever you decide) have to have at least 1 repair job/order?

    As an exercise, try to define each of the things (your current tables) and see if there are some "needed changes".

    Have you tried to "work your current proposed structure" with some sample data and scenarios?

    From an old post
    I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.


    You may find this "Stump the model" helpful.

    Design and vetting is a journey - not an event.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  2. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  3. Help with Relationships
    By indians207 in forum Database Design
    Replies: 7
    Last Post: 11-25-2013, 09:53 AM
  4. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  5. Relationships
    By IT_GIRL in forum Access
    Replies: 3
    Last Post: 06-27-2011, 02:27 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 - Senior Forums