Query Field Name based upon Text Box value (3 Viewers)

[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.