Relationship Problem

[Kevin_S]

Hi All!

I have a major problem with my db that I am hoping someone could help me out with. I have just created a new version of a db that holds approx. 1600 records. The old db was created poorly (to say the least) and has only two tables that are not related that hold an absurd amount of fields (about 60-70 each). Needless to say I had a major reconstruct to do. Now - I have broken those two massive tables into twenty tables all linked together through a unique ID (SITEID).

The problem that I have run into now is two-fold:

(1) The first issue is that I do not know how to import all of these records at once into the db as this is my first type of reconstruction work (I usually build dbs from the start before the data is collected) Is there a simple way to do this? Any suggestions would be most helpful

(2) The second issue relates to the first in that in order to get around the inability to import all of the files I decided to use a query in the old db to make a table in the new db with the appropriate fields for a given table (For example tblParking) Then I deleted the old table, renamed the new, and tried to link into the relationship with the master linking table ---- At this point Access told me that I could not create the relationship because of data mismatch---- this was not true as both fields (SITEID) are exactly the same!!!!


Does anyone know the reasoning for this error? I am trying to create a ono-to-one relationship between the two tables....

If seeing the db would help let me know and I can provide it.

Thanks,
Kevin

 

[nashat21]

hi kevin..

i went thru a similar problem sometime ago when i consolidated three dbs into one with many many more tables than in the original so i know exactly what you're experiencing.

after i created the make table queries in the old databases, i imported the tables into the new db and used append/update queries to add the data into my newly created tables. i did encounter the data mismatch error. is SITEID an autonumber? o some other data type? i also looked at the individual properties for the primary keys. ex i had to work with account numbers and these were text data types. i played around with the size of the filed in the new and old tables until it worked. also with percentages and other number fields, i had to ensure that they were all the same type (integer, long, single, number of decimal points) - it sounds tedious but it worked!

hth,

nashat

 

[The_Doc_Man]

The first issue is that I do not know how to import all of these records at once

Don't do it all at once. Do it one destination table at a time. Verify each table as you go. Get it right one table at a time. Massive, monolithic changes are for guys with serious God complexes or pointy-haired managers who don't understand anything more complex than a lead-filled leather blackjack. Use the method that Julius Caeser wrote up from so many centuries ago: Divide and conquer. This is a PERFECT example of a situation where that would work.

I decided to use a query in the old db to make a table in the new db

This is somewhat ambiguous. Did you write a query running from the old db to export a table to the new db? Or did you write a query in the new db to import a table from the old db? Makes a slight difference if the two db's are not the same version. (Are they?)

When you make relationships between tables, they gain special requirements for their key fields. I believe that if you have a one-to-xxx relationship, the table on the "one" side requires that the fields participating in the key are "prime keys." I.e. they MUST have an index with the "No Duplicates" attribute set. If you are making a one-to-one relationship, I think that means BOTH tables need that field to have a "No Duplicates" index.

So having field SITEID set for Long Integer in both tables isn't enough. They both have to be prime keys in their respective tables.

Note also that you might wish to allow the tables to have indexed, Duplicates OK except for the Site table itself. This would then allow you to define "sparse" tables. I.e. you could have tables for which the Site table does not have matching entries in some of the auxiliary tables. Folks don't always realize just how strict a one-to-one relationship can get. Whereas a one-to-many relationship is more forgiving. The "many" side of the relationship can be set up to allow for 0, 1, or many records matching the one on the "one" side of the relationship.

 

[Kevin_S]

Thank you both for replying as this issue is starting to drive me crazy and my time frame is rather short.

A little clarification on what I have here:

SITEID - This field is unique as each record has a specific site ID and this field is the primary key for those tables where a 1-to-1 relationship is to be established. This is a text field and all instances where this field is the primary key it is set to not allow duplicates. All SITEID field in the tables have the same everything (field size, type, length, etc.)*Note: all tables include the SITEID field but some have a one to many relationship and the primary key for these tables are autonumbers with the relationship established between the master table's primary key SITEID field and the child tables SITEID field that is not the primary key.

DOC_MAN:

Doing the data transfer in one step is not a requirement, I'm just not sure how to go about the transfer process.

To move the data I built a make-table query in the old db, pulled together the fields that are required in the new table, exported through the make-table to the new db, then I tried to rename the new make-table the same name as the blank table in the db. This imported table matches the forms for the blank table fine without a problem but I can not link it to the master table in a 1-to-1 by joining the two SITEID field together. They are both primary keys for their respected tables and when I drag/drop the fields in the relationship window - the join window says "One-to-One" but I can enforce referential integrity as it says there is a data mismatch! I have both fields set to 'No Duplicates' and I have even run queries to find duplicates in the field and nothing comes up.

I tried to build another query to find unmatched records in the two different tables and it tells me that 1600 records are unmatched between the two when it should have been 0! Since both tables hold the same Primary keys (SITEID) and have the same number of records!

I also tried making the autonumber field for the new imported table (I put a generic AutoID on each table) the primary key and joining the SITEID fields in a one-to-many- relationship and I get the same message about referential integrity and data mismatch.

Any idea how I can remedy this?

Thanks again, (p.s. using Access '97)
Kevin

 

[nashat21]

did you try appending the records from the imported make-table into the empty table that you created in the new db?

 

[Kevin_S]

Just wanted to say thanks to everyone for the posts and the help. I was able to get the records to import through a combination of using append, update, and make-table queries.

Again, thanks for your time and assistance!

Kevin