By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,037 Members | 1,839 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,037 IT Pros & Developers. It's quick & easy.

MS Access - How to Make Query "Updatable"?

P: 1
OK So here's my essential Dilemma.

I have set up my Table to import from 3 excel files and 1 other table. The Excel files may or may not be updated at the time of each refresh of my Table "Candid" (for Candidates). Each record is autonumbered (meaning every refresh the number may change, because I delete all records and refresh from the files/table).

I want to enable functionality which will allow modifications to records in the Candid table, even if they are imported from Excel. Here's what I've been doing:

I created a column called UpdateFlag. Whenever the user modifies a record in the table, I have them set this column to "True". Then, when the refresh Macro is executed, I store all records with UpdateFlag marked "True" to a temp table. The problem is because the unique ID number changes for those records in Candid once they are re-imported, I want to delete the old copy and replace with the one from my temp table. My attempt to do so was as follows:

SELECT *
FROM Candid
WHERE Candid.Autonum = (Select CandManuallyEntered.Autonum From CandManuallyEntered Where (CandManuallyEntered.UpdateFlag = "True" and Candid.Autonum = CandManuallyEntered.Autonum+(SELECT temp.Autonum from temp)-(Select temp2.Autonum from temp2)));

Explanation: I am trying to delete candidates from Candid where the Autonum matches the autonum(oldID). Since this is obviously not possible, I am grabbing the max autonum before the refresh and the min autonum before the refresh and I am trying to find records which match old + (max - min) + 1. Now, when I do this by hand, the math makes sense.

e.g. if old # was 1554 and the Max is 3106 and Min is 1554, then if I take (1554 + (3106 - 1554) + 1), I get 3107 -- which happens to be the REAL new ID #.

Maybe there's a better way to actually carry out this assignment -- make it so people in Access and people with the Excel base files can modify the database? Or other thoughts?

Please be nice because I am still somewhat new to the world of SQL and Access :-)




THANKS so much for any help!

--Michael
Jul 20 '07 #1
Share this Question
Share on Google+
1 Reply


JKing
Expert 100+
P: 1,206
Hi though you've written quite a bit I'm still unsure as to exactly what you're trying to do. So far I understand that you import data from excel into a table. The rest you kind of lost me on. So let's try to figure this out. Can you give me perhaps a more concrete example. Like user changes candidates name from Jon to Bill and you want to save that change without having to be deleting the record and reinserting it?
Jul 23 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.