Asking for query to total up numbers by criteria from multiple fields

[bravura]

Good morning. this is a simplified table and two examples of outputs, one of which is enough. I'm trying to do sql (or design view) in Access to count cntrs on a Route (RteCode1). The "Acceptable Result" would require me to do a pivot table afterwards, no issue with that, but ideally i'd like the query to give me "Ideal Result". Please provide sample sql (pseudo code acceptable) or advise how i go about this. thanks.

Result tells me how many Cntrs will be on aroute. For example, on Ann, second rotation, there will be 40 Cntrs | for Carl, there will be 75 on it's first rotation.

 

[Micron]

After a quick glance I suppose you could do this with UNION queries, but when that type of query is required for a solution, it's almost always because the tables design is wrong and that is the case here. You've designed tables as you would a spreadsheet and that is a problem for Access. You should have one field each for rteCode and rteName. What you have is referred to as repeating fields. The worst part about that is that you have values (e.g. B1) in multiple fields and to find all B1 values that might exist in a horizontal layout is a pitb which is exponentially magnified by the number of repeating fields.

Perhaps you ought to research db normalization and then fix your schema. Access data should be tall, not wide. Excel is usually wide, not tall.