Can I combine related tables?

[staceyd]

I am trying to combine two different sets of related tables into one. Is this possible? In a 97 Access Database I have the following tables.

HSInst
HSCont
HSSupply
COLInst
COLCont
COLSupply

Each table PK is an AutoNumber field. The HSCont table contains the HSInstCode, the HSSupply table contains the HSContCode. They are all related with a one to many relationship with the Referential Integrity set up to cascade update in the old database.

In a new Access 2000 database, I am trying to combine these two sets of tables into one Institution table so that instead of six tables, I only have three; Inst, Cont, and Supply. Is this possible?

The problem that I am running into (currently) is that because the PK is an AutoNumber, I can't update it. If I change it to a number field, I can't get the changes to cascade to the related tables. Also, afer they are combined, new records will need to be added and then a new unique number is no longer generated.

If I try to combine them now, I have two sets of numbers in what would have been that PK. With two sets of each, they can't be a PK and I can't relate tables based on the old numbers. If I add a new AutoNumber field, the tables don't relate to each other anymore.

I have searched the archives for some insight, but couldn't find anything. Is what I am trying to do possible? I am pretty new at this so I am not sure if my logic is flawed, or if I just don't have the knowledge. Any suggestions?

Thanks so much in advance for your insight!

 

[ggreg]

if you Can build a query on all tables and get all
data from all tables then you could do a query make table
after the table is made then you could do away with the
other 5 ids from the other 5 tables or should be able to
because they would just be extra fields in your table.

but I would suggest that you try and figure out why you
can not cascade up date with the old tables....I am not
understanding why you want to go with 3 tables instead of
6?

by going with 3 tables my guess is your doing away with normalization and thats not good because it also my guess is
that your taking away lookup tables and now you might
be getting data input errors

 

[staceyd]

Thanks for your response. I think that I have answered all your question here. If I misunderstood something, please let me know!

I previously built the database with separated contact and order tables so that the database would not be storing the same information several times. The database holds unlimited number of contacts per every school and an unlimited number of orders per contact. If for example there were one institution with 2 contacts and each contact ordered 2 times, the database would have to hold the Institution information 4 times in one table. I think that this is called normalization.

The HS and Colleges were originally split because they were a part of separate programs. Each program collected slightly different fields of information in the Institution tables and the Supply tables. These programs have since merged. So that now all the queries, forms and reports that are set up can be consolidated into half as many queries, forms and reports (that will take half as much time to run). If I can consolidate them and keep the relationship in tact.

The cascade effect works in all the fields on the old database except one. I am not sure if it doesn't cascade because it is the primary key (PK) or if it is because it is an AutoNumber field. If I change the PK to a number field, it will no longer automatically generate a new number for each new record in the newer database. I can't really count on my data input people to create their own PK number.

If I try to consolidate all the data into one table, then bring it to the database the relationship will definitely have been preserved. But it seems like the database would be holding a great deal of repeated information. Also it seems like some counts would be hard to do (e.g. how many member colleges, etc.)

Is there some way to re-separate the thee tables it on the other end? Can I pull out the three tables, re-establish their relationship and still have the database automatically create a unique identifier as the PK?

Is there something here I am missing?

 

[ggreg]

I was miss understanding but now i know what your trying to
do and yes. I would try and do like you want to do.
and yes you can do it if your 3 tables have the same fields

HSInst
HSCont
HSSupply
COLInst
COLCont
COLSupply

meaning if HSInst matches COLInst
HSCont matches COLCont and
HSSupply matches COLSupply
matches the number of fields

work adding the records from two copies of each of each
db or 6 tables.....JUST work from copies not your real
tables or db incase you make a mistake.

let the one with the most records be the one you add too.
and to be easy on yourself leave the table names as they are.
now heres my thoughts on how to do this, add a field to all
six tables a number field...leave your autonumbers alone.

if HS tables has the most records then to these tables you dont
have to do anything to the new number field so
with the COL tables copy your autonumbers and paste then into the new number field....this new number field is only for tracking
or holding together your COL records now do an append query
to each of the three sets of tables in this append query you dont
append the autonumber but all other fields including the new
number field
you will append COLInst into HSInst
and COLCont into HSCont
and COLSupply into HSSupply

now you have to match back up and do update query or
(find and replace all) to the COLSupply records to COLCont
in the HS tables use your COLSupply in the query or refer
back to it by using that new number field you added to keep track of the old records being added to the HS table to start matching up the new autonumbers that were generated when you did the append queries then you would now do the same with COLInst
records that were add to HSInst.....

do you see now how you could do it. if not ask me more
questions.....
also

HSCont..... COLCont and
HSSupply..... COLSupply if these field have similar records
you can do away with the repeats and match the new COL
records with the one HS records...the new number field you
add to end is the key to rematching up the COL records
in the HS tables

 

