SQL question

[Dave]

I have a table with name, month and retrun fields.
I am trying to create a sql statement in access to return any name that has
a positive(non zero) return in Jan,Oct,Dec.
I am using access query wizard but can't quite get it. Any help would be
great.

 

[John W. Vinson]

I have a table with name, month and retrun fields.
I am trying to create a sql statement in access to return any name that has
a positive(non zero) return in Jan,Oct,Dec.
I am using access query wizard but can't quite get it. Any help would be
great.

You have an incorrectly designed table.

Storing data in fieldnames IS WRONG. Is the data in Jan relevant to January
2009? or 2008? or 2021? If you have a one (something) to many (months?
returns? retruns???) relationship, you should have a second table with a date
field and a quantity field, related one to many to your main table.

The fieldname "Name" is also a problem; that's a reserved word and Access can
get confused.

To directly answer your question, create a query based on this spreadsheet (I

0

on the first Criteria line under [Jan]; then >0 on the *second* criteria line
under [Feb], and so on through December. You may need to use the Insert menu
option to insert more lines.

 

[Wayne-I-M]

SELECT IIf(DatePart("M",[TableName]![MonthField])=1 Or
DatePart("M",[TableName]![MonthField])=9 Or
DatePart("M",[TableName]![MonthField])=12,[TableName]![NameField]) AS Return,
TableName.NameField
FROM TableName
WHERE (((TableName.NameField) Is Not Null));

 

[Wayne-I-M]

Oh - I assume that your month field is really a date field ???
or it wont work


--
Wayne
Trentino, Italia.

SELECT IIf(DatePart("M",[TableName]![MonthField])=1 Or
DatePart("M",[TableName]![MonthField])=9 Or
DatePart("M",[TableName]![MonthField])=12,[TableName]![NameField]) AS Return,
TableName.NameField
FROM TableName
WHERE (((TableName.NameField) Is Not Null));


--
Wayne
Trentino, Italia.

I have a table with name, month and retrun fields.
I am trying to create a sql statement in access to return any name that has
a positive(non zero) return in Jan,Oct,Dec.
I am using access query wizard but can't quite get it. Any help would be
great.

 

[Dave]

I was not clear. My table (FundRets) consists of 3 fields fundname,retdate
which is a date field and return.
I want to query all distinct fundnames that for the following 3 retdates
1/1/2008, 10/1/2008, and 12/1/2008 have a return greater than 0.

 

[Dave]

Thank you for your response.
My table is fundname,retdate, and return.
Month is a date field, but I am trying to get distinct fundnames that had
return that was greater than 0 for all 3 dates(
1/1/2008,10/1/2008,12/1/2008).

Oh - I assume that your month field is really a date field ???
or it wont work


--
Wayne
Trentino, Italia.

SELECT IIf(DatePart("M",[TableName]![MonthField])=1 Or
DatePart("M",[TableName]![MonthField])=9 Or
DatePart("M",[TableName]![MonthField])=12,[TableName]![NameField]) AS
Return,
TableName.NameField
FROM TableName
WHERE (((TableName.NameField) Is Not Null));


--
Wayne
Trentino, Italia.

I have a table with name, month and retrun fields.
I am trying to create a sql statement in access to return any name that
has
a positive(non zero) return in Jan,Oct,Dec.
I am using access query wizard but can't quite get it. Any help would
be
great.

 

[John Spencer (MVP)]

If you have exactly one record per fund per date you could try

SELECT FundName
FROM YourTable
WHERE RetDate in (#1/1/2008#,#10/1/2008#,#12/1/2008#)
AND Return > 0
GROUP BY FundName
HAVING Count(*) = 3

Otherwise you might try something along this line
SELECT FundName
FROM YourTable
WHERE RetDate in (#1/1/2008#,#10/1/2008#,#12/1/2008#)
AND Return <> 0
GROUP BY FundName
HAVING Sum(Return) = Sum(Abs(Return))
And Count(*) >= 3

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you for your response.
My table is fundname,retdate, and return.
Month is a date field, but I am trying to get distinct fundnames that had
return that was greater than 0 for all 3 dates(
1/1/2008,10/1/2008,12/1/2008).

Oh - I assume that your month field is really a date field ???
or it wont work


--
Wayne
Trentino, Italia.

SELECT IIf(DatePart("M",[TableName]![MonthField])=1 Or
DatePart("M",[TableName]![MonthField])=9 Or
DatePart("M",[TableName]![MonthField])=12,[TableName]![NameField]) AS
Return,
TableName.NameField
FROM TableName
WHERE (((TableName.NameField) Is Not Null));


--
Wayne
Trentino, Italia.



:

I have a table with name, month and retrun fields.
I am trying to create a sql statement in access to return any name that
has
a positive(non zero) return in Jan,Oct,Dec.
I am using access query wizard but can't quite get it. Any help would
be
great.

 

[John W. Vinson]

I was not clear. My table (FundRets) consists of 3 fields fundname,retdate
which is a date field and return.
I want to query all distinct fundnames that for the following 3 retdates
1/1/2008, 10/1/2008, and 12/1/2008 have a return greater than 0.

Thanks for the clarification.

Put a criterion of >0 on Return, and

IN (#1/1/08#, #10/1/08#, #12/1/08#)

on retdate.

Of course if there's a return on 10/2/08, or with a retdate value of
#10/1/2008 11:31:28am#, it won't be found, so I hope you're reporting the
field contents correctly!