Database query possibilities

Welcome, Per.

One approach. Add a new query table to the file pulling the project identifier and comment fields from your current Excel table. Such as "SELECT project_number, comment FROM table".

Now edit your existing query to also pull the comment from this new table. Such as, it now pulls "SELECT * FROM db" and change it to something like

Now always refresh the new comments table query before refreshing the main query.

Regards, Fazza

Is there a way to do this without writing code? I'm not even sure where to enter this code. Thank you!

Fazza said:

Welcome, Per.

One approach. Add a new query table to the file pulling the project identifier and comment fields from your current Excel table. Such as "SELECT project_number, comment FROM table".

Now edit your existing query to also pull the comment from this new table. Such as, it now pulls "SELECT * FROM db" and change it to something like

Code:

SELECT db.*, new.comment
FROM db, new_table new
WHERE db.project = new.project

Now always refresh the new comments table query before refreshing the main query.

Regards, Fazza

Click to expand...

Fazza said:

Welcome, Per.

One approach. Add a new query table to the file pulling the project identifier and comment fields from your current Excel table. Such as "SELECT project_number, comment FROM table".

Now edit your existing query to also pull the comment from this new table. Such as, it now pulls "SELECT * FROM db" and change it to something like

Code:

SELECT db.*, new.comment
FROM db, new_table new
WHERE db.project = new.project

Now always refresh the new comments table query before refreshing the main query.

Regards, Fazza

Click to expand...

Hello All,
I have a live data connection feed table in my Excel worksheet and I have created a new table with the key field and extra comments field and it refreshes nicely but I cannot figure out how to incorporate the new query into the existing SQL code for the original live connected table so that they join to re-feed the data back in after refresh. Any ideas?

Thank you,

Lindsay

hi, Lindsay

A live data connection feed table. The specifics of this will impact a solution. A possible slight variation on earlier method is suggested.

That new table with key field and extra comments that refreshes nicely, have it refresh every time a change is made to the comments field that is part of the original live connected table. So that new table is always in synch. Trigger by worksheet change event or whatever works best.

Then upon refresh of the live data connection and you want to get the comments back, if it is not easy to change the table's definition to join in the comments make it a separate update query. So some trigger to do something like "UPDATE main_table M INNER JOIN new_table N ON M.project_number = N.project_number SET M.comment = N.comment". Untested SQL. Trigger could be an on refresh event for live connection or maybe basic worksheet change or whatever works: should be able to work out something without resorting to manual button.

howzat sound?

another thought on this. there is another step needed, I think. before the already suggested UPDATE run this additional one, "UPDATE main_table SET comment = Null"

another thought, UPDATE queries might be a bit slow? consider VBA using worksheet formulas instead to re-populate the comments. regards