not sure where to start

S

sacredarms

I have a table with fields city and then sinlge room, doulbe room or suite.
What I am trying to accomplish is having the user input town name and then
choose only one of the other fields and return only the room that was entered
in the parameter. Another words if double room is picked I do not want to see
the query return the other 2 fields (single room or suite) although they are
part of the same record.

Any help appreciated. Hope this makes sense to someone.

Joe
 
J

John Spencer (MVP)

First bit of advice would be to restructure your table, so you had

TableName: YourTable
CityField
RoomTypeField
RoomFieldThatStoresYourCurrentInformation

Then the query would be really simple

SELECT CITY, RoomFieldThatStoresYourCurrentInformation
FROM YourTable
WHERE City = "Chicago" and RoomTypeField = "Single"

If you cannot restructure your table, then you need to build the query in VBA or
use something like

SELECT City,
Choose(Forms!FormName!OptionControlRoomType,[SingleRoom],[DoubleRoom],[Suite])
as WhatEver
FROM YourTable
Where City = [Forms]![FormName]![cityInput]

Since you didn't tell us how the user is going to input town name and field
choice, I arbitrarily assumed you were doing so through a form that had a
control for inputting the City and an Option control for specifying the field.
 
S

sacredarms

Thanks for the response. I restructured the tabe and it did make it easier.
Thanks for the help.

Joe

John Spencer (MVP) said:
First bit of advice would be to restructure your table, so you had

TableName: YourTable
CityField
RoomTypeField
RoomFieldThatStoresYourCurrentInformation

Then the query would be really simple

SELECT CITY, RoomFieldThatStoresYourCurrentInformation
FROM YourTable
WHERE City = "Chicago" and RoomTypeField = "Single"

If you cannot restructure your table, then you need to build the query in VBA or
use something like

SELECT City,
Choose(Forms!FormName!OptionControlRoomType,[SingleRoom],[DoubleRoom],[Suite])
as WhatEver
FROM YourTable
Where City = [Forms]![FormName]![cityInput]

Since you didn't tell us how the user is going to input town name and field
choice, I arbitrarily assumed you were doing so through a form that had a
control for inputting the City and an Option control for specifying the field.
I have a table with fields city and then sinlge room, doulbe room or suite.
What I am trying to accomplish is having the user input town name and then
choose only one of the other fields and return only the room that was entered
in the parameter. Another words if double room is picked I do not want to see
the query return the other 2 fields (single room or suite) although they are
part of the same record.

Any help appreciated. Hope this makes sense to someone.

Joe
Click to expand...
Click to expand...
 
Top