combining two files

[Guest]

I have an access file with several tables; one of them, the [Students] table
contains 40 fields and over 4,000 records. This table has several fields that
are required and an autonumber field that is used as the primary key.
My boss created another access file to type his own data using his own field
descriptions. He used the basic fields(only 10 fields): Last, First ,address,
phone, etc. (it has over 600 records).
He wants me to combine the data in both tables; however, if a phone number
is the same in both tables,(his and [Students]) the record is a duplicate
(according to him) and it should show only once.
Question, can this be done relatively easily? (Frankly, i don't think so.)
Your answer may help me to prove my point!

 

[Ron Hinds]

I have an access file with several tables; one of them, the [Students] table
contains 40 fields and over 4,000 records. This table has several fields that
are required and an autonumber field that is used as the primary key.
My boss created another access file to type his own data using his own field
descriptions. He used the basic fields(only 10 fields): Last, First ,address,
phone, etc. (it has over 600 records).
He wants me to combine the data in both tables; however, if a phone number
is the same in both tables,(his and [Students]) the record is a duplicate
(according to him) and it should show only once.
Question, can this be done relatively easily? (Frankly, i don't think so.)
Your answer may help me to prove my point!

Actually, it IS possible! Referring to the example below, replace Field1-10
with the field names in YOUR db that correspond to the fields in your boss's
db. Replace BossDb with the full path and filename of your boss's db.
Replace BossTable with the name of your boss's table. Replace Fld1-10 with
the field names from your boss's db. PhoneNumber is a guess on my part,
replace those names with the appropriate ones.

INSERT INTO [Students] (Field1, Field2, Field3, Field4, Field5, Field6,
Field7, Field8, Field9, Field10) SELECT Fld1, Fld2, Fld3, Fld4, Fld5, Fld6,
Fld7, Fld8, Fld9, Fld10 FROM [BossDb].[BossTable] WHERE
[Students].[PhoneNumber] NOT IN (SELECT PhoneNumber FROM
[BossDb].[BossTable])

 

[Jeff Boyce]

'Fraid so... (but then, it's all relative ...)

Open a database and link to both tables.

Create a query that matches rows in the two tables on phone number.

Create another query that lists all of your boss's rows that aren't in the
match query. Convert it to an append query and append the data to the
relevant fields in your table.

By the way, a table with 40 fields is unusually "wide". There's a
possibility that your table structure (and database) would benefit from
further normalization.

Good luck

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

P.S.

When both my son and daughter were attending the same school, they both used
a single home phone number. I also question your boss's logic in asserting
that a match on phone number means a match on Student.

Jeff Boyce
<Access MVP>

 

[Guest]

Sorry about the delayed answer but I posted my Q. from home. Thank you for
your help!
I know that there are too many fields in my table. This table started with a
few fields and I have been adding more fields as requested by my boss [who
thinks is an Access guru(?)]. This table one fo the main tables to everything
else because it is linked to several users' front-ends. The table uses an
autonumber as its primary key. How can i further normalize it while it is
actually being used? Can Access do the normalization? Are the links, to the
fornt-ends, going to be lost?
Thank you for all your help

'Fraid so... (but then, it's all relative ...)

Open a database and link to both tables.

Create a query that matches rows in the two tables on phone number.

Create another query that lists all of your boss's rows that aren't in the
match query. Convert it to an append query and append the data to the
relevant fields in your table.

By the way, a table with 40 fields is unusually "wide". There's a
possibility that your table structure (and database) would benefit from
further normalization.

Good luck

Jeff Boyce
<Access MVP>

I have an access file with several tables; one of them, the [Students]
table
contains 40 fields and over 4,000 records. This table has several fields
that
are required and an autonumber field that is used as the primary key.
My boss created another access file to type his own data using his own
field
descriptions. He used the basic fields(only 10 fields): Last, First
,address,
phone, etc. (it has over 600 records).
He wants me to combine the data in both tables; however, if a phone number
is the same in both tables,(his and [Students]) the record is a duplicate
(according to him) and it should show only once.
Question, can this be done relatively easily? (Frankly, i don't think so.)
Your answer may help me to prove my point!

 

[Jeff Boyce]

Normalization is part art, part science. You can ask Access to attempt to
normalize your data (see Tools | Analyze | Table) but consider what it
produces as only a start.

Consider shutting off your computer and using paper/pencil to sketch out the
entities about which you need to store data and the relationships among
those entities. Check Google.com for normalization, entity, relationship
for some leads. Try Jeff Conrad's website for possible other leads
(http://home.bendbroadband.com/conradsystems/accessjunkie.html). Check into
books about relational database design (see Rebecca Riordan).

After you've considered and (possibly) re-designed your table structure (on
paper), create a new Access database and create that structure. Link from
your new db to your existing db tables and create queries to "load" your new
structure.

This new structure is your test db -- keep it backed up, and try different
approaches to working with it, including your current front-end (modified to
point to the new back-end).

Good luck!

Jeff Boyce
<Access MVP>

Sorry about the delayed answer but I posted my Q. from home. Thank you for
your help!
I know that there are too many fields in my table. This table started with
a
few fields and I have been adding more fields as requested by my boss [who
thinks is an Access guru(?)]. This table one fo the main tables to
everything
else because it is linked to several users' front-ends. The table uses an
autonumber as its primary key. How can i further normalize it while it is
actually being used? Can Access do the normalization? Are the links, to
the
fornt-ends, going to be lost?
Thank you for all your help

'Fraid so... (but then, it's all relative ...)

Open a database and link to both tables.

Create a query that matches rows in the two tables on phone number.

Create another query that lists all of your boss's rows that aren't in
the
match query. Convert it to an append query and append the data to the
relevant fields in your table.

By the way, a table with 40 fields is unusually "wide". There's a
possibility that your table structure (and database) would benefit from
further normalization.

Good luck

Jeff Boyce
<Access MVP>

I have an access file with several tables; one of them, the [Students]
table
contains 40 fields and over 4,000 records. This table has several
fields
that
are required and an autonumber field that is used as the primary key.
My boss created another access file to type his own data using his own
field
descriptions. He used the basic fields(only 10 fields): Last, First
,address,
phone, etc. (it has over 600 records).
He wants me to combine the data in both tables; however, if a phone
number
is the same in both tables,(his and [Students]) the record is a
duplicate
(according to him) and it should show only once.
Question, can this be done relatively easily? (Frankly, i don't think
so.)
Your answer may help me to prove my point!