Query Impossible?

  • Thread starter Thread starter Claudette Hennessy
  • Start date Start date
C

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
 
SELECT dealer , iif( [year]=[criteria value], [year], null)
FROM tableName



Vanderghast, Access MVP
 
Claudette said:
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
Click to expand...

You will need to use the iif() function:

select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table
 
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

Bob Barrows said:
Claudette said:
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
Click to expand...

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.
Click to expand...
 
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

Claudette said:
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

Bob Barrows said:
Claudette said:
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
Click to expand...

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.
Click to expand...
Click to expand...
 
You nailed it..thank you very, very much.

Claudette
Bob Barrows said:
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

Claudette said:
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

Bob Barrows said:
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.
Click to expand...
Click to expand...

--
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.
Click to expand...
 
Back
Top