A Count of Non Zero Columns Created from a Crosstab

[longlam]

Hello All,

I am using Access 2007. I have created a crosstab that does unit sells per week for a specified date range (Over 20 weeks).

The crosstab looks as such (The last column is what I am trying to create):

Week 1 Week 2 Week 3 Week 4 Total Weeks
Item A 1 2 0 3 3
Item B 2 0 0 2 2
Item C 0 0 5 0 1

Is there I way for me to add another column to the query that will count all the non-zero columns and tell me how many weeks that the unit count sold was more than zero?

 

[dhookom]

This is a bit difficult to provide an answer since we don't know the SQL of your query, the base table information, etc.

I would think you can first create a totals query that sums items by week. Then create a crosstab query based on the totals query the sets the items as the row headings, weeks as the column heading, sum of sum of as the value, and a Count of Items as the Total Weeks. It won't show on the right since it is a row heading.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[longlam]

Hi dhookom,

I already have the crosstab created except for the total count of weeks that the items were sold.

The intial data looks as follows:

Item ID Total Sold TranDate
1 3 1/1/07
2 1 1/2/07
3 2 1/9/07

The crosstab is a simple general crosstab that uses item id as the row heading, trandate as the columns and total sold as the value. I do a pivot against the trandate that formats it into weeks, i.e. format(trandate, "yyyy/ww").

I've tried doing the count but what it will give me is the total days that it has been sold, not the total weeks and that is what I'm currently looking for.

Thanks in advance for your help and please let me know if you need any additional clarification.

 

[GingerR]

Please provide the SQL of the crosstab query so we can help. Also, how are you defining the "weeks"?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

Hi GingerR, it's great seeing you posting here again

longlam,
As GingerR states, you need to come back with your SQL views.

You would first create a totals query that looks like:

SELECT [Item ID], Sum([Total Sold]) as SumTotSold, 
Format(trandate, "yyyy/ww") as WeekSold
FROM [Initial Data]
GROUP BY [Item ID],Format(trandate, "yyyy/ww");

You can then create a crosstab that has ItemID as Item ID as a row heading, Count of Item ID as the "total weeks" row heading, WeekSold as the Column heading, and Sum of SumTotSold as the value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[longlam]

Thanks! The idea given with the SQL above was enough for me to figure it out! I format about doing the kind of grouping that you have laid out for me.