Using a query to match multiple criteria across 2 tables

[david.semadeni]

Hi,

I would first like to thank you all and apologize if this is a simple or already answered question. I am working on a system that will be hosted on the web and I have found it hard to work around the limitations that Access' web database puts on everything. The purpose of the system is to allow a user to select certain attributes (i.e. services, application delivery method, industry, biz size) and then receive a report with a list of applications that match these attributes. Currently I have a product table with yes/no check boxes for each attribute. I have created a matching table with one entry that will receive the user selections via a form.
My plan is to create a query that will look in the user selection table to see what attributes have a True value, and then return only those products who have a true value for these same attributes (regardless of whether the product has other attributes not selected). Right now I have the following in the first criteria row for each attribute field:

IIF([tbluserselection].[attribute1]=true, [tblproduct].[attribute1]=true, [tblproduct].[attribute1]=true OR [tblproduct].[attribute1]=false

Instead of returning products that fit the user selections, the query only returns a test product that has all of the attributes checked.



Is there a way to achieve my goal in a web database, or do I have to go at it in some other way?

Thank you in advance,

David

[June7]

Show the complete SQL statement. Is the query a join of the two tables?

[david.semadeni]

How do I get the SQL for a web query? I only have the datasheet and design view options. I hope this isn't a ridiculous question, but I've spent an hour looking through help menus and trying to find it myself.

[June7]

Sorry, never built an Access web db. You are building a query in Access project with the query designer and SQL View is not available? Did not know that would happen.

What I was trying to confirm is if both tables are in the query? I guess they must be or you would get an error on the reference to tbluserselection.

I am not clear on the data structure. I have an idea but really need to be sure of data. Provide examples by typing in post or attaching a file (a spreadsheet will work).

[david.semadeni]

Here is a trimmed version of the product table:

Product ID, Product Name, Biz Size Small, Biz Size Small-Med, Biz Size Medium, Biz Size Med-Large, Biz Size Large
1, Test, TRUE, TRUE, TRUE, TRUE, TRUE,
2, Test2,TRUE, FALSE, FALSE, FALSE, FALSE,

Here is a trimmed version of the search result table:
Search Parameter ID, User Search Parameters, Biz Size Small, Biz Size Small-Med, Biz Size Medium, Biz Size Med-Large, Biz Size Large
1, User Search Parameters, FALSE, FALSE, FALSE, FALSE, FALSE

Right now the two tables are joined in the query and I have selected the "all records from tblProduct and only matching records from tblsearchresults" join option. The web query wouldn't let me use the tables together unless I joined them somehow.

Thanks for your patience,

David

[June7]

Not making sense to me. What fields are you joining with? User is allowed to select more than one size? The example shows user did not choose a size. How are the other attributes (delivery, industry, etc) selected?

[david.semadeni]

Since I had to pick a join (and it only allows 1), I just joined the ID fields. The user will select the attributes using a form tied to the tblSearchResults, which allows edits to the 1 record there, but not deletions or new records. There are 20+ attributes, so I just included the BizSizes for simplicity. As far as allowing more than one bizsize selection, I know it isn't glamorous, but I did not see it as a fatal flaw.

[June7]

tblSearchResults has only the ONE record? Then that join means the user selection criteria are available only to the one product record (ID 1).

Are you saying the other attributes are also repetitively named yes/no fields in both tables?

Again, are users allowed to select more than one biz size? Will this be the case for other attributes - multiple selections in each?

As I said, I have never designed a web db, and not understanding why this one shouldn't follow accepted principles of normalization.

[david.semadeni]

The relationship between tblProduct and the attribute categories like bizsize or industry is a many-to-many relationship. Any product can have (be appropriate for) multiple bizsizes or industries, and multiple products will have each attribute. I understand that generally you would create intermediate tables like tblproductbizsize, tblproductindustry, etc. and create two one-to-many relationships btw that table and the product and attribute tables. This was the first route I tried, but the web functionality limitations led me into a dead end.

I don't believe the checkboxes are violating normalization rules given the many-to-many nature of this system, but I am also not having any luck using this matching approach, so it seems like I am really stuck.

I see what you mean about the join on the product id only sorting the first record, is there a way to compare one record in one table to all records in another table? What if I created a search ID field in tblproduct, set the value at 1 for all products and joined that to the search result ID, would that create the functionality I need?

[david.semadeni]

I just tried the last approach I listed, created a searchID field in tblProduct, and in the query I joined the two tables that way. However, I am still not getting the right result. I left the search results attributes unchecked, so if my criteria were set up right, it should return all of the products. However, it only returns products with all attributes checked. Is there something incorrect in the logic of my criteria? For your reference, this is what it looks like:

IIf([tblsearchresults].[bizsize_small]=True,True,([tblProduct].[BizSize_Small])=True Or ([tblProduct].[BizSize_Small])=False)

[June7]

I understand that each attribute can have multiple categories. Should a user be allowed to select more than one category for each attribute? A simple Yes or No will clarify.

What are the design limitations you encounter? Comboboxes and radio buttons option group are often used in web apps.

Also, verify if I understand correctly. User selects attribute category and records are returned where the product also has that attribute category checked True?

Did you say the query errored if it did not include a join clause? Omitting the join clause will cause the user selection record to relate to every product record, in a normal db anyway.

I did a test based on those two conditions. I calculated a field for each BizSize category like: BSS: IIf([tblProduct]![BizSizeSmall]=[tblUserSelection]![BizSizeSmall],True,Null). Then I set criteria for this constructed field of True. Each criteria on a different OR row. Try this with a non-web db and see if it gives the desired results. Then will have to figure out how to reproduce for the web.

[david.semadeni]

Thank you for your continued patience.

When I use the calculated field you gave me and put True in the same criteria row it seems to do the trick. I will revert if it doesn't work when I have built all the fields. otherwise I will mark it as solved

I appreciate your assistance and hope you have a nice day,

David

[david.semadeni]

So when I stack the true criteria on the same row, it only returns perfect matches and when I put each true criterion in its own row, the query returns all of the products. I'm looking to return those products that have the attributes the user selected, regardless of those attributes the user did not select. Do you have any suggestions for how to adapt your IIF statement to achieve this?

Thanks,

David

[June7]

Can you provide project so I can work with your data structure and I don't have to do a total duplication?

[david.semadeni]

It came to me to add an OR clause to the expression and it seems to have finally produced what I was hoping for.

Thank you and have a nice day

IIf([tblProduct]![BizSizeSmall]=[tblUserSelection]![BizSizeSmall] OR [tblUserSelection]![BizSizeSmall]=False,True,Null)