By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

database formation--relationship and forms

P: n/a
Hi, I have a database with four tables.

tbl_Client
client_alias (PK, text)

tbl_Matters
matterID (PK, autonumber)
matter (text)
client_alias (foreign key, tbl_Client)

tbl_Storage_Inventory
box_number (PK, number)
date
employee_initials (text)

tbl_File_Details
box_number (foreign key, tbl_Storage_Inventory)
matterID (foreign key, tbl_Matters)
contents (memo field)

All of the joins between tables are a one-to-many enforced join

eg--[tbl Client].[client alias] to [tbl Matters].[client alias] or
[tbl Storage Inventory].[box number] to [tbl File Detalis].[box number]

My problem has arisen from making a form for data-entry.

I need to make a form which draws from tbl_Storage_Inventory and
tbl_File_Details. In the past I did a simple subform linking the box
numbers (Storage Inventory is the master form). However, the user need
to select a matter based on client alias and matter, rather than
matterID.

here is sample data:

Client (Alias)--John Doe (DOEJ)
Matters--1001:The Body Shop on Melrose; 1002: New Age Books; 4007:ABC
lawsuit
Storage--DOEJ-4007 stored in box 10 and box 11; DOEJ 1002 stored in box
9
Details--DOEJ-4007 in box 10 has documents and notes, in box 11 is
client produced invoices; DOEJ 1002 in box 9 is complete file

Client (Alias)--Kate Bell (BELLK)
Matters--1001:Dog Grooming; 1002: PetForYou; 4001:Case BC112, Dog
Parking
Storage--BELLK-1001 stored in box 10 with client documents and lease;
BELLK-4001 stored in box 9 with settlement file and exhibits, in box 8
is misc. files

Our office only needs the file details in reference to storage boxes
since we need to know which box and which file to remove. In addition,
we input data either by new file, or storage; so I only need to forms.
The new file form is easy, but the storage form is giving me problems.

any help appreciated,

danthrom

Jul 7 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're doing what's know as "attribute splitting" by having 2 tables
describing the one entity: inventory (StorageInventory & FileDetails).

Your example data doesn't match the table descriptions. You should
display example data like this, for clarity:

Customer Items ItemDescription
====== ====== ==================
Fred 25 left-handed widget
Sally 35 right-handed wodget
.... etc. ...

The tables StorageInventory & FileDetails should be combined like this:

CREATE TABLE Inventory (
box_number INT NOT NULL ,
matter_id LONG NOT NULL
REFERENCES Matters (matter_id) ,
contents MEMO NOT NULL ,
change_date DATE NOT NULL ,
employee_initials CHAR(3) ,
CONSTRAINT PK_Inventory PRIMARY KEY (box_number, matter_id)
)

Then you can have subforms like this:

Client form
Matter subform inside Client form - linked by client_alias
Inventory subform inside Matter subform - linked by matter_id

I don't like the column "contents." It looks like it probably violates
First Normal Form (only one item can be in a column), but I can't be
sure, based on your data example.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLFu9YechKqOuFEgEQJmMgCfTg1ZkB5cGYV93fobPY43Ek X9BVcAoK5Q
RrMC6rc2nwIiTntJGsBiP+VC
=Vo85
-----END PGP SIGNATURE-----
danthrom wrote:
Hi, I have a database with four tables.

tbl_Client
client_alias (PK, text)

tbl_Matters
matterID (PK, autonumber)
matter (text)
client_alias (foreign key, tbl_Client)

tbl_Storage_Inventory
box_number (PK, number)
date
employee_initials (text)

tbl_File_Details
box_number (foreign key, tbl_Storage_Inventory)
matterID (foreign key, tbl_Matters)
contents (memo field)

All of the joins between tables are a one-to-many enforced join

eg--[tbl Client].[client alias] to [tbl Matters].[client alias] or
[tbl Storage Inventory].[box number] to [tbl File Detalis].[box number]

My problem has arisen from making a form for data-entry.

I need to make a form which draws from tbl_Storage_Inventory and
tbl_File_Details. In the past I did a simple subform linking the box
numbers (Storage Inventory is the master form). However, the user need
to select a matter based on client alias and matter, rather than
matterID.

