count of nonzero at a row

[gcyeung]

Hi, I have this table mytable

ID Level Amount1 Amount2 Amount3 Amount4

10 1 9 10 15 6
10 2 2 15 19 2
11 1 5 11 23 0
11 2 6 12 73 0
12 1 7 2 0 0
12 2 3 25 0 0
13 1 5 45 18 0
13 2 7 48 73 0
13 3 2 235 55 0

The prim. keys are ID and Level. For each line I would like to count the nonzeros from the amount1-4 put it in the 3rd column and take out the trailing zeros to produce a .txt file:

mytable.txt:

Non zero Counter
|
V
10, 1, 4, 9 , 10, 15 ,6
10, 2, 4, 2, 15 ,19 ,2
11, 1, 3, 5 , 1 ,23
11, 2, 3, 6 , 12 ,73
12, 1, 2, 7 , 2
12, 2, 2, 3 , 25
13, 1, 3, 5 , 45, 18
13, 2, 3, 7 , 48, 73
13, 3, 3, 2 , 235, 55

My 1st question is how can I use sql query to find the count of nonzero of amount1 - amount4 to produce the counter value for the txt file?
My 2nd question is, I have been using macro with TransferText to transfer table to .txt straight, can I still use it at this case while the number of column may varies depending on the trailing zeros to be omitted?

Thanks
gcyeung

 

[dhookom]

Try this sql:
Select ID, Level, Abs((Amount1<>0)+(Amount2<>0)+(Amount3<>0)+(Amount4<>0)) as Counter, IIf(Amount1<>0, Amount1, Null) as Amt1,IIf(Amount2<>0, Amount2, Null) as Amt2, IIf(Amount3<>0, Amount3, Null) as Amt3,IIf(Amount4<>0, Amountr, Null) as Amt4
FROM mytable

This can still be used with transfer text but you may get extra commas. If you need to get rid of the commas, you may need to concatenate all the expressions into a single expression.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gcyeung]

Thanks for the quick reply..
Yes I need to get away those commas..
Can you please explain more on the part &quot;concatenate all the expressions into a single expression&quot;? Do you mean the expressions on the query itself?

thanks!!

 

[dhookom]

I would probably create a small function that accepts the 4 quantity fields and returns a string as required. If you don't want to write a function you could modify my previous sql similar to:

Select ID & &quot;, &quot; & Level & &quot;, &quot; & Abs((Amount1<>0)+(Amount2<>0)+(Amount3<>0)+(Amount4<>0)) & &quot;, &quot; & IIf(Amount1<>0, &quot;, &quot; & Amount1, Null) & IIf(Amount2<>0, &quot;, &quot; & Amount2, Null) & IIf(Amount3<>0, &quot;, &quot; & Amount3, Null) & IIf(Amount4<>0, &quot;, &quot; & Amount4, Null) as OneBigExpression
FROM mytable

This would create only one column/field in your query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gcyeung]

I have tried to group the &quot;Amount&quot; expression together as one Text column using your suggestion, however because it is a text coulmn, the whole word is enclosed by the &quot; &quot;, like
10, 1, &quot;4, 9 , 10, 15 ,6&quot;
10, 2, &quot;4, 2, 15 ,19 ,2&quot;
11, 1, &quot;3, 5 , 1 ,23&quot;
11, 2, &quot;3, 6 , 12 ,73&quot;
12, 1, &quot;2, 7 , 2&quot;
12, 2, &quot;2, 3 , 25&quot;
13, 1, &quot;3, 5 , 45, 18&quot;
13, 2, &quot;3, 7 , 48, 73&quot;
13, 3, &quot;3, 2 , 235, 55&quot;

should I not make it as a column of text? how should I get rid the &quot; &quot; now? Do I have only choice to write a VB function for it? I am a beginnner in Access VB programming, where should I start?

Thanks!!

 

[dhookom]

I had suggested concatenating all the columns together. If this would not work then you might need:
1) a code solution to write the file
2) open your result in Word and replace all the quotes with nothing.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

On the export specification, you can set it to not enclose text in quotes. Redo an export manually and then save the export spec over the old one.