Sub Tables

[unclesteve]

In my client database, I have a table for client names and another for file information. They are linked through the client ID. There is one file ID for each item in a client's file, but a client's file could contain many files, or none at all. It is possible for a file ID to be used multiple times, but only once per client. (ie: Client A001 and client Y017 might both have a file that has an ID number of 01. I was thinking of "joining" the two (they're already linked in a relationship), but I'm unsure. Any help will be appreciated.

[Scott Price]

Hi unclesteve,

Could you post the meta data of your tables in this kind of a format:

table1
CustID AutoNumber PK
CustFName  Text
CustLName Text
FileID FK

etc?

This will give a little better idea of what you have and what you want to have!

Regards,
Scott

[unclesteve]

Hi unclesteve,

Could you post the meta data of your tables in this kind of a format:

table1
CustID AutoNumber PK
CustFName  Text
CustLName Text
FileID FK

etc?

This will give a little better idea of what you have and what you want to have!

Regards,
Scott

Client_Table

Client Key AutoNumber PK
Client ID Text
ClientIs_Entity Y/N
Client_PriName text
Client_SecName text

File_Table

File_Key PK
File_ID number
File_Description text
File_boxed Y/N
File_Destroy Y/N
Client Linked to Client_ID
File_Status_FK FK, linked to Status_Table
File_Location_FK FK, linked to Location_Table
Box_Key Linked to Box_Table

[Scott Price]

From your description, it sounds like you are having a little naming trouble with what really is a file. If I understand you correctly, each file will contain many items? Each client may be associated with more than one file, each file may contain more than one item?

Rather than joining the two tables (I consider that would be the wrong approach), you should create another table to hold the items that will be associated with the files.

A sample of the data you have entered in the tables would help clarify the case also.

Regards,
Scott

[unclesteve]

From your description, it sounds like you are having a little naming trouble with what really is a file. If I understand you correctly, each file will contain many items? Each client may be associated with more than one file, each file may contain more than one item?

Rather than joining the two tables (I consider that would be the wrong approach), you should create another table to hold the items that will be associated with the files.

A sample of the data you have entered in the tables would help clarify the case also.

Regards,
Scott

Scott:

I can see where the confusion comes in. I'm using the table names that the office administrator gave me. Personally, I would prefer "item" to the term "file". Yes, each client will have a number of different "items" in each client's file folder (that's probably a bad term to use, too). And each client's folder could have items with the same code as another client.

So far I've just been making up test data. Eventually the whole thing will be the front end (Probably another imprecise term. I would eventually be splitting the whole thing into a front end and a back end so that the end users wouldn't be able to mess with the tables and queries) for an Excel database that they already have built up. Hope this helps out. Thanks for your time.



Steve

[Scott Price]

Scott:

I can see where the confusion comes in. I'm using the table names that the office administrator gave me. Personally, I would prefer "item" to the term "file". Yes, each client will have a number of different "items" in each client's file folder (that's probably a bad term to use, too). And each client's folder could have items with the same code as another client.

So far I've just been making up test data. Eventually the whole thing will be the front end (Probably another imprecise term. I would eventually be splitting the whole thing into a front end and a back end so that the end users wouldn't be able to mess with the tables and queries) for an Excel database that they already have built up. Hope this helps out. Thanks for your time.



Steve

A couple test questions might help point us in the right direction :-)

Using their nomenclature:

Can one client be associated with more than one file folder?

Can more than one client be associated with the same file folder?

Can a file be associated with more than one file folder?

Can more than one file be associated with the same file folder?

Can more than one client be associated with more than one file? (i.e. can 2 clients be associated with one file?)

It looks to me like you've really got three subjects (i.e. tables) here... Clients, File Folders, and Files.

The above questions should clarify the One to Many relationships that exist between the three.

If the last question is true, then you have a Many to Many relationship between Clients and Files, and that would require a third table to link the two, or break the Many to Many down into two One to Many relationships.

Regards,
Scott

[unclesteve]

A couple test questions might help point us in the right direction :-)

Using their nomenclature:

Can one client be associated with more than one file folder?

Each client can have zero, one, or several "files" associated with it. Each file/item is one piece of correspondence, or a contract, etc. (This is a law firm)

Can more than one client be associated with the same file folder?

No. Different clients can have a file/item with the same number, but the numbers do not consistently relate to a type of item (ie: will).

Can a file be associated with more than one file folder?

As they use it, they are the same: a file/item is a thing unto itself, hence your confusion.

Can more than one file be associated with the same file folder?

Each file/item is one thing: a will, correspondence, articles of incorporation.

Can more than one client be associated with more than one file? (i.e. can 2 clients be associated with one file?)

No. Although two clients can have a file/item with the same number, each item is only associated with one client.

It looks to me like you've really got three subjects (i.e. tables) here... Clients, File Folders, and Files.

The above questions should clarify the One to Many relationships that exist between the three.

If the last question is true, then you have a Many to Many relationship between Clients and Files, and that would require a third table to link the two, or break the Many to Many down into two One to Many relationships.

Regards,
Scott

I hope that this clears up my murky previous posts and heads me towards solving this one problem. (Yes, there are others)

steve

[Scott Price]

I hope that this clears up my murky previous posts and heads me towards solving this one problem. (Yes, there are others)

steve

Hi Steve,

Looking at the way they use their nomenclature is a little confusing! However, the way you have explained it makes it seem more clear that you have a One to Many relationship between Clients and Files. Something still nags at me about that situation, but it's not coming to the surface at the moment!

Having a One to Many relationship means you need two tables, and to answer your first question, then: no, you shouldn't join(make one table out of two), however, yes you should join(create a relationship between the two). The relationship should be: Client_Key FK field in the File table linked to Client_Key PK field in the Client table.

Regards,
Scott

[Scott Price]

Part of the problem that was nagging at me is that you have two ID fields. The _Key field and the _ID field.

I understand that you have to use their system of numbering their ID fields, and am assuming that you added the _Key field to uniquely identify each record in the tables. Just remember that when creating the relationships you will need to join the tables on the _Key (unique) field rather than the _ID (non-unique) field!

Regards,
Scott

[unclesteve]

Part of the problem that was nagging at me is that you have two ID fields. The _Key field and the _ID field.

I understand that you have to use their system of numbering their ID fields, and am assuming that you added the _Key field to uniquely identify each record in the tables. Just remember that when creating the relationships you will need to join the tables on the _Key (unique) field rather than the _ID (non-unique) field!

Regards,
Scott

Thank you. I'll plow back into this and let you know how I do.

[unclesteve]

Part of the problem that was nagging at me is that you have two ID fields. The _Key field and the _ID field.

I understand that you have to use their system of numbering their ID fields, and am assuming that you added the _Key field to uniquely identify each record in the tables. Just remember that when creating the relationships you will need to join the tables on the _Key (unique) field rather than the _ID (non-unique) field!

Regards,
Scott

It seems to be working okay. Thanks for your help

[Scott Price]

It seems to be working okay. Thanks for your help

Glad I could help, Steve.

Regards,
Scott