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.

[mike60smart]

Hi Vinay

Attached is an example of how you should construct your tables.

[mike60smart]

In my example database you would need to run the "qryAllFields_Crosstab" to show the desired output.

To get the Totals for each Column you would run a Report based on this query.

[Ken Sheridan]

Unfortunately, by having multiple columns for the branches, your table is 'encoding data as column headings'. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way. The attached file illustrates how data incorrectly modelled in this way can be corrected by means of VBA code which inserts data into a pre-defined table which models the many to many relationship type. In the demo the 'Quantity in Stock Values' option is appropriate to your database.

[madpiet]

Unfortunately, by having multiple columns for the branches, your table is 'encoding data as column headings'. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way. The attached file illustrates how data incorrectly modelled in this way can be corrected by means of VBA code which inserts data into a pre-defined table which models the many to many relationship type. In the demo the 'Quantity in Stock Values' option is appropriate to your database.

This is where I like to resort to Excel. (Yeah, I know!) but I can do an unpivot in PowerQuery and it's done.

[mike60smart]

Hi Vinay

Your query example is based on a table that is based on an Excel Spreadsheet.

You should think Access which would display your data as shown in the attached screenshot.

[Ken Sheridan]

Hi Vinay

You should think Access which would display your data as shown in the attached screenshot.

That's how the UnencodeColumns demo which I attached earlier recasts the incorrectly modelled data. It then requires a simple crosstab query to return the data in the OP's desired format:

TRANSFORM MIN(StoreStock_Vals.Quantity)
SELECT
    Stocks_Vals.Stock
FROM
    Stocks_Vals
INNER JOIN (
        Stores
INNER JOIN StoreStock_Vals ON Stores.StoreID = StoreStock_Vals.StoreID
    ) ON Stocks_Vals.StockID = StoreStock_Vals.StockID
WHERE
    DSUM (
        "Quantity",
        "StoreStock_Vals",
        "StoreID = " & Stores.StoreID
    ) > 0
GROUP BY
    Stocks_Vals.Stock 
PIVOT Stores.Store;

Normally a correlated subquery would be used to restrict the result table to those stores where the quantity totals more than zero, but I couldn't get that to work in a crosstab query, so have had to resort to calling the VBA DSum function.