MS Sql Query

[tchandra]

Hello Everyone-

Glad to be part of Access Programmers

i am very new to SQL in MS Access, i have some example project in my mind- here is the requirements

Here is the question in Brief

Example TableMatrix:

India USA UK CHINA
India AB1 BC2 BC87 AC98

USA BC09 Bv09 IU89 AF76

UK NA NA IU89 AF65

CHINA AD56 NA NA NA
​

(Row- Home Country )
(Column - Host Country)


matrix form :

Home Country :
Host Country :
Lookup output : ????

here if i select India in (ROW--> Home Country) and UK in (Column--> Host Country) then the output should be BC87 - nothing but a matrix lookup

i want to do this using SQL Query or Dlookup -this we can achieve in excel using Index and Match Functions-

Please reply if my questions in not clear- request you to help me with brief steps in access

Many Thanks in advance

Best Regards
Chandrakanth Terala
Hyderabad, India.

 

[isladogs]

Hi

Sounds like your data isn't normalised.
It looks like you've just imported excel data into access

 

[tchandra]

Hi ridders -

thanks for your quick reply

data is in correct position - requirement is to do a matrix lookup

and i did not imported the data in to access -

Here is the example for your quick understanding

Home Country - ROW
Host Country -Column

Row India UK USA
India AB65 IK98 UH89

UK AB76 OI98 PO09

USA UY78 NA OI09


FORM :

Home Country : (Select from the Dropdown list Country)
Host Country : (Select from the Dropdown list Country)
Output : ????

If i Select the Home Country as India and Host Country as UK then the Output should be IK98

Hope my question is clear now

Many thanks in advance

 

[isladogs]

Hi ridders -

thanks for your quick reply

data is in correct position - requirement is to do a matrix lookup

and i did not imported the data in to access -

Here is the example for your quick understanding

Home Country - ROW
Host Country -Column

Row India UK USA
India AB65 IK98 UH89

UK AB76 OI98 PO09

USA UY78 NA OI09


FORM :

Home Country : (Select from the Dropdown list Country)
Host Country : (Select from the Dropdown list Country)
Output : ????

If i Select the Home Country as India and Host Country as UK then the Output should be IK98

Hope my question is clear now

Many thanks in advance

I understand what you want to achieve but need to know how that data is stored.

Is it a linked excel table or a local table.
What are the field names and datatypes.
Which field is the primary key
Is the data normalised?

 

[Minty]

Your data isn't normalised. You should have a table with with three fields

HomeCountry, HostCounrty, LinkCode

So your entries would be;

India , UK , IK98
India , India , AB65

Your current data layout will break as soon as you have another country to add. And as you have discovered is almost impossible to query. You are storing data in your field name (Host Country) - that is a no no.

 

[tchandra]

To ridders :


Is it a linked excel table or a local table.
Ans : its a local table

What are the field names and datatypes.
Ans : Home Country - String
Host Country - String
Output : String

Which field is the primary key
Ans: Not Sure about this - i think no primary key is required here - Correct me if am wrong
Is the data normalised?
- yeah data isn't normalised - i want to do with the exact data -

To Minty : yeah your answer is correct but i want to work without normalisng the data- same like this i have achieved using Index and match functions in Excel - Request you to suggest in other way

Many thanks to both

 

[tchandra]

Clear Example Table

Row India UK

India AN87 IK98

UK YU89 PO09



Row - Home Country
Column - Host Country


If i Select the Home Country as India(Row) and Host Country as UK(Column) then the Output should be IK98

 

[Galaxiom]

To Minty : yeah your answer is correct but i want to work without normalisng the data- same like this i have achieved using Index and match functions in Excel - Request you to suggest in other way

Access is a database, Excel is a spreadsheet. Despite the apparent similarities of tables to a worksheet, they are completely and utterly different things.

If you insist on staying with that structure you should return to using Excel.

 

[isladogs]

To ridders :


Is it a linked excel table or a local table.
Ans : its a local table

What are the field names and datatypes.
Ans : Home Country - String
Host Country - String
Output : String

Which field is the primary key
Ans: Not Sure about this - i think no primary key is required here - Correct me if am wrong
Is the data normalised?
- yeah data isn't normalised - i want to do with the exact data -

To Minty : yeah your answer is correct but i want to work without normalisng the data- same like this i have achieved using Index and match functions in Excel - Request you to suggest in other way

Many thanks to both

All Access tables need a primary key if you intend to do anything with them e.g. a query

Output isn't a helpful field name - is that for your query result or does each record contain several values like this? AB65 IK98 UH89

Unless the data is normalised, there is no point using Access - stick with Excel.
To normalise, you need one record per row