Tables

Hi all. Thanks in advance.

Ive been putting together this database for the office. Luckily I've had
time to experiment with quite a few suggestions and they work well. Now Its
come down to decision time. Here's my dilema.


In our Agency we have records dating back to the 1800's.
We had also inhereted a few files from surrounding areas way back when.

One suggestion was to have one (Names table) and avoid replication of
tables. Okay, so here's where it gets tricky. Two systems (or file types)
need to auto number. FhID and CaseID.

The other types of cases are (i.e. UP, WD, D, YC etc...) These numbers are
set and will never grow, they are old. I guess ultimately Id like to be able
to search all names from all era's. and that is okay but how can I set these
others up?

I thought about A table with each type of ID listed with (Names) then a
table for each (file type) that either autonumbers or doesn't (for older).

Any suggestions would be greatly appreciated.

I'm not quite clear on your data, so data structure suggestions are only
general.

If you have something you're calling "Name", consider changing this word --
Access treats "Name" and "Date" (and a host of other words) as reserved. If
"Name" refers to a person, consider adding LastName and FirstName (?and
MiddleName, and Suffix?) fields, and, if necessary, parse your "Name" data
into these.

Autonumbers are designed to provide unique row identifiers, to help in
relating tables. I'm not clear on whether you have more than one table, and
if so, how they might be related. There'd be no reason you couldn't add a
(new) Autonumber field for your (new, combined) table, and keep the "old"
IDs as other fields, for cross-reference purposes.

You don't (generally) get to set Autonumbers, that's why they're called
"auto"numbers. But unless you are relating a table to others, and provided
you already have a unique identifier, you wouldn't need to use Autonumbers.

--
Good luck

Jeff Boyce
Access MVP

"Lin" wrote in message
...
Hi all. Thanks in advance.

Ive been putting together this database for the office. Luckily I've had
time to experiment with quite a few suggestions and they work well. Now
Its
come down to decision time. Here's my dilema.


In our Agency we have records dating back to the 1800's.
We had also inhereted a few files from surrounding areas way back when.

One suggestion was to have one (Names table) and avoid replication of
tables. Okay, so here's where it gets tricky. Two systems (or file
types)
need to auto number. FhID and CaseID.

The other types of cases are (i.e. UP, WD, D, YC etc...) These numbers
are
set and will never grow, they are old. I guess ultimately Id like to be
able
to search all names from all era's. and that is okay but how can I set
these
others up?

I thought about A table with each type of ID listed with (Names) then a
table for each (file type) that either autonumbers or doesn't (for older).

Any suggestions would be greatly appreciated.

Hiya Jeff and thanks for reply.

Basically I have 4 tables right now.
FHID, Case ID, Other ID (for old files Up, WD etc) and a Child Table.
Each table has a file number, surname, first name, etc.
I also have 2 related tables for FHID and CaseID to autonumber files for me.

Someone, I forget who suggested that I may only need 1 table for Surname,
Given etc....But, based on the differences between the various cases I wasn't
so sure.

Thanks

"Jeff Boyce" wrote:

I'm not quite clear on your data, so data structure suggestions are only
general.

If you have something you're calling "Name", consider changing this word --
Access treats "Name" and "Date" (and a host of other words) as reserved. If
"Name" refers to a person, consider adding LastName and FirstName (?and
MiddleName, and Suffix?) fields, and, if necessary, parse your "Name" data
into these.

Autonumbers are designed to provide unique row identifiers, to help in
relating tables. I'm not clear on whether you have more than one table, and
if so, how they might be related. There'd be no reason you couldn't add a
(new) Autonumber field for your (new, combined) table, and keep the "old"
IDs as other fields, for cross-reference purposes.

You don't (generally) get to set Autonumbers, that's why they're called
"auto"numbers. But unless you are relating a table to others, and provided
you already have a unique identifier, you wouldn't need to use Autonumbers.

--
Good luck

Jeff Boyce
Access MVP

"Lin" wrote in message
...
Hi all. Thanks in advance.

Ive been putting together this database for the office. Luckily I've had
time to experiment with quite a few suggestions and they work well. Now
Its
come down to decision time. Here's my dilema.


In our Agency we have records dating back to the 1800's.
We had also inhereted a few files from surrounding areas way back when.

One suggestion was to have one (Names table) and avoid replication of
tables. Okay, so here's where it gets tricky. Two systems (or file
types)
need to auto number. FhID and CaseID.

