Can There Be Too Many Parameters?

[ALewis06]

Below is a simple parameter query that I created, however the end users don't think it is useful as is. What they'd like to see is the current parameter to remain in place, but also to be able to select any combination of months for comparison. In other words, for example, what if I (the end user) want to see results that only included MRP results from ActualQ42015, ActualFeb2016 and FcstJul2016. Is that possible in an on-demand GUI sort of way? I know that I can open the query results and hide/filter certain rows but what if I wanted to be able to do it from a switchboard menu?


SELECT qry944_RGECombined.[MRP-NRP], qry944_RGECombined.[S/L Bucket], qry944_RGECombined.[S/L Prod], qry944_RGECombined.ProdRevType, qry944_RGECombined.ActualQ12015, qry944_RGECombined.ActualQ22015, qry944_RGECombined.ActualQ32015, qry944_RGECombined.ActualQ42015, qry944_RGECombined.ActualJan2016, qry944_RGECombined.ActualFeb2016, qry944_RGECombined.FcstMar2016, qry944_RGECombined.Q12016Fcst, qry944_RGECombined.FcstApr2016, qry944_RGECombined.FcstMay2016, qry944_RGECombined.FcstJun2016, qry944_RGECombined.Q22016Fcst, qry944_RGECombined.FcstJul2016, qry944_RGECombined.FcstAug2016, qry944_RGECombined.FcstSep2016, qry944_RGECombined.Q32016Fcst, qry944_RGECombined.FcstOct2016, qry944_RGECombined.FcstNov2016, qry944_RGECombined.FcstDec2016, qry944_RGECombined.CYTD, qry944_RGECombined.PYTD, qry944_RGECombined.[YTDVar%], qry944_RGECombined.[2016YTDProj], qry944_RGECombined.[2015TotalRev], qry944_RGECombined.[YoYVar%], qry944_RGECombined.[2017Proj], qry944_RGECombined.[YoY17/16Growth%]
FROM qry944_RGECombined
WHERE (((qry944_RGECombined.[MRP-NRP])=[MRP or NRP? If both, leave blank])) OR ((([MRP or NRP? If both, leave blank]) Is Null))
ORDER BY qry944_RGECombined.[MRP-NRP], qry944_RGECombined.[S/L Bucket] DESC , qry944_RGECombined.[S/L Prod];

 

[plog]

The easiest, and best way would be to fix your query (and possibly table structure).

Dates/timeframes shouldn't be stored in field names (e.g. FcstMar2016, ActualQ32015, etc). You can't apply criteria to a field name, you'd have to do some sort of dynamic query builder which lets them select which fields to show.

Instead those dates/timeframes should be values in a fields. You would have a ReportQuarter field which would hold what quarter a record fell into, ReportYear which would hold what year a record fell into, ReportMonth, etc. Then when you wanted to limit the data, you would apply the appropriate criteria to the appropriate field.

Further, 'Fcst' and 'Actual' should be handled similarly. Fcst and Actual are data types, which mean you should store them in a field (possibly ReportType) and then the values would be Fcst and Act.

Tables and queries should grow vertically (with more rows) and not horizontally (with more columns). You do that and letting users see just they data they want becomes a lot easier.

 

[MarkK]

It seems unusual to see the dates in the field names. Like, I would never name a field "FirstNameIsMark." "Mark" is data, and that data belongs IN A FIELD called "FirstName."

Similarly with dates, I would create a query that does a GROUP BY on date periods that matter to me, but I would not expect to see a field named "ActualJan2016." Jan 2016 should be specified in the WHERE clause, not the field name.

 

[ALewis06]

Thanks, I know this is basic Access table design 101, but I was trying to do a "quick & dirty" query based on am Excel file. Clearly there are no shortcuts.

 

[MarkK]

There are no rules for Q&D. If you can make it work, fly at it, load it up with parameters.