Multiple Many to Many and querying

[cboath]

Hopefully I use the right terminology here...

Anyhow, I have a database with multiple many to many relationships.

At the moment, i'm working with a Product. Each version has multiple categories of add-ons. For example.

Item A can have

Accessory1
Accessory2

Category1
Category3
Category17

CategoryB1
CateforyB3

​

Etc, etc.

There are join tables for each Accessory/Category/etc.

I have a query that, due to all it's options, shows up like 17 times. That's fine. I understand how to deal with that on a form so it shows just 'Item1' for example.

After all that, the actual question is, I can't seem to figure out how to return item numbers that have, for example, CategoryB1 AND CategoryB3. I'm not sure if it's not knowing proper terminology, but i can't seem to figure out how it's supposed to be handle.

Most lilkely complicating the matter is that eventually I need to be ble to find products that have Accessory1, CategoryB1 AND CategoryB3, and Category1 AND Category17. I've seen plenty of options where query's get limited to just 2-3 columns, but that doesn't work here. Somethings they'll want to search 1-2 columns, sometimes they want to search 5-6 and search for multiple items.

Hopefully that makes some sense. Just an idea of how to handle it or what to look up would be helpful.

Thanks!

 

[cboath]

Maybe i didn't explain it well enough.Short short version would be I need to be able to find products that exist in both red and blue and also come with widgets 3 and 4 and gadgets 2, 8, 14.I've really hit a wall on this and am completely blocked.

 

[JHB]

... Somethings they'll want to search 1-2 columns, sometimes they want to search 5-6 and search for multiple items.

Hopefully that makes some sense. Just an idea of how to handle it or what to look up would be helpful.

Thanks!

I think the easiest way for you to handle with above is to create a search form with list boxes for Product, Accessory and Category where you can select the (multiple) different criteria to search for.
Take a look at the database in Post #20 of that thread, it is more and less the same what you wish to do. http://www.access-programmers.co.uk/forums/showthread.php?t=247619

 

[cboath]

I've been searching exhaustively and I hadn't seen that one, so thank you for pointing it out.

Hopefully I can figure out what it's doing since the database is written in Croation for the most part.

Being able to translate the fields would help as well, but I doubt babelfish would help much with field names

Thanks again - it does indeed look like a good starting place.

If anyone knows of an english example, please let me knw

 

[JHB]

I don't think you need to translate the fields/control name, to get an idea how it could be done.
As you, I'm not able to speak that language!
Luck with your project.

 

[cboath]

I'm not sure that does what i'm trying to do.

I see that it takes an existing query of all information and then does a query of that data based on a form that lets you choose multiple criteria.

However, the first query seems to generate unique records. I don't see it having records that belong to multiple categories. For example, an article by newspaper A, referencing architecture, tourism, and entertainment, that then in turn applies to two different districts.

If I run a query of my infromation now, a single entry will return upwards of 15 records due to all the many/many relationships. The example seems to have two fields for specialty (like tourism) and that an article can have two (and no more) different specialties.

Me, if I have An article that references 3 specialties and 2 districts, i'd get something like 6 record listings in the query. Each district would show up three times - one for each specialty for a total of 6.

Honestly, i get that. It's what happens when you use many/many. I can filter that in a form to show me just the top level item.

What I don't know is how to search the main query to find main records that have tourism AND architecture in that setup. Especially when with all categories used, one item could have 20 entries due to the many/many relationships.

I'm attaching the db to hopefully better illustrate my point. The last form is the main form, tipinfo. Basically all the subforms listed there need to be separate and be able to be searched on a 'this item contains these parts' basis. I need to be able to look things up in them manner of 'which items contain the following parts' where the parts are stored in (potentially) in different tables linked to the main via many/many relationships. Eventually this will be connected to an overall project number as well. It's in there, it's just not hooked up via relationships yet.

Hopefully someone sees something
Thanks

 

[JHB]

Sorry, but although I have read the post several times, I am still unsure about what it is you want/ or want to perform. Probably also because I do not know the data well enough to decide what is right and what is wrong.
When I look back at your post#1 you write.

