SQL problem...

  • Thread starter Thread starter Raphaël Désalbres
  • Start date Start date
R

Raphaël Désalbres

Hello,

The following query works fine on SQL Server...

SELECT TOP 100 PERCENT D.IDDiary as 'N',D.[Date] as 'Date',Debt.[Name] as
'Debt Account', Credit.[Name] as 'Credit Account', D.Historic as
'Historic',D.Value as 'Value'
FROM Diary D
INNER JOIN Accounts Debt
ON Debt.IDAccounts=D.IDAccountDeb
INNER JOIN Accounts Credit
ON Credit.IDAccounts=D.IDAccountCred
INNER JOIN AccountTypes ADebt
ON ADebt.IDAccountTypes=Debt.IDAccountTypes
INNER JOIN AccountTypes ACred
ON ACred.IDAccountTypes=ACred.IDAccountTypes
GROUP BY D.IDDiary,Debt.[Name],Credit.[Name], D.Historic,
D.[Date],D.[Value]
ORDER BY D.IDDiary

But in Access it doesn't, as Access seems not to accept table aliases...

How can I do to solve this problem?

Any help would be appreciated,

Thanks,

Raphaël.
 
Access accepts table aliases. Your problem come from the fact that you have
not grouped your INNER JOIN(s) with parenthesis; this is mandatory with
Access. Use the Query Designer of Access to learn how to write your
multiples inner join(s).

Also, I don't remember if Access accepts single quote as string delimiters
instead of double quotes.

S. L.
 
Raphaël Désalbres said:
Hello,

The following query works fine on SQL Server...
Click to expand...
But in Access it doesn't, as Access seems not to accept table
Click to expand...
aliases...

Table aliases work normally in Access.

How can I do to solve this problem?

Any help would be appreciated,

Thanks,

Raphaël.
Click to expand...

Query:

SELECT TOP 100 PERCENT
D.IDDiary as 'N'
,D.[Date] as 'Date'
,Debt.[Name] as 'Debt Account'
,Credit.[Name] as 'Credit Account'
,D.Historic as 'Historic'
,D.Value as 'Value'
FROM Diary D
INNER JOIN
Accounts Debt
ON Debt.IDAccounts = D.IDAccountDeb
INNER JOIN
Accounts Credit
ON Credit.IDAccounts = D.IDAccountCred
INNER JOIN
AccountTypes ADebt
ON ADebt.IDAccountTypes = Debt.IDAccountTypes
INNER JOIN
AccountTypes ACred
ON ACred.IDAccountTypes = ACred.IDAccountTypes
GROUP BY D.IDDiary
,Debt.[Name]
,Credit.[Name]
,D.Historic
,D.[Date]
,D.[Value]
ORDER BY D.IDDiary

First: Tables and columns with spaces in their names require the names
to be enclosed in [].

Note: Using spaces in table and column and query names is *not*
encouraged. In fact, it's very strongly discouraged.

Second: AccountTypes is aliased as ADebt, and at the same time there
is a table named [Accounts Debt]. It would seem more appropriate to
alias [Accounts Debt] as ADebt, and AccountTypes as AType (and is far
less confusing).

Third: Some tables are aliased, others aren't. This is very
confusing.

Fourth: There are Joins done with an alias (Debt, Credit, D), and
those aliases weren't declared anywhere.

Fifth: The alias naming convention is all over the place. D, Debit,
Credit, ACred, etc. This is confusing. Firm up the naming
convention, use similar Alias-naming structures.

Sixth: There are JOINs being done like: Credit.IDAccounts =
D.IDAccountCred. Columns IDAccounts and IDAccountCred (and IDAccounts
and IDAccountDeb) are being compared for a JOIN, and this means they
contain identical data. Yet, the columns have different names. This
is very confusing. The same attribute should retain the same column
name everywhere it is found throughout the database (and in reality,
this is going to be PKs distributed as FKs only, otherwise, there is
no need to repeat other attributes, that would be a violation of 1NF).

Seventh: AccountTypes is being JOINed into this twice, and the second
time, it is doing so by referencing itself. The second instance of
the table doesn't look necessary for this query, and the self
reference is probably throwing a wrench into things.

Eighth: TOP 100 PERCENT just selects everything . . . so why use it?

Redo As:

SELECT TOP 100 PERCENT
D.IDDiary as 'N'
,D.[Date] as 'Date'
,Debt.[Name] as 'Debt Account'
,Credit.[Name] as 'Credit Account'
,D.Historic as 'Historic'
,D.Value as 'Value'
FROM (((Diary AS D
INNER JOIN
[Accounts Debt] AS ADebt
ON ADebt.IDAccounts = D.IDAccountDeb)
INNER JOIN
[Accounts Credit] AS ACred
ON ACred.IDAccounts = D.IDAccountCred)
INNER JOIN
AccountTypes AS AType
ON AType.IDAccountTypes = ADebt.IDAccountTypes)
GROUP BY D.IDDiary
,Debt.[Name]
,Credit.[Name]
,D.Historic
,D.[Date]
,D.[Value]
ORDER BY D.IDDiary


The above will save without errors, but without the full DDL of the
Tables and some sample data, I have no idea whether it will run
correctly.
 
Access does accept aliases, however they should not be quoted. And if they have
"Special" characters such as spaces the aliases need to be bracketed. As well as
reserved words (date, name, and value) need to be bracketed also. In addition
giving a field the same alias as the field name will often cause syntax errors.
In addition, Access requires parenthesis in joins of multiple tables. And why
do you even bother with TOP 100 Percent since that returns the same records as
if it were not there.

That said, I think the following UNTESTED SQL should work. I've added the
optional AS when aliasing the tables. It is not needed for table aliases, but
it makes it clearer to me (not to the software) that the table is being aliased.


SELECT
D.IDDiary as N,
D.[Date],
Debt.[Name] as [Debt Account],
Credit.[Name] as [Credit Account],
D.Historic,
D.[Value]

FROM (((Diary D
INNER JOIN Accounts as Debt
ON Debt.IDAccounts=D.IDAccountDeb)
INNER JOIN Accounts as Credit
ON Credit.IDAccounts=D.IDAccountCred)
INNER JOIN AccountTypes as ADebt
ON ADebt.IDAccountTypes=Debt.IDAccountTypes)
INNER JOIN AccountTypes as ACred
ON ACred.IDAccountTypes=ACred.IDAccountTypes

GROUP BY D.IDDiary,Debt.[Name],Credit.[Name], D.Historic,
D.[Date],D.[Value]
ORDER BY D.IDDiary
 
Back
Top