Generate query criteria for subform from form

[djmtek]

First, not sure if I should be posting this in the "Forms" forum or "Query" forum but since it is seems essentially a query problem will post here first.
I am trying to develop a form with several Yes/no fields representing an applicants room preferences and as these fields are checked of (-1) it will re-query the rooms list with the new criteria.
So...
Form: Form!AppChoice Fields (all yes/no): Floor1, Floor2, etc PlanA, PlanB, etc, ...
Subform: Form!Rooms (not linked) Fields: same as Form but with roomnumber
ID as identifier in both.

I initially did this with a filter but that only worked for one filtered field at a time. I need to check several criteria and end up with a list of rooms that meets that criteria.

Been working and researching for many hours. I know there are members that can likely do this in their sleep. Will very much appreciate their help.

[ranman256]

queries want to search vertically, not across 1 record,
so the data should be stored as:
[clientID], [Choice]
12, Floor 1
12, Blan B
etc...

then queries can search for any choice for any person. No new fields need be added. Only values.
you CAN make a checkbox form, but the data would be stored as I described.

[djmtek]

For clarification, I need to compile a list of rooms that match any combination of several choices by the applicant. Originally I started with this:

https://www.lucidchart.com/invitatio...0-d9a4ed404c3e

I got stuck on how I would relate the list of rooms to multiple records (choicedetails). The applicant can make more than one application over a lifetime so the first table is the Applicant info (tbApplicant), the second is the application info(tbAppCh), and the tbAppChDetails is the individual choices. There are a number of things I am leaving out for simplicity but essentially, the application choices must be one or MORE of each category. So when all the choices are inputed, how do I get a list of rooms that might match these? Example: Bob is willing to accept a room on the North or East side, on the 3rd, 4th or 5th floor, and Floorplan A or B but only in Section 1. This translates into Direction 1 or 2, Floor 3,4,or 5, Floorplan 1 or 2 and Section 1. If each choice is a separate record, then we end up with 8 records which need to somehow be combined to match up with one or more rooms.
I came to an impasse so considered the following instead:

https://www.lucidchart.com/invitatio...9-08cb5d89caba

and maiking a relation between every single criteria field. Now if this wouldn't have gotten me an F in Database development I don't know what would but I feel closer this way than the previous. Your thoughts?

[June7]

You loaded something on a site where I have to create an account to view your info? Not gonna do it. Insert images to post or attach your file to post (follow instructions at bottom of my post) or load to a site that doesn't require registration to download, such as Box.com.

[djmtek]

Sorry about that. Didn't realize it would require an account.
Link1 Diagram


Link 2 Diagram

[djmtek]

This is the form

[June7]

You want to select rooms where all of the selected choices match?

The second option might be easiest for your situation. However, it also probably means have to use a Cartesian relationship query. This means every record in each table associates with every record of other table. Cartesian query can perform slowly with large dataset.

SELECT AppID, RoomNum FROM tbCH, quRooms WHERE quRooms.S1=tbCH.S1 AND ... AND AppID = [enter app ID];

The first structure would likely need a VBA custom function to determine which rooms meet the selection criteria.

So either design can be made to work. Just have to weigh the pros and cons of each. It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

[djmtek]

Working on the Cartesian Solution and will see how it comes out though I should probably pursued the module.
Love the quote you left! "Normalize until it hurts, denormalize until it works."
If I can get things working the easy way I'm going to go back and re-tackle the project the hard way (Normalize!) just so I can be at peace with myself.
Will post back. Thank-you June7

[djmtek]

Sorry for the long time posting back. I ran into some difficulties and as a result spent some time restructuring. I went with just four tables; tbApp, tbCH, tbChDet, tbRM.

I created corresponding queries (quApp, quCH, quChDet, quRM) and an Application form with a Choices subform which in it has a Choices details subform which in it contains an unlinked form with the rooms. I didn't get a full screen shot but the one below shows the bottom of the Main form (fmApp), then the Choices (sfmCH) then two more sub-subforms (sfmChDet1 and sfmChDet2) representing each section (Phase 1 and Phase 2), and then there should be another subform for the rooms; a sub-sub-subform I suppose).


The subform (sfmCH) containing the details and rooms (sfmChDet and sfmRM) work perfectly together when I open it outside the Main form, but when I open the Main form fmApp with these subforms in it the query throws a "Enter Parameter Value dialog box" at me and it doesn't work.
Are there too many subforms for this to work? Or is there something I'm missing to make this work? I don't care if ALL the rooms are displayed or none at first, I just need the list of rooms to be filtered when I tick off a set of criteria.