here is sample data:

Client (Alias)--John Doe (DOEJ)
Matters--1001:The Body Shop on Melrose; 1002: New Age Books; 4007:ABC
lawsuit
Storage--DOEJ-4007 stored in box 10 and box 11; DOEJ 1002 stored in box
9
Details--DOEJ-4007 in box 10 has documents and notes, in box 11 is
client produced invoices; DOEJ 1002 in box 9 is complete file

Client (Alias)--Kate Bell (BELLK)
Matters--1001:Dog Grooming; 1002: PetForYou; 4001:Case BC112, Dog
Parking
Storage--BELLK-1001 stored in box 10 with client documents and lease;
BELLK-4001 stored in box 9 with settlement file and exhibits, in box 8
is misc. files

Our office only needs the file details in reference to storage boxes
since we need to know which box and which file to remove. In addition,
we input data either by new file, or storage; so I only need to forms.
The new file form is easy, but the storage form is giving me problems.
Jul 9 '06 #2

P: n/a
thanks for the advice. I am not sure I understand how you sent up the
tables, but I get the idea. The way I have the tables set up gives me
a heirarchy of client table--matter table--inventory details table (the
table with box number and contents). My contents field is simple a
memo which gives all the files located in the box. The matters can
have several files (these are legal cases), one particular matter has
over 15 boxes worth of files, each file is listed in the contents field
so when we have to retrieve a file we can search through the boxes for
that particular file. There are some re-occurring files, such as
correspondence and attorney notes, but most matters also have case
specific files

I am just wondering how to set up a form going from inventory to
matters. Ideally, there should only be two forms for data-entry in
this database. One for inputting new client matters and another for
creating boxes and assigning matters to the boxes. There can be
duplicates in that one matter can have several boxes and one box can
have several matters.

Thanks again,

danthrom

tMGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're doing what's know as "attribute splitting" by having 2 tables
describing the one entity: inventory (StorageInventory & FileDetails).

Your example data doesn't match the table descriptions. You should
display example data like this, for clarity:

Customer Items ItemDescription
====== ====== ==================
Fred 25 left-handed widget
Sally 35 right-handed wodget
... etc. ...

The tables StorageInventory & FileDetails should be combined like this:

CREATE TABLE Inventory (
box_number INT NOT NULL ,
matter_id LONG NOT NULL
REFERENCES Matters (matter_id) ,
contents MEMO NOT NULL ,
change_date DATE NOT NULL ,
employee_initials CHAR(3) ,
CONSTRAINT PK_Inventory PRIMARY KEY (box_number, matter_id)
)

Then you can have subforms like this:

Client form
Matter subform inside Client form - linked by client_alias
Inventory subform inside Matter subform - linked by matter_id

I don't like the column "contents." It looks like it probably violates
First Normal Form (only one item can be in a column), but I can't be
sure, based on your data example.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLFu9YechKqOuFEgEQJmMgCfTg1ZkB5cGYV93fobPY43Ek X9BVcAoK5Q
RrMC6rc2nwIiTntJGsBiP+VC
=Vo85
-----END PGP SIGNATURE-----
danthrom wrote:
Hi, I have a database with four tables.

tbl_Client
client_alias (PK, text)

tbl_Matters
matterID (PK, autonumber)
matter (text)
client_alias (foreign key, tbl_Client)

tbl_Storage_Inventory
box_number (PK, number)
date
employee_initials (text)

tbl_File_Details
box_number (foreign key, tbl_Storage_Inventory)
matterID (foreign key, tbl_Matters)
contents (memo field)

All of the joins between tables are a one-to-many enforced join

eg--[tbl Client].[client alias] to [tbl Matters].[client alias] or
[tbl Storage Inventory].[box number] to [tbl File Detalis].[box number]

My problem has arisen from making a form for data-entry.

I need to make a form which draws from tbl_Storage_Inventory and
tbl_File_Details. In the past I did a simple subform linking the box
numbers (Storage Inventory is the master form). However, the user need
to select a matter based on client alias and matter, rather than
matterID.

here is sample data:

