Query Field Name based upon Text Box value

[Malcolm17]

Hello,

I have created a grid using a table Fields are DinnerTable, 18:00, 18:15, 18:30, 18:45 etc

On a form I have a textbox named txtGrid1 and a button

I'm looking to run a query where if the textbox value was 18:30 then my query fields would be 'DinnerTable' and '18:30' - however I cannot work out how to make the field names variable (if txtGrid1 = 18:30 then field name = 18:30, OR if txtGrid1 = 18:00 then field name = 18:00 etc)

Is this possible to make query field names variable please?

Thank you,

Malcolm

 

[Gasman]

Your DB is incorrect.
You would have one field that holds time and day. Then just query that data.

Your way, you will need to build the sql string in VBA with concatenation and set that form's record source to that string.
Always debug.print your sql before even trying to use it.

 

[GPGeorge]

Hello,

I have created a grid using a table Fields are DinnerTable, 18:00, 18:15, 18:30, 18:45 etc

On a form I have a textbox named txtGrid1 and a button

I'm looking to run a query where if the textbox value was 18:30 then my query fields would be 'DinnerTable' and '18:30' - however I cannot work out how to make the field names variable (if txtGrid1 = 18:30 then field name = 18:30, OR if txtGrid1 = 18:00 then field name = 18:00 etc)

Is this possible to make query field names variable please?

Thank you,

Malcolm

What you describe as a table with multiple fields that each contain values of the same thing, "dinnertimes" (18:00, 18:15, 18:30, 18:45, etc.), is commonly referred to as "repeating columns" or "spreadsheet design". It's actually a fairly common mistake among newcomers to Access who have previous experience with Excel.

Here is a series of blog posts describing the problem and demonstrating how to correct it. http://www.rogersaccessblog.com/category/problems-with-repeated-columns/

 

[mike60smart]

Hello,

I have created a grid using a table Fields are DinnerTable, 18:00, 18:15, 18:30, 18:45 etc

On a form I have a textbox named txtGrid1 and a button

I'm looking to run a query where if the textbox value was 18:30 then my query fields would be 'DinnerTable' and '18:30' - however I cannot work out how to make the field names variable (if txtGrid1 = 18:30 then field name = 18:30, OR if txtGrid1 = 18:00 then field name = 18:00 etc)

Is this possible to make query field names variable please?

Thank you,

Malcolm

Can you upload your database so we can see your table structures/ relationships?

 

[The_Doc_Man]

I'm going to presume that this part of your question, "DinnerTable, 18:00, 18:15, 18:30, 18:45", represents some type of restaurant reservation system? Is that a correct assumption?

Typically, this kind of "scheduling" app uses a "reservation" table that has three primary elements (and could have more). You have "table number" as a field, you have "start time" as a field, and you have EITHER "estimated end time" or "estimated duration" as a field. This lets you decide what table resources are available or occupied at any given time. Before we go down this path, can you verify your intention or did I read something that wasn't really there?

 

[Ken Sheridan]

The attached little demo file illustrates the basics of the sort of reservations database which The_Doc_Man describes. Its model is:

The central functionality of the database is that it detects intersecting date ranges (date/time ranges in your case) to determine when a room ( table in your case) is available for the whole of a specified range. In addition to the auxiliary Calendar table (Calendar_1 in the above is a second instance of the table, not a separate table per se), you'd need a table of all 15 minute time slots.

 

[Malcolm17]

Hi All,

I appreciate that my idea wasn't right, so I have gone back to plan A, however i cannot work out how to show tables that are free between times. In my example attached I can show 10 tables available, but when I add times I have nothing available - attached should show all 10 tables available because the time is before and after all bookings, however nothing.

I'm not sure where I am going wrong, but I'm frustrated because I've been looking at this for so long.

I'm trying to query restaurant tables that are available when they are not booked.

This query will be going in to a combobox as part of my forms.

Thanks for any help you can give me!!

 

[The_Doc_Man]

The key is remember that your query to find an open table must actually be to find (and eliminate) all tables that are NOT open... a sort of reverse logic approach. Your [Bookings] table can be used by looking for these cases to perform elimination. You have a record that contains everything in the bookings table EXCEPT no table number (yet).

