Re: SQL Server not recognizing aliased table

Tech-Archive recommends: Fix windows errors by optimizing your registry

Gary,

I liked your first recommendation.

This particular application has to be able to run against either Access or
SQL Server backends. So all my queries have to be Access queries against
linked tables. I'll give your first suggestion a try on Monday.

Thanks

"Gary Walter" <gary@xxxxxxxxxxx> wrote in message
news:eVylvaZZIHA.4712@xxxxxxxxxxxxxxxxxxxxxxx
Also (meaning no offense if this is obvious)...

Did you try creating a View(s) in Enterprise Manager
that does the work in SQL Server?

or stored procedure?

I forgot to mention that in almost all cases
we fill these scratch tables from Views or Stored Procs...

If you want to post the query(s) though, I will try to sort
through it (them) and see if these old eyes can spot something.

good luck,

gary

"Gary Walter" <gary@xxxxxxxxxxx>
"Dale Fye" wrote:
I've recently upsized an Access b.e. to SQL Server 2000. Most of it is
running great, with very few application modifications. However, I am
having trouble with one report. The query that this report is based on
is based on two subqueries (both of which run fine from within Access).
However, when I try to run the reports query, I get an error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]
The correlation name 'tbl_Nodes_1' is specified multiple times in a FROM
clause. (#1011)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
dbo. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
'MS3'. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
the keyword 'NOT'. (#156)

Regarding the first line, I have two copies of the same table, one
aliased
as tbl_Nodes_1 in one of the queries. But did not anticipate that this
would be a problem. I can post the queries, but they are rather long.
If anyone can provide some advise, I would greatly appreciate it.

Hi Dale,

I am not an expert, but I believe I fit category "anyone."

Almost all of our mdb's hit the SQL Server for reports.

In almost all cases, we use a "scratch" table(s) for reports
that we empty, then fill based on user-supplied parameters
via a report query.

It always seemed to me that with the complex reports
like we produce (subreports, group headers/footers,
running sums, percentage calcs, etc.)
that you have to "help" that "internal report query"
-- the behind-the-scenes mechinations that can
sometimes give rise to "Multi-level group not allowed"
for instance.

Access reports are so powerful (I love them and have
not moved away from Access for that very reason),
but sometimes they need some simplification help.

I don't know if that is your problem, but if I had
queries that "run fine within Access," I would try
developing the report scratch table(s) as one
strategy.

good luck,

gary





.

Relevant Pages

  • RE: Alternative program calls
    ... Yes, exactly, I want to pick a table and close any queries and reports, etc. ... And, in this particular situation I am within a macro, ... or is it an issule of permissions at the SQL server or file ...
    (microsoft.public.access.modulesdaovba)
  • Re: Counting, summing, matching, summing again...
    ... Create Views, Stored Procs, and UDF's on your Sql server to handle ALL of ... Then use Access ADO, DAO, PassThru queries, or ... > I build reports that show data such as how many people from a census table ... I learned to do this all with queries ...
    (microsoft.public.access.forms)
  • Re: Access DB Upgrade
    ... I have an existing Access DB that consists of tables, forms, queries and reports. ... I need to get it online and into SQL server, what is the quickest and best way to do this? ...
    (microsoft.public.dotnet.languages.vb)
  • Re: A2007 ADPs
    ... Passthrough queries can be good for complex reports but excerpt for the ... I admit I had forgotten about this feature, not having done much MDB work ... When the schema of the database is not too complexe, ...
    (microsoft.public.access.adp.sqlserver)
  • Missing Statistics - sysindexes.rows = 0 for ClusteredIndex on 5 million row table.
    ... All queries are operating - just very slowly. ... We cannot get SQL Server ... but rows still reports 0. ... DBCC CHECKDB & DBCC CHECKCATALOG report no problems. ...
    (microsoft.public.sqlserver.server)