Change Table Index Values Based on query Values

[thuzkee02]

Hi All,



I am new to Access and SQL but I am fairly adept in Excel. I have linked an Excel workbook with several sheets as separate tables in Access. It updates the Access DB based on whatever changes were done on the sheets. I also linked the tables together so that instead of the index (ID), the equivalent values are shown through a query. Sample table below:

tbl_Country
ID	COUNTRY
1	United States of America
2	Canada
3	South Africa
4	China
5	India
tbl_Customer
CUSTOMER	CountryID
Alex Blanks	1
Peter Garrison	3
Mahindra Jones	5
query
CUSTOMER	COUNTRY
Alex Blanks	United States of America
Peter Garrison	South Africa
Mahindra Jones	India

My question is, is it possible to change the values of the country in the query itself or in excel and it automatically adjusts the CountryID on tbl_customer?

Thanks for any feedback you can give!

[ItsMe]

My question is, is it possible to change the values of the country in the query itself or in excel and it automatically adjusts the CountryID on tbl_customer?

Is it possible? It probably is possible. But, why would you? What you are doing is going against convention and you are trying to run up a sandy slope when there are better options.

First, you speak about linking to Excel and having users interact with and make changes via Excel. When you link an Excel spreadsheet to Access, the spreadsheet becomes Read Only. So, right off the bat, you have that hurdle. Then you want to somehow maintain referential integrity by updating Keys? That is not something you would integrate into the design of a production database. The idea of building a graphical interface is to help manage Constraints and Referential Integrity.

I suggest you study and learn how to use an RDBMS and create applications that rely on Relational Databases. Otherwise, you will be in a perpetual state of unhappiness.

[thuzkee02]

Thanks for the feedback, ItsMe.

I tinkered with the actual process I want to implement and found a solution. The entire process is actually doable in MS Excel but the problem is that I will be working with 120,000+ rows of temporary data and you can just imagine how excel would react with that huge amount of information; even just a simple vlookup function will definitely crash the entire file. Hence, I turn to Access.

As mentioned, the DB will be entirely temporary since I will just be using it to process the data, update it, clean it up and prepare it to be reverse-processed to fit the requirements of the Main SQL DB for bulk uploads.

I just used a bunch of "Select * Into", Subquery and Update query to fit my needs. I am still very new to this stuff so the working with just the basics us all that I can handle.