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

[TABLE="width: 331"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Object[/TD]
[TD]Diso**** 1[/TD]
[TD]Diso**** 2[/TD]
[TD]Diso**** 3[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]1%[/TD]
[TD]2%[/TD]
[TD]4%[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]3%[/TD]
[TD]1%[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]5%[/TD]
[TD]0%[/TD]
[TD]1%[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 240"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Object[/TD]
[TD]TYPE[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]Diso**** 1[/TD]
[TD]1%[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]Diso**** 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]Diso**** 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Diso**** 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Diso**** 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Diso**** 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]Diso**** 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]Diso**** 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]Diso**** 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Joe4]

In database terms, adding data (records/rows) is cheap, quick, and efficient.
Adding new fields is cumbersome, and could involve you having to update a bunch of different things.

So in your example, you may only have 20 records, but you have 52 fields!!!
What happens when you need to add more countries?
You would then need to add more fields, and then probably rewrite all your queries, reports, forms, and extracts.

If you choose the normalized model I suggested, if you have to add more countries, you do not need to add any new fields to your table. Simply add more rows of data (adding data instead of fields is always preferrable).
On top of that, in order to get the information out that you want, you would need to create a Union query like Alan showed up in post 3. Only, it will be much longer than that.
If you look at the structure:

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;

notice how the pattern repeats (I show with different colors).

You would need one section for each of your Countries. So while this query has 3 sections, yours would have 50!
And anytime you added more Countries, you would need to add additional sections (there is that ongoing maintenance I mentioned earlier).
And quite frankly, I am not sure how fast a Union query of 50 different Countries will run. It could be a slow query.

I was very much in the same place you are in right now when I had to create my first database (I was very Excel proficient, but new to Access at the time). I had a similar situation where I had 20 investment options, so at first I created 20 fields. It was an absolute nightmare to work with! Even seemingly simple tasks became cumbersome and difficult. Then someone helped me out and told me I had to re-design the tables to follow the rules of normalization. It was a big undertaking (because I had already created many queries, forms, and reports around it), but once I did, everything fell into place, and I was now able to do everything I wanted without pulling out the rest of my hair.

A key thing to understand for those new to Access and relational databases is that it is very different from Excel. You really cannot approach it with an Excel spreadsheet state-of-mind.
You need to learn about relational databases, and how to design them (especially the Rules of Normalization). If you don't, it will probably cause you nothing but continued aggravation.
Trust me, I have been there.

 

[horatiu00]

Tks again @Joe4! Really useful information! Then, I'm receiving the date as i said... 20 rows and 50 countries every 6 month. Do you have any solution on how to transform the data i'm receiving in the format you recommend? i mean, faster..no manual work and than to append the date to a table to be used as source for the entire database....

 

[Joe4]

Is the file coming over in Excel format?

If it were me, I would probably create a little Excel macro to convert the data file in the format that they are sending it to the format that I should you.
Then import that converted file into Access.

Alternatively, if it were a text file, you probably could create an Access VBA procedure that would read throught the data table and write the data directly to the table.
A bit more complex to create, but then bypasses the need for Excel.

Either way, once you create this conversion process, it should be good to go and you shouldn't have need to update it (unless they totally changed the format of the file).

 

[horatiu00]

Yes, the file is coming over in excel format. I will try to create a macro for that.

 

[Joe4]

Yes, the file is coming over in excel format. I will try to create a macro for that.

Great!

If you need help, you can post a question over in the "Excel Questions" forum and someone can help you with that (I probably can, if I see it).
Just be sure to include the exact format, since that is critical, and you will want to be sure to mention that number of countries could grow in the future.
You may also want to create a link back to this thread, if anyone questions why you want to do this.

 

[alansidman]

I would normalize the data as Joe has suggested using Power Query. Here is the Mcode to make that happen

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Model", type text}, {"Austria", type text}, {"Germany", type text}, {"UK", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Model"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Country"}})
in
    #"Renamed Columns"

ID	Model	Austria	Germany	UK		ID	Model	Country	Value
1	LaptopS	$2.00	$7.00	$11.00		1	LaptopS	Austria	$2.00
2	LaptopM	$3.00	$5.00	$8.00		1	LaptopS	Germany	$7.00
3	DesktopE	$5.00	$9.00	$10.00		1	LaptopS	UK	$11.00
4	DesktopE	$12.00	$14.00	$7.00		2	LaptopM	Austria	$3.00
5	LaptopM	$6.00	$15.00	$17.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