Aligning manually entered data with external data (query)

[Giunca]

(Running XL 2007 on XP)
I have a basic query that returns data from a database to a sheet in my workbook (one record per row). I want to enter data into a cell adjacent to the returned data that will "stick with" the record that it is next to. The problem is that if the external data changes (records added or deleted), upon refresh of the query my text doesn't move with the record it was originally next to.

I have worked through the External Data Properties options (i.e. "Insert Cells...", "Insert Rows...", "Overwrite Existing..."), but none of these options gives me what I need. My text always gets misaligned from the record I originally put it next to. Is there a way to make manually entered data stick next to the records of the underlying external data?

Basic example:

Column A = records returned from the query
Column B = data manually entered into the spreadsheet by me

A * B
-------------------------------------------------
0003 *
0005 *
0010 * comments about record # 0010
0014 *
0019 *
0024 * comments about record # 0024

If record 0004 is added and record 0019 is deleted with the next refresh, I want the comments for records 0010 and 0024 to stay with their respective records. Chart would then look like this:

0003 *
0004 *
0005 *
0010 * comments about record # 0010
0014 *
0024 * comments about record # 0024

Make sense? Any ideas?
Thanks much.

 

[Fazza]

Hi Giunca.

Maybe like this similar question from a few weeks ago? http://www.mrexcel.com/forum/showthread.php?t=376984

HTH, Fazza

 

[Giunca]

Fazza,
Thank you -- that looks like the same problem, and I think I understand the principle of your solution, but I don't understand the coded part of your solution (is that VBA? I don't recognize it).

I will attempt to figure this out through the standard interface options -- if you can dumb it down for me, that would help.

Thanks much.

 

[Fazza]

Hi.

Currently you have a basic query from a database to a sheet in your workbook. The idea is to add another basic query this time from your workbook. This new query will pull the column A information and the column B information. This query is executed just before you are ready to refresh the query that populates column A. Now you have a table in another database of the column A and column B data. The basic query that originally populates column A becones modified, like the SQL in the other thread, so that instead of just populating column A it also draws data from the new table. So the entire refresh operation is now (1) refresh the new table and then immediately after, (2) refresh the modified original query.

Regards, Fazza

 

[Samoht_lee]

Hi.

Currently you have a basic query from a database to a sheet in your workbook. The idea is to add another basic query this time from your workbook. This new query will pull the column A information and the column B information. This query is executed just before you are ready to refresh the query that populates column A. Now you have a table in another database of the column A and column B data. The basic query that originally populates column A becones modified, like the SQL in the other thread, so that instead of just populating column A it also draws data from the new table. So the entire refresh operation is now (1) refresh the new table and then immediately after, (2) refresh the modified original query.

Regards, Fazza

Can someone please explain the concept in the above solution. I understand that one can pull data from an external source but I'm confused about the second query. Like this post states, I have a set of names which I'm getting from a SQL dataset. Next to the column "Names", I have columns which are manually entered in reference to the Name(s). Once I refresh the dataset, I'd like to have the comments line up with the name(s). Any clerification on this issue would help.

 

[ene310]

Can someone please explain the concept in the above solution. I understand that one can pull data from an external source but I'm confused about the second query. Like this post states, I have a set of names which I'm getting from a SQL dataset. Next to the column "Names", I have columns which are manually entered in reference to the Name(s). Once I refresh the dataset, I'd like to have the comments line up with the name(s). Any clerification on this issue would help.

The idea is to create a dynamic query which will read column A data that is in your original spreadsheet and run the query for those in that order. This is what I understand. Need to make it smart enough to add the new Column A data from database at the bottom i guess, I am not sure if this is possible.

I have the same problem and i cannot even send back input to the server directly from the spreadsheet. Need to figure out a way without lookup from inside the sheet. Anyone else with ideas? Microsoft should be reading these threads and improving their product. Or they should read this and hire me. I have so many ideas on what Excel users need.... Need something like take column A create a new column with that data on the right side of refreshable data. Break the link and paste values right before refresh on refresh command. and now you have an identifier but is there a way to sort data to match two different columns with unique identifiers?

 

[Fazza]

The idea is to create a dynamic query which will read column A data that is in your original spreadsheet and run the query for those in that order. This is what I understand. Need to make it smart enough to add the new Column A data from database at the bottom i guess, I am not sure if this is possible.

