Index match equivalent in Access

[Kobierok]

Hi All,

Im trying to create a query in Access which in fact is replication of index match excel function:

I have a source table

Object	Diso**** 1	Diso**** 2	Diso**** 3
X	1%	2%	4%
Y	3%	1%	2%
Z	5%	0%	1%

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

and would like to create a query which would feed another table

Object	TYPE	%
X	Diso**** 1	1%
X	Diso**** 2
X	Diso**** 3
Y	Diso**** 1
Y	Diso**** 2
Y	Diso**** 3
Z	Diso**** 1
Z	Diso**** 2
Z	Diso**** 3

<colgroup><col><col><col></colgroup><tbody>
</tbody>

in excel I would use index match, but dont know how to proceed in access as all typical relation type queries failed.
Is anyone dealt with such an issue and could suggest any solution?


Regards,

Karolina

 

[Chrisdontm]

This is just a thought; You could use a tab in the Excel doc., and do the Index-Match formulas, then reference the answer in the Access database, and work with it from there...

 

[alansidman]

Here is a SQL statement for you.

SELECT table3.Object, "Diso**** 1" AS Type, table3.[Diso**** 1] AS [Percent]
FROM table3;
UNION
SELECT table3.Object, "Diso**** 2" AS Type, table3.[Diso**** 2] AS [Percent]
FROM table3;
UNION
SELECT table3.Object, "Diso**** 3" AS Type, table3.[Diso**** 3] AS [Percent]
FROM table3;

this could all be avoided if your data in your source table was normalized.

 

[Kobierok]

Thank you so much it worked perfectly!

Here is a SQL statement for you.

SELECT table3.Object, "Diso**** 1" AS Type, table3.[Diso**** 1] AS [Percent]
FROM table3;
UNION
SELECT table3.Object, "Diso**** 2" AS Type, table3.[Diso**** 2] AS [Percent]
FROM table3;
UNION
SELECT table3.Object, "Diso**** 3" AS Type, table3.[Diso**** 3] AS [Percent]
FROM table3;

this could all be avoided if your data in your source table was normalized.

 

[Micron]

I would not use Percent for a field name, even if it is an alias.
Microsoft Access tips: Problem names and reserved words in Access

 

[alansidman]

@Micron

Excellent Point.

 

[horatiu00]

hello Mr @alansidman, i have the same problem and i don't know exactly how to use your solution. could you please support? it would be very helpful as i have a project for work.. horatiu00@gmail.com is my email. Thank you so much in advance!

 

[horatiu00]

Prices Prices

ID	Model	Austria	Germany	UK
1​	LaptopS	$2.00​	$7.00​	$11.00​
2​	LaptopM	$3.00​	$5.00​	$8.00​
3​	DesktopE	$5.00​	$9.00​	$10.00​
4​	DesktopE	$12.00​	$14.00​	$7.00​
5​	LaptopM	$6.00​	$15.00​	$17.00​

Table1 Table1

ID	Model	Price	Country
1​	LaptopS	​	Germany
2​	DesktopE	​	Austria
3​	LaptopM	​	UK

I need the Price field from table Table1 to be populated with the data from table Prices based on the Model and Country.

 

[Joe4]

Prices Prices

ID	Model	Austria	Germany	UK
1​	LaptopS	$2.00​	$7.00​	$11.00​
2​	LaptopM	$3.00​	$5.00​	$8.00​
3​	DesktopE	$5.00​	$9.00​	$10.00​
4​	DesktopE	$12.00​	$14.00​	$7.00​
5​	LaptopM	$6.00​	$15.00​	$17.00​

Table1 Table1

ID	Model	Price	Country
1​	LaptopS	​	Germany
2​	DesktopE	​	Austria
3​	LaptopM	​	UK

I need the Price field from table Table1 to be populated with the data from table Prices based on the Model and Country.

One of your big problems is your Proces table is not normalized (see the "Applying the normalization rules" section in this article: Database design basics). It can be very difficult to get data from tables that are not normalized.

In a normalized table, you typically do NOT need to create new fields to add new data. In your example, what if you wanted to add more countries? You could not do it without adding new fields, which creates a whole lot of other database maintenance.

If you designed your Prices data table according to the Rules of Normalization, your strucutre should look something like this:

ID	Model	Country	Price
1​	LaptopS	Austria	$2.00​
1​	LaptopS	Germany	$7.00​
1​	LaptopS	UK	$11.00​
2​	LaptopM	Austria	$3.00​
2​	LaptopM	Germany	$5.00​
2​	LaptopM	UK	$8.00​
3​	DesktopE	Austria	$5.00​
3​	DesktopE	Germany	$9.00​
3​	DesktopE	UK	$10.00​
4​	DesktopE	Austria	$12.00​
4​	DesktopE	Germany	$14.00​
4​	DesktopE	UK	$7.00​
5​	LaptopM	Austria	$6.00​
5​	LaptopM	Germany	$15.00​
5​	LaptopM	UK	$17.00​

(Note: I am not sure if "ID" is meant to by tied to the "Model", or if it is just a unique record identifier. If the later, you would just have the numbers 1-15 then).

Notice how "County" is a field. So if you need to add other countries, you don't need to add new fields,. just new data rows.

Then if you have another Table of Countries and Models, you would just get the price by doing a query between the two tables, joining on the "Model" and "Country" fields, and retuning the price from the Prices table.

Note in databases, you should NEVER put/store calculations or fields that are calculated at the table level. You return these in a query.

 

[horatiu00]

Thank you for the support @Joe4 ! My original database is having 50 countries and 20 models and every 6 months the prices are changing. So instead of 20 rows i will have to do 1000 every 6 months . Is no other way?