Solved Pull string from table instead of ID (1 Viewer)

[mafhobb]

On a form I have a combobox pulling its values from a table (tblResOrigin). The table has two fields (ID and ResOrigin) and on the combobox the bound column is the first one (ID, a number) but it is not visible and the visible text is the second column (ResOrigin, a string). This is the rowsource for the combobox:

SELECT [tblResOrigin].[ID], [tblResOrigin].[ResOrigin] FROM tblResOrigin;

The value selected here is stored in the table tblReservations, as ReservationSource, along with other info.
I am now running into an issue on a query that fills a listbox because when I try to pull the value of "ReservationsSource" from tblReservations, I naturally get a number, which is what is stored in tblReservations.ReservationSource, but what I need is the string in tblResOrigin that corresponds to that numbe. Can that be done?
This is the query:

SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationDate, tblReservations.Status, tblReservations.ReservationSource
FROM tblReservations, tblResOrigin
WHERE (((tblReservations.GuestName) Like "*" & [forms]![frmPropertyHistory]![txtFindGuest] & "*"))
ORDER BY tblReservations.CheckInDate;

Please, note that there is no relationship between those two tables

 

[The_Doc_Man]

When pulling data from a combo box that has an ID and a text value, you can use combobox.Column(1) to select the text value.

ComboBox.Column property (Access)

Office VBA reference topic

learn.microsoft.com

Remember, the columns are ZERO-based, so the first column is Column(0).

By the way, as a general rule that will save you a potload of typing, use

SELECT ID, ResOrigin FROM tblResOrigin;

because... (a) both fields come from the same (and ONLY) table listed in the FROM clause, and (b) you can omit the brackets when the names do not have spaces in them.

Now, you are doing something that causes you to get what we call a Cartesian JOIN. That clause

FROM tblReservations, tblResOrigin

is going to give you every record in tblReservations combined with every record in tblResOrigin, because SQL is a COMBINATORIAL engine and you didn't constrain how those tables were related. You say there is no relationship involved between the tables, which is going to be REALLY bad for you, long term, as you try to deal with this.

It seems to me that if you are planning to remember the origin of a reservation, there needs to be a place in a reservations record for that info, and thus you could store it when you make the reservation. Otherwise, there is no way to logically do what it appears you wanted to do, which is to align the reservation to a source. Ideally, you have a ResOrigin field in the Reservation table so you can remember the origin ID and use it in a JOIN clause to pull the text equivalent. Further, by using a proper JOIN clause you eliminate the Cartesian JOIN.

One last thing, while talking about design, is the unfortunate choice to use the same name for different fields - tblReservations.ID and tblResOrigin.ID - two ID fields that will be used in proximity to each other, thus opening the door to forgetting which one you were using some late afternoon when post-lunch slump is underway and you make the wrong choice.

 

[mike60smart]

On a form I have a combobox pulling its values from a table (tblResOrigin). The table has two fields (ID and ResOrigin) and on the combobox the bound column is the first one (ID, a number) but it is not visible and the visible text is the second column (ResOrigin, a string). This is the rowsource for the combobox:

SELECT [tblResOrigin].[ID], [tblResOrigin].[ResOrigin] FROM tblResOrigin;

The value selected here is stored in the table tblReservations, as ReservationSource, along with other info.
I am now running into an issue on a query that fills a listbox because when I try to pull the value of "ReservationsSource" from tblReservations, I naturally get a number, which is what is stored in tblReservations.ReservationSource, but what I need is the string in tblResOrigin that corresponds to that numbe. Can that be done?
This is the query:

SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationDate, tblReservations.Status, tblReservations.ReservationSource
FROM tblReservations, tblResOrigin
WHERE (((tblReservations.GuestName) Like "*" & [forms]![frmPropertyHistory]![txtFindGuest] & "*"))
ORDER BY tblReservations.CheckInDate;

Please, note that there is no relationship between those two tables

In your query you should include tblResOrigin and select ResOrigin and remove ReservationSource

 

[mafhobb]

When pulling data from a combo box that has an ID and a text value, you can use combobox.Column(1) to select the text value.

ComboBox.Column property (Access)

Office VBA reference topic

learn.microsoft.com

Remember, the columns are ZERO-based, so the first column is Column(0).

