Re: trying to make since out of a database

#1

sparks wrote:

Quote:

I just received a mess.
They admit it and are willing to do whatever it takes to fix.

they have 4 tables and they entered the data in the tables, they have
no forms.

each table is a different set of data for the same people
they used an autoid and set it to a primary key, not sure why, but
they did this in each table.

so they had
table 1
autoid 1 bob jones
table 2
autoid 1 bob jones
table 3
autoid 1 bob jones

they all match id and name until you get to table 4

in table 4 its multi records for the same people

autoid 1 bob jones
autoid 2 bob jones
autoid 3 jim smith
autoid 4 jim smith

I made a table demographics and put their main identifiers in here
I can link this to table 1 2 3 all works fine.
Now to table 4

is there a way to get table 4 structured to work as a 1 to many with
the new tbldemographics?

I can't think of any way to get table 4 to work but I wanted to ask
before going to any extremes like re entering the data.

thanks for any ideas

Change the autoid columns in tables 1-4 to type Long Integer, and populate
them with the autoid values from your new tblDemographics. Since tables
1,2,3 already have the correct ID you won't have to make any updates, but
you'll have to join table 4 to tblDemographics on Name and update the ID
field in table 4.

After doing the above, you should drop the Name columns from tables 1,2,3,4
(so it exists only in one place: tblDemographics)

Also, if tables 1,2,3 have one row per Name you should probably combine the
columns from tables 1,2,3 into one new table.

#2

got all of this done except one thing

you'll have to join table 4 to tblDemographics on Name and update the
ID field in table 4.

not sure how to do this since its a 1 to many relationship.

On Tue, 31 Mar 2009 10:00:07 -0500, "Builder" <nospam (AT) buildit (DOT) com>
wrote:

Quote:

you'll have to join table 4 to tblDemographics on Name and update the ID
field in table 4.

#3

I just stuck in a new RecordNumber in each and then updated the table
with a little code. Worked fine
so
1 bob jones is now tied to three records in the multi table all with 1
as its id.

On Tue, 31 Mar 2009 15:13:02 GMT, sparks <sparks (AT) comcast (DOT) net> wrote:

Quote:

got all of this done except one thing

you'll have to join table 4 to tblDemographics on Name and update the
ID field in table 4.

not sure how to do this since its a 1 to many relationship.

On Tue, 31 Mar 2009 10:00:07 -0500, "Builder" <nospam (AT) buildit (DOT) com
wrote:

you'll have to join table 4 to tblDemographics on Name and update the ID
field in table 4.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: trying to make since out of a database

comp.databases.ms-access comp.databases.ms-access

Re: trying to make since out of a database - 03-31-2009 , 10:00 AM

Re: trying to make since out of a database - 03-31-2009 , 10:13 AM

Re: trying to make since out of a database - 03-31-2009 , 11:26 AM