Newbie Design question re: how many tables

[larpup]

This is basically a shipping program. I have a file for shipments.

Each shipment record has a;
1. Shipper (address, city, etc.)
2. Consignee (address, city, etc.)
3. Bill To Party (address, city, etc.)

The Bill to Party can be the Shipper, Consignee or a third party.

I've developed an app in a legacy database and basically used one file
for all three. When selecting a Shipper, Consignee or Billing Party I
used DLookups. Yes it added to the database however, it's been working
for 15 years. The database has a different mindset that Access so I
have been making the transition slowly.

I have just started writing apps in Access and have enjoyed the
functionality that it provides. I would appreciate any information
regarding an approach to the above (without using DLookups). By the
way, a shipper can be a consignee, and a consignee can (and will often)
be a shipper. So, I want to prevent as much duplicate data as possible.


Again, if anyone has any advice or where I can study some examples will
be greatly appreciated.

Lar

 

[Allen Browne]

Use a single Client table that contains all the individuals and companies
you deal with.

Add a Role table that has a record for each possible role a client might
have, e.g.:
shipper
consignee
consignor
employee
supplier

Then create a ClientRole table, with fields ClientID and RoleID. The records
will look like this:
ClientID RoleID
===== =====
1 consignee
1 consignor
2 shipper
...

Now you have a single Client table to manage, and the ability to create a
relation to any other table. You can restrict it to the desired type by
using a combo box to offer the choices. For example a combo offering a list
of shippers would have RowSource:
SELECT ClientID, Company
FROM Client INNER JOIN ClientRole
ON Client.ClientID = ClientRole.ClientID
WHERE ClientRole.RoleID = 'shipper'
ORDER BY Client.Company;

 

[larpup]

Allen,

I have the idea, but am not applying it properly. Are you saying that..

1. Use a single client table - got it.
2. Make a role table with one field describing the possible role a
client might have
(shipper or consignee) in this case
3. Make a 3rd table as you have describe above.

What I do not understand is how I link these in the actual
relationships.

My shipments table has a shipper and consignee. Do I link that to the
ClientRole Table? and if so, do I link that to the Client table? If
that is correct, where do I use the 2nd (Role) table.

A little confused. Would appreciate a little clarification on this if
you can. Maybe I'm just dense, but I see where you are going, just
don't know how to set it up.

Lar

 

[Allen Browne]

In the Relationships window, add a 2nd copy of the Client table. Access will
alais it as Client_1.

In your Shipments table you will have a fields named:
ShipperID
ConsigneeID

Create a relationship from the ShipperID field to the Client table by
dragging Shipments.ShipperID and dropping onto Client.ClientID.

Then create another relationship from the ConsigneeID field, by dragging
Shipments.ConsigneeID and dropping onto Client_1.ClientID.

If you have a 3rd field that also needs to relate to a ClientID, add another
copy of the Client table to the Relationshps window, and you can go again.

These relationships have no direct connection to the Role or ClientRole
table. Those tables are purely there so you can limit the drop-down lists so
that only valid shippers show up in the combo where you chose the shipper.

HTH.

 

[larpup]

Allen,

Thanks for your clarification. This is new to me and will test when I
get home today.

Thanks again for your valuable advice.

Regards,

Lar

 

[larpup]

Allen,

Thanks for your clarification. This is new to me and will test when I
get home today.

Thanks again for your valuable advice.

Regards,

Lar

 

[larpup]

Allen,

I understand however, still confused why I need two role tables.
Since a shipper can be a consignee and a consignee can be a shipper, it
shouldn't matter,yes? Are you saying that I should have a field in my
clients table to define role or just link it to the role table and if
so, how does that help?

Lar

 

[larpup]

Allen,

I think my confusion is as follows:
I've attached the tables as you have suggested.

Re: Role Table
One field (?) How does this relate the client role table? I don't see
the value with the query example you gave

Re: ClientRole Table
Is the ClientId an AutoNumber or Number (Long) (and if so, how is it
populated?)

I already have (of course a ShipperID and ConsigneeID). If I run
(within the shipments form) the query as you have written no records
appear. If I run the query (as just the info from the client table
(within the shipment form) the data is displayed in the Shipper Cbo and
Consignee Cbos.

What am I missing?

Lar

 

[larpup]

I complete the relationships as you suggested. however, no shippers
appear. I do not understand youw the Client table relates tot the
ClientRole Table. How is the ClientId populated? Do I link this table
to the Client table?

IF I just select the table (Client) on the drop down, it shows in the
consignee dropdown as well.

Still need a little clarification. What am I missing?

Lar

 

[larpup]

Allen,

I understand the alias and have applied that. My problem is with the
two dropdowns... (Perhaps this should really be in the forms forum?)

The problem I am having is that the query comes up blank, so I am not
populating the ClientRole table properly, even though it is exactly as
you have suggested.

I agree there should be one table, however, i cannot get the dropdowns
to work properly. Still a little lost.

Lar

 

[larpup]

Allen,

I'm still not quite sure how you utilize the role and ClientIdRole,
however, I've figured out that I can populate the textboxes based upon
the combobox by utilizing the Column(#) property.

Would still like clarification on the above. My queries for the cbo
come out blank so I must not be populating the ClientRole correctly.

Lar

 

[Allen Browne]

The database will work fine without the Role and ClientRole tables. If they
are not helpful, just leave them out.

As you suggested, you could just add a RoleID field to the Client table to
specify the role a client has. The trouble with that is it only allows a
client to have *one* role. That's not adequate for your project, where a
client can have many roles. Since one client can have many roles, and one
role can relate to many clients, you have a classic many-to-many
relationship between your clients and roles. This implies a junction table.
This ClientRole junction table has 2 foreign keys:
ClientID relates to Client.ClientID
RoleID relates to Role.RoleID
If Client.ClientID is an autonumber, then ClientRole.ClientID will be a
Number (not autonumber), so that it can appear many times. If a client has 3
roles, there will be 3 records with that ClientID.

The point of this table is to let you use a drop-down list for Shippers. The
ShipperID field will be a foreign key to Client.ClientID. On your form, your
ShipperID field will be a combo, and its RowSource will be a query that uses
the Client table and the ClientRole table, with criteria that restrict it to
the shipper role.

Again, if the concept of the Role and ClientRole tables is not useful, just
skip them for now, and set the database up without them. Once you have done
that, these tables will probably suggest themselves to you.

(Sorry for the delay in replying: am currently submerged in a sizeable
Access development to automate production by sending the information to the
Programmable Logic Controllers of the correct production machines to create,
punch, and identify the parts.)