A
Ange Kappas
Hi,
I have a query in the Design View, where I have grouped all relevant
fields like RESERVENAME ,ROOMNO, ARRIVAL and DEPARTURE.
I want to add a calculated field called DAYS which calculates the DEPARTURE
minus the ARRIVAL.
I put in a calculated field like:
DAYS:[DEPARTURE]-[ARRIVAL] but returns an error message saying 'The
specified field '[DEPARTURE}' could refer to more than one table listed in
the FROM clause of your SQL statement'
which is:
SELECT [RESPEL ALL CHARGES].DATE, [RESPEL ALL CHARGES].ROOMNO, [RESPEL ALL
CHARGES].ROOMTYPE, RESERVATIONS.PERSONS, [RESPEL ALL CHARGES].BASIS, [RESPEL
ALL CHARGES].RESNO, [RESPEL ALL CHARGES].RESNAME, [RESPEL ALL
CHARGES].COMPANY, [RESPEL ALL CHARGES].ARRIVAL, [DEPARTURE]-[ARRIVAL] AS
DAYS, [RESPEL ALL CHARGES].DEPARTURE, Sum([RESPEL ALL CHARGES].[DAILY
CHARGE]) AS [SumOfDAILY CHARGE]
FROM [RESPEL ALL CHARGES] INNER JOIN RESERVATIONS ON [RESPEL ALL
CHARGES].RESNO = RESERVATIONS.RESNO
GROUP BY [RESPEL ALL CHARGES].DATE, [RESPEL ALL CHARGES].ROOMNO, [RESPEL ALL
CHARGES].ROOMTYPE, RESERVATIONS.PERSONS, [RESPEL ALL CHARGES].BASIS, [RESPEL
ALL CHARGES].RESNO, [RESPEL ALL CHARGES].RESNAME, [RESPEL ALL
CHARGES].COMPANY, [RESPEL ALL CHARGES].ARRIVAL, [RESPEL ALL
CHARGES].DEPARTURE;
I don't know what I'm doing wrong, mind you I'm not so good in SQL that is
why I prefer the simple design view of the query.
Thanks
I have a query in the Design View, where I have grouped all relevant
fields like RESERVENAME ,ROOMNO, ARRIVAL and DEPARTURE.
I want to add a calculated field called DAYS which calculates the DEPARTURE
minus the ARRIVAL.
I put in a calculated field like:
DAYS:[DEPARTURE]-[ARRIVAL] but returns an error message saying 'The
specified field '[DEPARTURE}' could refer to more than one table listed in
the FROM clause of your SQL statement'
which is:
SELECT [RESPEL ALL CHARGES].DATE, [RESPEL ALL CHARGES].ROOMNO, [RESPEL ALL
CHARGES].ROOMTYPE, RESERVATIONS.PERSONS, [RESPEL ALL CHARGES].BASIS, [RESPEL
ALL CHARGES].RESNO, [RESPEL ALL CHARGES].RESNAME, [RESPEL ALL
CHARGES].COMPANY, [RESPEL ALL CHARGES].ARRIVAL, [DEPARTURE]-[ARRIVAL] AS
DAYS, [RESPEL ALL CHARGES].DEPARTURE, Sum([RESPEL ALL CHARGES].[DAILY
CHARGE]) AS [SumOfDAILY CHARGE]
FROM [RESPEL ALL CHARGES] INNER JOIN RESERVATIONS ON [RESPEL ALL
CHARGES].RESNO = RESERVATIONS.RESNO
GROUP BY [RESPEL ALL CHARGES].DATE, [RESPEL ALL CHARGES].ROOMNO, [RESPEL ALL
CHARGES].ROOMTYPE, RESERVATIONS.PERSONS, [RESPEL ALL CHARGES].BASIS, [RESPEL
ALL CHARGES].RESNO, [RESPEL ALL CHARGES].RESNAME, [RESPEL ALL
CHARGES].COMPANY, [RESPEL ALL CHARGES].ARRIVAL, [RESPEL ALL
CHARGES].DEPARTURE;
I don't know what I'm doing wrong, mind you I'm not so good in SQL that is
why I prefer the simple design view of the query.
Thanks