Country Matrix look up - Need Help

[tchandra]

Hello - am glad to join Access Programmers - I need help in looking the country value - below is the brief in example

Table Format

Rows - Home Country
Columns - Host Country

S

no India Albania USA UK

India SD87 C34 C46 C98

Albania t1 - j87 k09

USA v78 b98 h87 o98

UK o90 k98 j87 o98
​

my question in brief :

if i select homecountry as india and hostcountry as USA then the lookup would be "C46" -we can overcome this in excel using index and match - but how to do it in MS access - i am good to go with Query,VB in access -

hope my question make sense

Help is highly helpful- many thanks in advance

Best Regards,
Chandrakanth Terala
Hyderabad, India.

 

[CJ_London]

Access is very different from Excel and looks like you are storing your data excel style.

You should have by the looks of it one table listing all the countries. Here is a solution I've adapted from this post https://www.access-programmers.co.uk/forums/showthread.php?t=294672

1. create a table called tblCountries, populate it with one column - Country - and enter USA, India etc


2. create a crosstab

TRANSFORM First(" ") AS Expr1
SELECT HM.Country AS Home
FROM tblCountries AS HM, tblCountries AS HS
GROUP BY HM.Country
ORDER BY HM.Country, HS.Country
PIVOT HS.Country;

3. create a continuous form based on this query

4. in a module create a function

Function selData()
 
    MsgBox "Selected Data is Home: " & Screen.ActiveControl.Parent.Home & " Host: " & Screen.ActiveControl.Name 
 
End Function

5. in your new form, select all the Host textboxes and in the click event put

=selData()

you'll need to modify your function to do something more than display a message box - perhaps assign to a global variable that your main form can reference. All depends on how you want things to work

 

[pbaldy]

FYI, I moved your thread out of the introductions forum.

 

[tchandra]

Thank you so much CJ(Super moderator) i will try this and let you know
am very new to access - though will try the max





[