MS Query

[FMDev]

Hello

I'm new to creating queries with Excel, and could do with some help.

Basically, I have a worksheet with about 200 rows of data. One of the
columns is empty and I want to populate this column with data from a
Filemaker Pro database.

I have created an ODBC link to the database via MS Query, and can access the
field I need, however, I am struggling to find a way to match the data in
the database to the rows in the worksheet.

I would like pull over the matching data based on a unique reference number
present in the worksheet and database, but I don't seem able to link the two
in that way.

Can anyone offer any advice?

Many thanks

 

[Tom Ogilvy]

You wouldn't be able to do it directly.

One way would be to use msquery to bring in the data with the unique
identifier and the column you want into another portion of the worksheet (or
on another worksheet) and then use the vlookup formula to pull appropriate
value from there.

The other would be a blind pull where you order the results by the unique
identifier and have your worksheet data sorted the same way. That would
require that the match is one to one and both the returned dat and the
worksheet data are in the same exact order. I would go with the first
personally.

 

[FMDev]

Thanks for the advice Tom, I hadn't thought about doing that. As I
mentioned, I am an experienced Query / Excel user. Would the vlookup
formula work automatically? I am trying to present an 'easy' solution to a
client, so if there was just one 'run query' step to do that would be great.

Think I'd better investigate the formulas in Excel!

Thanks again

 

[FMDev]

Whoops, should have said I am an _in_experienced Query / Excel user. But
you probably guessed that!

 

[GysdeJongh]

Hello

I'm new to creating queries with Excel, and could do with some help.

Basically, I have a worksheet with about 200 rows of data. One of the
columns is empty and I want to populate this column with data from a
Filemaker Pro database.

I have created an ODBC link to the database via MS Query, and can access
the
field I need, however, I am struggling to find a way to match the data in
the database to the rows in the worksheet.

I would like pull over the matching data based on a unique reference
number
present in the worksheet and database, but I don't seem able to link the
two
in that way.

Hi FMDev,

I have a Database with a Table Experiments
That Table contains both a KeyExperiments and a CodeExperiment
I have the CodeExperiment , input by the user , but need the KeyExperiments
Here is the part of the code that works for me :

1) Create a New ADODB.RECORDSET
2) Use the Find function on the recordset to retrieve the record from the
database ; the construction of the criteria for the find function is a bit
tricky ....

Dim strSQL As String
Dim rcsExperiments As New ADODB.Recordset
Dim intKeyExperiments As Long
Dim strCriteria As String
Dim strCodeExperiment As String



'
'Open the Table Experiments in a Recordset
'to lookup the KeyExperiments for the Experiments
'with CodeExperiment
strSQL = "SELECT KeyExperiments , CodeExperiment FROM Experiments ;"
'
rcsExperiments.Open strSQL, _
"Provider=MSDASQL;DSN=KinomicsDSN", adOpenStatic, adLockOptimistic


'Lookup the next KeyExperiments
'Mark the sequence : Double quote , single quote , double quote to enter
'the CodeExperiment in the search string as a string !!
strCriteria="CodeExperiment = " & "'" & strCodeExperiment & "'"
rcsExperiments.MoveFirst
On Error GoTo NotFound
rcsExperiments.Find (strCriteria)
intKeyExperiments = rcsExperiments.Fields("KeyExperiments")
On Error GoTo 0

hth
Gys