Filtering values and keeping track of them separately

[amt7565]

Hello All,


This is my first question on this forum.

Here is a table with > 100K rows. Each row has a number under columns ABCD&E. For the most part, each row will contain only a number under one column. But occasionally it may contain values in 2 or more columns like that shown in the 2nd row #48145. For this row, columns B and E both have values.
At the end of the table, the totals for ABCD&E are provided. But if there are any rows with greater than 1 value (like that in the 2nd row), then that needs to be subtracted from the table. That total (in this case 4) must be counted in a separate placeholder for ABCDE. In this case B will have 3 and E will have 1. They will be running totals.

Trying to avoid syntax code. Is there anyone who can provide some guidance on this? I am fresh to Access and have limited knowledge at this time.

thanks

#	A	B	C	D	E	Grand Total
48130				1		1
48145		3			1	4
48146					8	8
48154					4	4
48157				2		2
48161				1		1
48176				1		1
48177					8	8
48182				1		1
48185		14				14
48187				2		2
Totals		17		8	21	46

[CJ_London]

firstly, it looks like your data is not normalised, which makes any solution significantly more complex and difficult to maintain.

next I don't understand

But if there are any rows with greater than 1 value (like that in the 2nd row), then that needs to be subtracted from the table. That total (in this case 4) must be counted in a separate placeholder for ABCDE

Are you just trying to count the number of entries? Or are you saying that despite what you have shown, the actual value for col B in the second row is 4?

next what does this mean?

Trying to avoid syntax code

what is syntax code?

and

They will be running totals.

of what?

How do you plan to display the data? in a form or report? You should be aware you would not do this in a table. You could perhaps do it in a query or a number of queries.

and finally - what exactly are you asking? Suggest show 'real' data (i.e. is col B 4 or 3?) and the required outcome

[Micron]

Agree with Ajax; your data does not look normalized and shows a spreadsheet mindset. Not only that, you should NOT store calculated values in a table, ESPECIALLY grand totals. Without benefit of a complete understanding, the impression I get is that 48145 should have 4 rows in some table under a field that categorizes the values (no idea what that is since I have no concept of what the data means). 3 rows should have the value set to B and 1 set to E. A totals query would report that for 48145, the counts for B and E are 3 and 1, and the grand total is 4.

[alansidman]

To help you understand normalization, here is a link to RDBMS design.

http://sbuweb.tcu.edu/bjones/20263/a...sdb_design.pdf