table design and relationships

[danthrom]

I have posted this question in the general section, but thought it would
apply in this forum as well...

I have four tables:
TABLE A: client information ex. John Doe (DOEJ); Kate Bell (BELLK)
TABLE B: client file information ex. John Doe’s Business One (DOEJ 1001),
John Doe’s Business Two (DOEJ 1002); Kate Bell Business One (BELLK 1001),
Kate Bell’s Lease (BELLK 2004).
TABLE C: storage information ex. Box 10 was stored on 6-1-06, Box 11 was
stored on 7-1-06
TABLE D: extended information for both boxes and files ex. Box 10 has DOEJ
1001, BELLK 2004, Box 11 also has BELLK 2004. Inside Box 10, file BELLK 2004
is correspondence and notes, inside Box 11, file BELLK 2004 is documents and
final drafts.

I have set it up that the alias (DOEJ) is the primary key in Table A, and an
autonumber is primary key in table B (the alias is a foreign key in this
table), Table C’s primary key is the box number (the box number is a foreign
key for Table D). But I am having problems joining Table D to Table B. So
far I have a one-to-many enforced join from Table B’s autonumber to Table D
in a number field. This works for building a relationship. But how can I
set up a query/form which makes selecting a file simple?

I was thinking that through a query I can build a combo box displaying the
autonumber as a combination of client alias and file number ex. DOEJ-1001.
For data-entry the user needs access to a list of file numbers to add to a
new box (table C "Storage Box Inventory" with subform of table D).

Is there a way to accomplish this? Or am I approaching this wrong?

Thanks for the help.

 

[KARL DEWEY]

TABLE A: It would be best to used an Autonumber as ClientID and not use DOEJ
for John Doe as you could have a Jane Doe.
TABLE B: John Doe’s Business One (DOEJ 1001),
John Doe’s Business Two (DOEJ 1002); It appears you are putting multiple
business functions in the same record. It is better to have table like this
--
ClientBusID - Autonumber
ClientID - number - integer - foreign key - related to TABLE A
Name - text
TABLE C: Like this --
BoxID - autonumber
ClientBusID - number - integer - foreign key - related to TABLE B
Location - text
Contents - Memo - extended information for both boxes and files ex.

Set up a one-to-many relationship from Table A’s autonumber (ClientID) to
Table B number field. Set up a one-to-many relationship from Table B’s
autonumber (ClientBusID) to Table C number field.

 

[danthrom]

My example wasn't clear enough. But the primery keys for all tables A thru C
work fine. My problem comes with how to present the data for data entry.

For example: A user needs to update a storage box, number 15. This user
is putting John Does's 1001: Dog Grooming Shop, and John Doe's 1002: Body
Shop on Park Street, and Kate Bell's 1001: The New Italian Kitchen all in
this box 15. In addition, Kate Bell's 1001 file is also going in box 16.

So how do I make this easy to input?

another user suggested cascading combo boxes and a query based form.

Thanks,

danthrom

 

[KARL DEWEY]

You see I thought that the hierarchy was like this --
John Doe
Business-1
Box-10
File-22
Electric Invoice - 10/11/05
Sales Slip 10 Paper Clips - 11/12/05
Business-2
Box-11
File-41
Tax Return - 2005
Birth Certificate
Kate Bell
Business-1
Box-14
File-51
Etc.
That is why I said to use the relations that I suggested.

If you hierarchy starts with the box number then you have to relate it to
the next level in the chain.
Yes you need to use a query/form for data entry. Use subforms for the next
level down in the hierarchy.