SQL Statement

[kleagle]

Hi Guys,

I have two set of databases, namely database1, database2. However, both of these db contains a member table with similar fields.

How do I do a select statement from both databases?

I tried the following statement but failed.

SELECT FROM dbo.database1.member as D1 INNER JOIN dbo.database2.member AS D2 ON D1.MemberID = D2.MemberID

Please advise.

Thanx in advance.

Regards.

KT

[HunterMcCray]

Originally posted by kleagle
Hi Guys,

I have two set of databases, namely database1, database2. However, both of these db contains a member table with similar fields.

How do I do a select statement from both databases?

I tried the following statement but failed.

SELECT FROM dbo.database1.member as D1 INNER JOIN dbo.database2.member AS D2 ON D1.MemberID = D2.MemberID

Please advise.

Thanx in advance.

Regards.

KT

Do not work so hard. Create a new Query in ACCESS and select the two tables. Play with it until it does what you want then in View goto ViewSQL. Copy the Sql and paste it into your App. Replace any hard coded values with variables that your program will supply. Using the SQL generator in ACCESS will save you tons of time in debugging your SQL, and it will always work.

Hope it helps,

Hunter

[kleagle]

Hi Hunter,

Many thanx to your valuable advice.

From what I know, the query generator in access can only retrieve recordsets from its own database. But what I need now is to access two database in MsSQL, which is something access can't help.

Cheers. :>

Best Regards.

KT