Match and Index in Access

[kittyfool via AccessMonster.com]

Hi Gurus,

Is there any function like Excel's match and index?

I have a table as follow

(1 2 3 4 5)
1) 1 2 3 4 5
2) 7 8 9 1011

If i match row 2 with col 3 it will return me 9. How can i do this in access?

Thanks for your help.

 

[Marshall Barton]

Is there any function like Excel's match and index?

I have a table as follow

(1 2 3 4 5)
1) 1 2 3 4 5
2) 7 8 9 1011

If i match row 2 with col 3 it will return me 9. How can i do this in access?

It sounds like your mindset is stuck in spreadsheet mode
;-)

That kind of question is inappropriate in a database system
where data needs to be structured into tables and records
that meet the normalization rules of relational databases.
Note that I am speaking in terms that apply to all
relational database systems, not just Access. If you have
no idea what I'm talking about, you need to do some homework
to understand these fundamental concepts.

The database answer to your question is to use a query that
joins the tables and uses criteria to "match" the indexes.

 

[kittyfool via AccessMonster.com]

hi marshall barton,

i dont quite get you. Can u please elaborate further on how i can use a query
that

joins the tables and uses criteria to "match" the indexes? Can you related it to my example?

Thanks

Your help is very much appreciated.

Is there any function like Excel's match and index?

[quoted text clipped - 5 lines]

If i match row 2 with col 3 it will return me 9. How can i do this in access?

It sounds like your mindset is stuck in spreadsheet mode
;-)

That kind of question is inappropriate in a database system
where data needs to be structured into tables and records
that meet the normalization rules of relational databases.
Note that I am speaking in terms that apply to all
relational database systems, not just Access. If you have
no idea what I'm talking about, you need to do some homework
to understand these fundamental concepts.

The database answer to your question is to use a query that
joins the tables and uses criteria to "match" the indexes.

 

[Marshall Barton]

Sorry, there is no answer to that question until after you
have created the tables that contain the data. Then you
will have to explain what tables and fields are involved
with whatever it is you are trying to accomplish.

You can start your homework assignment here ;-)
http://support.microsoft.com/kb/283878/en-us
--
Marsh
MVP [MS Access]

hi marshall barton,

i dont quite get you. Can u please elaborate further on how i can use a query
that

joins the tables and uses criteria to "match" the indexes? Can you related it to my example?

Thanks

Your help is very much appreciated.

Is there any function like Excel's match and index?

[quoted text clipped - 5 lines]

If i match row 2 with col 3 it will return me 9. How can i do this in access?

It sounds like your mindset is stuck in spreadsheet mode
;-)

That kind of question is inappropriate in a database system
where data needs to be structured into tables and records
that meet the normalization rules of relational databases.
Note that I am speaking in terms that apply to all
relational database systems, not just Access. If you have
no idea what I'm talking about, you need to do some homework
to understand these fundamental concepts.

The database answer to your question is to use a query that
joins the tables and uses criteria to "match" the indexes.

 

[Tim Ferguson]

(1 2 3 4 5)
1) 1 2 3 4 5
2) 7 8 9 10 11

If i match row 2 with col 3 it will return me 9. How can i do this in
access?

select Field3
from MyTable
where Field1 = 7


(there is no concept of a row number in databases, so for the sake of the
exercise I assumed your primary key is in the first field)


Hope that helps


Tim F