Based on criteria in 1 colum, return data from a different colum.

[dcozzi]

My current situation is as follows.

I receive a text document of specific assets and balance sheet transactions
that I must analyze. For the annual statement, I receive a current holdings
page. For the other 3 quarters, I receive only buys and sells. Each set of
transactions, when imported, do not have the same data in the same columns.
This makes using a pivot table impossible to sum what we have during the
course of the quarter.

What i want to do is be able to search for a value in Field 1, then based on
a value, it returns the value from a specific filed, say any where from field
5-15, and return that value in field 16. This way, i can have all the book
values, from all the asset classes returned in the same column to perform
analysis. I have about 10 different schedules which i use as criteria and out
of those, probably 7 different columns in the table that the book value is.

I think logically, it would be something like

If F1 (field 1) = "sch1" than F16 = F2
If F1 = "sch2" than F16 = F3
If F1 = "sch3" or "sch4" than F16 = F5
and so on.

How would I write this in a query?

I’m new to all this and syntax-ually challenged.

 

[Kernow Girl]

Hi dcozzi - IF statements follow this pattern IIF(test,true,false) and you
can imbed if statements inside of IF statements
In F16 on the Field Line enter the following (XXX is the name of the
Statement and the : is the divider, must be IIF, not IF)

XXX:IIF(F1="sch1",F2,IIF(F1="sch2",F3,F5))

Hope this helps - yell if you need more info>

Yrs - Dika

 

[dcozzi]

Kernow Girl,

Thanks for the quick reply.

A couple of questions since I'm really new to this.

What is XXX?

Just to be clear, so I have a table that has 4 rows and 5 columns.
The table that contains the data is names TABLE1
The columns are F1,F2,F3,F4,F5.
The rows are SA,SB,SC,SD, which are in column F1.
The values in the rows are as follows.
SA, F2=1
SB, F3=1
SC, F4=1
SD, F5=1

The name of the query we are using to get the result is QUERY1.

What is the exact expression I would put in the builder in the first an only
column in the QUERY1, to get an output of all the 1's in a single column,
which would be able to be added up later on. I really only need this one
column returned in data sheet view of the query. Let’s call that field, Q1.

I really appreciate your help.

-dcozzi

 

[Kernow Girl]

Glad to help, BUT, you have kind of lost me. Can you explain a bit more about
your rows and columns.

Am I understanding you that
- F1 contains SA or SB or SC or SD
- you want F2 to be 1 if F1 is SA
- you want F3 to be 1 if F1 is SB
- you want F4 to be 1 if F1 is SC
- you want F5 to be 1 if F1 is SD

What do F2 - F5 currently contain? Are they in your table or in the query?

Send me a bit more info, I'm sure we can sort out a way!

The XXX is the field name - I think what you were calling Q1

Yours - Dika

 

[dcozzi]

Sorry for the confusion.

It is actually i am trying to get info from a specific column based on what
is in column F1. So if column F1 is SA, then i want the query to return the
value in F2. If F1 = SB, i want it to return F3. My issue is i have a lot of
data imported from a text file, but when imported, other then the first
column, the same type of data is spread out on in different columns. I am
simply trying to get all the same data, in this case, book value, in one
column.

 

[Kernow Girl]

Hi dcozzi - sounds like you want to set up several Update Queries to test F1
for each of your pieces of text and update the corresponding field. ie - test
F1 for SA and update F2 with a 1, then test F1 for SB and update F3 with a
one. I use this a lot when my users put things in the wrong fileds. yrs - Dika