Somethings they'll want to search 1-2 columns, sometimes They want to search 5-6 and search for multiple items.

But when I look at your main form "tipinfo", I can not see you perform any search, or offering opportunities to select something to search for.
Can you based on the data in the database tell, what you are getting and what you want to get and how you want to get the result shown?

 

[cboath]

That's the main input form. I haven't begun a search form because I can't figure out exactly how to search for more than one thing in one column multiple times.

That form is how it's input and also largely how it'll need to be searched. People will want to be able to look up multiple pieces from each subform.

For example, one time they may just want to look up Series1 tips - that's easy.

But they may also want to find Series1 tips that have Pilot1 AND Pilot4 (tblPilot) and VesselA AND VesselB (tblVess).

That's what I need to be able to search. The simple part is easy, but having to match multiple criteria in multiple joined tables has me at a loss.

In the statment you quoted each 'column' is a many to many relationship joined with a junction table.

That make more sense?

 

[Pat Hartman]

You cannot include more than a single 1-many relationship in a single query UNLESS the relationship is hierarchical. Parallel relationships (which is what yours are) are independent and including them in a single query will result in a Cartesian Product. In your example, it looks like you have three "child" tables. On a form or report, you need a separate subform or subreport for each. If you want to select items as from a Chinese menu (1 from column A, 2 from column B), you need to create a separate query for each child table to apply that table's criteria. Then join the three queries. The result set will include only items that have something returned for each table. You might need to build the criteria for the three tables as well as the final join with VBA since I don't think you can make the query work if you don't have criterial for all "three" child tables.

 

[cboath]

I believe I understand that. I have 5 tables in parallel relationships, therefore, i'd need to have 5 queries - each query looking up 1 relationship. Makes sense when I think about it.

That leaves me with 2 questions...
1. Can you point me to a link that explains how to set up a query that looks at a many to many where you can search on 2 or more criteria in the table - as in my case, say, two different structure types: Derrick and Guy Wire. I know if I right a standard query that would return two lines for the single tip. One saying Tip Derrick, the other saying Tip Guy Wire. It's also return any tip that referenced either - how do you make sure it returns tips with both items?

2. How do you go about joining the queries? Do you structure a join in VBA so that if the variable isn't in use, it doesn't join it to the overall query? Basic if then?

 

[cboath]

Your last comments got me past a major block.

I think i've got it functioning pretty well in this simple version of it. This searches two different m2m tables for matching criteria.

It works as long as you make at least one choice per column, but i've got to figure out how to write a final query join with an inderminant amount of sections.

One search may want to search just people in a department (no choices in the skills list). Right now that errors because an empty query is inserted into the final join.

Simplistically I can do this;

If Me.DeptList.ItemsSelected.Count > 0
add query to join here
end if

However formatting multiple joins in access is odd.

Can each join be in parenthes? As in, can each segment of the final join be identical in structure so i can simply join anywhere from 1-5 lines together like so:

SELECT Employees.ID, Employees.Name
FROM qryDeptVBA
INNER JOIN
(qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID)
INNER JOIN
(Employees ON qryDeptVBA.ID = Employees.ID);

etc... adding in as many lines as people have searched topics?

 

[JHB]

It isn't soooo difficult, (if the attached database gives you the result you are looking for).

 

[Pat Hartman]

The simplest way to allow n selections in a set is to use multi-select ListBox. Play with simple and extended to see which works best for your situation. Then use a code loop that creates a string you can use with In(..) for the list. I've attached a simplistic example that uses the In() as a filter but you can add code to create a query with it. There is no way I have found to use the In() as an argument in a saved querydef because you can't pass in an array. But please post a solution anyone, if you have one but test it for both numeric and text strings. I seem to have some recollection of actually getting an argument to work when the search values were numeric but not when they were text.

 

[cboath]

I think JHB has done what i was shooting for in about 1/3 the amount of code that I had

