Problem with Alias in WHERE clause

[skyrat]

In an Accessdatabase there are roughly the following tables :




Table Pers
PersID Autonumber
FirstName VarChar(15)
LastName VarChar(40)


Table Book
BookID Autonumber
PersID LongInteger
BookDate Date
BookActivity VarChar(40)
NumberOfArt1 integer
Art1Price
NumberOfArt2 integer
Art2Price
.
.
.
FirstPay
SecondPay


A customer can book for an activity andrent some articles.
Now I want to know at 14 days afterbooking if/what has not (completely) been payed.


SELECTPers.FirstName,Pers.LastName,Book.BookDate,
Book.NumberOfArt1,Book.Art1Price,Book.NumberOfArt1 * Book.Art1Price AS Art1Total,
Book.NumberOfArt2,Book.Art2Price,Book.NumberOfArt2 * Book.Art2Price AS Art2Total,
(etc.)
Art1Total + Art2Total + … AS Total,
Book.FirstPay,BookSecondPay,Book.FirstPay+ Book.SecondPay AS TotalPayed,
Total – TotalPayed AS Remaining
FROM Book INNER JOIN Pers ONPers.PersID = Book.PersID
WHERE (DateDiff(“d”,BookDate,Date()>= 14) AND (Remaining > 0)
ORDER BY BookDate


This Select keeps asking me to INPUT Remaining !! If I input some number the column Remaing shows thatnumber. As Access is very unclear about using (), [] or “” Itried them all around Remaining , but nothing seems to work.
Please help me !!

[ArviLaanemets]

This must work (you can't refer to calculated fields in WHERE, GROUP, ORDER etc.)

SELECT
    p.FirstName,
    p.LastName,
    b.BookDate,
    b.NumberOfArt1,
    b.Art1Price,
    b.NumberOfArt1 * b.Art1Price AS Art1Total,
    b..NumberOfArt2,
    b.Art2Price,
    b.NumberOfArt2 * b.Art2Price AS Art2Total,
    ...
    b.NumberOfArt1 * b.Art1Price+b.NumberOfArt2 * b.Art2Price + … AS Total,
    b.FirstPay,
    b.SecondPay,
    ...
    b.FirstPay+ b.SecondPay+... AS TotalPayed,
    (b.NumberOfArt1 * b.Art1Price+b.NumberOfArt2 * b.Art2Price + …) - (b.FirstPay+ b.SecondPay+...) AS Remaining
FROM Book b INNER JOIN Pers p ON p.PersID = b.PersID
WHERE (DateDiff(“d”,b.BookDate,Date()>= 14) AND ((b.NumberOfArt1 * b.Art1Price+b.NumberOfArt2 * b.Art2Price + …) - (b.FirstPay+ b.SecondPay+...) > 0)
ORDER BY b.BookDate

Your main problem is your database structure is wrong. Had you have a 3rd table like BookArt: BookArtID, BookID, NumberOfArt, ArtPrice, you hadn't any issues.

[skyrat]

Thanks for helping me ! I tried that trick already, but in the real database that would make the Where clause much too long. In the meantime I've been Googling for "Alias in Where Clause" and found that aliases are not alowed there ! As Access did NOT give an errormessage (!!!) I didn't know that. It looks as if I have to try and fix the problem by putting the whole query into a subquery, so that's what I'm going to try now !

YEEEHA, it works. To satisfy other people with the same problem, the solution is :

SELECT * FROM
( ... the whole Query WITHOUT the WHERE clause ..) AS InnerTable
WHERE .... the old WHERE clause.

Merry Christmas everybody !!