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

bravura

New Member
Joined
Jan 27, 2010
Messages
44
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.

1606835119765.png
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,016
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top