I'll need to re-create it on a step by step basis to verify my understanding, but I think i've got a good idea of what it's doing right now. If that's the case, it's a matter of exanding to 5 junction tables instead of two, then adding a couple sub junction (think breaking office into a subtable that lists word,excel,access,etc separately) tables to the whole thing.

Pat, it took me a while to get the terminology and procedure right, but i finally did get the the process In() down. I'm by no means a polished programmer and my only real reference is internet searches. But I do understand multi selection list boxes and extracting their info now.

The process I had used was saving each junctionbox query to a separate query, and then joining them together. I wasn't passing the in(), but rather it's query result.

Thanks for the help, i think it's finally coming together.

Update:
In going through JHB's solution - i like a lot of what he's done from a code perspective - however, it's not doing what's needed.

If I choose Windows and Office from skills and Accounting from department, it's supposed to find people in Accounting who have both Windows and Office listed as skills. Presently it's finding people in Accounting who have either of the skills, not just those with both skills.

Examples i've seen do this with the count function. Putting everything into a single query like that doesn't seem to work with what i've seen as the counts aren't really calculable that way. This is why my example was based off an earlier comment from JHB where i took smaller queries I could limit using count (making sure the result matched all chosen skills) and then joined those mini-queries together.

 

[JHB]

Sometimes the difficult part is to find out what people wants, (because of lack of knowledge of what the data are used for).
Therefore I've made an other solution where you can choose, if all selected in a list should be the criteria, or if one or more selected in a list should be the criteria.
If nothing is selected in the list, none of the above will take effect.

 

[cboath]

Sometimes the difficult part is to find out what people wants, (because of lack of knowledge of what the data are used for).
Therefore I've made an other solution where you can choose, if all selected in a list should be the criteria, or if one or more selected in a list should be the criteria.
If nothing is selected in the list, none of the above will take effect.

I so didn't realize how big a chunk i'd bitten off with this

Thank you! That update I think is exactly it except i don't need the choice of and/or on each field. They're all And's. However, I do like the ability and the example of showing both of them.

This example adds a lot of new stuff to my SQL experience.

If I understand the code, it starts off selecting all the Employees matching the skill choice into a new table.

It then adds to that table the choice for departments. If not department is chosen, it just adds them all.

Then in a final query it digests the new table information and makes a list of each employee with the lowest number in the table for their skill/department value (either -1 or 0 for t/f) for a final listing.

That actually answers the next question I had about how to handle non-existant values - for example, if you had an employee without a department, there'd be no value for that employee at all in the department tables. So if you did a query for skills and a query for deparments and and then joined them together listing the employees who match up in each query would result in that employee being left out because he has no department assignment.

 

[JHB]

That actually answers the next question I had about how to handle non-existant values - for example, if you had an employee without a department, there'd be no value for that employee at all in the department tables. So if you did a query for skills and a query for deparments and and then joined them together listing the employees who match up in each query would result in that employee being left out because he has no department assignment.

Yes - he would be left out, and he should in all these occasions where some departments are selected in the list.
If no departments are selected you can handle this by a query, where you select all people from the skills table and all people from the department table and see if people are in both table. If they are not then add them to the table, but ONLY if no departments are selected.

 

[cboath]

When reading your code that actually jumped out at me. Each step looked at three things" Was it AND, OR, or no selection. It's one of the things you get when working with a limited amount of information. You tend to fill in all the blanks so you can check stuff and forget about what to do when fields aren't entered (and aren't required).

Thanks for the help here - it's been a good education.

 

[JHB]

You tend to fill in all the blanks so you can check stuff and forget about what to do when fields aren't entered (and aren't required).

Do you think it bad/ not good?

Thanks for the help here - it's been a good education.

You're welcome - happy to help, and extra happy when people like you, wants to learn and understand what happens in the code, (it keeps my brain working )!

 

[cboath]

I meant that when working with sample data that I tend to fill in all my fields leaving nothing out. Most people opt to leave a field blank instead of putting in NONE or something. Thing is, you can different results if fields aren't filled in and I just forgot to check for that occurence in the code