UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combo Box Limit Number Of Times Option Can Be Used, Access 2016    
 
   
MrFormula
post Oct 3 2019, 03:52 PM
Post#1



Posts: 612
Joined: 28-May 05



Combo Number 1 assigns Room Type (Single, Double Tripple) and stores max occupancy in (HotelRoomType.RoomOccupants)
Combo Number two assigns grouping via random names. I am using these room names so I can assign people to the correct room.
If I used "Falls" twice and combo number 1 only allows a maximum of two I don't want to see "Falls" show up again as an option in Combo Number 2
It is currently Counting properly the number of times the option is being used. How to I make it remove the rooms that are full from the grouping options.


Currently Combo number two

CODE
SELECT RoomNames.RoomNameID, RoomNames.RoomName, HotelRoomType.RoomOccupants, Count(TeamRoomAssgn.RoomNameID) AS CountOfRoomNameID
FROM (RoomNames LEFT JOIN HotelRoomType ON RoomNames.HotelRoomTypeID = HotelRoomType.HotelRoomTypeID) LEFT JOIN TeamRoomAssgn ON RoomNames.RoomNameID = TeamRoomAssgn.RoomNameId
GROUP BY RoomNames.RoomNameID, RoomNames.RoomName, HotelRoomType.RoomOccupants;
Go to the top of the page
 
theDBguy
post Oct 3 2019, 03:55 PM
Post#2


UA Moderator
Posts: 76,889
Joined: 19-June 07
From: SunnySandyEggo


Hi. Your Row Source doesn't include a Criteria. Try adding a criteria to it to filter out the unwanted options. However, you will probably find this type of approach will have some side effects to it. For example, as you filter the options, you might notice the other records could show up blank even if they had valid choices selected in them (this is usually on a datasheet or continuous forms). My preferred approach is to "validate" the selection to reject invalid choice but not take them away from the options. Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MrFormula
post Oct 3 2019, 04:00 PM
Post#3



Posts: 612
Joined: 28-May 05



Thank you for the quick response.
How do I add a criteria based on the max occupancy of the "double" room.
RoomnameID can't "Count" more than roomOccupants

I am working in the design view but can't figure out what the criteria would look like...
Can you give me a starter what the critera would look like
Go to the top of the page
 
arnelgp
post Oct 4 2019, 02:30 AM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


SELECT RoomNames.RoomNameID, RoomNames.RoomName, HotelRoomType.RoomOccupants, Count(TeamRoomAssgn.RoomNameID) AS CountOfRoomNameID
FROM (RoomNames LEFT JOIN HotelRoomType ON RoomNames.HotelRoomTypeID = HotelRoomType.HotelRoomTypeID) LEFT JOIN TeamRoomAssgn ON RoomNames.RoomNameID = TeamRoomAssgn.RoomNameId
WHERE Count(TeamRoomAssgn.RoomNameID) < HotelRoomType.RoomOccupants

GROUP BY RoomNames.RoomNameID, RoomNames.RoomName, HotelRoomType.RoomOccupants;
This post has been edited by arnelgp: Oct 4 2019, 02:31 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
MrFormula
post Oct 4 2019, 11:55 PM
Post#5



Posts: 612
Joined: 28-May 05



Thanks, I will work on that. Looks like my original query is missing data for one field. Now I will work on that. When that data show up I will be able to properly test your criteria.... Thank you so much.
Go to the top of the page
 
MrFormula
post Oct 5 2019, 09:38 AM
Post#6



Posts: 612
Joined: 28-May 05



So I finally got my database normalization figured out.
I applied the criteria but it seems to be working "backwards" If there are no roomnameid assigned then no options from the room name table show up.
If I start with one room name selected in a previous record then it only shows me the one room name.

I need the opposite result. If no room names are slected I want to see them all, If one room name has been used = to the room occupancy then I want that room name to disappear.

Here is my Updated Query

CODE
SELECT RoomNames.RoomNameID, RoomNames.RoomName, Count(TeamRoomAssgn.RoomNameId) AS CountOfRoomNameId, HotelRoomType.RoomOccupants, HotelRoomType.RoomDesc
FROM HotelRoomType RIGHT JOIN (RoomNames LEFT JOIN TeamRoomAssgn ON RoomNames.RoomNameID = TeamRoomAssgn.RoomNameId) ON HotelRoomType.HotelRoomTypeID = TeamRoomAssgn.HotelRoomTypeId
GROUP BY RoomNames.RoomNameID, RoomNames.RoomName, HotelRoomType.RoomOccupants, HotelRoomType.RoomDesc
HAVING (((Count(TeamRoomAssgn.RoomNameId))<[HotelRoomType].[RoomOccupants]));

Go to the top of the page
 
arnelgp
post Oct 5 2019, 10:14 AM
Post#7



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


then reverse the logic:

HAVING ([HotelRoomType].[RoomOccupants] < Count(TeamRoomAssgn.RoomNameId));

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
MrFormula
post Oct 5 2019, 11:40 AM
Post#8



Posts: 612
Joined: 28-May 05



That was my first thought. I did that but got nothing in the Combo box at that point.

So as I have been working on this I think this code ALMOST has it working. I think we need to add something else to the criteria so it will also show all options not selected as well.
Currently it will only show options that have been selected but have not reached the max roomOccupants. It does not show any other roomNames that have not yet been selected.

CODE
SELECT RoomNames.RoomNameID, RoomNames.RoomName, Count(TeamRoomAssgn.RoomNameId) AS CountOfRoomNameId, HotelRoomType.RoomOccupants
FROM HotelRoomType RIGHT JOIN (RoomNames LEFT JOIN TeamRoomAssgn ON RoomNames.RoomNameID = TeamRoomAssgn.RoomNameId) ON HotelRoomType.HotelRoomTypeID = TeamRoomAssgn.HotelRoomTypeId
GROUP BY RoomNames.RoomNameID, RoomNames.RoomName, HotelRoomType.RoomOccupants, HotelRoomType.RoomDesc
HAVING (((Count(TeamRoomAssgn.RoomNameId))<[HotelRoomType]![RoomOccupants]));

Go to the top of the page
 
arnelgp
post Oct 5 2019, 12:55 PM
Post#9



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


does RoomOccupant field contains the maximum no. of person
that can accommodate that room?

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
MrFormula
post Oct 5 2019, 01:48 PM
Post#10



Posts: 612
Joined: 28-May 05



Yes that is correct
Go to the top of the page
 
arnelgp
post Oct 5 2019, 10:10 PM
Post#11



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you also need to add the first combo roomNameID to the criteria.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 10:26 PM