The other types of cases are (i.e. UP, WD, D, YC etc...) These numbers
are
set and will never grow, they are old. I guess ultimately Id like to be
able
to search all names from all era's. and that is okay but how can I set
these
others up?

I thought about A table with each type of ID listed with (Names) then a
table for each (file type) that either autonumbers or doesn't (for older).

Any suggestions would be greatly appreciated.

If you are embedding data in your table names (you mention "differences
between the various cases"), you are not using the strengths of Access as a
relational database.

I'm not there, so I don't understand "FHID, Case ID, Other ID (for old files
Up, WD, ..." -- these are meaningful in what you are trying to do, but I
don't get the meaning...

--
Good luck

Jeff Boyce
Access MVP

"Lin" wrote in message
...
Hiya Jeff and thanks for reply.

Basically I have 4 tables right now.
FHID, Case ID, Other ID (for old files Up, WD etc) and a Child Table.
Each table has a file number, surname, first name, etc.
I also have 2 related tables for FHID and CaseID to autonumber files for
me.

Someone, I forget who suggested that I may only need 1 table for Surname,
Given etc....But, based on the differences between the various cases I
wasn't
so sure.

Thanks

"Jeff Boyce" wrote:

I'm not quite clear on your data, so data structure suggestions are only
general.

If you have something you're calling "Name", consider changing this
word --
Access treats "Name" and "Date" (and a host of other words) as reserved.
If
"Name" refers to a person, consider adding LastName and FirstName (?and
MiddleName, and Suffix?) fields, and, if necessary, parse your "Name"
data
into these.

Autonumbers are designed to provide unique row identifiers, to help in
relating tables. I'm not clear on whether you have more than one table,
and
if so, how they might be related. There'd be no reason you couldn't add
a
(new) Autonumber field for your (new, combined) table, and keep the
"old"
IDs as other fields, for cross-reference purposes.

You don't (generally) get to set Autonumbers, that's why they're called
"auto"numbers. But unless you are relating a table to others, and
provided
you already have a unique identifier, you wouldn't need to use
Autonumbers.

--
Good luck

Jeff Boyce
Access MVP

"Lin" wrote in message
...
Hi all. Thanks in advance.

Ive been putting together this database for the office. Luckily I've
had
time to experiment with quite a few suggestions and they work well.
Now
Its
come down to decision time. Here's my dilema.


In our Agency we have records dating back to the 1800's.
We had also inhereted a few files from surrounding areas way back
when.

One suggestion was to have one (Names table) and avoid replication of
tables. Okay, so here's where it gets tricky. Two systems (or file
types)
need to auto number. FhID and CaseID.

The other types of cases are (i.e. UP, WD, D, YC etc...) These
numbers
are
set and will never grow, they are old. I guess ultimately Id like to
be
able
to search all names from all era's. and that is okay but how can I
set
these
others up?

I thought about A table with each type of ID listed with (Names) then
a
table for each (file type) that either autonumbers or doesn't (for
older).

Any suggestions would be greatly appreciated.

Hi Jeff Thanks for reply.

Ill try to explain it.

Prior to the 1950's our cases were stored on old microfilm rolls.
Files were numbered in accordance with the case type, for example:

a UP file number would be (Unmarried Parent)
a WD file number would be (A Ward of the Society)
a FH file (Foster Home)
a YC (York COunty Files inherited)
there are also BH (boarding homes) IH (Infant Homes) etc...

Each of these file types had their own set of numbering 1 to whatever.

They are no longer used (in terms of opening) However, they are still
accessed for histories and reading etc...

Then we have todays cases which are sequential from 1950's starting from
number 14673 today we are at 202000 (give or take). As you can see they are
all very different.

I could create different tables but I would be replicating (surname, given
name etc.)

Some of these older cases are accompanied b y a Roll number indicating which
microfilm roll they are on.

Does this help a bit?


"Jeff Boyce" wrote:

