How to give Decode in select statement in Ms Access

[pol]

How I can give the following Sqls

I have table with the followng

year date sales

2008 02-01-2008 2500
2008 03-02-2008 2000
2009 02-01-2009 2000
2009 03-01-2009 1500

In select statement I have to get the result as follows

2008 sales 2009 sales

 

[Wayne-I-M]

Assuming that your date field is a date

SELECT DatePart("yyyy",[TableName]![DateField]) AS DateYear,
Sum(TableName.DateField) AS SumOfDateField
FROM TableName
GROUP BY DatePart("yyyy",[TableName]![DateField]);

 

[Tom Wickerath]

Hi Pol,

You should not need a separate field for the year, as long as you are
storing the date, since you can always derive this information from the date.
Storing both risks the possibility of errors (for example, year = 2007, but
date = 02-01-2008.....in this case, which year is the correct year?). Also,
be aware that both Year and Date are considered reserved words. You should
avoid using any reserved words for things that you assign a name to within
Access (fields, tables, queries, forms, reports, macros, modules, variables,
controls, etc.). I suggest that you take a look at this list, and consider
downloading the free tool available:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Finally, to answer your question, it appears as if you want a crosstab type
result:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[pol]

Thank for the reply

But I need to show the sales in a two column 2008 sales 2009 sales


Invoice date 2008 2009

02/01/2008 500.50 0.0
02/01/2009 0.00 800.90

So I need to give the following ways

The table structure as follows

Invoicedate date
year number
sales currency


Is there any mathod to show in seperate column on the basis of the year , I
mean any decode statement

With thanks and reagards

Pol

Assuming that your date field is a date

SELECT DatePart("yyyy",[TableName]![DateField]) AS DateYear,
Sum(TableName.DateField) AS SumOfDateField
FROM TableName
GROUP BY DatePart("yyyy",[TableName]![DateField]);



--
Wayne
Trentino, Italia.

How I can give the following Sqls

I have table with the followng

year date sales

2008 02-01-2008 2500
2008 03-02-2008 2000
2009 02-01-2009 2000
2009 03-01-2009 1500

In select statement I have to get the result as follows

2008 sales 2009 sales

 

[pol]

I hope this will be very useful . I will try with cross tab query and I have
to use that query in my front end application. I will make a query using
that wizard with my database and I hope that query can be used in my front
end application

With thanks and regards

Pol