Client (Alias)--John Doe (DOEJ)
Matters--1001:The Body Shop on Melrose; 1002: New Age Books; 4007:ABC
lawsuit
Storage--DOEJ-4007 stored in box 10 and box 11; DOEJ 1002 stored in box
9
Details--DOEJ-4007 in box 10 has documents and notes, in box 11 is
client produced invoices; DOEJ 1002 in box 9 is complete file

Client (Alias)--Kate Bell (BELLK)
Matters--1001:Dog Grooming; 1002: PetForYou; 4001:Case BC112, Dog
Parking
Storage--BELLK-1001 stored in box 10 with client documents and lease;
BELLK-4001 stored in box 9 with settlement file and exhibits, in box 8
is misc. files

Our office only needs the file details in reference to storage boxes
since we need to know which box and which file to remove. In addition,
we input data either by new file, or storage; so I only need to forms.
The new file form is easy, but the storage form is giving me problems.
Jul 10 '06 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yeah, I was right, your Contents column has more than one item in it
("all the files located in the box"). Correct DB design advises
[demands?] that each or your files should have a separate row (record)
in the inventory table. IOW, something like this:

CREATE TABLE Inventory (
box_number INT NOT NULL ,
matter_id LONG NOT NULL
REFERENCES Matters (matter_id) ,
file_nbr INT NOT NULL ,
change_date DATE NOT NULL ,
employee_initials CHAR(3) ,
CONSTRAINT PK_Inventory PRIMARY KEY (box_number, matter_id)
)

You could also have a Comment column for notes about the record.

Then an example record (row):

box_number, matter_id, file_nbr, change_date, employee_initials
================================================== =============
1 25 3 2/3/2006 FH
1 25 4 2/3/2006 GG
1 25 3 4/3/2006 LS
1 25 25 3/3/2006 MG

With a set up like this you can search for a file_nbr much more quickly
that searching thru a memo field (using, e.g., "LIKE '*File 2*'").
E.g.:

SELECT *
FROM Inventory
WHERE file_nbr = 25
AND matter_id = 1

Your statement: ". . . one matter can have several boxes and one box can
have several matters" is modeled by my table. It is an intersection
table between Matters and Boxes. You can have many boxes that have many
matters and vice-versa.

Read a book on DB design. I recommend _Database Design for Mere
Mortals_ by Hernandez.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLL19oechKqOuFEgEQL+HgCguYG8NB8zrEKfzLxMnGfqbs RDI6oAoNIq
phUjvGweHiL0BKyYKvIbXbrQ
=WqtR
-----END PGP SIGNATURE-----

danthrom wrote:
thanks for the advice. I am not sure I understand how you sent up the
tables, but I get the idea. The way I have the tables set up gives me
a heirarchy of client table--matter table--inventory details table (the
table with box number and contents). My contents field is simple a
memo which gives all the files located in the box. The matters can
have several files (these are legal cases), one particular matter has
over 15 boxes worth of files, each file is listed in the contents field
so when we have to retrieve a file we can search through the boxes for
that particular file. There are some re-occurring files, such as
correspondence and attorney notes, but most matters also have case
specific files

I am just wondering how to set up a form going from inventory to
matters. Ideally, there should only be two forms for data-entry in
this database. One for inputting new client matters and another for
creating boxes and assigning matters to the boxes. There can be
duplicates in that one matter can have several boxes and one box can
have several matters.

Thanks again,

danthrom

tMGFoster wrote:
>>-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're doing what's know as "attribute splitting" by having 2 tables
describing the one entity: inventory (StorageInventory & FileDetails).

Your example data doesn't match the table descriptions. You should
display example data like this, for clarity:

Customer Items ItemDescription
====== ====== ==================
Fred 25 left-handed widget
Sally 35 right-handed wodget
... etc. ...

The tables StorageInventory & FileDetails should be combined like this:

CREATE TABLE Inventory (
box_number INT NOT NULL ,
matter_id LONG NOT NULL
REFERENCES Matters (matter_id) ,
contents MEMO NOT NULL ,
change_date DATE NOT NULL ,
employee_initials CHAR(3) ,
CONSTRAINT PK_Inventory PRIMARY KEY (box_number, matter_id)
)

Then you can have subforms like this:

Client form
Matter subform inside Client form - linked by client_alias
Inventory subform inside Matter subform - linked by matter_id