No, that isn't right. The order of the data is not relevant, and adding new data to the bottom is not relevant.

Say there is a database with AnyOtherFields and

ID
1
2
3

This is queried into a worksheet : SELECT ID, AnyOtherFields FROM database

Now for some records information is added to the worksheet in (a) new field/s. This/these field/s "Info" exist/s only on the worksheet. Field order unimportant.

ID Info AnyOtherFields
1 one
2
3 three

Now there is a requirement to refresh the ID (+ whatever other fields come from the database)

First, query the worksheet data and keep the critical identifying code & fields added in the worksheet : SELECT ID, Info FROM [worksheetname$]

Store that on a worksheet. Maybe hidden.

The original query from the database is modified to now also grab the Info from this (maybe hidden) new sheet.

So that query changes,
from original : SELECT ID, AnyOtherFields FROM database
to become : SELECT D.ID, W.Info, D.AnyOtherFields FROM database D LEFT OUTER JOIN worksheet W ON D.ID = W.ID

Any time a refresh is wanted it is a two step process,
1. refresh the worksheet that has the wanted Info field/s
2. refresh the modified original query to get data from both the main database & the wanted Info field/s from the just refreshed query 1

This is explained in several threads but doesn't seem to be understood. Some slight variation on the idea might be needed for specific requirements.

If the idea of the queries isn't easy, do something similar via VBA. Such as store the data from the worksheet either in another worksheet, or an array, or a recordset, or whatever. Refresh the data from the database, then loop through the copy of the data before the refresh - stored in the worksheet/array/recordset/whatever - and re-populate the Info field/s. The advantage of the first described approach is it is just two queries to refresh instead of one, and there is no need to loop. I guess instead of a loop, one could use VBA and INDEX/MATCH formulas. Or VLOOKUP. Or whatever.

hope this helps

 

[Pinaceous]

No, that isn't right. The order of the data is not relevant, and adding new data to the bottom is not relevant.

Say there is a database with AnyOtherFields and

ID
1
2
3

This is queried into a worksheet : SELECT ID, AnyOtherFields FROM database

Now for some records information is added to the worksheet in (a) new field/s. This/these field/s "Info" exist/s only on the worksheet. Field order unimportant.

ID Info AnyOtherFields
1 one
2
3 three

Now there is a requirement to refresh the ID (+ whatever other fields come from the database)

First, query the worksheet data and keep the critical identifying code & fields added in the worksheet : SELECT ID, Info FROM [worksheetname$]

Store that on a worksheet. Maybe hidden.

The original query from the database is modified to now also grab the Info from this (maybe hidden) new sheet.

So that query changes,
from original : SELECT ID, AnyOtherFields FROM database
to become : SELECT D.ID, W.Info, D.AnyOtherFields FROM database D LEFT OUTER JOIN worksheet W ON D.ID = W.ID

Any time a refresh is wanted it is a two step process,
1. refresh the worksheet that has the wanted Info field/s
2. refresh the modified original query to get data from both the main database & the wanted Info field/s from the just refreshed query 1

This is explained in several threads but doesn't seem to be understood. Some slight variation on the idea might be needed for specific requirements.

If the idea of the queries isn't easy, do something similar via VBA. Such as store the data from the worksheet either in another worksheet, or an array, or a recordset, or whatever. Refresh the data from the database, then loop through the copy of the data before the refresh - stored in the worksheet/array/recordset/whatever - and re-populate the Info field/s. The advantage of the first described approach is it is just two queries to refresh instead of one, and there is no need to loop. I guess instead of a loop, one could use VBA and INDEX/MATCH formulas. Or VLOOKUP. Or whatever.

hope this helps

Hi So Basically, what is happening is that your suggesting a "loop" by opening up ms database and throwing it back into the original worksheet.

Right?

 

[Fazza]

It isn't a loop in my mind.

The text you quote describes it as best I can. And as I wrote, it doesn't seem to be understood. I guess it is my explanation.

Not a loop, but a two step refresh.

Please can you explain step by step how you understand it? And explain the loop part.

Or if you don't understand it, please explain steps to provide the functionality. (Even if not detailed, just generally.)

regards

 

[strongman86]

Faaza,

I have similar issue with my spreadsheet any help would be much appreciated. Thanks. https://www.mrexcel.com/forum/micro...table-issue-after-adding-row.html#post5051463