Question Complex Query/Report help. Not sure how to do this.

[cdoyle]

I'm not sure where to ask this question, it's a complicated one.

Not sure if this can be done with a query, or in a report, or if it's even possible.

In our database we have 3 fields. We'll name them FieldA, FieldB, FieldC to keep it simple
Each field represents a different code type.

We have some reports that run, and have a section for comments. The comments that are displayed are determined by what is entered in each of those fields.

What they are wanting is to have the comments automatically display on the reports, depending on what is selected in those fields.

But it gets more complicated, because there are special 'conditions' where exception comments could be used.

For example:

If Field A has code 001 chosen, then comment ABC should be displayed on the report.
or
If Field A has code 001 and Field B has code 005, then comment EFG needs to be displayed on the report.

Or
If field A has code 001 and Field B has code 005 but Field C is Code 2A. Then comment XYG needs to be displayed.

The possibilities of different combinations is endless, and the special conditions make it even more complex.

I'm thinking it would have to be coded into the reports, but I'm not even sure how to do that. There are just so many possible combinations, to get the comments they want.

I purposed having a subform, where they could just select from a continuous form dropdown, which comments should be displayed for each record, but they want it to be automated.

Any ideas?

 

[namliam]

Why not 'simply' make a related table to contain these comments???

 

[cdoyle]

That's what I have now, I have a comments table. Where they can select the comments they want to use.

It's the automation I don't know how to do.

I don't know how to make it so it pulls the appropriate comments, depending on the combination of codes selected.

They even need a special comment if one of the Code Type fields is blank.

 

[namliam]

Just make a table with the 3 codes + comments.
Then link the tables together in a query and done...

Offcourse you will have to join this table 3 times:
1) for only the first code
2) For the first two
3) For all three

 

[cdoyle]

I'm not visulazing how that would work.

Right now, each code type field has maybe 20 or so codes that could be selected from dropdown

So on the form, field A they make a selection. Then do the same for the Field B and C.

Example 1.

If Field A has code 2F. It would normally say comment abc.....

But if Field C is left blank, then it would need to use the exception comment that is based on Field A.

example 2
If Field A has code 2A, it would normally say comment defg
But Field C was left blank so now it needs to display the exception comment that is based on Field A.
It won't be the same exception comment that was used in the first example, it's an exception based on what field A has selected.

Then the comments could change yet again, depending on what Field B has selected.

 

[namliam]

Yes that is exactly that...

Make a comments table, Field A, Field B, Field C, Comment

and use it as a lookup table to find the comments to be linked by Fields A,B and C.

 

[neileg]

Is this a reasonable request? If you have a thousand possible values for each of three codes (999 plus null) that gives you a billion combinations! And I bet there are still 'special cases'. You can't automate that. Even if there are only 10 possible values for the codes that's still a thousand combinations. To make the automation work, you have to have a useable number of combinations. Then you can have a lookup table with those possibilities that you can compare with your live data to make a selection. If you have a very small or a very large number of possiblities, it isn't practical. Somewhere between those extremes, there's a level that makes it worthwhile.

 

[cdoyle]

Is this a reasonable request? If you have a thousand possible values for each of three codes (999 plus null) that gives you a billion combinations! And I bet there are still 'special cases'. You can't automate that. Even if there are only 10 possible values for the codes that's still a thousand combinations. To make the automation work, you have to have a useable number of combinations. Then you can have a lookup table with those possibilities that you can compare with your live data to make a selection. If you have a very small or a very large number of possiblities, it isn't practical. Somewhere between those extremes, there's a level that makes it worthwhile.

This is exactly how I'm feeling, that it's just going to be too complex to actually implement, and then try to maintain.

There could be so many different combinations of codes/comments, that's when I came up with just allowing the user select the appropriate comments, since they know what needed to be listed.

But they want it all to be automated, to reduce the risk of the data entry person selecting the wrong comments.

I'm trying to create a testdb using what was suggested earlier. I'm not sure how to link the comments table, with what the user selected in the form tho.

 

[namliam]

Find attached your sample db...

Query "Option1" will work for your current setup...
"Option2" is an alternative if you have actual Null-values in your table...

I did manipulate the values a little to actually make matches.

 

[cdoyle]

Find attached your sample db...

Query "Option1" will work for your current setup...
"Option2" is an alternative if you have actual Null-values in your table...

I did manipulate the values a little to actually make matches.

Thank You, I'm going to play around with this and see if I can make this work.
I'm affraid tho, like neileg said that there might be just too many combinations of codes to make this work, and still be able to maintain.

I looked and there are currently 25 codes they can select for each of those fields.

 

[namliam]

Us developers just provide information and advice... Then provide the infrastructure for the users to do what they deside they want to do...
Ultimatly the users are going to have to maintain this, not you... right?

Just provide the infrastructure so they can maintain their precious automated commenting and let them swell in their own filth for a while.