Move data fields from one table to another

[Guest]

My database holds data in two tables in a one to many relationship. tblCase
(primary key, CaseNum) is linked with tblProcess (foreign key, CaseNum).
What I would like to do is move two fields of existing data (not autonumber
info) from tblProcess to tblCase while maintaining the integrity of the
information, that is, keeping the data assigned to the proper case number.
Any help would be greatly appreciated.

 

[Jeff Boyce]

If you've correctly described the relationship (1:m), what is it about the
two fields in the many table that make them more appropriately kept in the 1
table?

I can envision an update query (after modifying the tblCase to include two
new fields) that joins the two tables and updates the fields in tblCase
based on matching records in tblProcess... but what happens if either of
these two fields in tblProcess have, in separate records related to the same
"parent" in tblCase, different values? Which value should be updated into
tblCase?

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You bring up a very good point. What happened is that after 5 years of use,
a new user asked the question, "Why are we typing the 'claim number' and the
'date of loss' each time a process (the many side of the relationship)
related to a process is performed when the information is unique to the case
to which the process is related? This was a design flaw that should have
been resolved at the beginning. It is possible that the same ''claim number'
and 'date of loss' are entered in multiple processes (each with a unique
process autonumber) all of which are related to the one case autonumber.
Under these circumstances can I append the two tables and move the two data
fields to the proper case number to which it belongs even if the data exists
with multiple processes in the tblProcess?

 

[Jeff Boyce]

Sounds like a very valid approach.

First, make a backup of your database.

Create two (empty) fields in the Case table to hold the values.

Then create a query against the Process table to return CaseNum and both
fields -- and set the Unique Values property to Yes.

Inspect that query's output -- if you truly have only one 'claim number' and
one 'date of loss' per CaseNum in that Process table, you'll have only one
(query output) row per CaseNum. Inspect carefully ... since the values had
to be re-entered in each Process row, you have the opportunity for ... "fat
fingers and other oops". Do whatever cleanup is needed.

When you're satisfied that all Process rows belonging to (each) Case have
only one 'claim number' and one 'date of loss' across all rows, create
another query.

Add in your Unique Values query to get what these two fields should be. Add
in the Case table, and join the query and table on the common field
(CaseNum).

Convert the (new) query to an Update query. Update the two Case table
fields (should be empty still) to the linked values from the Unique Values
query.

?Did I mention making sure you have a backup copy of the .mdb file before
starting this?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you for your help. I will give it a try and let you know how I make out.

 

[Guest]

Jeff Boyce,

Help! Why won't the named ranges I create in excel appear in the import
wizard named range selection box for access? There's a whole list of named
ranges but none of them exist according to the error box.

 

[Jeff Boyce]

What version of Access are you using? What version of Excel?

I just created a named range in Excel, opened an empty db in Access, used
File | Get External Data ... | Import, changed the file type to Excel,
located/selected the new Excel file, opened the wizard, clicked on the Named
Range radio button, and saw my (new) named range. It imported fine.

Is this how you are doing it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff Boyce,

I'm using 2003 in both Access and Excel. I am using the file/get external
data/import as you described. I did, however, set up multiple tables in
Access and am trying to import small sections of a larger spreadsheet into
those individual tables. I have also just been told that Access doesn't
recognize non-contiguous named ranges in excel to access imports. Is that
correct? It seems like my manuals have said the opposite.

 

[Jeff Boyce]

I haven't tried that, but it makes sense that non-contiguous (and
potentially non-uniform) ranges in Excel would not be accepted in Access.
After all, if they weren't the same shape, what values would belong in what
fields?!

Regards

Jeff Boyce
Microsoft Office/Access MVP