Date Difference In Query

[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

 

[John Spencer]

The message means that you need to specify the table name as well as the
field name in your expression. Since apparently there is a field named
Departure in both the tables you are using in the query.

[DEPARTURE]-[ARRIVAL] AS DAYS
should be something like
[RESPEL ALL CHARGES].[DEPARTURE]- [RESPEL ALL CHARGES].[ARRIVAL] AS DAYS

Or perhaps you want Reservations
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;


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ange Kappas]

Thanks John Worked Like A Charm !!!!

Ange

The message means that you need to specify the table name as well as the
field name in your expression. Since apparently there is a field named
Departure in both the tables you are using in the query.

[DEPARTURE]-[ARRIVAL] AS DAYS
should be something like
[RESPEL ALL CHARGES].[DEPARTURE]- [RESPEL ALL CHARGES].[ARRIVAL] AS DAYS

Or perhaps you want Reservations
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;


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

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