Date Difference In Query

  • Thread starter Thread starter Ange Kappas
  • Start date Start date
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
 
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
..
 
Thanks John Worked Like A Charm !!!!

Ange


John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top