Database-Query and AutoFill??????
 

Hi,
In an Excel 2003 table there are 2 coloumns that contain values, which are
also contained as values in an ODBC-database.
In the third coloumn I'd like to have further values, that belong to the
values of coloumn 1 and 2. These values should come from the database.
There for I made a query (Data, import external Data, new query ....) with
two parameters, which I've linked with the first two cells.
This works fine in the first line of the table.
Is there any possibility to complete the third coloumn using AutoFill, so
that I get the value that belongs to the values of coloumn 1 and 2 in each
line??

Hi

When both data are retrieved from same source, then you have to edit the
first query, so it gets data from both source tables. I.e. when the
SQL-string of original query was something like
SELECT SourceTable.Field1, SourceTable.Field2 FROM SourceTable SourceTable
, then the new SQL-string will be something like
SELECT SourceTable1.Field1, SourceTable1.Field2, SourceTable2.Field3 FROM
SourceTable1 SourceTable1, SourceTable2 SourceTable2 WHERE
SourceTable2.ID=SourceTable1.ID

When sources are different - like one table is from Excel worksheet and
another is a Dbf-table, or both are in different Excel workbooks, or source
tables are in different folders, etc. - then you have to query both tables
separately (and you have to include ID column to both result tables - it may
be one of 2 columns you get with original query, but it may also be an
additional column). You can add to first result table a column (next to
rightmost one - leave no gaps) with formula, which will retrieve according
values from second result table through worksheet formulas (p.e. VLOOKUP).
In data range properties for first query, check 'Overwrite existing cells
with new data, clear unused cells' and 'Fill down formulas in columns
adjacent to data' field - whenever the first query is refreshed, missing
formulas are added and abundant ones are removed.
Usually it is enough, when you set in data range properties, that queries
are refreshed on open. When you want to refresh them manually, then the best
way will be to add an AfterRefresch event for first query, from where the
seqond query is restarted.
Probably it'll be wise to hide the sheet with second query result table - no
need for user to fiddle on it.
When there exist a possibility, that the first query may return nothing,
then it'll be wise to write the formula in 3rd column like this:
=IF(ROW()=1;ColumnHeaderString;YourFormula)
and to have the this formula instead of 3rd column header too. Otherwise you
lost the your formula after an empty query.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"mattse_f" wrote in message
...
Hi,
In an Excel 2003 table there are 2 coloumns that contain values, which are
also contained as values in an ODBC-database.
In the third coloumn I'd like to have further values, that belong to the
values of coloumn 1 and 2. These values should come from the database.
There for I made a query (Data, import external Data, new query ....) with
two parameters, which I've linked with the first two cells.
This works fine in the first line of the table.
Is there any possibility to complete the third coloumn using AutoFill, so
that I get the value that belongs to the values of coloumn 1 and 2 in each
line??