Condition in join

[DK]

i want to create one report in MsAccess 2000. In my report there is a
condition in join below is my query.

SELECT ME_BMS.UNIQUE_ID,VALUELIST.VALDESC AS 'MON_TYPE'
FROM ME_BMS
LEFT OUTER JOIN VALUELIST ON ME_BMS.MON_TYPE = VALUELIST.CODE
AND VALUELIST.TNAME = 'ME_BMS '
AND VALUELIST.CNAME = 'MON_TYPE';

can anybody guide me on how to create a report based on the above query.

Thanks in advance.

 

[Jeff Boyce]

You can create a report based on a table or on a query.

Create the report in design mode, select the table or report as the Record
Source.

If you are saying that the SQL statement IS the query, use the SQL statement
as your record source.

Or have I misunderstood your question?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[DK]

i already created the report based on the join only but i want to give the
condition in join so that query will look like same which i mentioned in my
request.
i am connecting to SQL Server thru ODBC DSN.
How can i achive it? I have to use Queries of MS Access? I tried to create a
Query but Access is not allowing me to give condition in Join.

 

[John Spencer]

Pardon me,

This M I G H T work for you. I've not tested it, but someone else
posted it as a valid solution. Notice the parentheses around the
expression. By the way, should there be a trailing space in 'ME_BMS '
or did you mean 'ME_BMS"?

I don't believe you can use LEFT OUTER JOIN unless you switch Access to
use ANSI-92 SQL (I believe you can do that in Access2003)


SELECT ME_BMS.UNIQUE_ID,VALUELIST.VALDESC AS 'MON_TYPE'
FROM ME_BMS
LEFT JOIN VALUELIST
ON ME_BMS.MON_TYPE =
(VALUELIST.CODE AND VALUELIST.TNAME = 'ME_BMS '
AND VALUELIST.CNAME = 'MON_TYPE');

If that doesn't work, you can use a pass-through query or use Access SQL
that gets interpreted by the ODBC driver.

SELECT ME_BMS.UNIQUE_ID,VALUELIST.VALDESC AS 'MON_TYPE'
FROM ME_BMS
LEFT JOIN VALUELIST ON ME_BMS.MON_TYPE = VALUELIST.CODE
WHERE VALUELIST.TNAME = 'ME_BMS '
AND VALUELIST.CNAME = 'MON_TYPE';