Differentiate different "kinds" of NULL

[Big Pat]

Hi,

I need help to work out how to differentiate to a user things that are null and things that are null but are not supposed to be. I’d better explain!

Hundreds of products can be sold in any of 25 categories across 12 regions. For each product and region we have StartDate, EndDate ,Target and Current. Some products will not be sold in some regions so will not have records in that particular table. However each category manager will have at least some products in all regions. Where records do exist, we have some missing data (dates and/or targets) for historical reasons.

I need to design a simple screen for Category managers, such that it will show only the products in their category, with a calculated value against each one. This value is Current/Pro-RataTarget, where ProRataTarget is in turn calculated by working out how much of the “sale period” has elapsed. This is all built into a query and seems to be working OK.

The attached mockup shows the kind of layout I’m going for. The question is: how to differentiate between (a) results that are blank because the product is not sold in that region and (b) those that are blank because there is missing data making the calculation impossible. In case (a) no action is needed whereas in case (b) we need the manager to be alerted that he has a data quality issue.

I’m a bit stuck as to how to proceed with this one and wondered if anyone had any bright ideas? What am I saying? There’s ALWAYS bright ideas round here!!

 

[mdlueck]

The question is: how to differentiate between (a) results that are blank because the product is not sold in that region

So can you query to determine this state? If so, then run that query and if true then value = "N/A" sort of a scenario.

and (b) those that are blank because there is missing data making the calculation impossible.

Then you could specifically flag cells which still do not have value, as "Err!".

 

[Big Pat]

Thanks for your reply. That's pretty much the conclusion I was coming to too, but I'm not really sure what that query would show. Currently there are 12 near-identical queries which calculate the %values for each region, then one master query pulls those togther with a query that lists the products in the selected category. I don't know if that's the best way to have about this. And I don't know whether I now need to adjust each of the 12 queries (no big deal if I knew WHAT to adjust!) or if I should do something in the master query.

I guess I'll dive in and attempt something and come back when I get stuck.

Thanks,

Pat

 

[mdlueck]

Currently there are 12 near-identical queries which calculate the %values for each region...
<snip>
And I don't know whether I now need to adjust each of the 12 queries (no big deal if I knew WHAT to adjust!) or if I should do something in the master query.

I would think the 12 queries would be the one to know if their region even has XYZ. So if that query detects that XYZ is not possible, then fill in N/A.

The outer query which collects from the 12 queries just collects together for presentation.

 

[Big Pat]

Hi,

I just wanted to come back and say thank you. It took a little bit of trial and error but I burned some midnight oil last night and cracked it. Knowing I was taking the right approach helped me persevere.

Thanks a lot.

Pat

 

[mdlueck]

You are most welcome, Pat!