Sum count fields in a table - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Sum count fields in a table

Edward Shi

4 years ago

Hello,
I have a MS Access table in which I would like to:
a. Add the total no. of fields across the table that contain numerical data
b. Sum up the above fields and display the total sum in a new/added field (i.e., a "Totals" field).

For item (b) above, while I know how to do that using a sum expression in a newly added (calculated) field from a query, my challenge is that the table in question contains 40+ fields to add up, which becomes very laborious, and impractical (each field name is also pretty long), to reference them all in a sum expression. I'm hoping there is a way to reference a range of fields in a sum expression, rather than having to list each and everyone in said expression.

Pls. see the attached pics. for an example of what the table looks like as well as the two right most calculated fields I'd like to see in the resulting table. Note: The latter two fields shown are just examples. the data type should be numeric, but in the example they are shown as character data.

Thanks in advance for any help with this!
Edward Shi
California

	Edward Shi OP @Reply 4 years ago

	Richard Rost @Reply 4 years ago
If you don't want to type all of those field names in, your only option is to loop through the Fields collection (or the Controls collection) and add them up based on the ControlType. Yeah... it's complex. I cover something similar in Access Developer 30.

Edward Shi OP @Reply

4 years ago

Richard,
Thank you for the comment/suggestion. There may be something in there that I can use, so will look into it.

Richard (or anyone that has related suggestion):
Regarding my question (a), and to keep things simple(r), I was hoping there is command/function I can use in a query to at least count up the total number of fields in the subject table, and display this count in a new field after the query is run?

Thanks for any addition help/info.!
Edward Shi

	Richard Rost @Reply 4 years ago
The TableDef Fields Collection has a Count property. That's pretty advanced though.

Edward Shi OP @Reply

4 years ago

Richard,
Thank you for the update. I probably didn't do a good job describing my question regarding my earlier question (a). That is, I'm setting up a query/macro to regularly import the subject table unto my local Access dbase, where I extract and summarize the contents of same. However, from time to time, additional fields may be added to the table (by the group that provides it, via automatic uploads). If/when this happens (not a frequent occurrence), I'll need to adjust my queries to account for the additional field(s) and related data. So, to my earlier question, I just need a 'quick' way of checking if/whether any additional fields have been added to the table since the last download. I'm hoping there is a function or command I can issue from a query to check if/whether the total field count in said table has changed. Pls. advise if there is such command/function? if so, some detail regarding usage would be appreciated.

Thanks,
Edward Shi

	Alex Hedley @Reply 4 years ago
All About Context

	Alex Hedley @Reply 4 years ago
How is this source data made available to you? Is this a csv?

	Edward Shi OP @Reply 4 years ago
The file provided to me is in the form of a excel workbook, via email, which I download unto my local computer. There are a few worksheets in the workbook, one of which contains the data/table I referred to earlier. Using a saved import, I download same unto my local Access dbase.

	Alex Hedley @Reply 4 years ago
So in one worksheet on a given week there could be columns A-G Then the following week there could be A-N?

Edward Shi OP @Reply

4 years ago

Yes, that is correct. For the most part, I only expect such changes only occasionally (1-2 per year?) - The data contains information about products in production, and some new ones may be added as they come into production, or old ones deleted as they are phased out of production. So, short of doing a visual inspection, I was hoping for a more 'automatic' way of catching if/when said changes occur. So, I was hoping for a relatively 'quick' way of assessing this, w/o having to spend a lot of time with VBA or SQL coding. I'm relatively new to Access, so, I posted this question in the hopes I may not be aware of some function or command that could accomplish the above check. If this is not the case, then at least I've confirmed there is no 'easy' way of checking. :-)

	Richard Rost @Reply 4 years ago
There's no easy way IF the fields change. But, Access is not like Excel. You probably don't want to import the data in COLUMNS like you have there. I'd need to see the data you're importing to tell you for sure. Post a screen shot or two here if you can. Here is a video that might help you in the mean time: Import New Changed Records.

	Alex Hedley @Reply 4 years ago
Would the column names be matching the fields you have in your Table? If it's a new product then you'd want to add that product to a ProductT and handle accordingly. Otherwise you'd be looking at actually modifying your Table Fields and adding it there which isn't the best way to handle it.

Edward Shi OP @Reply

4 years ago

Hi All,
Richard - Thank you for the video tip. This could be useful in the future, unfortunately it does not address my main question/issue. The format/layout of the table I receive is already fixed, which it actually is not in a nice dbase format (i.e., One field for product name, one field for PN, one field for qty.), but changing (unfortunately) is not an option.
Alex - The table I get always consists of the same field names, unless a product is added or deleted, in which case the table will consist of more or less fields. The content of the fields is different from one report to the other. These represent production quantities of various parts for the various products (i.e., fields) in the table, over the span of X months.
All - So, I was hoping I'm not aware of some function or command that I could use in a query to alert me the field count has changed (I don't know before hand what they would be), so then I can update my queries/macros accordingly. Is there such thing?

Scott Axton @Reply

4 years ago

Edward -
Unfortunately the "magic bullet" function that you are seeking doesn't exist for what you are speaking about.
I understand that you probably don't have control over the spread sheet that you receive but from there it's on you as to how to handle it.  This can be done but not with out some work.

The guys have provided links to get you started but it appears from what I see in your account that you don't have the courses under you belt to appreciate the guidance they are giving.

So there are two options that I see moving forward.
1)  Submerge yourself into the courses, from the beginning, and learn Access to the point that you can manipulate it they way you want on your own.
or
2) Hire a developer - one of those recommended in the AccessDev.net forum for instance, to help you out.

	Scott Axton @Reply 4 years ago
Here is yet another video that may help you in what you are attempting to accomplish. Import New, Changed Records

	Richard Rost @Reply 4 years ago
Edward, it's very hard to picture what you're talking about without being able to see some samples. Can you post some screen shots?

Edward Shi OP @Reply

4 years ago

Hi All,
Scott: Thanks for the tip on developer for hire. This is an option I can keep in the back on my mind, for future reference.

Richard/All: From everything stated here, I agree w/ Scott's comment that the 'easy' solution I sought does not exist. This is the takeaway answer/conclusion I was after. As to my my challenge to determine if/when the referenced file has changed, I've come up w/ an in-between work around that will serve my need, w/o having to spend a ton of extra time (A good trade off given changes are expected to be rather infrequent). That is, in a query, I've relabeled/simplified all field names I'm currently getting, so in the resulting table, said fields will be listed alphabetically (F1 thru F106). New or Deleted fields (from the previous report) will be obvious from a quick visual scan of the table.

Thanks anyway for all your comments - and appreciated.
Edward

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View