The Many Flavors of SQL - Can a SQL Server query work in MS Access?

D

Damian Carrillo

I am trying to take a complex query that works via the Query Analyzer
in SQL Server 2005 to work in MS Access 2003. Suffice it to say Access
is not my preferred environment in which to do this work, however given
the circumstances pertaining to licensing and systems policy, its the
only thing availible for this project. The following query works fine
in the Query Analyzer on a Windows 2000 Server box running SQL Server
2005:

Select DISTINCT rtrim(v.vendor_id) as vendor_id, rtrim(i.invoice_num)
as invoice_num,
i.invoice_status, i.invoice_date,i.period, i.inv_amt, v.vendor_name,
i.image_id as 'barcode',ch.check_num, ch.check_date,
'Office: ' + a.offc + '; Dept: ' + a.dept + '; Acct:' +
rtrim(c.acct_code) + ' - ' + c.acct_desc As description,
i.tran_uno,
i.session, ch.check_status
From cmsopen.dbo.apt_invoice i
Click to expand...
inner join cmsopen.dbo.apm_vendor v ON i.vendor_uno = v.vendor_uno
left join cmsopen.dbo.apt_invoice_amt ia ON i.tran_uno =
ia.inv_tran_uno And ia.tran_type='CH'
left join cmsopen.dbo.apt_check ch ON ia.source_tran_uno = ch.tran_uno
inner join cmsopen.dbo.act_tran_je a ON i.tran_uno = a.tran_uno
inner join cmsopen.dbo.glm_chart c ON a.acct_uno = c.acct_uno
Where 1=1 And a.offc='01' And a.tran_type = 'AP' Order By 1,2 ASC

I set up the same tables in MS Access 2003 as Linked tables. I can run
a huge number of queries but this is the most complex one I've tried,
and I can't seem to make it work, so I am thinking it must be
punctuation or keywords or something from SQL Server 2005 that isn't
common to the Microsoft JET SQL or ANSI SQL. Here's my attempt to
convert the statement, but it always erros out with: "Syntax Error
(missing Operator) in Query Expression"

SELECT DISTINCT rtrim(v.vendor_id) AS VendorID, rtrim(i.invoice_num) AS
invoice_num,
i.invoice_status, i.invoice_date, i.period, i.inv_amt, v.vendor_name,
i.image_id AS BarCode, ch.check_num, ch.check_date,
'Office: ' + a.offc + '; Dept: ' + a.dept + '; Acct:' +
rtrim(c.acct_code) + ' - ' + c.acct_desc AS Description,
i.tran_uno, i.session, ch.check_status
FROM dbo_apt_invoice i
INNER JOIN dbo_apm_vendor v ON i.vendor_uno = v.vendor_uno
LEFT JOIN dbo_apt_invoice_amt ia ON i.tran_uno = ia.inv_tran_uno AND
ia.tran_type='CH'
LEFT JOIN dbo_apt_check ch ON ia.source_tran_uno = ch.tran_uno
INNER JOIN dbo_act_tran_je a ON i.tran_uno = a.tran_uno
INNER JOIN dbo_glm_chart c ON a.acct_uno = c.acct_uno
WHERE 1=1 AND a.offc='01' AND a.tran_type = 'AP' ORDER BY 1,2 ASC

Anyone care to hypothesize as to why this is failing?
 
J

John Spencer

One thing is that if you have more than one join in the From clause you
have to use Parentheses

Another is not to use quoted aliases.

Another possible problem is that you may need to move "And
ia.tran_type='CH'" out of the FROM clause and into the Where clause

Making all those changes should give you something like the following.
You might try building the base of the query using the query grid. Add
all the tables you want and one field form one table. Then switch to
SQL view and modify the SELECT statement and the where clause. Test
step by step and see where things go wrong.



Select DISTINCT rtrim(v.vendor_id) as vendor_id
, rtrim(i.invoice_num)as invoice_num
, i.invoice_status
, i.invoice_date
, i.period
, i.inv_amt
, v.vendor_name
, i.image_id as barcode
, ch.check_num
, ch.check_date
, 'Office: ' + a.offc + '; Dept: ' + a.dept + '; Acct:' +
rtrim(c.acct_code) + ' - ' + c.acct_desc As description
, i.tran_uno
, i.session
, ch.check_status

From ((((cmsopen.dbo.apt_invoice i
inner join cmsopen.dbo.apm_vendor v
ON i.vendor_uno = v.vendor_uno)
left join cmsopen.dbo.apt_invoice_amt ia
ON i.tran_uno = ia.inv_tran_uno)
left join cmsopen.dbo.apt_check ch
ON ia.source_tran_uno = ch.tran_uno)
inner join cmsopen.dbo.act_tran_je a
ON i.tran_uno = a.tran_uno)
inner join cmsopen.dbo.glm_chart c
ON a.acct_uno = c.acct_uno
Where 1=1 And a.offc='01' And a.tran_type = 'AP' And ia.tran_type='CH'
Order By 1,2 ASC
 
B

Baz

In Access, the "+" concatenation operator always returns null if one of the
operands is null. In SQL Server, by default, if one of the operands is null
it is treated as a zero-length string i.e. by default, the SQL Server "+"
concatenation operator is equivalent to the Access "&" operator, not the
Access "+" operator. However, if the SQL Server database option
CONCAT_NULL_YIELDS_NULL is set to ON, the the SQL Server "+" operator is
equivalent to the Access "+" operator.

