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

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

Damian Carrillo wrote:
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
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?

.

Relevant Pages

  • Re: "EXEC" in SQL Server 2000 Views
    ... Kalen Delaney ... > INNER JOIN b ... > Could not execute query against OLE DB provider 'MSDASQL'. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Ac
    ... John and Baz, ... Now that I understand the underlying differences in query syntax, ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • Re: update query: still having problems
    ... I will have to adapt the other query. ... So I will need the inner join. ... UPDATE Department INNER JOIN Sheet1 ON Department.employeeID = ... If this is what you want, fine, else, add a WHERE clause to ...
    (microsoft.public.access.queries)
  • Re: Sum based on date range
    ... FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON ... And I am not sure why the Where clause has three date references in it when I am trying to get today as one date and the date 6 months ago as the other date. ... You can then try to switch back to design view or try to run the query. ... I need to be able to query by EMP_ID total number SEVERITY over the last ...
    (microsoft.public.access.queries)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... if the where clause is on an indexed column I'd ... be *hugely* surprised if the inner join were fully evaluated by SQL ... select distinct c.* from customers c inner join orders o ... The second query has less reads than the first query. ...
    (microsoft.public.dotnet.languages.csharp)