Union query on calculated column

  • Thread starter Thread starter Wayne-I-M
  • Start date Start date
W

Wayne-I-M

Hi

I have a need to produce reports in Italian and English from a query
(I know you "could" use up to 7 IF's - but trying to see if there is a
better way)

eg.
I have a table - tblTrips
with a field [tripDayOut] (date)

I have created a simple table with the name of the week in English and Italian
tblDayTranslate
DayNumber 1, 2, 3, etc
DayUK Sunday, Monday, Tuesday,etc
DayItaia Domenica, Lunedì, Martedì, etc

I have tried a really simple UNION (which is not working) like this

SELECT Weekday([TripDayOut]) AS txtTransDay,
FROM tblTrips;
UNION SELECT tblDayTranslate.DayUK
FROM tblDayTranslate;

Maybe it's not possible to use a calculated coluumn like
Weekday([TripDayOut])
as the join.

Any ideas on how to link these two unrelated tables so I can get
Weekday([TripDayOut]) = the Italian name of the day

Thanks for any tips you can give.
 
In design view put both tables in the open area above the grid.
Drag [DayNumber] into the Field row of the grid. Drag [DayItaia] into the
Field row of the grid.
Enter WeekDay([TripDayOut]) as Criteria for [DayNumber].
 
Perfect

SELECT tblDayTranslate.DayNumber, tblTrips.TripOutDate,
tblDayTranslate.DayItalia, tblDayTranslate.DayUK
FROM tblDayTranslate, tblTrips
WHERE (((tblDayTranslate.DayNumber)=Weekday([TripOutDate])));


thanks for that

--
Wayne
Manchester, England.



KARL DEWEY said:
In design view put both tables in the open area above the grid.
Drag [DayNumber] into the Field row of the grid. Drag [DayItaia] into the
Field row of the grid.
Enter WeekDay([TripDayOut]) as Criteria for [DayNumber].

--
Build a little, test a little.


Wayne-I-M said:
Hi

I have a need to produce reports in Italian and English from a query
(I know you "could" use up to 7 IF's - but trying to see if there is a
better way)

eg.
I have a table - tblTrips
with a field [tripDayOut] (date)

I have created a simple table with the name of the week in English and Italian
tblDayTranslate
DayNumber 1, 2, 3, etc
DayUK Sunday, Monday, Tuesday,etc
DayItaia Domenica, Lunedì, Martedì, etc

I have tried a really simple UNION (which is not working) like this

SELECT Weekday([TripDayOut]) AS txtTransDay,
FROM tblTrips;
UNION SELECT tblDayTranslate.DayUK
FROM tblDayTranslate;

Maybe it's not possible to use a calculated coluumn like
Weekday([TripDayOut])
as the join.

Any ideas on how to link these two unrelated tables so I can get
Weekday([TripDayOut]) = the Italian name of the day

Thanks for any tips you can give.
Click to expand...
Click to expand...
 
Back
Top