2 customer tables?

[TomAtSBio]

Greetings all,


I'm a access newb and I've been tasked with creating a database for my jobs customers and their equipment leasing.
The issue Im having is that one customer may be the parent company to many other customers, and all of them will be leasing equipment.
Later in the project I am to create a form for a contract renewal where the parent company will be billed for the equipment at all of the equipment at their facility and the equipment at other customers who they are the parent company for.

Will his require 2 customer tables with all of the same customers in each?

Any 1 customer may own some of the others, like when a dentist office acquires another practice and then owns all of their original practices and the ones they just purchased.

Also what kind of relationships would then need to be made.

I have been watching some udemy courses, but this example does not seem to be covered.

[ranman256]

I would use ONE tCustomer table
never duplicate data

and another table to hold all companys having customers linked say: tCoCustomers

CoID, CustID

then 1 customer can exist in many companies.

[TomAtSBio]

Please see the pic below. Is this what you mean?
The parent companies will lease equipment as well, so I tried to tie the parent company table to the equipment table and the customer table to the equip table as well.
Not sure if this is correct though.

I have also added a tax table as I must correlate a tax rate to the customer which is set by their county/state.
Does this seem correct?

[ranman256]

it still looks like double employee tables.
tCompany should only be company data. No people.
only 1 tCustomer table

a joining table tCoCustomers with 2 flds
CoID, CustID

[Welshgasman]

I'd probably have a field for parent id, and it would be perhaps null or zero if a parent company?

[orange]

I recommend you tell us about your "business/opportunity" in simple, plain English.
Perhaps you could describe " a day in my business". Or an example of a "Customer leases equipment......"
Sounds like job may be involved as well as contract, but need clarification.
What is it in your business that would require you to record company/customer hierarchy?

From an Access perspective, you should avoid names with embedded space(s).

[ssanfu]

You should not have spaces in object names
There should not be punctuation or special characters (except the underscore) in object names.


Consider:


ParentCompanyID_FK = 0 when company is a Parent company.
ParentCompanyID_FK = CompanyID_PK when a company is contracted to a parent company



This is how I would begin.... you really have not given enough (any) info on what the process is or the requirements.



"Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

[TomAtSBio]

OK. My customers tbl was copied from M$'s Northwind example. I will refrain from using spaces/punctuation/special characters in the object names. Thnx

For my job:
I work for a service company and we have many customers that purchase service agreements to cover the equipment they own.
The service agreement cost will be determined by the amt of equipment they own. (each piece of equip has an associated cost shown in my table above)
Some customers are parent companies to other customers, so the bill for the service agreement covering the parent company and their sub-companies must be sent to the parent company.
That being said, the parent company will be in one county of a state and their sub-companies in other counties, each with their own tax rate (which changes often), so I will later build a report that shows the parent company and their service agreement covering their equipment and then all of their sub-companies and each of their equipment inventories along with the total price of the agreement and the sales tax for each.
Any customer in our list may be a parent company and this is always subject to change as one customer may purchase another and become the parent.

So in the end, we will use the db to:
1. log and create the service contract
2. total the equipment price which will then be the service agreement cost
3. We must be able to change the tax rates easily
4. swap the equipment from one customer to another as it may be sold from one to another or sometimes trashed
5. Show the relationship of parent company and sub-companies

[davegri]

DB based on ssanfu's schema. See if this is on the right track.

TomAtSbio-davegri-v01.zip

Attachment 45548

[TomAtSBio]

Thank you for the reply. Ive been looking at the relationships and it seems so straight forward.
I am going to keep looking into all the parts of this.
BTW what does

Call ClearAll("Dummy")

do when the Clear all sub is called?
What is a dummy ctl?

[davegri]

Thank you for the reply. Ive been looking at the relationships and it seems so straight forward.
I am going to keep looking into all the parts of this.
BTW what does

Call ClearAll("Dummy")

do when the Clear all sub is called?
What is a dummy ctl?

It's just a workaround. ClearALL requires an argument. The argument is the combobox name NOT to clear. When I want to clear all the comboboxes, I just send it the string "Dummy", which it won't find a match for, and all the comboboxes get cleared.

Bye the way, I have an enhanced version with some bug fixes here:
Edit: The new version has some more functional queries as well.

TomAtSbio-davegri-v02.zip

[Cotswold]

What you want to do is fairly standard stuff.
Your final invoice will be to the main account and the main account will have sub accounts, depots or sites.
In your customer Table there will be the Account Code to match accounts for the Parent and a Sub Code for the Child accounts, to which you will add charges.
This came up on another Forum where someone needed help on creating an invoice header and the invoice charges related to the header. I described the
process that I have used for more years than I can remember in a PDF, which I attach. I suggested that there is no need for an invoice header Table and an
Invoice body Table. The PDF should explain. The Tax or VAT is covered.

Maybe you will find this helpful.