Help on Querying with Cascading ComboBoxes (1 Viewer)

Hi,

I am trying to build a query that collects some information and export it to Excel. But since many values from the DB is coming from cascading Combobox.

During the tests I noticed when I run the query it is duplicating the entries, i.e. I have just 4 records, but when I simply include the tables on the Query Designer and run it is repeating the records to show each of the cascading combo values.

Any ideas to help me to build this query? :banghead:

PS: DB is attached and the query is qryExportFullCardsXls, the cascading are PillarFK and PillarElements among others.

That query appears to run correctly, returns 5 records. If it doesn't include the combo's table, make sure you include a join. Without a join you get a Cartesian product, all combinations of the records in the tables.

pbaldy said:

That query appears to run correctly, returns 5 records...

Click to expand...

It is not working as expected, pbaldy. Despite of returning 5 records, the last column that was intended to return the "Methodology" instead of "3".

If you check the new attachment you will see that the 2 last columns will only return the IDs from their original tables. Also you will notice that it is returning only one record that repeat itself... a strange Cartesian product.

pbaldy said:

...If it doesn't include the combo's table, make sure you include a join. Without a join you get a Cartesian product, all combinations of the records in the tables.

Click to expand...

Can you show me where I should put the JOIN?

Thanks!

Add the pillar table to the query and then return the description from that table:

SELECT tblCardObservations.CardNumber, tblCardStatuses.CardStatus, tblCardObservations.ObservationStartDate, tblAreas.AreaName, tblCategories.Category, tblCardObservations.ObservationDescriptionENG, tblCardObservations.ImmediateActionENG, tblCardObservations.CommentsClassConsidENG, tblCardObservations.FeedbackMsgENG, tblCardObservations.FeedbackByTeamSupervisorFK, tblCardObservations.FeedbackDate, tblPillars.PillarName
FROM tblPillars RIGHT JOIN (tblCategories INNER JOIN (tblCardStatuses INNER JOIN (tblCardRatings INNER JOIN (tblAreas INNER JOIN tblCardObservations ON tblAreas.AreaID = tblCardObservations.AreaFK) ON tblCardRatings.CardRatingID = tblCardObservations.RatingFK) ON tblCardStatuses.CardStatusID = tblCardObservations.CardStatusFK) ON tblCategories.CategoryID = tblCardObservations.CategoryFK) ON tblPillars.PillarID = tblCardObservations.PillarFK
ORDER BY tblCardObservations.ObservationStartDate DESC;

pbaldy said:

Add the pillar table to the query and then return the description from that table...

Click to expand...

OK, it is working now and returns the "WORKPLACE" as I need. Thanks!!!!

But how do I put the value from the PillarElement? This value depends on the SafetyPillar.

I've logged out of my computer to start fixing dinner. I can take a look later tonight or someone will chime in.

pbaldy said:

I've logged out of my computer to start fixing dinner. I can take a look later tonight or someone will chime in.

Click to expand...

No rush and everybody is more than welcome to jump in!

Sorry, got distracted last night.

The selection of the value depended on the first combo, but the value once selected doesn't, does it? How's this?

SELECT tblCardObservations.CardNumber, tblCardStatuses.CardStatus, tblCardObservations.ObservationStartDate, tblAreas.AreaName, tblCategories.Category, tblCardObservations.ObservationDescriptionENG, tblCardObservations.ImmediateActionENG, tblCardObservations.CommentsClassConsidENG, tblCardObservations.FeedbackMsgENG, tblCardObservations.FeedbackByTeamSupervisorFK, tblCardObservations.FeedbackDate, tblPillars.PillarName, tblPillarElements.Element
FROM tblPillarElements RIGHT JOIN (tblPillars RIGHT JOIN (tblCategories INNER JOIN (tblCardStatuses INNER JOIN (tblCardRatings INNER JOIN (tblAreas INNER JOIN tblCardObservations ON tblAreas.AreaID = tblCardObservations.AreaFK) ON tblCardRatings.CardRatingID = tblCardObservations.RatingFK) ON tblCardStatuses.CardStatusID = tblCardObservations.CardStatusFK) ON tblCategories.CategoryID = tblCardObservations.CategoryFK) ON tblPillars.PillarID = tblCardObservations.PillarFK) ON tblPillarElements.PillarElementID = tblCardObservations.PillarElementFK
ORDER BY tblCardObservations.ObservationStartDate DESC;

pbaldy said:

Sorry, got distracted last night.
The selection of the value depended on the first combo, but the value once selected doesn't, does it? How's this? ...

Click to expand...

Regarding your question, that´s right: the dependency is only when I have those cascading combos on the form, once the user chooses one and the other the bound table will save the information independently.

You code works like a charm, Paul!

So when it comes to this type of join I should go for RIGHT JOIN, am I right? Unless I missed something from you SQL code.

Thanks a lot for the help!

Happy to help!

Not necessarily a RIGHT JOIN. I had to do that because there wasn't always data in those fields in the observations table. Double or right click on the join line between one of those pillar tables and the observations table to edit it. The three options describe what the different joins will do. Those 3 options will change the join to either RIGHT, LEFT or INNER as appropriate to the choice. Play with them and see how the results change.