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

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


"John Spencer" wrote:

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: Why has my query become read-only?
    ... Thanks, John. ... In terms of my old query and form, ... FROM tblAcquisitions INNER JOIN (((tblGenPubDetails INNER JOIN tblMarketing ... Instead, consider using Combo Boxes for lookup tables, and Subforms for "many" ...
    (microsoft.public.access.queries)
  • Re: Nested Full Text Queries
    ... You're welcome, John, ... your query should be re-written as follows: ... FROM tblarticles WHERE CONTAINS (, ... FROM authors as FT_TBL INNER JOIN ...
    (microsoft.public.sqlserver.fulltext)
  • SQL Server updates slower than MS Access?
    ... We're having trouble with a new SQL Server installation upgraded from MS ... A very large update query affecting 750,000 records with an inner join takes ... few seconds, but as soon as it hits about 400k records, SQL Server hits the ...
    (microsoft.public.sqlserver.programming)
  • SQL Server slower than MS Access
    ... We're having trouble with a new SQL Server installation upgraded from MS ... A very large update query affecting 750,000 records with an inner join takes ... few seconds, but as soon as it hits about 400k records, SQL Server hits the ...
    (microsoft.public.sqlserver)