If you are embedding data in your table names (you mention "differences
between the various cases"), you are not using the strengths of Access as a
relational database.

I'm not there, so I don't understand "FHID, Case ID, Other ID (for old files
Up, WD, ..." -- these are meaningful in what you are trying to do, but I
don't get the meaning...

--
Good luck

Jeff Boyce
Access MVP

"Lin" wrote in message
...
Hiya Jeff and thanks for reply.

Basically I have 4 tables right now.
FHID, Case ID, Other ID (for old files Up, WD etc) and a Child Table.
Each table has a file number, surname, first name, etc.
I also have 2 related tables for FHID and CaseID to autonumber files for
me.

Someone, I forget who suggested that I may only need 1 table for Surname,
Given etc....But, based on the differences between the various cases I
wasn't
so sure.

Thanks

"Jeff Boyce" wrote:

I'm not quite clear on your data, so data structure suggestions are only
general.

If you have something you're calling "Name", consider changing this
word --
Access treats "Name" and "Date" (and a host of other words) as reserved.
If
"Name" refers to a person, consider adding LastName and FirstName (?and
MiddleName, and Suffix?) fields, and, if necessary, parse your "Name"
data
into these.

Autonumbers are designed to provide unique row identifiers, to help in
relating tables. I'm not clear on whether you have more than one table,
and
if so, how they might be related. There'd be no reason you couldn't add
a
(new) Autonumber field for your (new, combined) table, and keep the
"old"
IDs as other fields, for cross-reference purposes.

You don't (generally) get to set Autonumbers, that's why they're called
"auto"numbers. But unless you are relating a table to others, and
provided
you already have a unique identifier, you wouldn't need to use
Autonumbers.

--
Good luck

Jeff Boyce
Access MVP

"Lin" wrote in message
...
Hi all. Thanks in advance.

Ive been putting together this database for the office. Luckily I've
had
time to experiment with quite a few suggestions and they work well.
Now
Its
come down to decision time. Here's my dilema.


In our Agency we have records dating back to the 1800's.
We had also inhereted a few files from surrounding areas way back
when.

One suggestion was to have one (Names table) and avoid replication of
tables. Okay, so here's where it gets tricky. Two systems (or file
types)
need to auto number. FhID and CaseID.

The other types of cases are (i.e. UP, WD, D, YC etc...) These
numbers
are
set and will never grow, they are old. I guess ultimately Id like to
be
able
to search all names from all era's. and that is okay but how can I
set
these
others up?

I thought about A table with each type of ID listed with (Names) then
a
table for each (file type) that either autonumbers or doesn't (for
older).

Any suggestions would be greatly appreciated.

On Fri, 3 Dec 2004 06:55:05 -0800, Lin
wrote:

a UP file number would be (Unmarried Parent)
a WD file number would be (A Ward of the Society)
a FH file (Foster Home)
a YC (York COunty Files inherited)
there are also BH (boarding homes) IH (Infant Homes) etc...

Each of these file types had their own set of numbering 1 to whatever.

I'd suggest using TWO fields for the primary key: a FileType ("UP",
"WD", "FH" etc.) and a numeric field. Even that field might need to be
Text - if it's typical of such older filing systems, I bet dollars to
donuts that there are some records like "FH 3122A" and "FH 3122B"
buried in there somewhere.

You can make a Primary Key which consists of up to *ten* fields; it's
not necessary to have them in the same field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Hi John Thank You

You are absolutely right. Thats what I've done, using exactly 10 (field size)
However could I still get away with only one Person table with lastname,
firstname etc....



"John Vinson" wrote:

On Fri, 3 Dec 2004 06:55:05 -0800, Lin
wrote:

a UP file number would be (Unmarried Parent)
a WD file number would be (A Ward of the Society)
a FH file (Foster Home)
a YC (York COunty Files inherited)
there are also BH (boarding homes) IH (Infant Homes) etc...

Each of these file types had their own set of numbering 1 to whatever.

I'd suggest using TWO fields for the primary key: a FileType ("UP",
"WD", "FH" etc.) and a numeric field. Even that field might need to be
Text - if it's typical of such older filing systems, I bet dollars to
donuts that there are some records like "FH 3122A" and "FH 3122B"
buried in there somewhere.

You can make a Primary Key which consists of up to *ten* fields; it's
not necessary to have them in the same field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

On Fri, 3 Dec 2004 12:05:03 -0800, Lin
wrote:

Hi John Thank You

You are absolutely right. Thats what I've done, using exactly 10 (field size)
However could I still get away with only one Person table with lastname,
firstname etc....

Well... if you (CAREFULLY!) allow for the fact that names are
emphatically not unique. My late father and his father were also named
John W. Vinson, and last year I met another John W. Vinson at the auto
repair shop where we were both picking up our cars.. You need to have
some reliable way (difficult with historical data!) to make sure that
each record in the People table refers to a unique person, and you
absolutely cannot use just names to determine this.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps