Table Design & Relationships

[mastromb]

I am trying to design a Project Management database for my work however I am running into a few snags in my table and relationship design.



The first issue I am having is that I have a table tblAP & tblAPDetails for my Accounts Payable. The tblAP is linked to the tblSupplier using the SupplierID from the tblSupplier as the Primary Key. The issue that has come into play now is that I have discovered that I also need to link to the tblShippers & tblCustomers from as well because of times that I have A/P from them as well.

I have tried to create a Union Query called qryPayees and have that referenced to the tblAP but when I did that I discovered that there were records from the tblSuppliers & the tblShippers that had the same autonumber and the query override the tblSuppliers records for the tbl Shipper records. The other idea I had was to combine all the tables and add a field for payee type ie. Customers, Suppliers, Shippers, etc.

Anyway that my first road block I guess I will address the rest in additional threads. Thanks in advance for the help.

[RuralGuy]

Have you read up at all on Normalization?

[mastromb]

Yes, I have been doing some reading on normalization and one of the things that a discover that I did not know about was concatenated primary keys. It seems that may help me with my union query if that is the right route to take with this problems but what I can't figure out because of my lack of knowledge in SQL is how to define that in a Union Query.

[RuralGuy]

Are you aware of the fact that Union Queries are *not* updateable?

[mastromb]

To put it simply, no I am not.

So if I have one table that records the acounts payable transactions and three seperate tables; tblCustomers, tblSuppliers, & tblShippers; that contains there information how do I create a relationship between them? I am guessing that I am going to need some intermediate table or query that establishes this relationship but I can't seem to wrap my head around the solution to accomplish it.

Thanks for your patience. I consider myself to be a fairly intelligent individual but some of these concepts make me feel like an idiot.

[RuralGuy]

I don't really understand your design. What fields do you have in your tblAP table?

[mastromb]

Attached is a PDF of the table relationships as I have them. The goal as I see it is to have the Payee field in the tblAP to lookup the Primary Key in the tblSuppliers, tblShippers, & tblCustomers. Thanks again for the help.

[RuralGuy]

You do *not* want to use Lookup Fields.

[RuralGuy]

When Shippers and Customers become Suppliers than why not put them in that table?

[mastromb]

It's not that Shippers and Customers become Suppliers its that they may submit an A/P Invoice that needs to be charged to a job say from Freight charges in the case of the Shipper or backcharges in the case of the Customer. The other reason I didn't want to add them to the Suppliers table is because it seems that it would create a relundent record and would have to be updated in both places if changes where necessary.

To the point about Lookup Fields, I understand the points that are being made in the link you posted, but unless I missed something I don't see or understand how to do it differently. I thought the whole point to a relational database was to be able to reference data through primary keys or the like.

The more I learn about this stuff the more I realize I have so much more to learn.

[RuralGuy]

Using Lookup Tables in ComboBoxes on Forms is just fine and the preferred method; just not LookupFields in a table. You would not duplicate the other table data, just include the PK of that table.

[mastromb]

First off thank you for your help on this.

Ok, I see what you are getting at with the Lookup Field in a table. I haven't created the Forms yet for this Database but I have been developing it with real data so I just assumed that since there existed a Lookup Wizard that you should use it. I guess I need to move forward with forms and edit/input the data from there.

You are still confusing me a little on the inserting or combining of the Customers/Shippers/Suppliers tables by the Primary Key. How can you either insert the Primary Key of the Shippers or Customers Table into the Suppliers Table or create a relationship in an intermediate table or query to the Primary Key of multiple tables with out it being a Union Query?

[RuralGuy]

The table structures for the three tables tblSuppliers, tblShippers and tblCustomers appears to be identical. Why three tables? Why not just one?

[mastromb]

I guess that was one of my original points is do I just combine all of the tables and add an additional field that categories the various contacts as Company, Customer, Shipper, etc. for sorting purposes at the various tables they are used on to limit the choices.

[RuralGuy]

I generally favor more tables but in this case they are really just names of companies. What function and roll they play in your project depends on what other tables have their PK and a FK. You can add the extra field if you want but you can probably get the same information by simply looking at DISTINCT values of FK's in the other tables.