UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup Aganst Column And Row, Access 2016    
 
   
ordnance1
post Mar 13 2018, 11:13 PM
Post#1



Posts: 541
Joined: 7-May 11



How do I do a dlookup against this table. For example if me.txt_Percent = 66% and me.txt_AgeDiff =-14 the resulting value should be 0.958

Attached File  Untitled_picture.png ( 25.78K )Number of downloads: 6


Go to the top of the page
 
MadPiet
post Mar 13 2018, 11:43 PM
Post#2



Posts: 2,494
Joined: 27-February 09



If you did it as (AgeDiff, Percent, Value), so you only ever had 3 columns, then you could just join on AgeDiff and Percent in your query, and you'd be done. You would just have something like

CODE
DataTable t INNER JOIN LookupTable lu ON (t.AgeDiff = lu.AgeDiff AND t.Percent=lu.Percent)


but don't use reserved words in your table names... that could cause bad things to happen. My point is that you can join two tables on more than one column... so you could just use a plain JOIN instead of one of those LOOKUP headaches.
Go to the top of the page
 
projecttoday
post Mar 14 2018, 05:27 AM
Post#3


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


MadPiet, interesting idea. Please explain how you decide which value goes in the Percent column?

Go to the top of the page
 
projecttoday
post Mar 14 2018, 05:29 AM
Post#4


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


Not what you're looking for but you could do a UDF with a case select (or if, else if, else if) and 3 dlookups.
Go to the top of the page
 
LPurvis
post Mar 14 2018, 11:15 AM
Post#5


UtterAccess Editor
Posts: 16,280
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

You could (and, it can be well argued, should) normalise the table. Ideally permanently, but if not then a query could do that for you.
Its definition could be something like:
CODE
SELECT [Age Diff], 100 As PercentVal, [100%] As Result
FROM TableName
UNION ALL
SELECT [Age Diff], 66 As PercentVal, [66%] As Result
FROM TableName
UNION ALL
SELECT [Age Diff], 50 As PercentVal, [50%] As Result
FROM TableName

Your Dlookup against that would then be trivial.
CODE
=DLookup("Result", "QueryName", "[Age Diff] = " & me.txt_AgeDiff  & " AND  PercentVal = " & Val(me.txt_Percent))


Cheers
Go to the top of the page
 
projecttoday
post Mar 14 2018, 12:08 PM
Post#6


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


Okay, I get it now. Ignore post #3 please.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 01:45 AM