I don't like the column "contents." It looks like it probably violates
First Normal Form (only one item can be in a column), but I can't be
sure, based on your data example.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLFu9YechKqOuFEgEQJmMgCfTg1ZkB5cGYV93fobPY43Ek X9BVcAoK5Q
RrMC6rc2nwIiTntJGsBiP+VC
=Vo85
-----END PGP SIGNATURE-----
danthrom wrote:
>>>Hi, I have a database with four tables.

tbl_Client
client_alias (PK, text)

tbl_Matters
matterID (PK, autonumber)
matter (text)
client_alias (foreign key, tbl_Client)

tbl_Storage_Inventory
box_number (PK, number)
date
employee_initials (text)

tbl_File_Details
box_number (foreign key, tbl_Storage_Inventory)
matterID (foreign key, tbl_Matters)
contents (memo field)

All of the joins between tables are a one-to-many enforced join

eg--[tbl Client].[client alias] to [tbl Matters].[client alias] or
[tbl Storage Inventory].[box number] to [tbl File Detalis].[box number]

My problem has arisen from making a form for data-entry.

I need to make a form which draws from tbl_Storage_Inventory and
tbl_File_Details. In the past I did a simple subform linking the box
numbers (Storage Inventory is the master form). However, the user need
to select a matter based on client alias and matter, rather than
matterID.

here is sample data:

Client (Alias)--John Doe (DOEJ)
Matters--1001:The Body Shop on Melrose; 1002: New Age Books; 4007:ABC
lawsuit
Storage--DOEJ-4007 stored in box 10 and box 11; DOEJ 1002 stored in box
9
Details--DOEJ-4007 in box 10 has documents and notes, in box 11 is
client produced invoices; DOEJ 1002 in box 9 is complete file

Client (Alias)--Kate Bell (BELLK)
Matters--1001:Dog Grooming; 1002: PetForYou; 4001:Case BC112, Dog
Parking
Storage--BELLK-1001 stored in box 10 with client documents and lease;
BELLK-4001 stored in box 9 with settlement file and exhibits, in box 8
is misc. files

Our office only needs the file details in reference to storage boxes
since we need to know which box and which file to remove. In addition,
we input data either by new file, or storage; so I only need to forms.
The new file form is easy, but the storage form is giving me problems.

Jul 11 '06 #4

P: n/a
I believe I understand what you are doing. I should have a table for
the matter details, listing each file

ie Matter 15 has folder 1correspondence, 1b correspondence, 2 attorney
notes, 3 motion to ___.. each of these number files a seperate record.
and this table should reference both the matter table and the inventory
table.

thanks for your help, i'll look up the book you recommended,

danthrom
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yeah, I was right, your Contents column has more than one item in it
("all the files located in the box"). Correct DB design advises
[demands?] that each or your files should have a separate row (record)
in the inventory table. IOW, something like this:

CREATE TABLE Inventory (
box_number INT NOT NULL ,
matter_id LONG NOT NULL
REFERENCES Matters (matter_id) ,
file_nbr INT NOT NULL ,
change_date DATE NOT NULL ,
employee_initials CHAR(3) ,
CONSTRAINT PK_Inventory PRIMARY KEY (box_number, matter_id)
)

You could also have a Comment column for notes about the record.

Then an example record (row):

box_number, matter_id, file_nbr, change_date, employee_initials
================================================== =============
1 25 3 2/3/2006 FH
1 25 4 2/3/2006 GG
1 25 3 4/3/2006 LS
1 25 25 3/3/2006 MG

With a set up like this you can search for a file_nbr much more quickly
that searching thru a memo field (using, e.g., "LIKE '*File 2*'").
E.g.:

SELECT *
FROM Inventory
WHERE file_nbr = 25
AND matter_id = 1

Your statement: ". . . one matter can have several boxes and one box can
have several matters" is modeled by my table. It is an intersection
table between Matters and Boxes. You can have many boxes that have many
matters and vice-versa.

Read a book on DB design. I recommend _Database Design for Mere
Mortals_ by Hernandez.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLL19oechKqOuFEgEQL+HgCguYG8NB8zrEKfzLxMnGfqbs RDI6oAoNIq
phUjvGweHiL0BKyYKvIbXbrQ
=WqtR
-----END PGP SIGNATURE-----

