Query Impossible?

[Claudette Hennessy]

I have hit a wall with this..The relevant table data is:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6 2008
....
The desired output for 2008 criteria in the query would be
Dealer1 2008
Dealer2
Dealer3
Dealer4
Dealer5
Dealer6 2008

The desired output for 2009 criteria would be
Dealer1
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6



Is this impossible? It seems so simple. Any help is much appreciated.
Claudette

 

[Michel Walsh]

SELECT dealer , iif( [year]=[criteria value], [year], null)
FROM tableName



Vanderghast, Access MVP

 

[Bob Barrows [MVP]]

I have hit a wall with this..The relevant table data is:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6 2008
...
The desired output for 2008 criteria in the query would be
Dealer1 2008
Dealer2
Dealer3
Dealer4
Dealer5
Dealer6 2008

The desired output for 2009 criteria would be
Dealer1
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6

You will need to use the iif() function:

select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table

 

[Claudette Hennessy]

Thank you, tried lots of approaches but never thought of IIF, almost
there..should have included the case that:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer3 2008
Dealer4
Dealer5 2009
Dealer6

So: using IIf([ShowYear]=[criteria value],[ShowYear],Null) results in

Dealer Year
Dealer1 2008
Dealer2
Dealer3
Dealer3 2008
Dealer4
Dealer5
Dealer6

Can I get rid of that extra Dealer3?

Claudette

I have hit a wall with this..The relevant table data is:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6 2008
...
The desired output for 2008 criteria in the query would be
Dealer1 2008
Dealer2
Dealer3
Dealer4
Dealer5
Dealer6 2008

The desired output for 2009 criteria would be
Dealer1
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6

You will need to use the iif() function:

select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

You will need to group the results from the first query and return the
max dealeryear:

Select Dealer,Max(DealerYear) As FilteredYear FROM
(select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table) as q
group by Dealer

Thank you, tried lots of approaches but never thought of IIF, almost
there..should have included the case that:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer3 2008
Dealer4
Dealer5 2009
Dealer6

So: using IIf([ShowYear]=[criteria value],[ShowYear],Null) results in

Dealer Year
Dealer1 2008
Dealer2
Dealer3
Dealer3 2008
Dealer4
Dealer5
Dealer6

Can I get rid of that extra Dealer3?

Claudette

I have hit a wall with this..The relevant table data is:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6 2008
...
The desired output for 2008 criteria in the query would be
Dealer1 2008
Dealer2
Dealer3
Dealer4
Dealer5
Dealer6 2008

The desired output for 2009 criteria would be
Dealer1
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6

You will need to use the iif() function:

select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[KARL DEWEY]

For Year criteria use this ---
[Enter year] Or Is Null

 

[Claudette Hennessy]

You nailed it..thank you very, very much.

Claudette

You will need to group the results from the first query and return the
max dealeryear:

Select Dealer,Max(DealerYear) As FilteredYear FROM
(select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table) as q
group by Dealer

Thank you, tried lots of approaches but never thought of IIF, almost
there..should have included the case that:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer3 2008
Dealer4
Dealer5 2009
Dealer6

So: using IIf([ShowYear]=[criteria value],[ShowYear],Null) results in

Dealer Year
Dealer1 2008
Dealer2
Dealer3
Dealer3 2008
Dealer4
Dealer5
Dealer6

Can I get rid of that extra Dealer3?

Claudette

Claudette Hennessy wrote:
I have hit a wall with this..The relevant table data is:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6 2008
...
The desired output for 2008 criteria in the query would be
Dealer1 2008
Dealer2
Dealer3
Dealer4
Dealer5
Dealer6 2008

The desired output for 2009 criteria would be
Dealer1
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6


You will need to use the iif() function:

select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.