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?
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.
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?
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.
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?
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?
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.
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.
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.
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.
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:
SQL:
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.
I'm back at this and I have learned so much. One of you (I'm sorry I can't remember who) very kindly gave me a query which I have added to and I must admit I don't really understand queries in SQL view, however I have added to it as I needed.
I have now added up to 15 joins available for Restaurant Tables, so in my database (which I can't really upload as it is large and has various parts to it) In my table tbl_Net_RestaurantTables I have the Fields TableNo, J1, J2, J3 ... J15. When Table name 'Join 6 & 7' is selected I save 3 records in to my Bookings table; 1 showing Table Name Join 6 & 7, another showing Table Name T6 and another showing Table Name T7 - this was how the query worked out which tables was available at the time.
The problem with this is Table 6&7 can join - the above method deals with this, however Table 5,6 & 7 also join together, so even if the above is booked, it will allow T5,6 & 7 to be double booked.
Could you please help me by adding code to the query please so as well as checking TableNo, but will also check J1, J2, J3 etc to J15 please?
I have tried for the past couple of days and I cannot work out how to do this.
Many thanks,
Malcolm
Code:
PARAMETERS txtResStart DateTime,
txtResEnd DateTime;
SELECT
tbl_Net_RestaurantTables.[Area],
[TableNo],
[Covers],
[J1],
[J2],
[J3],
[J4],
[J5],
[J6],
[J7],
[J8],
[J9],
[J10],
[J11],
[J12],
[J13],
[J14],
[J15]
FROM
tbl_Net_RestaurantTables
WHERE
(tbl_Net_RestaurantTables.TableNo) NOT IN (
SELECT DISTINCT
TableNo
FROM
(
SELECT
tbl_Net_RestaurantTables.TableNo,
tbl_Net_RestaurantBookings.CheckIn,
tbl_Net_RestaurantBookings.CheckOut
FROM
tbl_Net_RestaurantBookings,
tbl_Net_RestaurantTables
WHERE
tbl_Net_RestaurantBookings.TableNo = tbl_Net_RestaurantTables.TableNo
AND (
(
(
([tbl_Net_RestaurantBookings].[CheckIn]) BETWEEN txtResStart AND txtResEnd
)
)
OR (
(
([tbl_Net_RestaurantBookings].[CheckOut]) BETWEEN txtResStart AND txtResEnd
)
)
OR (
(
([tbl_Net_RestaurantBookings].[CheckIn]) < txtResStart
)
AND (
([tbl_Net_RestaurantBookings].[CheckOut]) > txtResEnd
)
)
)
)
)
AND tbl_Net_RestaurantTables.Covers >= txtCovers
ORDER BY
tbl_Net_RestaurantTables.[SortOrder];
I'm back at this and I have learned so much. One of you gave me a query which I have added to and I must admit I don't really understand queries in SQL view, however I have added to it as I needed.
I have now added up to 15 joins available for Restaurant Tables, so in my database (which I can't really upload as it is large and has various parts to it) In my table tbl_Net_RestaurantTables I have the Fields TableNo, J1, J2, J3 ... J15. When Table name 'Join 6 & 7' is selected I save 3 records in to my Bookings table; 1 showing Table Name Join 6 & 7, another showing Table Name T6 and another showing Table Name T7 - this was how the query worked out which tables was available at the time.
The problem with this is Table 6&7 can join - the above method deals with this, however Table 5,6 & 7 also join together, so even if the above is booked, it will allow T5,6 & 7 to be double booked.
Could you please help me by adding code to the query please so as well as checking TableNo, but will also check J1, J2, J3 etc to J15 please?
I have tried for the past couple of days and I cannot work out how to do this.
Many thanks,
Malcolm
Code:
PARAMETERS txtResStart DateTime,
txtResEnd DateTime;
SELECT
tbl_Net_RestaurantTables.[Area],
[TableNo],
[Covers],
[J1],
[J2],
[J3],
[J4],
[J5],
[J6],
[J7],
[J8],
[J9],
[J10],
[J11],
[J12],
[J13],
[J14],
[J15]
FROM
tbl_Net_RestaurantTables
WHERE
(tbl_Net_RestaurantTables.TableNo) NOT IN (
SELECT DISTINCT
TableNo
FROM
(
SELECT
tbl_Net_RestaurantTables.TableNo,
tbl_Net_RestaurantBookings.CheckIn,
tbl_Net_RestaurantBookings.CheckOut
FROM
tbl_Net_RestaurantBookings,
tbl_Net_RestaurantTables
WHERE
tbl_Net_RestaurantBookings.TableNo = tbl_Net_RestaurantTables.TableNo
AND (
(
(
([tbl_Net_RestaurantBookings].[CheckIn]) BETWEEN txtResStart AND txtResEnd
)
)
OR (
(
([tbl_Net_RestaurantBookings].[CheckOut]) BETWEEN txtResStart AND txtResEnd
)
)
OR (
(
([tbl_Net_RestaurantBookings].[CheckIn]) < txtResStart
)
AND (
([tbl_Net_RestaurantBookings].[CheckOut]) > txtResEnd
)
)
)
)
)
AND tbl_Net_RestaurantTables.Covers >= txtCovers
ORDER BY
tbl_Net_RestaurantTables.[SortOrder];
Hi Malcolm
I am not following your table structures.
In tbl_Net_RestaurantTables you have :-
1. set the Autonumber on the field name "TableNo" which is a Text Field?
2. You have an ID field set as Number
3. Most recommend that you have a PK field Autonumber LongInteger in all tables
4. You have fields J1 through to J15 in which you are inserting TableNo's ? - these fields should be in a seperate related table.
If you want to be able to make a single booking for multiple tables, then you should model the many to many relationship type between Tables and Bookings like this:
You can then return the available tables for each time slot with the following query:
SQL:
SELECT
Tables.TableNo,
Calendar.calDate,
TimeSlots.TimeFrom,
TimeSlots.TimeTo
FROM
Tables,
Calendar,
TimeSlots
WHERE
NOT EXISTS (
SELECT
*
FROM
TableBookings
INNER JOIN Bookings ON TableBookings.BookingID = Bookings.BookingID
WHERE
TableBookings.TableNo = Tables.TableNo
AND Bookings.TheDate = Calendar.calDate
AND Bookings.Depart >= TimeSlots.TimeFrom
AND Bookings.Arrive <= TimeSlots.TimeTo
)
ORDER BY
Calendar.calDate,
[Tables].TableNo,
TimeSlots.TimeFrom;
Your viewpoint of the table structure is totally wrong - in this sense: A table is table. When you butt two tables together to increase seating capacity, you still have two tables. Both tables have been reserved. The "joined tables 5, 6, and 7" are not A table. They are three tables that HAPPEN to be in use at the same time.
I don't have a quick answer for the topology question of joining tables together and of saying which sets of tables work together vs. which sets do not work together. But trying to join two tables and treat that combination as a separate table of its own is the reason that you can double-book in the case you described. A joined table is NOT a table. It is multiple tables.
The correct solution MAY be that you must pre-define in their own SEPARATE table a list of joinable tables that you would search when looking for tables with a given capacity greater than that of a single table. In such a case, your software must run through the list by checking each single table listed in a combo to see if a particular combo can be booked. But this must be done in a way that ALWAYS checks back to the states of the single tables that are the components, since you are allowed to book any single tables. And this putative list of allowed combo tables is how you would prevent from booking a join of two tables that were across the room from each other. Tables that are too far apart simply wouldn't be part of the combo list.
Mike - Thanks for looking, I agree that joins need to be in a seperate table somehow.
Ken - Thank you, I did put your structure in to my database this afternoon and I see exactly what it's doing and I like it, if I use this I will be able to build a grid too, which I'd like to do. I cannot work out how to show the available tables in that slot as yet, but when I get started with it then I will get it quite quickly, I didn't proceed down this road earlier as it still gave me the same problem with Table Join Availability - I cannot work out how to do Multiple tables under the one booking with this either.
Doc Man - Thank you, food for thought for me, my quick thoughts are that I might need a seperate query depending upon covers and for joins and at a quick thought I might be able to do a union query or or something for it.
I'm not sure how to do the Table Joins as yet, something to keep me awake with tonight!!
Any help would be very much appreciated with the joins issue.
Thank you for that, I can create that and I have a way to book multiple tables. The issue that I am facing is table availability with joined tables, especially when they can be used in multiple ways, but only once at the same time.
Thank you for that, I can create that and I have a way to book multiple tables. The issue that I am facing is table availability with joined tables, especially when they can be used in multiple ways, but only once at the same time.
Hi Malcolm
The attached screenshot is probable easier to manage.
You have a Form where you select a Specific Date.
Then a Continuous Subform to enter the Multiple Bookings.
Then you data Input form would look like the attached screenshot.