danthrom wrote:
thanks for the advice. I am not sure I understand how you sent up the
tables, but I get the idea. The way I have the tables set up gives me
a heirarchy of client table--matter table--inventory details table (the
table with box number and contents). My contents field is simple a
memo which gives all the files located in the box. The matters can
have several files (these are legal cases), one particular matter has
over 15 boxes worth of files, each file is listed in the contents field
so when we have to retrieve a file we can search through the boxes for
that particular file. There are some re-occurring files, such as
correspondence and attorney notes, but most matters also have case
specific files

I am just wondering how to set up a form going from inventory to
matters. Ideally, there should only be two forms for data-entry in
this database. One for inputting new client matters and another for
creating boxes and assigning matters to the boxes. There can be
duplicates in that one matter can have several boxes and one box can
have several matters.

Thanks again,

danthrom

tMGFoster wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're doing what's know as "attribute splitting" by having 2 tables
describing the one entity: inventory (StorageInventory & FileDetails).

Your example data doesn't match the table descriptions. You should
display example data like this, for clarity:

Customer Items ItemDescription
====== ====== ==================
Fred 25 left-handed widget
Sally 35 right-handed wodget
... etc. ...

The tables StorageInventory & FileDetails should be combined like this:

CREATE TABLE Inventory (
box_number INT NOT NULL ,
matter_id LONG NOT NULL
REFERENCES Matters (matter_id) ,
contents MEMO NOT NULL ,
change_date DATE NOT NULL ,
employee_initials CHAR(3) ,
CONSTRAINT PK_Inventory PRIMARY KEY (box_number, matter_id)
)

Then you can have subforms like this:

Client form
Matter subform inside Client form - linked by client_alias
Inventory subform inside Matter subform - linked by matter_id

I don't like the column "contents." It looks like it probably violates
First Normal Form (only one item can be in a column), but I can't be
sure, based on your data example.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLFu9YechKqOuFEgEQJmMgCfTg1ZkB5cGYV93fobPY43Ek X9BVcAoK5Q
RrMC6rc2nwIiTntJGsBiP+VC
=Vo85
-----END PGP SIGNATURE-----
danthrom wrote:

Hi, I have a database with four tables.

tbl_Client
client_alias (PK, text)

tbl_Matters
matterID (PK, autonumber)
matter (text)
client_alias (foreign key, tbl_Client)

tbl_Storage_Inventory
box_number (PK, number)
date
employee_initials (text)

tbl_File_Details
box_number (foreign key, tbl_Storage_Inventory)
matterID (foreign key, tbl_Matters)
contents (memo field)

All of the joins between tables are a one-to-many enforced join

eg--[tbl Client].[client alias] to [tbl Matters].[client alias] or
[tbl Storage Inventory].[box number] to [tbl File Detalis].[box number]

My problem has arisen from making a form for data-entry.

I need to make a form which draws from tbl_Storage_Inventory and
tbl_File_Details. In the past I did a simple subform linking the box
numbers (Storage Inventory is the master form). However, the user need
to select a matter based on client alias and matter, rather than
matterID.

here is sample data:

Client (Alias)--John Doe (DOEJ)
Matters--1001:The Body Shop on Melrose; 1002: New Age Books; 4007:ABC
lawsuit
Storage--DOEJ-4007 stored in box 10 and box 11; DOEJ 1002 stored in box
9
Details--DOEJ-4007 in box 10 has documents and notes, in box 11 is
client produced invoices; DOEJ 1002 in box 9 is complete file

Client (Alias)--Kate Bell (BELLK)
Matters--1001:Dog Grooming; 1002: PetForYou; 4001:Case BC112, Dog
Parking
Storage--BELLK-1001 stored in box 10 with client documents and lease;
BELLK-4001 stored in box 9 with settlement file and exhibits, in box 8
is misc. files

Our office only needs the file details in reference to storage boxes
since we need to know which box and which file to remove. In addition,
we input data either by new file, or storage; so I only need to forms.
The new file form is easy, but the storage form is giving me problems.
Jul 11 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.