Suppress Zero Rows? Sum columns?

[Maxwell]

Hello,

I need to delete rows that have zeroes across all my metrics. let me explain...

Quick visual on my file I am looking at.

Items (UPC/SKU's) going top-down in column A. Then column B, C, D, etc to the right is TIME (Week 1 ~ Week 52), then the report shows sales of the item based on time.

Basically its a simple table.

I have over 100k rows of data. Some rows have zeroes across all 52 columns to the right. This means the SKU is either not active or an old SKU with no activity.

I want to suppress or delete those ROWS that have zeroes all the way across.

If I can do this...my new rows should go from 100k down to something like 20k or less.



I tried to sum all 52 columns into a column to the far right...but it would not let me add all 52 columns. out of space or something...

Any thoughts to how I can solve this? or is there a more practical way to do this?

Please no VBA solutions....I don't know VBA

[June7]

52 fields, 1 for each week, is not a normalized data structure and accomplishing what you want will not be easy. Even an aggregate (GROUP BY) Totals query won't eliminate records with all zeros.

However, you could try replacing all the zeros with Null then do an aggregate (GROUP BY) Totals query. The records with Null all the way across should be ignored.

Otherwise, unless you want to rebuild the database and normalize data, only solution lies with a custom VBA function.

[AnandKhinvasara]

Output to Excel, Sum up the columns, Filter out the SKU's that show total as 0, copy paste it back into a new table in Access.

Else if you want to do in Access, VBA maybe the only option.

[June7]

Of course, Excel! Should work if don't hit memory issues.

[Maxwell]

Output to Excel, Sum up the columns, Filter out the SKU's that show total as 0, copy paste it back into a new table in Access.

Else if you want to do in Access, VBA maybe the only option.

Thanks...but I was actually trying to use Access to cleanse then data and import back into Excel.
Reason being...is some of the flat files I get grow to close to 300-400k rows!!
I know Access can digest that data much quicker than Excel. When I even try to sort that in Excel to get rid of the zeroes...it just sits there and thinks for like 30 min.
The computer I use is strong enough and up to date computer...(i5 Intel, 8G ram, lots of HD space, Win 7 OS) i7 could be better...but this is a work computer...so...
I wish the flat file I get from our systems could filter those out for me...but unfortunately, it does not. I get what I get and have to deal with it. thus why I have turned to Access for help.
Also the TIME ACROSS vs. TIME DOWN I get...but our systems only provide time across which is a headache I know. It is easier to view when looking at sales...but difficult to manage from a database perspective...

Anyhow...I was hoping there was a solution but I guess I will have to do this in excel...ugh like watching paint dry...

thanks for the input though. much appreciated.

Cheers

[June7]

Does look like that is the only way to avoid VBA.

I tested the aggregate query idea and it did not work.

[AnandKhinvasara]

Hi
I would like to give another shot at this.. Please tell me how exactly you are adding up 52 rows, thats causing the memory problem.
Maybe the way you are doing it is incorrect. Please let me know so I can investigate further. Thanks

"I tried to sum all 52 columns into a column to the far right...but it would not let me add all 52 columns. out of space or something..."

[June7]

OP probably tried simply:

Week1 + Week2 ... + Week52

I don't know the character limit for expression in query or textbox ControlSource, but apparently OP hit it.

[AnandKhinvasara]

Because if he/she tried expression or table field with formula - this can hang... but query should be capable enough to handle 52 fields addition... it should be able to handle it..