Lookup/Match function help please

[Asoul]

I am trying to perform, in Access, the equivalent of a HLookup combined with a Match function in Excel. Converting and automating a process I originated in Excel into Access, but its been several years since I used Access regularly. Here's the data and what I am trying to do....

Table [Constants] contains field "UniqKey" with the following fields which represent all possible season codes (S1-S7):

UniqKey S1__S2__ S3__S4__S5__S6__S7
1_______10__10__9___8___8___8___9
2_______10__10__9___8___8___8___9
3_______8___8___8___8___8___8___8
4_______10__10__10__10__10__10__10
5_______5___5___5___4___4___4___5


I want to query the value of "SsonCd" in another table called [PrdctnData] that could be any individual value of S1-S7 to return the result "GrwWks" in that field that matches the unique key.

UniqKey SsonCd
1_______S2
2_______S3
3_______S4
4_______S2
5_______S5

It should return:
UniqKey GrwWks
1_______10
2_______9
3_______8
4_______10
5_______4

This is:
=HLOOKUP(AO6,SsonLkup,MATCH(C6,UniqueKey,FALSE),FALSE)
in Excel
Where
AO6=cell reference of "SsonCd"
SsonLkup = named array of S1-S7
C6 = UniqKey
UiqueKey = named array of UniqKey's

but I am struggling with the Access/Sql equivalent.

Attempting to return the production time of units that can vary in production time depending upon the time of year they go into production.

Thanks in advance -

Asoul

 

[pbaldy]

The proper normalized structure of that table would be

UniqKey   Season   Value
1              S1          10
1              S2          10
1              S3           9
...

Then your query is simple.

 

[plog]

This is something you are trying to automate in Access? Then you should set up a proper table structure. Anytime you have numerated fields (i.e. S1, S2, etc), you are probably doing it wrong.

Constants should have 3 fields:

UniqKey--same as it is now
Season---instead of having numbered fields you would place the number in here
ValueField--this would hold the individual values in there now.

This is what constants should look like:

UniqKey, Season, ValueField
1, 1, 10
1, 2, 10
1, 3, 9
.
.
.
1, 7, 9
2, 1, 10
2, 2, 10

Once your data looks like that, getting the results you want will be trivial.

 

[Asoul]

pbaldy & plog,
I get that...
But my data from the source system already outputs the data in the one row across multiple columns along with about 20 other columns ofcomponent data of the over 27,000 UniqKey's.
Assuming a table with over 175K rows would not slow the database, is there a way to automate this reconfiguration?

Asoul

 

[pbaldy]

A UNION query:

SELECT UniqKey, "S1" AS Season, S1 As GrwWks
FROM TableName
UNION ALL
SELECT UniqKey, "S2" AS Season, S2 As GrwWks
FROM TableName
UNION ALL
...

 

[Asoul]

Thank you Paul!!!!!
Wow - took me a minute to translate the SQL into the UNION query but after that worked like a charm.

Many, many, thanks!

Asoul

 

[pbaldy]

Happy to help Asoul! I think you'll have a lot easier time querying that data than the original.