You want to take the data in the "preliminary" record to find tables large enough to hold the prospective party and for which a few other conditions are true... remember, this will be a process of elimination.

1. Find all cases where Preliminary [Arrival] is BETWEEN [Booking].[Arrival] AND [Booking].[Departs] - (new booking partially overlaps earlier-arriving booking)
2. Find all cases where Preliminary [Departs] is BETWEEN [Booking].[Arrival] AND [Booking].[Departs] - (new booking partially overlaps later-arriving booking)
3. Find all cases where [Booking].[Arrives] is BETWEEN Preliminary [Arrival] AND Preliminary [Departs]
4. Find all cases where [Booking].[Departs] is BETWEEN Preliminary [Arrival] AND Preliminary [Departs]
(#3 and #4 ) cover the "complete overlap" cases.

Now that you know which tables are booked, pick a table that isn't in the above list.
IF you have one and only one station where this can be managed, then you can make your list of possible tables have a flag field (Yes/No) to help. Remember, I said this would be a reverse logic approach, a "process of elimination"...

You COULD do this with a single SELECT query that involved a sub-query where you would find the tables that are booked and then do a NOT IN type of selection. That is, SELECT all tables that are NOT IN (Select list of reserved tables at the requested time.)

OR you could do a behind-the-scenes sequence to reset the Possible Tables list flag to Yes for all tables, then run four quick updates to set NO to that flag field for the four cases I named. Then any table still set to YES is available at the requested time. IF there is more than one place that can perform a reservation, this is not a good approach because of possible destructive interference when both states try making reservations at once.

 

[mike60smart]

Hi All,

I appreciate that my idea wasn't right, so I have gone back to plan A, however i cannot work out how to show tables that are free between times. In my example attached I can show 10 tables available, but when I add times I have nothing available - attached should show all 10 tables available because the time is before and after all bookings, however nothing.

I'm not sure where I am going wrong, but I'm frustrated because I've been looking at this for so long.

I'm trying to query restaurant tables that are available when they are not booked.

This query will be going in to a combobox as part of my forms.

Thanks for any help you can give me!!

You can generate a query to show tables booked and tables free as shown in the attached screenshot.

You achieve this by having the Relationships as shown in the attached screenshot.

 

[Ken Sheridan]

I'm trying to query restaurant tables that are available when they are not booked.

To identify tables which are available for the whole of any given date/time range you firstly need to identify the intersections of two sets. You can then exclude those results from the available tables/times. This requires firstly an auxiliary calendar table, which is simply a table of all dates over a range, and secondly a table of time-slots, the intersection of which with booked times can then be identified.

I'm attaching an amended version of your database with these two tables added. When defining date/time ranges it's important that these are distinct, i.e the end value of a range should not be the same as the start value of the next range. I have therefore reduced the Depart dates in your bookings table by one minute. The values now match with the end of range values in the time slots table.

The criteria for detecting intersecting ranges are quite simple, they just require that the start of one range is on or earlier than the end of the other range, and conversely that the end of the first range is on or later than the start of the second range. The query for dong this is:

SELECT
    tblTables.TableNo,
    Calendar.calDate,
    TimeSlots.TimeFrom,
    TimeSlots.TimeTo
FROM
    tblTables,
    Calendar,
    TimeSlots
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            tblBookings
        WHERE
            tblBookings.TableNo = tblTables.TableNo
            AND tblBookings.TheDate = Calendar.calDate
            AND tblBookings.Depart >= TimeSlots.TimeFrom
            AND tblBookings.Arrive <= TimeSlots.TimeTo
    )
ORDER BY
    Calendar.calDate,
    tblTables.TableNo,
    TimeSlots.TimeFrom;

This query returns all available tables/time slots for the whole of the period covered by the auxiliary calendar table. In booking systems like yours the query is normally restricted on the calDate column to a range of dates from the current date (returned by the Date() function) to however many days beyond that for which you would anticipate taking bookings for (returned by Date() + n). The query can then be used as the RowSource property for a combo or list box .

I've used time slots of 15 minutes duration, but you can easily change this to longer durations if you wish by deleting the rows with unnecessary start times, and amending the end times of the remaining rows to one minute before the start date of the next slot.

I've also created and enforced the necessary relationships to protect the integrity of the data.