Solved Change report field value depending on table

[mafhobb]

So I have a report which pulls data from a table (tblreservations) via a query.

SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.CleaningComments, tblReservations.Status, tblReservations.PhoneNumber, tblReservations.CheckInContacted, tblReservations.CheckInReady, tblReservations.CheckInTime, tblReservations.ReservationSource FROM tblReservations WHERE (((tblReservations.CheckInDate)=Date()) AND ((tblReservations.Status)="Active"));

The value for the field "Reservation Source" is a number on this table.
There is another table (tblResorigin) which lists the alphanumeric value of "ReservationSource, for example "1 = Expedia, 2 = Booking, 3 = Bookiply,...."
When this report is created, the value that shows up in the field is naturally a number (1, 2, 3,...) but I would like it to be the value in tblResorigin instead; Expedia, or Booking, or...
Can that be done?
thanks
mafhobb

 

[Gasman]

Join the tables and use the description.

I could swear I have seen this topic on another forum?

 

[GaP42]

Yes - something like

SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate,
tblReservations.CleaningComments, tblReservations.Status, tblReservations.PhoneNumber, tblReservations.CheckInContacted, tblReservations.CheckInReady,
tblReservations.CheckInTime, tblReservations.ReservationSource, tblResOrigin.SourceName
INNER JOIN tblReservations.ReservationSource = tblResOrigin.ReservationSource
FROM tblReservations, tblResOrigin
WHERE (((tblReservations.CheckInDate)=Date()) AND ((tblReservations.Status)="Active"));

or Left Join depending on needs.

 

[mafhobb]

I've tried to adapt your sugegstion, but I am runnign into some issues. This is the query code:
SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.CleaningComments, tblReservations.Status, tblReservations.PhoneNumber, tblReservations.CheckInContacted, tblReservations.CheckInReady, tblReservations.CheckInTime, tblReservations.ReservationSource, tblResOrigin.ResOrigin left Join tblReservations.ReservationSource=tblResOrigin.ID FROM tblReservations,tblResOrigin WHERE (((tblReservations.CheckInDate)=Date()) AND ((tblReservations.Status)="Active"));
I tried both an inner join and a left join but the result is the same; I cannot add the filed "ResOrigin" to the report as I get the message "No fields available to be added to the current view"
For reference, the two table names are correct and the field in tblResOrigin that matches tblReservartion.ReservationSource is "ID". The field with the names in tblResOrigin is "ResOrigin"
What am I missing?

 

[ComputerVersteher]

@GaP42:

Some typos and bad format. I used PoorSQL

SELECT tblReservations.ID
    ,tblReservations.PropertyName
    ,tblReservations.GuestName
    ,tblReservations.CheckInDate
    ,tblReservations.CheckOutDate
    ,tblReservations.CleaningComments
    ,tblReservations.STATUS
    ,tblReservations.PhoneNumber
    ,tblReservations.CheckInContacted
    ,tblReservations.CheckInReady
    ,tblReservations.CheckInTime
    ,tblReservations.ReservationSource
    ,tblResOrigin.SourceName
FROM tblReservations
INNER JOIN tblResOrigin
    ON tblReservations.ReservationSource = tblResOrigin.ReservationSource
WHERE (
        ((tblReservations.CheckInDate) = DATE ())
        AND ((tblReservations.STATUS) = "Active")
        );

@mafhobb:

The table with ReservationSources should be named as that (tblReservationSources) and the FK (ForeignKey) tblReservations.ReservationSource should be named tblReservations.ReservationSourceID. Then no further description is needed. Also tblReservations.STATUS should be the FK to a table tblReservationsSTATUS, similar to the Sources.

 

[mafhobb]

This is what did it in the end:

SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.CleaningComments, tblReservations.Status, tblReservations.PhoneNumber, tblReservations.CheckInContacted, tblReservations.CheckInReady, tblReservations.CheckInTime, tblReservations.ReservationSource, tblResOrigin.ResOrigin From tblReservations Inner Join tblResOrigin On tblreservations.ReservationSource=tblResOrigin.ID WHERE (((tblReservations.CheckInDate)=Date()) AND ((tblReservations.Status)="Active"));

I'll take your advice and review some items, and thank you for your help.
Also, thank you for the suggestion about PoorSQL.
mafhobb

 

[ComputerVersteher]

Please post only formated SQL (e.g. format with my link above), as not indendet Code is unreadable (what makes it unreasonable)!