Here is the current SQL for the quRM query for Section 1 (Phase 1 in the above form diagram):

SELECT tbRM.S1, tbRM.North, tbRM.South, tbRM.ID, tbRM.Room, tbRM.Rented, tbRM.AvailDate, tbRM.East, tbRM.West, tbRM.F1, tbRM.F2, tbRM.F3, tbRM.F4, tbRM.F5, tbRM.F6, tbRM.F7, tbRM.PA, tbRM.PB, tbRM.PC, tbRM.PD, tbRM.PE, tbRM.PM, tbRM.Poth, tbRM.PothDesc
FROM tbRM
WHERE (((tbRM.S1)=True) AND ((tbRM.North)=IIf([Forms]![sfmCHS1]![North]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.South)=IIf([Forms]![sfmCHS1]![South]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.East)=IIf([Forms]![sfmCHS1]![East]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.West)=IIf([Forms]![sfmCHS1]![West]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.F1)=IIf([Forms]![sfmCHS1]![F1]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.F2)=IIf([Forms]![sfmCHS1]![F2]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.F3)=IIf([Forms]![sfmCHS1]![F3]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.F4)=IIf([Forms]![sfmCHS1]![F4]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.F5)=IIf([Forms]![sfmCHS1]![F5]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.F6)=IIf([Forms]![sfmCHS1]![F6]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.PA)=IIf([Forms]![sfmCHS1]![PA]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.PB)=IIf([Forms]![sfmCHS1]![PB]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.PC)=IIf([Forms]![sfmCHS1]![PC]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.PD)=IIf([Forms]![sfmCHS1]![PD]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.PE)=IIf([Forms]![sfmCHS1]![PE]=True,True,False)))
OR (((tbRM.S1)=True) AND ((tbRM.PM)=IIf([Forms]![sfmCHS1]![PM]=True,True,False)));


I realize this could be confusing so If posting the db itself would help I will create a virgin copy and do so.

[djmtek]

I take the previous back. Now the subform by itself is throwing the "enter parameter value" at me as well. Not sure what I did as the subforms used to work outside the main form.

[djmtek]

Ok. that was dumb of me; I had the wrong form name in the query. Here is the corrected SQL statement:
SELECT tbRM.S1, tbRM.North, tbRM.South, tbRM.ID, tbRM.Room, tbRM.Rented, tbRM.AvailDate, tbRM.East, tbRM.West, tbRM.F1, tbRM.F2, tbRM.F3, tbRM.F4, tbRM.F5, tbRM.F6, tbRM.F7, tbRM.PA, tbRM.PB, tbRM.PC, tbRM.PD, tbRM.PE, tbRM.PM, tbRM.Poth, tbRM.PothDesc
FROM tbRM
WHERE (((tbRM.S1)=True) AND ((tbRM.North)=IIf([Forms]![sfmChDet1]![North]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.South)=IIf([Forms]![sfmChDet1]![South]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.East)=IIf([Forms]![sfmChDet1]![East]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.West)=IIf([Forms]![sfmChDet1]![West]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.F1)=IIf([Forms]![sfmChDet1]![F1]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.F2)=IIf([Forms]![sfmChDet1]![F2]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.F3)=IIf([Forms]![sfmChDet1]![F3]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.F4)=IIf([Forms]![sfmChDet1]![F4]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.F5)=IIf([Forms]![sfmChDet1]![F5]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.F6)=IIf([Forms]![sfmChDet1]![F6]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.PA)=IIf([Forms]![sfmChDet1]![PA]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.PB)=IIf([Forms]![sfmChDet1]![PB]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.PC)=IIf([Forms]![sfmChDet1]![PC]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.PD)=IIf([Forms]![sfmChDet1]![PD]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.PE)=IIf([Forms]![sfmChDet1]![PE]=True,True)))
OR (((tbRM.S1)=True) AND ((tbRM.PM)=IIf([Forms]![sfmChDet1]![PM]=True,True)));

This does work in the subform with the sub-sub forms but not when loaded in the Main form.

[June7]

I NEVER use dynamic parameterized query.

Because when a form is used as a subform it is not in the active Forms collection.
Referencing subform requires referencing through the subform container control. Which means won't work when the form is not used as subform.

So you aren't using the Cartesian approach?

[djmtek]

