My Current situation:
I input data into a form, monthly, which writes to a db - this works fine.
DB entries are written to:
State_Mileage
STATE, MONTH, YEAR, MILES
What I am attempting to do is two-fold:
1: Extract data from the State_Mileage table and write it BY STATE to a second table set up as follows:
tbSTATES
STATE, ABBREVIATION, MILES
State and Abbreviation columns are already populated. Miles column is currently all at '0'
My State_Mileage table is currently populated as such (sample/random pull):
STATE MONTH YEAR MILES
STATE | MONTH | YEAR | MILES |
---|---|---|---|
ALABAMA | 1 | 2018 | 598 |
ARKANSAS | 1 | 2018 | 281 |
ARIZONA | 1 | 2018 | 729 |
CALIFORNIA | 1 | 2018 | 574 |
CONNECTICUT | 1 | 2018 | 83 |
DELAWARE | 1 | 2018 | 15 |
ALABAMA | 2 | 2018 | 295 |
DELAWARE | 2 | 2018 | 21 |
GEORGIA | 2 | 2018 | 14 |
IOWA | 2 | 2018 | 370 |
IDAHO | 2 | 2018 | 267 |
ILLINOIS | 2 | 2018 | 837 |
ALABAMA | 3 | 2018 | 311 |
ARKANSAS | 3 | 2018 | 223 |
DELAWARE | 3 | 2018 | 30 |
IOWA | 3 | 2018 | 1259 |
IDAHO | 3 | 2018 | 750 |
ALABAMA | 1 | 2019 | 709 |
DELAWARE | 1 | 2019 | 51 |
GEORGIA | 1 | 2019 | 729 |
IOWA | 1 | 2019 | 171 |
ILLINOIS | 1 | 2019 | 589 |
INDIANA | 1 | 2019 | 184 |
KENTUCKY | 1 | 2019 | 832 |
MARYLAND | 1 | 2019 | 459 |
MINNESOTA | 1 | 2019 | 175 |
MISSISSIPPI | 1 | 2019 | 221 |
NORTH CAROLINA | 1 | 2019 | 1197 |
NEW JERSEY | 1 | 2019 | 99 |
NEW YORK | 1 | 2019 | 236 |
OHIO | 1 | 2019 | 756 |
PENNSYLVANIA | 1 | 2019 | 706 |
SOUTH CAROLINA | 1 | 2019 | 506 |
TENNESSEE | 1 | 2019 | 872 |
VIRGINIA | 1 | 2019 | 1195 |
WISCONSIN | 1 | 2019 | 334 |
WEST VIRGINIA | 1 | 2019 | 431 |
I am looking to read through the State_Mileage table and populate TOTAL miles for each state into the new column which then can be re-written or overwritten every time I input data into the State_Mileage table. I need to understand how to write data into the State_Mileage table than either run a script or auto-populate the tbSTATE table at the same time.
My entry each month consists of the MONTH, STATE, MILES. Basically I need to append the total STATE miles in the tbSTATE table after entry is completed. All I want is a running total for each state.
The tbSTATE table should result in something similar to below and where the miles would be a grand total of all miles driven at anytime in any state:
STATE | ABBREVIATION | MILES |
---|---|---|
Alabama | AL | 1602 |
Alaska | AK | 0 |
Arizona | AZ | 729 |
Arkansas | AR | 504 |
California | CA | 574 |
Colorado | CO | 0 |