Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-26-2018, 10:52 AM   #1
Modify_inc
Newly Registered User
 
Join Date: Mar 2013
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Modify_inc is on a distinguished road
Help with deciding best relationship for 3 tables

Hello all!
I'm trying to create a simple database to keep up with my few clients I provide service for. I'm trying to figure out the best way to relate my three tables I created: tblClients, tblDevices and tblJobs.

The way I see it is, Clients own Devices that require Jobs to service them, so at first I thought the tblClient would relate to tblDevices, and then tblDevices could relate to the tblJobs. But then I realized, not every client needs a job done on a device. Sometimes a client just needs consultation or other service that does not involve a device. What is a fairly simple way to resolve this as I'm still learning Access? Should I just add a non-device field entry to tblDevices or could I resolve this by using a Left Join type or any other ideas?

Modify_inc is offline   Reply With Quote
Old 04-26-2018, 11:08 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,686
Thanks: 67
Thanked 377 Times in 340 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Help with deciding best relationship for 3 tables

Honestly, the first step you may want to take is to just write down every field you intend to track. Right off the top of my head, I can see a need for tables for:

Clients
Services
WorkOrders
WorkOrderItems
Devices

What about expenses? Purchase orders/supplies? What if a client moves, do you want the old invoices to show the old address or the new one?

Figure out what you need to track first, THEN make the tables.
__________________
Doctor House was entirely too much of a 'people person'.
Frothingslosh is offline   Reply With Quote
Old 04-26-2018, 11:41 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,271
Thanks: 47
Thanked 1,817 Times in 1,767 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Help with deciding best relationship for 3 tables

I agree with Frothingslosh that writing things down would help, and his suggested tables make sense.
I would also suggest that using in plain, simple English --not database tech talk -- you describe to us a day or week in the business. We need to hear more about Jobs and Services and Consultation and how they inter-relate. Relationships are a reflection of business facts/rules ---they are not arbitrary lines drawn between tables/boxes.
Good luck.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 04-26-2018, 12:07 PM   #4
Modify_inc
Newly Registered User
 
Join Date: Mar 2013
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Modify_inc is on a distinguished road
Re: Help with deciding best relationship for 3 tables

Quote:
Originally Posted by Frothingslosh View Post
Honestly, the first step you may want to take is to just write down every field you intend to track. Right off the top of my head, I can see a need for tables for:

Clients
Services
WorkOrders
WorkOrderItems
Devices

What about expenses? Purchase orders/supplies? What if a client moves, do you want the old invoices to show the old address or the new one?

Figure out what you need to track first, THEN make the tables.
Thanks for the reply!

I have already done the first step. I have it all laid out in Excel. I'm just trying to figure out what is the best way to relate them now in Access. Also, I'm not worried about the financials and such, as I have other software for that. I'm just trying to design a simple database to keep track of the actual work being done for each client. For my basic needs I think those 3 tables are core to my database, I'm just not sure how you relate a table that may not be used all the time (tblDevices).

I'm thinking I could use a Left Join on the tblDevices, that would show all the clients and then only the devices assigned. I could then relate the tblClients to the tblJobs using a Inner Join. But then I'm concerned that I will not be able to query jobs for specific devices, as devices will have no relationship with jobs. Or is it possible by the transitive relationship of C (Clients) = J (Jobs) and C (Clients) = D(Devices), will D = J, so I can query jobs based on devices?
Modify_inc is offline   Reply With Quote
Old 04-26-2018, 03:34 PM   #5
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,686
Thanks: 67
Thanked 377 Times in 340 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Help with deciding best relationship for 3 tables

You set the join type in the query, not the relationships. The entire point of the standard one-to-many join is that the child (many) requires the existence of a parent (one), but not the other way around.

In fact it HAS to be that way, because the database itself still has to load data into one table first. As an example, you create the invoice first, THEN start adding line items to it.

It sounds like you're seriously, seriously overthinking this. Watching this video may help: https://www.youtube.com/watch?v=mEkOWAEF-nQ

I was going to point you to a really good website by Chip Pearson, but it is returning nothing but 404 errors all of a sudden.

Also, step back and re-read what jdraw said. Your relationships have to mimic your business processes. There's more to them than just connecting the boxes, and if you don't set them up well, they'll actually end up handicapping you.
__________________
Doctor House was entirely too much of a 'people person'.
Frothingslosh is offline   Reply With Quote
Old 04-26-2018, 07:20 PM   #6
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,597
Thanks: 41
Thanked 1,020 Times in 930 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Help with deciding best relationship for 3 tables

Quote:
tblClients, tblDevices and tblJobs
Clients own devices. Clients ask for jobs. Some jobs relate to devices. Some jobs relate to no devices.

Sounds like Clients is an independent table (potential parent). Both Jobs and Devices are dependent tables (potential children). They parallel each other, particularly if a job can be for more than one device as well as being for NO device.

Jobs will point back to the client.
Devices will point back to the client.

What I might do for the job is to have a JobDetails table (child of Jobs) that links back to a master Job entry (always) and optionally links to the Device if it is that type of job, but the device link can be blank for a consultation. Maybe. This JobDetails table gives you the ability to service multiple devices plus have a consultation all in one job. (If your business model allows that to happen, of course...)
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 04-26-2018, 09:24 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,937
Thanks: 13
Thanked 1,318 Times in 1,257 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Help with deciding best relationship for 3 tables

Quote:
I think those 3 tables are core to my database
You need at least four tables if one job can service multiple devices. In a relational database, you use a junction table to implement a many-to-many relationship. A Job might service multiple devices and each device might be serviced by multiple jobs.

If a job is only for a specific, single device, then 3 tables would suffice because the relationship between Jobs and devices would be 1 device - to - many jobs and so the device being serviced could be stored in the job table.


__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
access , database , relationships

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help deciding a name... TJPoorman The Watercooler 6 09-08-2013 07:27 AM
help in deciding the best way to set up my database Jason_c Theory and practice of database design 1 12-11-2012 12:10 PM
deciding which table for manual entries... kaiwalya1234 Tables 5 04-06-2012 12:44 AM
Relationship between tables zcampos Tables 1 07-18-2011 08:15 PM
Deciding WHICH field to display based on form control. Xx_TownDawg_xX Queries 2 04-07-2010 11:48 AM




All times are GMT -8. The time now is 02:58 AM.


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

Sponsored Links

How to advertise

Media Kit


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