By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Need help with Conditional Query

P: 5
Hi! This is my first question to the forum. I read the guidelines and will try to follow them as closely as possible. I have also searched past posts but was not able to find the specific answer to my question. Thanks in advance for the help.

My OS is Vista, and we are operating Access 2003 on our home computer.

I have a table in which all records have a designation of either "X" or "I" under the [cd_trd_type] column. E.g.,

A: nbr_cusip; B: amt_par; C: cd_trd_type

1 "3128X6JP2" "15,000,000" "I"
2 "3128X6JP2" "4,400,000" "X"

I want to generate two new columns: [Amt_par1] and [Amt_par2]. When [cd_trd_type] equals "X," I want [Amt_par1] to have the value listed for that record in [amt_par], and for [Amt_par2] to be 0. When [cd_trd_type] equals "I," I want [Amt_par2] to have the value in [amt_par], and for [Amt_par1] to be 0.

In Excel, I used the formulas
Expand|Select|Wrap|Line Numbers
  1. =IF(C1="X",B1,0)
and
Expand|Select|Wrap|Line Numbers
  1. =IF(C1="I",B1,0)
in the new columns, respectively. But I want to be able to automatically generate it using an Access query.

This is what I tried using design view (here is the SQL, though I'm no SQL programmer):
Expand|Select|Wrap|Line Numbers
  1. SELECT [Q3 total].nbr_cusip, [Q3 total].SumOfamt_trd_fee1, IIf([cd_trd_ type]="I",[amt_par],"0") AS [Amt_ par1], IIf([cd_trd_ type]="I",[amt_par],"0") AS Amt_par2
  2. FROM [Q3 total];
  3.  
What I get when I attempt to run the query is a parameter dialogue box with "cd_trd_ type". This is not supposed to be a parameter query.

What am I doing wrong?

Thanks!!
Jan 2 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 634
Hi! This is my first question to the forum. I read the guidelines and will try to follow them as closely as possible. I have also searched past posts but was not able to find the specific answer to my question. Thanks in advance for the help.

My OS is Vista, and we are operating Access 2003 on our home computer.

I have a table in which all records have a designation of either "X" or "I" under the [cd_trd_type] column. E.g.,

A: nbr_cusip; B: amt_par; C: cd_trd_type

1 "3128X6JP2" "15,000,000" "I"
2 "3128X6JP2" "4,400,000" "X"

I want to generate two new columns: [Amt_par1] and [Amt_par2]. When [cd_trd_type] equals "X," I want [Amt_par1] to have the value listed for that record in [amt_par], and for [Amt_par2] to be 0. When [cd_trd_type] equals "I," I want [Amt_par2] to have the value in [amt_par], and for [Amt_par1] to be 0.

In Excel, I used the formulas
Expand|Select|Wrap|Line Numbers
  1. =IF(C1="X",B1,0)
and
Expand|Select|Wrap|Line Numbers
  1. =IF(C1="I",B1,0)
in the new columns, respectively. But I want to be able to automatically generate it using an Access query.

This is what I tried using design view (here is the SQL, though I'm no SQL programmer):
Expand|Select|Wrap|Line Numbers
  1. SELECT [Q3 total].nbr_cusip, [Q3 total].SumOfamt_trd_fee1, IIf([cd_trd_ type]="I",[amt_par],"0") AS [Amt_ par1], IIf([cd_trd_ type]="I",[amt_par],"0") AS Amt_par2
  2. FROM [Q3 total];
  3.  
What I get when I attempt to run the query is a parameter dialogue box with "cd_trd_ type". This is not supposed to be a parameter query.

What am I doing wrong?

Thanks!!
Hi
I think, maybe, this
[cd_trd_ type]
should be this
[cd_trd_type]

ie. delete the space !!??

MTB
Jan 2 '08 #2

NeoPa
Expert Mod 15k+
P: 31,418
Firstly let me say that it is a pleasure helping someone who puts in the effort up front :)

As MTB says you have some extraneous spaces in your SQL (from typing it in to the QBE grid).
Try this amended SQL. I've removed spaces and changed the first IIf() to check "X" instead of "I".
Expand|Select|Wrap|Line Numbers
  1. SELECT [Q3 total].nbr_cusip,
  2.        [Q3 total].SumOfamt_trd_fee1,
  3.        IIf([cd_trd_type]="X",[amt_par],"0") AS Amt_par1,
  4.        IIf([cd_trd_type]="I",[amt_par],"0") AS Amt_par2
  5. FROM [Q3 total];
Jan 2 '08 #3

P: 5
Thanks very much to both of you for the quick and helpful responses. Deleting the spaces and adding the X did the trick. I'm glad to know that we were at least on the right track :)
Happy new year!!
S
Jan 2 '08 #4

NeoPa
Expert Mod 15k+
P: 31,418
You're welcome. I'm glad that worked for you :)
Jan 2 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.