Do not display column when total is zeo

[vinaynaran]

Hi all,

Part	Branch1	Branch2	Branch3	Branch4	Branch5	Branch6
2345	23	9	0	97	0	7
45667	5	7	0	5	0	5
78855	6	0	0	4	0	4
55776	7	8	0	32	0	34
45790	7	9	0	24	0	2
45664	1	87	0	2	0	90
	49	120	0	164	0	142

I have a table like above and I would like to create query where any branch total is ZERO do not display in output result like below.

in short I want below output result from above table.

Part	Branch1	Branch2	Branch4	Branch6
2345	23	9	97	7
45667	5	7	5	5
78855	6	0	4	4
55776	7	8	32	34
45790	7	9	24	2
45664	1	87	2	90

Please advise.
Thanks
Vinay

[CJ_London]

If your data was properly normalised, it would not be a problem- you would simply use a crosstab query to display the data. As it is you would need some complex vba code to determine the zero columns and then hide or remove the column and then move the columns to the right, left to hide the gap

manually in a datasheet you could do this by right clicking on the header and select hide. Can also be done in vba by setting the column width property to 0

[Welshgasman]

I would create a count for each branch for each record.
If a branch count of zero for a branch equals the record count, then omit that branch.

So you need to pre process the data first, then amend the sql to remove the column(s).

Normalise your data and it would be so much easier, as mentioned.

[jojowhite]

what you will be needing is a VBA to build your query.
then you need a form to display the resulting query (see SampleForm on the demo).
see the code that create the query in Module1.