[Pat Hartman]

The problem is that the autonumbers from the two sets of tables overlap. You can leave one set alone but you'll need to change the other set. Find out what the largest autonumber is from each table. For example let the highest key in HSSupply be 8709 and the hightst key in COLSupply be 22304. You can "bump" the autonumber value from the HS tables by 25000 to make sure they do not conflict with any of the COL values.

In the append query, Select the autonumber value from the HS tables and add 25000 to it to append it to the COL tables.

 

[ggreg]

I disagree you dont need to worry about the autonumbers
that why I said you dont append the autonumbers I have done
many queries appending to autonumbers and when you
append fields to autonumbers they will just keep on
counting
Ex:you have two tables with 10 numbers both autonumber fields
and you append fields from one table to the other you append all
fields but the autonumbers and when your done the
records you added will be 11 to 20 in the autonumbers field

now tho with the new autonumbers you will have to fix the
relationship of COL records in the HS tables (two tables).

now on copy and paste(only the original COL tables, if you shade the whole autonumber to copy you can shade your new number field and paste them all in with one copy and paste.

you will also be able to use Is not Null to not get the original HS
records and this will help you update the new records in
the HS tables so you can reset the relationships

 

[staceyd]

Here is what I don't understand. My tables are currently related through the unique AutoNumber field. Each HSSupply record contains the related HSContcode. Each HSContact record contains the related HSInstcode. If I update or add the number in the original table, then the records will no longer be related.

For example, let's say HSInst #2234 has a HSContact #4567. The HSContact #4567 record contains another field that holds the HSInst #2234.

Lets say first that I update the AutoNumbers to add 10,000 so that there are no duplicates. (which I am not quite sure how to do). I have to update HSInst #2234 to be #102234, how do I update that same number in the HSContact #4567 record so that these records are still related?

If I store the old number and add a new AutoNumber, I'm still not sure how to relate the records? Lets say that I append 10,000 records before it so that the new AutoNumber is HSInst #102234. I am still not sure how to change the HSInst number that is stored in the HSCont record. I can't relate the tables through the old stored InstCodes, because they are repeated. So I still end up with the same question.

Am I not understanding something about the potential relationship? Can I relate the tables some other way?

Thanks for your consideration on this one. I am really stumped!

 

[ggreg]

Staceyed,

I am starting to wonder if it would be worth it or not,
seem like you may have thousands of records in your second and
third tables and if that is the case thats how many update queries
you would have to run to reset the relationships after running the 3 append queries and the fact that you got to reset the
relationship on not just one table but two tables and your dealing
with numbers makes it confusing. I just set up a small attachment
to show you how to do the append queries and one update query
and it was hard making sure I was setting up the update query
right.

to run the append queries you have to delete the relationship
from the relationship table.

if you look at my attachment look at the data in the tables
then look at the design view of the queries before you run
them them run them and go back and look at the change in
the tables.

In the update query I name it 5 =2 but equal 5 = 1
the way I set it up.

 

[Pat Hartman]

ggreg, the problem is that you can't just arbitrarily change an autonumber if there is related data in another table.

In the following example, CodeID is an autonumber primary key. The append query is adding 10000 to the current value of codeID and appending the new record to the same table. Since there is no selection criteria, this query will copy ALL rows in the table, adding 10000 to their autonumber values and append the new rows. The effect of this is to double the size of the table. In your case, you would be selecting ALL rows from one table, adding some fixed amount to the autonumber, and appending the modified rows to a different table. I told you to find the value of the largest existing autonumber because you need to make sure that you will generate new numbers outside of the existing range. If table1 contains autonumbers in the range 1-4085 and table2 contains autonumbers in the range 1-2020, you could add 5000 to the values of the table2 autonumbers and be sure they would not conflict with the existing values in table1. So the first record from table2 would become 5001 and the last record would become 7020. Access would start autonumbering again at 7021.

Since you are adding a consistant value to the autonumbers, you will "know" what the "new" key value is. So when you import data from the related tables, you increment their autonumber primary key and you ALSO increment the foreign key reference for the parent table. That means the the append queries for the other two tables will contain TWO calculated fields. One for their own pk and a second for the foreign key that points to their parent table. You of course will need to populate the tables in hierarchial sequence to avoid any RI issues.

Does that make it a little clearer?

Example query:
INSERT INTO dbo_ERCCodes ( CodeId, CodeType, CodeDesc )
SELECT [CodeId]+10000 AS Expr1, c.CodeType, c.CodeDesc
FROM dbo_ERCCodes AS c;

 

[ggreg]

Now I understand what your doing. that will make it
a thousand time more easier and its the way to go!
Sorry Pat, I just was not understanding thanks for
responding back.