By the way, as a general rule that will save you a potload of typing, use

SELECT ID, ResOrigin FROM tblResOrigin;

because... (a) both fields come from the same (and ONLY) table listed in the FROM clause, and (b) you can omit the brackets when the names do not have spaces in them.

Now, you are doing something that causes you to get what we call a Cartesian JOIN. That clause

FROM tblReservations, tblResOrigin

is going to give you every record in tblReservations combined with every record in tblResOrigin, because SQL is a COMBINATORIAL engine and you didn't constrain how those tables were related. You say there is no relationship involved between the tables, which is going to be REALLY bad for you, long term, as you try to deal with this.

It seems to me that if you are planning to remember the origin of a reservation, there needs to be a place in a reservations record for that info, and thus you could store it when you make the reservation. Otherwise, there is no way to logically do what it appears you wanted to do, which is to align the reservation to a source. Ideally, you have a ResOrigin field in the Reservation table so you can remember the origin ID and use it in a JOIN clause to pull the text equivalent. Further, by using a proper JOIN clause you eliminate the Cartesian JOIN.

One last thing, while talking about design, is the unfortunate choice to use the same name for different fields - tblReservations.ID and tblResOrigin.ID - two ID fields that will be used in proximity to each other, thus opening the door to forgetting which one you were using some late afternoon when post-lunch slump is underway and you make the wrong choice.

I think that I understand much of what you are saying, however perhaps I did not explain the tables well. TblReservations does have a field that contains the reservation source. This is the filed ReservationSource on tblReservations. Unfortunatelly the data saved in it is the ID value of the reservation source instead of the actual name. This was clearly my mistake, However now I need to try to "convert" that ID number into the actual source name and that is when I run into trouble.
I also agree with you on the field name choices. I should do better.

 

[mafhobb]

In your query you should include tblResOrigin and select ResOrigin and remove ReservationSource

Thanks, but I tried that earlier and when I do I get a bunch of results that are not what I am looking for. I believe that this is what The Doc Man was referring to as a "Cartesian JOIN"

 

[mafhobb]

The Doc Man:

It seems to me that if you are planning to remember the origin of a reservation, there needs to be a place in a reservations record for that info, and thus you could store it when you make the reservation. Otherwise, there is no way to logically do what it appears you wanted to do, which is to align the reservation to a source. Ideally, you have a ResOrigin field in the Reservation table so you can remember the origin ID and use it in a JOIN clause to pull the text equivalent. Further, by using a proper JOIN clause you eliminate the Cartesian JOIN.

So is the solution to have three queries, one for tblreservations, one for tblResOrigin and the a joint one to combine the two results?

 

[mafhobb]

The Doc Man:

So is the solution to have three queries, one for tblreservations, one for tblResOrigin and the a joint one to combine the two results?

Nope. I don't seem to be able to make this work either. Same results.

 

[The_Doc_Man]

IF there is a slot in tblReservations for the ID field from tblResOrigin, then a simple JOIN is all you need. If not, you have painted yourself into a corner. Unless you somehow have the possibility that one reservation can come from more than one place. This appears to me to be a data design issue more than anything else.

The simple rule is, if you want to remember something about something described by a table, you need a slot in which to remember it. Just like you have slots in the Reservations table for PropertyName, GuestName, CheckInDate, etc. You want to remember those facts - so you placed fields in which to store them. Well, if you want to remember the Reservation Origin, you need to place a field in which to remember that fact, too. AND once you have that, you can do a simple two-table equi-JOIN that completely resolves the problem.

 

[mafhobb]

I think that I solved it.
I added a relationship between tblreservations and tblresorigin, joining ReservationSource and ResOrigin. This did not seem to create any issue as both fields had matching data. Then, I modified the query to this:

SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationDate, tblReservations.Status, tblResOrigin.ResOrigin
FROM tblResOrigin INNER JOIN tblReservations ON tblResOrigin.ID = tblReservations.ReservationSource
WHERE (((tblReservations.GuestName) Like "*" & [forms]![frmPropertyHistory]![txtFindGuest] & "*"))
ORDER BY tblReservations.CheckInDate;

and that seems to do the trick.
Is there anything that could be problematic with this?

 

[The_Doc_Man]

