Strange join on SQL server source in Access

[MichaelintheUK]

I have to use 2 tables from SQL Server via an ODBC link in an Access enviroment.

For example

Table one contains a field with 6 characters and typical contents could be

100
100B
100C
101

Table two has a field with 3 characters and typical contents

100
101

On a join based on these fields the output is;

100, 100
100, 100A
100, 100B
100, 100C

There is an explicit join based on equality between the two fields - (i.e.not a non linked cartesian).

Can anyone explain the problem

thanks

Michael

 

[r937]

looks like 100A came out of nowhere, and 101 failed to match 101

what was the question again?

r937.com | rudy.ca

 

[MichaelintheUK]

Yes that was my error - to repeat the problem is using Access to retrieve SQL Server data via an ODBC connection with a strange result on relational data.

Table One
100
100A
100B
100C
101

Table Two
100
101

Show 2 fields as output in a joined query

100,100
100A,100
100B,100
100C,100
101,101

thanks

Michael

 

[r937]

i'm not sure i understand

you're saying that your access query does return these strange results? or that you want your access query to return these strange results?

is this a passthrough query?

r937.com | rudy.ca

 

[lespaul]

and can you post the SQL you're using to get these strange results....

 

[MichaelintheUK]

These are the results returned e.g. 100 in table two returns a match with
100
100A
100B
100C in table one.

i.e. where there should be one row returned there are 4 visible - clearly the wrong result.

The data is linked data from Access and is not using an SQL passthrough.

thanks

Michael

 

[r937]

so, could we actually see this query?

my copy of Microsoft® CrystalBall© doesn't seem to be working at the moment ;-)

r937.com | rudy.ca

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelintheUK]

Hello

Here is a copy of the SQL

SELECT dbo_MonitoringSpecialties.Specialty, dbo_InpatientsMain_FinSpells.Conspef
FROM dbo_MonitoringSpecialties INNER JOIN dbo_InpatientsMain_FinSpells ON dbo_MonitoringSpecialties.Specialty = dbo_InpatientsMain_FinSpells.Conspef;


thanks

Michael

 

[r937]

you are absolutely right, that is weird!

you should not be getting the results you are getting

r937.com | rudy.ca