One time only conversion of idx

Discussion created by Mosa on Aug 9, 2016



I have been battling my database for ages now trying to make three tables relate to one table and I have been advised to combine the three tables into one.Technically I am only combining two tables into one because the third table has no records yet so it isn't a concern.


I am VERY anxious about this seeing as I am beginner and I hate the idea of meddling with the idx and any guidance, steps would be massively appreciated. I will obviously save before I do anything!


The two tables are Venues and Schools and they want to become one table called Locations.



The idx_venues goes from 1-700

the idx_schools goes from 1-4500

The way I have figured out to do it is to do a calculation that adds 5000 to all of the idx_venues that resuts in a new field called idx_location; to rename the idx_schools to idx_locations; and to start the new idx_locations (the new table of both venues and schools combined) from 6000. I presume I then export it all and import it again?



Currently the Venues has a one to many relationship with contacts (idx_venues = fk_venuescontacts) and the Schools have a one to many relationship with contacts (idx_schools = fkschoolcontacts). I understand that these numbers will need to change as well to correspond with the new idx. Unless there is a way to make them change automatically when I change the others?


Any help would be greatly appreciated