Dynamcially add a field to a query?

[DubboPete]

Hi Collective wisdom,

I want to know if it's possible to dynamically choose a field to base
a query around?

Scenario: 8 consulting rooms, and my table has Booking_Date, RM01,
RM02 thru RM08... A consultant's name goes in the RMxx field on any
particular date, so 17 May 12, RM07 is Dr Ocean; 17 May 12 RM08 is
Prof Lake, etc.

It's great at displaying on a daily basis what rooms are occupied, but
I am trying to 'flip' the data and query what future dates are
occupied for any one room, should anyone want to book for more than
one day/session...

I know it's easy with eight queries (one for each room) and the sql
looks like this:
SELECT TblCurrentYear.BOOKING_DATE, TblCurrentYear.RM03
FROM TblCurrentYear
WHERE
(((TblCurrentYear.BOOKING_DATE)>=Date())
AND
((TblCurrentYear.RM03) Is Not Null));

I'd like to dynamically change the Rmxx instance to whatever is chosen
in a Combo box.

Is it possible, or am I going about it the wrong way?

TIA

Pete
the 17th Century goat-herder

 

[John W. Vinson]

Hi Collective wisdom,

I want to know if it's possible to dynamically choose a field to base
a query around?

Scenario: 8 consulting rooms, and my table has Booking_Date, RM01,
RM02 thru RM08... A consultant's name goes in the RMxx field on any
particular date, so 17 May 12, RM07 is Dr Ocean; 17 May 12 RM08 is
Prof Lake, etc.

Then your table design is WRONG.

You have a classic many to many relationship between Consultants and Rooms.
The correct structure would have three tables: a table of Consultants (with
name, contact information, etc.), a table of Rooms (with eight rows, at least
until the building is remodeled); and a RoomInuse table with a field for the
ConsultantID, consultation date, and room number. You would add a new RECORD
(not a new field value) to the table when a room is specified.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[JHB]

Hi Collective wisdom,

I want to know if it's possible to dynamically choose a field to base
a query around?

Scenario: 8 consulting rooms, and my table has Booking_Date, RM01,
RM02 thru RM08... A consultant's name goes in the RMxx field on any
particular date, so 17 May 12, RM07 is Dr Ocean; 17 May 12 RM08 is
Prof Lake, etc.

It's great at displaying on a daily basis what rooms are occupied, but
I am trying to 'flip' the data and query what future dates are
occupied for any one room, should anyone want to book for more than
one day/session...

I know it's easy with eight queries (one for each room) and the sql
looks like this:
SELECT TblCurrentYear.BOOKING_DATE, TblCurrentYear.RM03
FROM TblCurrentYear
WHERE
(((TblCurrentYear.BOOKING_DATE)>=Date())
AND
((TblCurrentYear.RM03) Is Not Null));

I'd like to dynamically change the Rmxx instance to whatever is chosen
in a Combo box.

Is it possible, or am I going about it the wrong way?

TIA

Pete
the 17th Century goat-herder

Hello Pete (the goat-herder )

Yes it is possible.



Create a form, set the properties as "Continuous forms".

Create 1 combo box in then forms header and call it "ChoosenRoom".

Place the following code in the combo box afterupdate event.



Private Sub ChoosenRoom_AfterUpdate()

Me.RecordSource = "SELECT BOOKING_DATE, " & Me.ChoosenRoom & " AS Room " _

& "FROM TblCurrentYear " _

& "WHERE BOOKING_DATE>=Date() AND " & Me.ChoosenRoom & " Is Not Null;"

Me.BOOKING_DATE.ControlSource = "Booking_date"

Me.Room.ControlSource = "Room"

Me.Requery

End Sub



Create 2 text fields in the detail section of the form:

Call one of them "BOOKING_DATE" and the other "Room"



Place the following code in the forms open event.



Private Sub Form_Open(Cancel As Integer)

Dim dbs As Database, fld



Set dbs = CurrentDb

Me.ChoosenRoom.RowSource = ""

For Each fld In dbs.TableDefs("TblCurrentYear").Fields

If InStr(1, fld.Name, "RM") Then

Me.ChoosenRoom.RowSource = Me.ChoosenRoom.RowSource & fld.Name & ";"

End If

Next

End Sub



The table setup looks for me, as it first was created in a spreadsheet and afterwards imported to MS-Access without changing.

The way the data are stored in the database is very bad and inappropriate.



Regards

Jørn