Re: Still can't solve this, multiple database query pls help.

Why not have a new Access database which links the tables from both the SQL and Access database?

--
David Parker
Microsoft MVP (Visio)
http://bvisual.spaces.live.com
http://www.visualizinginformation.com
"Maddog" <maddog2992@xxxxxxxxx> wrote in message news:1179988780.743212.311860@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Ok, i have tried many times now for a solution, but i guess explaining
the problem is the hardest part,

However i came closer to a solution so here goes:

I have 2 DB's.

1=SQL (CRM)

2=Access (Project details)

These DB's are third party DB's so i CAN NOT make stored procedures on
them, or extra table etc. i can ONLY Query them.

I have made a dataset page contaning queries of these 2 databases. (oh
BTW im using visual web developer Express)

First one(CRM) has this query:

SELECT dbo_tForecastFF.lFieldID, dbo_tForecast.lForecastID,
dbo_tContactPersoon.lContactID, dbo_tContactPersoon.sNaam,
dbo_tForecastFF.sData FROM (((dbo_tContactPersoon INNER JOIN
dbo_tForecast ON dbo_tContactPersoon.lBedrijfID =
dbo_tForecast.lBedrijfID) INNER JOIN dbo_tBedrijf ON
dbo_tContactPersoon.lBedrijfID = dbo_tBedrijf.lBedrijfID) INNER JOIN
dbo_tForecastFF ON dbo_tForecast.lCounter = dbo_tForecastFF.lKey)
WHERE (dbo_tForecast.lForecastID = ?) AND
(dbo_tContactPersoon.lContactID = ?) AND (dbo_tForecastFF.lFieldID =
26)

The lForecastID comes from a textbox the user fills in.

Second(project details) has this query to get the lCPID number:

SELECT sVarValue
FROM tAnswers
WHERE (sVarName = 'lCPID') AND (sKey = ?)

sKey is same as project number so can come from the same textbox the
user enters..(or another solution from here on of course)

I CANNOT make an inner join saying lCPID=lContactID inside dataset
cause lCPID=string and lContactID=integer. (i get an error when i try
to do this in dataset)

What i want to do in the end: Display the contacts name (called sNaam
in first query) inside a textbox.

What i DONT know how to do: how to use the lCPID result from the
second Query, inside the first query for lContactID to get sNaam
result..

Hope i am clearer with explaining the problem this time.

Thanks in advanced for looking into it !


.