Offhand, looks like you did the right thing. The JOIN makes sense... if tblReservations.ReservationSource is the ID number from tblResOriginID.

Since I don't know your entire structure, I offer no guesses about whether something else would trip you up, but this is clearly the right approach and should make life easier for you.

Specifically, the query is no longer a Cartesian JOIN so your CPU will have to process only as many records as there are reservations. Before you did this, it would have been as many records as tblReservations.RecordCount x tb.ResOrigin.RecordCount, which obviously is a much larger number.

However, one more possible "gotcha" occurs if you have reservations that don't have a recorded origin. The INNER JOIN would not include them. SO if any reservations appear to be missing, see if the missing records have an empty origin field. You could ALSO change that to a LEFT JOIN but then, if you were missing an origin, the ResOrigin field would be NULL. So the bottom line is that this will work best if the .ReservationSource field is never empty, 0, or blank. (OR if Origin ID 0 links to a Reservation Origin record that returns "(not on record)" for those undefined cases.

 

[Pat Hartman]

Your naming convention is causing some confusion. It saves a lot of brain cells if you simply always use the same name as the PK and the FK unless you can't - which is rare. NEVER use just "ID" as the PK name. This starts you down the road to confusion. For a PK name, use part of the table name with ID as the suffix. The "ID" as a suffix is used when the original field is an autonumber. So instead of ID, the PK name should be ResOriginID. Then ResOriginName would be the text value. Then in the other table, when you see ResOriginID, you KNOW it is the FK and are not confused into thinking you will be seeing a text string. Some people like to use _FK as a suffix so in the related table, the column name could be ResOriginID_FK. This is more precise if the other method confuses you.

For forms, you use a combo to bind ResOriginID to. That allows the text to show. However, for export queries and possibly even reports, you don't want to/can't use a combo and so you need to use a join in the query that joins the child table to the lookup table. You select the ResOriginID from the child table but you select ResOriginName from the lookup table. This solves the export problem and it means your reports use textboxes. They don't use combos which can look awkward.

 

[mafhobb]

Offhand, looks like you did the right thing. The JOIN makes sense... if tblReservations.ReservationSource is the ID number from tblResOriginID.

Since I don't know your entire structure, I offer no guesses about whether something else would trip you up, but this is clearly the right approach and should make life easier for you.

Specifically, the query is no longer a Cartesian JOIN so your CPU will have to process only as many records as there are reservations. Before you did this, it would have been as many records as tblReservations.RecordCount x tb.ResOrigin.RecordCount, which obviously is a much larger number.

However, one more possible "gotcha" occurs if you have reservations that don't have a recorded origin. The INNER JOIN would not include them. SO if any reservations appear to be missing, see if the missing records have an empty origin field. You could ALSO change that to a LEFT JOIN but then, if you were missing an origin, the ResOrigin field would be NULL. So the bottom line is that this will work best if the .ReservationSource field is never empty, 0, or blank. (OR if Origin ID 0 links to a Reservation Origin record that returns "(not on record)" for those undefined cases.

Thanks for the comments.
I make sure that all reservations have the required data by validating the data when entered, so while not perfect, I should be covered.

 

[mafhobb]

Your naming convention is causing some confusion. It saves a lot of brain cells if you simply always use the same name as the PK and the FK unless you can't - which is rare. NEVER use just "ID" as the PK name. This starts you down the road to confusion. For a PK name, use part of the table name with ID as the suffix. The "ID" as a suffix is used when the original field is an autonumber. So instead of ID, the PK name should be ResOriginID. Then ResOriginName would be the text value. Then in the other table, when you see ResOriginID, you KNOW it is the FK and are not confused into thinking you will be seeing a text string. Some people like to use _FK as a suffix so in the related table, the column name could be ResOriginID_FK. This is more precise if the other method confuses you.

For forms, you use a combo to bind ResOriginID to. That allows the text to show. However, for export queries and possibly even reports, you don't want to/can't use a combo and so you need to use a join in the query that joins the child table to the lookup table. You select the ResOriginID from the child table but you select ResOriginName from the lookup table. This solves the export problem and it means your reports use textboxes. They don't use combos which can look awkward.

Thanks for the comments.

 

[Pat Hartman]

You're welcome