dbTalk Databases Forums  

Assign value based on value field in query

Go Back dbTalk Databases Forums Databases comp.databases.ms-access Assign value based on value field in query

comp.databases.ms-access comp.databases.ms-access


Discuss Assign value based on value field in query in the comp.databases.ms-access forum.



Reply
Page 2 of 2 < 1 2
 
Thread Tools Display Modes
  #11  
Old   
Thomas Andersson
 
Posts: n/a

Default Re: Assign value based on value field in query - 08-14-2010 , 03:24 PM





SELECT Persona.PersonaID, UnitType.Main, Sortie.Result
FROM (Member INNER JOIN Persona ON Member.GameName = Persona.GameName) INNER
JOIN (UnitType INNER JOIN Sortie ON UnitType.Unit = Sortie.Unit) ON
Persona.PersonaID = Sortie.PersonaID
GROUP BY Persona.PersonaID, UnitType.Main, Sortie.Result, Sortie.Started,
Sortie.Ended, Sortie.TOM, Sortie.Valid
HAVING (((UnitType.Main)="Infantry") AND ((Sortie.Started)>=#7/22/2010#) AND
((Sortie.Ended)<=#7/23/2010#) AND ((Sortie.TOM)>=3) AND
((Sortie.Valid)="Yes"))
ORDER BY Persona.PersonaID;

That's the sql before I try to add the IIF(), can't show with is as Access
keeps throwing up the error requester and can't switch to SQL view without
removing the code.

Douglas J. Steele wrote:
Quote:
So what's the SQL for the entire query?

Invalid Syntax, missing operand, non quoted text or invalid
char/comma.
What error?

I thought bthis would do it but keep getting errors
IIf([Sortie].[Result] = "RTB", "1", "0,5")

I'm making a query where I want to create a new column with a
value based on another columns content
The source will contain either of 4 3-letter acronyms (RTB, MIA,
RES, KIA). the new coumn should check this and assign a value of 1
for RTB and 0,5 for all others.
How would I do this?

Reply With Quote
  #12  
Old   
Bob Quintal
 
Posts: n/a

Default Re: Assign value based on value field in query - 08-14-2010 , 06:14 PM





"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote in
news:8coc5mFkkkU1 (AT) mid (DOT) individual.net:

Quote:
SELECT Persona.PersonaID, UnitType.Main, Sortie.Result
FROM (Member INNER JOIN Persona ON Member.GameName =
Persona.GameName) INNER JOIN (UnitType INNER JOIN Sortie ON
UnitType.Unit = Sortie.Unit) ON Persona.PersonaID =
Sortie.PersonaID GROUP BY Persona.PersonaID, UnitType.Main,
Sortie.Result, Sortie.Started, Sortie.Ended, Sortie.TOM,
Sortie.Valid HAVING (((UnitType.Main)="Infantry") AND
((Sortie.Started)>=#7/22/2010#) AND ((Sortie.Ended)<=#7/23/2010#)
AND ((Sortie.TOM)>=3) AND ((Sortie.Valid)="Yes"))
ORDER BY Persona.PersonaID;
Why are you using a TOTALS query? a simple SELECT query would work.


Quote:
That's the sql before I try to add the IIF(), can't show with is
as Access keeps throwing up the error requester and can't switch
to SQL view without removing the code.

Douglas J. Steele wrote:
So what's the SQL for the entire query?

Invalid Syntax, missing operand, non quoted text or invalid
char/comma.
What error?

I thought bthis would do it but keep getting errors
IIf([Sortie].[Result] = "RTB", "1", "0,5")

I'm making a query where I want to create a new column with a
value based on another columns content
The source will contain either of 4 3-letter acronyms (RTB,
MIA, RES, KIA). the new coumn should check this and assign a
value of 1 for RTB and 0,5 for all others.
How would I do this?

Reply With Quote
  #13  
Old   
Thomas Andersson
 
Posts: n/a

Default Re: Assign value based on value field in query - 08-14-2010 , 07:42 PM


Bob Quintal wrote:
Quote:
"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote in
news:8coc5mFkkkU1 (AT) mid (DOT) individual.net:

SELECT Persona.PersonaID, UnitType.Main, Sortie.Result
FROM (Member INNER JOIN Persona ON Member.GameName =
Persona.GameName) INNER JOIN (UnitType INNER JOIN Sortie ON
UnitType.Unit = Sortie.Unit) ON Persona.PersonaID =
Sortie.PersonaID GROUP BY Persona.PersonaID, UnitType.Main,
Sortie.Result, Sortie.Started, Sortie.Ended, Sortie.TOM,
Sortie.Valid HAVING (((UnitType.Main)="Infantry") AND
((Sortie.Started)>=#7/22/2010#) AND ((Sortie.Ended)<=#7/23/2010#)
AND ((Sortie.TOM)>=3) AND ((Sortie.Valid)="Yes"))
ORDER BY Persona.PersonaID;

Why are you using a TOTALS query? a simple SELECT query would work.
Hmm, guess it was left over after some experimenting. doesn't make a
difference to my problem though. IIf just WON'T work for me. Spelling is
correct, no extra commas, all text quoted, only one opperand (tried both "="
and "like"). Tried [Sortie.][Result] as well as [Result] and Access keeps
giving me the same syntax error.
This have me really confused as by everything i can find it SHOULD work.

Reply With Quote
  #14  
Old   
Salad
 
Posts: n/a

Default Re: Assign value based on value field in query - 08-14-2010 , 10:14 PM


Thomas Andersson wrote:

Quote:
Bob Quintal wrote:

"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote in
news:8coc5mFkkkU1 (AT) mid (DOT) individual.net:


SELECT Persona.PersonaID, UnitType.Main, Sortie.Result
FROM (Member INNER JOIN Persona ON Member.GameName =
Persona.GameName) INNER JOIN (UnitType INNER JOIN Sortie ON
UnitType.Unit = Sortie.Unit) ON Persona.PersonaID =
Sortie.PersonaID GROUP BY Persona.PersonaID, UnitType.Main,
Sortie.Result, Sortie.Started, Sortie.Ended, Sortie.TOM,
Sortie.Valid HAVING (((UnitType.Main)="Infantry") AND
((Sortie.Started)>=#7/22/2010#) AND ((Sortie.Ended)<=#7/23/2010#)
AND ((Sortie.TOM)>=3) AND ((Sortie.Valid)="Yes"))
ORDER BY Persona.PersonaID;

Why are you using a TOTALS query? a simple SELECT query would work.


Hmm, guess it was left over after some experimenting. doesn't make a
difference to my problem though. IIf just WON'T work for me. Spelling is
correct, no extra commas, all text quoted, only one opperand (tried both "="
and "like"). Tried [Sortie.][Result] as well as [Result] and Access keeps
giving me the same syntax error.
This have me really confused as by everything i can find it SHOULD work.
What happens if you attempted to add the IIF() column without any
filtering or sorting of any kind. Have you compacted the mdb? Have you
created a new database and imported all of the objects and relationships
and see if that made a diff?

Reply With Quote
  #15  
Old   
Bob Quintal
 
Posts: n/a

Default Re: Assign value based on value field in query - 08-15-2010 , 06:40 AM


"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote in
news:8corepF4bdU1 (AT) mid (DOT) individual.net:

Quote:
Bob Quintal wrote:
"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote in
news:8coc5mFkkkU1 (AT) mid (DOT) individual.net:

SELECT Persona.PersonaID, UnitType.Main, Sortie.Result
FROM (Member INNER JOIN Persona ON Member.GameName =
Persona.GameName) INNER JOIN (UnitType INNER JOIN Sortie ON
UnitType.Unit = Sortie.Unit) ON Persona.PersonaID =
Sortie.PersonaID GROUP BY Persona.PersonaID, UnitType.Main,
Sortie.Result, Sortie.Started, Sortie.Ended, Sortie.TOM,
Sortie.Valid HAVING (((UnitType.Main)="Infantry") AND
((Sortie.Started)>=#7/22/2010#) AND
((Sortie.Ended)<=#7/23/2010#) AND ((Sortie.TOM)>=3) AND
((Sortie.Valid)="Yes")) ORDER BY Persona.PersonaID;

Why are you using a TOTALS query? a simple SELECT query would
work.

Hmm, guess it was left over after some experimenting. doesn't make
a difference to my problem though. IIf just WON'T work for me.
Spelling is correct, no extra commas, all text quoted, only one
opperand (tried both "=" and "like"). Tried [Sortie.][Result] as
well as [Result] and Access keeps giving me the same syntax error.
This have me really confused as by everything i can find it SHOULD
work.
Have you tried using a simplified IIf statement?
test1: iif(True,1,2)
If that works,
then try test1: iif(True,1,1/2)
then substitute your field names

Reply With Quote
  #16  
Old   
Thomas Andersson
 
Posts: n/a

Default Re: Assign value based on value field in query - 08-15-2010 , 07:32 AM


Bob Quintal wrote:
Quote:
Hmm, guess it was left over after some experimenting. doesn't make
a difference to my problem though. IIf just WON'T work for me.
Spelling is correct, no extra commas, all text quoted, only one
opperand (tried both "=" and "like"). Tried [Sortie.][Result] as
well as [Result] and Access keeps giving me the same syntax error.
This have me really confused as by everything i can find it SHOULD
work.

Have you tried using a simplified IIf statement?
test1: iif(True,1,2)
If that works,
then try test1: iif(True,1,1/2)
then substitute your field names
Yes I have, same error :/
My Access must be broken or something :P

Reply With Quote
  #17  
Old   
Chuck Grimsby
 
Posts: n/a

Default Re: Assign value based on value field in query - 08-15-2010 , 12:49 PM


On Aug 15, 7:32*am, "Thomas Andersson" <tho... (AT) tifozi (DOT) net> wrote:
Quote:
Bob Quintal wrote:

Hmm, guess it was left over after some experimenting. doesn't make
a difference to my problem though. IIf just WON'T work for me.
Spelling is correct, no extra commas, all text quoted, only one
opperand (tried both "=" and "like"). Tried [Sortie.][Result] as
well as [Result] and Access keeps giving me the same syntax error.
This have me really confused as by everything i can find it SHOULD
work.

Have you tried using a simplified IIf statement?
test1: iif(True,1,2)
If that works,
then try test1: iif(True,1,1/2)
then substitute your field names

Yes I have, same error :/
My Access must be broken or something :P
Personally, I'd just create a table and LEFT JOIN it in rather then
bothering with all the IIF and Switch statements. Easier for the
users to make changes to as well!

Reply With Quote
Reply
Page 2 of 2 < 1 2

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top