I did try the cartesian approach but ran into the problem of the query not selecting rooms when it should have. Example:
((quRM.North)=[tbCH].[North]) AND ((quRM.South)=[tbCH].[South]) AND ((quRM.East)=[tbCH].[East]) AND ((quRM.West)=[tbCH].[West]))
This does not bring up any rooms if the applicant checks them ALL off. Checking them all off should mean he is interested in any direction and bring up all rooms (assuming the other criteria also matched). So I tried using OR within the groups. Example:
(((quRM.S1)=[tbCH].[S1]) OR ((quRM.S2)=[tbCH].[S2])) AND (((quRM.North)=[tbCH].[North]) OR ((quRM.South)=[tbCH].[South]) OR ((quRM.East)=[tbCH].[East]) OR ((quRM.West)=[tbCH].[West])) AND...

But that results in rooms that match the false criteria. That's when I started going down the "dynamic parameterized" road.
Revisiting your suggestion, do you think the following could be adjusted to yield rooms that match only the True criteria?

SELECT tbCH.Active, tbCH.AppId, quRM.Room, quRM.S1, quRM.S2, quRM.North, quRM.South, quRM.East, quRM.West, quRM.F1, quRM.F2, quRM.F3, quRM.F4, quRM.F5, quRM.F6, quRM.PA, quRM.PB, quRM.PC, quRM.PD, quRM.PE, quRM.PF, quRM.PM
FROM tbCH, quRM
WHERE (((tbCH.Active)=Yes) AND ((tbCH.AppId)=1) AND ((quRM.S1)=Yes) AND (((quRM.North)=[tbCH].[North]) OR ((quRM.South)=[tbCH].[South]) OR ((quRM.East)=[tbCH].[East]) OR ((quRM.West)=[tbCH].[West])) AND (((quRM.F1)=[tbCH].[F1]) OR ((quRM.F2)=[tbCH].[F2]) OR ((quRM.F3)=[tbCH].[F3]) OR ((quRM.F4)=[tbCH].[F4]) OR ((quRM.F5)=[tbCH].[F5]) OR ((quRM.F6)=[tbCH].[F6]))AND (((quRM.PA)=[tbCH].[PA]) OR ((quRM.PB)=[tbCH].[PB]) OR ((quRM.PC)=[tbCH].[PC]) OR ((quRM.PD)=[tbCH].[PD]) OR ((quRM.PE)=[tbCH].[PE]) OR ((quRM.PF)=[tbCH].[PF]) OR ((quRM.PM)=[tbCH].[PM])));

Note: I simplified this a bit by pre-setting the AppID to 1 and the Section (quRM.S1) to Section 1. That results in 3 groups; Direction, Floor, and Plan. If South, F1, and PA, PB is checked off, the query should show all rooms on the North side that are on first floor and have EITHER Plan A or Plan B floor plan. But it doesn't.

Thank you again for your patience and help.

[djmtek]

Well this seems to work as a query. Any guidance on how best to reference the AppID?

SELECT tbCH.Active, tbCH.AppId, quRM.Room, quRM.S1, quRM.S2, quRM.North, quRM.South, quRM.East, quRM.West, quRM.F1, quRM.F2, quRM.F3, quRM.F4, quRM.F5, quRM.F6, quRM.PA, quRM.PB, quRM.PC, quRM.PD, quRM.PE, quRM.PF, quRM.PM
FROM tbCH, quRM
WHERE ((((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.North)=IIf([tbCH].[North]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.South)=IIf([tbCH].[South]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.East)=IIf([tbCH].[East]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.West)=IIf([tbCH].[West]=True,True))))
AND ((((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.F1)=IIf([tbCH].[F1]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.F2)=IIf([tbCH].[F2]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.F3)=IIf([tbCH].[F3]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.F4)=IIf([tbCH].[F4]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.F5)=IIf([tbCH].[F5]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.F6)=IIf([tbCH].[F6]=True,True)))
AND ((((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.PA)=IIf([tbCH].[PA]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.PB)=IIf([tbCH].[PB]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.PC)=IIf([tbCH].[PC]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.PD)=IIf([tbCH].[PD]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.PE)=IIf([tbCH].[PE]=True,True))) OR (((tbCH.Active)=True) AND ((tbCH.AppId)=1) AND ((quRM.S1)=True) AND ((quRM.PM)=IIf([tbCH].[PM]=True,True)))));

[June7]

Isn't the applicant ID available on the main form?

(tbCH.AppId)= Forms!Application!ID