Is there a Case function in Access

[Donna]

I am trying to create a Query that has a date period with a criteria of Where Between 200209 and 200302 (Sep 02 to Feb 03)

Problem is I need to see sales for this period split e.g.

Sep02 Sales Oct 02 Sales Nov 02 Sales etc

When I have done a similair query in SQL Server Analyzer I used the following statement:

Select sum(case when salesperiod = 200209 then sellvalue end) as Sep02Sales

I have tried going into the SQL view in Access and inputting this kind of statement but keep getting errors.

Is there anything that I can use to split the dates?

Hope this makes sense.

Thanks
Donna

 

[blueshark]

Can you explain a little more what you are trying to do? Do you have a list of sales on days and are trying to aggregate them? OR Do you have a table with monthly sales data?

 

[Donna]

I have a table that has the fields:

Material
Sales
Sales Period

I am bringing in:

Material - Group By
Sales - Sum
Sales Period - Between 200209 and 200302

The result of this gives me a total of the sales between Sep 02 and Feb 03.

What I want to see is the Sales for Sep, the sales for Oct etc as seperate columns.

The only way that I know how to do this is to create seperate tables for each month and then bring them all together in a final query.

Thank
Donna

 

[Corticus]

Hi Donna,

First bring all your data together in one query. Then try using a cross-tab query. Select queries|new|crosstab query wizard, and have the query look to the new one you just made. It should walk you through setting up your column heads as months, and you can tell it how you want your records organized and such.

HTH,

 

[blueshark]

Using the wizard to build a crosstab query should help you get what I think you need. Months across the top, materials down the left and monthly totals in the field. There is an option in the wizard to group by month.

-OR-

If the sales Period field is a date, you could use the sales period twice. Once as a "where" and once as a "group by". Reformat the Sales Period field you group on using

Format([Sales Period],"mm")

This won't get the columns you desire. But groups the data appropriately.

SQL Follows:

SELECT Table1.Material, Sum(Table1.sales) AS SumOfsales, Format([Sales Period],"mm") AS [month]
FROM Table1
GROUP BY Table1.Material, Format([Sales Period],"mm");