Contrary to what John has said, it's OK to do this in Access:

LEFT JOIN dbo_apt_invoice_amt ia ON i.tran_uno = ia.inv_tran_uno AND
ia.tran_type='CH'

except you need to use some parentheses which SQL Server doesn't need:

LEFT JOIN dbo_apt_invoice_amt ia ON (i.tran_uno = ia.inv_tran_uno AND
ia.tran_type='CH')
 
J

John Spencer

Thanks, Baz. I did say "may". I have had it work and have had it fail.
Maybe because of failure to include the additional parentheses at one time
or another.

I appreciate the additional information. And I definitely should have
picked up on the use of the "+" versus "&" concatenation operators.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

Order by 1,2 ASC ?????

(david)

Damian Carrillo said:
I am trying to take a complex query that works via the Query Analyzer
in SQL Server 2005 to work in MS Access 2003. Suffice it to say Access
is not my preferred environment in which to do this work, however given
the circumstances pertaining to licensing and systems policy, its the
only thing availible for this project. The following query works fine
in the Query Analyzer on a Windows 2000 Server box running SQL Server
2005:

Select DISTINCT rtrim(v.vendor_id) as vendor_id, rtrim(i.invoice_num)
as invoice_num,
i.invoice_status, i.invoice_date,i.period, i.inv_amt, v.vendor_name,
i.image_id as 'barcode',ch.check_num, ch.check_date,
'Office: ' + a.offc + '; Dept: ' + a.dept + '; Acct:' +
rtrim(c.acct_code) + ' - ' + c.acct_desc As description,
i.tran_uno,
i.session, ch.check_status
inner join cmsopen.dbo.apm_vendor v ON i.vendor_uno = v.vendor_uno
left join cmsopen.dbo.apt_invoice_amt ia ON i.tran_uno =
ia.inv_tran_uno And ia.tran_type='CH'
left join cmsopen.dbo.apt_check ch ON ia.source_tran_uno = ch.tran_uno
inner join cmsopen.dbo.act_tran_je a ON i.tran_uno = a.tran_uno
inner join cmsopen.dbo.glm_chart c ON a.acct_uno = c.acct_uno
Where 1=1 And a.offc='01' And a.tran_type = 'AP' Order By 1,2 ASC

I set up the same tables in MS Access 2003 as Linked tables. I can run
a huge number of queries but this is the most complex one I've tried,
and I can't seem to make it work, so I am thinking it must be
punctuation or keywords or something from SQL Server 2005 that isn't
common to the Microsoft JET SQL or ANSI SQL. Here's my attempt to
convert the statement, but it always erros out with: "Syntax Error
(missing Operator) in Query Expression"

SELECT DISTINCT rtrim(v.vendor_id) AS VendorID, rtrim(i.invoice_num) AS
invoice_num,
i.invoice_status, i.invoice_date, i.period, i.inv_amt, v.vendor_name,
i.image_id AS BarCode, ch.check_num, ch.check_date,
'Office: ' + a.offc + '; Dept: ' + a.dept + '; Acct:' +
rtrim(c.acct_code) + ' - ' + c.acct_desc AS Description,
i.tran_uno, i.session, ch.check_status
FROM dbo_apt_invoice i
INNER JOIN dbo_apm_vendor v ON i.vendor_uno = v.vendor_uno
LEFT JOIN dbo_apt_invoice_amt ia ON i.tran_uno = ia.inv_tran_uno AND
ia.tran_type='CH'
LEFT JOIN dbo_apt_check ch ON ia.source_tran_uno = ch.tran_uno
INNER JOIN dbo_act_tran_je a ON i.tran_uno = a.tran_uno
INNER JOIN dbo_glm_chart c ON a.acct_uno = c.acct_uno
WHERE 1=1 AND a.offc='01' AND a.tran_type = 'AP' ORDER BY 1,2 ASC

Anyone care to hypothesize as to why this is failing?
Click to expand...
 
J

John Spencer

Order by the first and second column in the select clause. NOT an option
that I use, but it works. I prefer to specify by name the sort columns, but
in some cases using the column number is easier.

I have used this syntax in cases where I was trying to squeeze in a complex
query string into the source for a combobox and didn't want to use a stored
query as the source.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

John and Baz,

I appreciate your insight and thank you so much! Your recommendations are
exactly what I needed to make this work.

The only change I had to make was to use the table names as they appeared in
my original query, since MS Access doesn't seem to like periods in table
names (when linking, Access automatically changed all periods to underscores
in the table names).

Now that I understand the underlying differences in query syntax, I should
have a much easier time with this effort as the project progresses.

Damian Carrillo
 
B

Baz

Hi again, Damian,

I may have missed something in your original explanation, but I'm not clear
as to why you want to create queries in SQL Server and then get them to run
in Access. If you want Access queries, why not create them in Access and
avoid the conversion headaches?

Maybe you want equivalent queries in both environments. If so, in my
opinion it is easier to create queries in Access and convert them to SQL
Server, because SQL Server is so much more forgiving. For instance, as you
have seen, Access needs lots of parentheses that SQL Server doesn't need:
SQL Server will not mind if they are there, but Access certainly minds if
they are not!
 
Top