Database Structure

[plog]

Only the Companies can have ContactPersons well most likely.

"Most likely", "Basically", "In most cases", "95% of the time", are all phrases that do not belong in database development. This is a technical pursuit which requires precise, specific and technical definitions.

First let me point out that you have same named fields in multiple tables that are in a relationship with another: Telefon, email, mobile, GenderIDRef, etc.. That's not correct. Most likely those should exist in one table and that one table should be related and referenced when you need that data. The key is to put the data at the lowest level it is needed.

I think the best way to eat this elephant is to open a blank Access database and paste in all your tables without data, just the structures, and build the Relationship Tool. Open the Relationship Tool and add one table to it--whichever one you deem your "main" table. Then choose a table you think relates to that one table and relate it properly. If they have any of the same fields, you must decide which table actually gets to house that field. Update your tables appropriately. Then, one by one, do that for every table--add it to the tool, making sure it relates to only 1 existing table and reconcilling duplicate fields so they exist in only one table. Do that for every table you have and you should end up with a valid Relationship Tool.

 

[GPGeorge]

Most likely", "Basically", "In most cases", "95% of the time", are all phrases that do not belong in database development. This is a technical pursuit which requires precise, specific and technical definitions.

I coined a saying to illustrate that point.

Ambiguity is required in poetry, humor and politics; ambiguity is anathema is relational database applications.

 

[silentwolf]

Wow lots to do and change

Maybe if you give us the 20,000 foot view of what this business does and what you are tracking, we can help you to simplify it.

I want to have it simplified but it is difficult it seams for me to get that correct model in place.

Of course I am not dealing with paper but I thought of keeping it in one place.
A Document could be Invoice, Order, and so on .. so that is why I tried to do it like this and not seperate tables for Orders, Invoices, Correspondence, and so on..
As each should be loged in a why I thought of them as Documents.
For Projects it is the same I like to track how much time is spent on a project.

But it is as you guys said not correct and multiple path of course also not but I just could and still cant work out the easiest and simples way
I rather get further into a big Hole of Tables )

Thanks also for the Link I am looking through this database I am sure it is very helpful!

Many thanks to all other to!

@plog
Will keep on trying to find a why out of this jungle

 

[silentwolf]

Did you consider actually describing what this business does or do you prefer to keep us in the dark?

Oh no not at all!!

Ok I try to explain!

It is for a friend of mine.

He is selfemployed. He mainly does maintainence work for either his own "Contacts" or "Customers" as well he gets some Contracts
from a "Supplier" or from serval suppliers.

The Supplier is also currently in the Contact Table as I got the data from him as he had it stored. But that is not the issue.

The Supplier provides him with following Information
1. Contact and Address of the Contact who wants this service or maintainance, or repair Service done

2. Is this Contract a "Waranty", or "Billto" Contact in that Service Contract. i.e BillingCode, R1 = Waranty, R2 = Bill to Contact, R3 = BillToOtherAddress ... meaning that the Contact can have a different Billing Address to the Address where this Repair Job needs to take place

If waranty then "Huber" is paying, or is the Contact on that ServiceContract paying for it and so on..

3. What Type of Product is it that needs to be repaired, or maintainanced ...including. Manufacturer, ProductTyp and Product

Now all of that needs somehow be accessable and correct entered and easily entered.
So he is not traveling to the billing Address instead of the address where the repair needs to be done.

After entering all that information he needs to have a plan when he needs to do what and where.
With DrivingRecords and so on but that is not the point... just saying..

And of course then after all is completed he needs to print out "Workhours" Service Report, and of course a Bill.

Hope that is a bit clearer now?

 

[silentwolf]

Hi Pat,

Yes that does make alot of sence and I did made some adjustments and thought about it alot too.

Unfortunatelly I am out for work today so will just need to get back to you with better details a little later.

But sure will give you feetback in more detail once I am back again!

For the meantime I thank you and all others so much for giving me a hand with this!

Cheers!