how do i look up the value of a cellusing access?

[kwokv616]

I have imported a number of tables into my access db, and i'm now writing a program that needs to look up values from these tables.



for example,

iif(fac<[value in third column 5th row], 1, 0)



how do i look up the value using access?

[PianoMan64]

Hey kwokv616,

Well, I can tell from what you're describing to me, you've been working in Excel for a great deal of time. Now that being said, I need to explain a few concepts to you that greatly differ from using Excel.

1. With excel you have a static list that is always kept in the order that it appears in the excel spreadsheet. With MS Access, this is a list of item that is kept in a format that is fluid at all time. there is not set order because there are records that are being added and removed, and it will find a place for new records that replaces old records that have been deleted as time go one.

So that prevents you from refering to an item in a table as being in a set order. because it can all be changed simply by sorting the table in a different manner.

2. One thing that is required when developing tables from MS Access is a Unique ID field that is part of the table structure. Like in Excel, you have a row number for each item in the list, In MS Access there is unique ID that will Identify each record in the table uniquely. This way if you need to get to a specific record, you're simply would have to give it the record Unique ID, and it will locate that record.

I know these concepts are different, but you'll have to understand them and many others before you can being to manipulate any data within tables.

Another concept to understand is Indexing.

And example of Indexing is: Lets say you have a last name list of:

Anderson
Zebo
Franklin

If they were indexed, they would be sorted as

Anderson
Franklin
Zebo

This way if you're searching for something by last name, you would be able to find that record right away because the index would exactly what record(s) that last name would be in.

Now to simply answer your question, you're going to need to define within the table what "fac" is and how that relates to the value that you're wanting to look at in column 5.

Take for example you have a table with the following fields in them.

ID          FirstName          LastName          PhoneNumber 
1           Fred               Flinstone         123-456-7890 
2           Barney             Rubble            123-456-7891 
 

If you want to be able to search for lastname Rubble, you would create a query, that would have the following information

Select FirstName, LastName,PhoneNumber FROM TableName WHERE LastName = 'Rubble'
 

So this is how you would search for specific information within tables.

If you create a query, you can specify what fields you want, from what table, and the condition that you want the records returned in?

If you have any more details questions, please feel free to ask, I would be more than happy to help.

Joe P.

[kwokv616]

age y0 y1 y2 y3 y4 y5
0 0.3067 0.2537 0.1978 0.1392 0.105 0.0794
1 0.3073 0.2538 0.1978 0.1392 0.105 0.0794
2 0.3074 0.2538 0.1978 0.1392 0.105 0.0794
3 0.3074 0.2538 0.1978 0.1392 0.105 0.0794
4 0.3074 0.2538 0.1978 0.1392 0.105 0.0794
5 0.3075 0.2539 0.1978 0.1392 0.105 0.0794

This is part of a table that i imported.

the first row with "age", "y0-y5" are column names.
in the vba codes im writing, there will be a value for age, and tmpyr (a variable) = "yx"

when given the value of age and "yx", i will need to calculate r=fac*max(dur,the value in cell[age,Yx])
that is y i need to lookup the value in a cell given a position instead of finding a cell value given some criteria.

is it possible to do this in access?

thanks =)

[PianoMan64]

Hey Kwokv616,

Hello again. I have one question about the formula that you're showing r=fac*max. How does that relate to the values that are in the table which value is fac, and which value is max?

Now with a little assumption on my part I can show you an example of what I was talking about before with the information that you provided?

One of the recommendations that I would do is to simplify the table structure like this:

TableName: AmozInfo
------------------------------------------------------------------------
Fields
------------------------------------------------------------------------
ID              Primary Key (AutoNumber)        Primary Key valuu
AGE             Integer                         Age of Client/Customer
TheYear         Integer                         What Year 
Offset          Currency                        What the Offset
------------------------------------------------------------------------
Sample Data would be:
ID (Will autogenerate)
AGE = 0
TheYear=0
Offset=0.3067
and so on according to your chart.
 
 
 

So as you start to import that information in, you can do a simple select statement like

SELECT TheYear*Offset FROM AmozInfo WHERE TheYear=1 AND Age = 2
 

This way you can search for an item where you've giving it the values that you know, "age" and the "years", then have it look it up in the table.

If you're wanting to do that within vba, if you would post your code that you have so far I can figure out what you need to do. Cause I'm not sure what the variables that you're describing are realted too. I can only assume.

Thanks and await your response.

Joe P.

[kwokv616]

Thank you very much for your reply! I have solved the problem now using Dlookup!
It is actually quite simple =P

I wrote:
temp = DLookup("Y" & dur, "mk" & sex, "Age =" & iage)
temp2 = DLookup("Y" & (dur + 1), "mk" & sex, "Age =" & iage)
in order to get the cell value in Column"Yx" and row "age"

thank you so much for you help!