Access Query Sum All Columns and Rows Except One

[MCTampa]

I have a database which creates a table of bookings by area code and occupancy month. The occupancy months are dynamic and can change.
For example, my current table runs from Mar 2021 through Dec 2021, but it could stretch out to May 2022 tomorrow. It all depends.

My table has the following columns:
CFARX
032021
042021
052021.....

I want to write an SQL query that sums all rows and all columns EXCLUDING anything in the column titled CFARX.
I want to exclude column CFARX because it is a numerical field and will inflate my bookings if included.

Thanks,
Mike

MonthCount2 MonthCount2

CFARX	032021	042021	052021	062021	072021	082021	092021	102021	112021	122021
1​	1​	2​	3​	​	2​	1​	​	​	​	​
8​	​	​	​	1​	​	​	​	​	​	​
30​	1​	​	​	​	​	​	​	​	​	​
33​	​	1​	1​	​	​	​	​	​	​	​
90​	1​	1​	1​	​	​	​	​	​	1​	​
180​	​	1​	​	​	​	​	​	​	​	​
210​	​	​	​	​	​	​	​	1​	​	​
232​	1​	​	​	1​	1​	1​	​	​	​	​
301​	​	​	1​	​	​	​	​	​	​	​
330​	​	4​	​	10​	​	1​	​	​	​	3​
450​	​	​	1​	​	2​	​	​	​	​	​
463​	​	​	1​	1​	​	1​	1​	​	​	​
510​	​	​	​	​	​	​	1​	​	​	​
610​	1​	1​	​	1​	2​	​	​	​	​	​
611​	​	​	​	​	​	​	​	1​	​	​
634​	​	​	2​	​	1​	​	2​	​	​	​
812​	1​	2​	1​	2​	​	​	​	​	​	​
814​	1​	1​	1​	​	​	​	​	1​	​	​
909​	​	1​	​	​	1​	​	​	​	3​	​
960​	​	​	​	1​	1​	​	​	​	​	​
980​	​	1​	2​	​	​	​	​	​	​	​
990​	​	​	1​	​	​	​	​	​	​	​
9999​	​	7​	2​	1​	3​	1​	1​	​	1​	​

 

[JonXL]

This structure is not conducive to what you're trying to pull out for data. You will need code to make this work.

Do you have any leeway to normalize the data and then create your queries?

 

[silentwolf]

Hi,
I do believe that Db Structure is not right and need some adjustment.
Maybe you can post a screenshot of your DB Structure?
That might help to set it up correctly so you can retrieve the relevant Data from Access.

HTH