Access Index Match type question

[karcpr]

I need your help if you don't mind. I am working off access and all of of "excel/access guru's" have never heard of index/match, so I didn't feel that I could really take their advice seriously.

So, my question is this. I have an index match that is relatively simple. Get the qty, find where it goes, identify the cost and be done. Is there a way to do this in access without SQL or VBA? I am not terribly skilled with either language and see that others have have luck but it is through writing code and I don't know if I can reproduce the code if I had to do it again.

Is there anything without doing a boatload of stacked IIF statement formulas?

Qty Quantity 01 Quantity 02 Quantity 03 Quantity 04 Quantity 05 Quantity 06 Quantity 07 Quantity 08 Quantity 09 Quantity 10 Quantity 11 Quantity 12 Unit Cost 1 Unit Cost 2 Unit Cost 3 Unit Cost 4 Unit Cost 5 Unit Cost 6 Unit Cost 7 Unit Cost 8 Unit Cost 9 Unit Cost 10 Unit Cost 11 Unit Cost 12 35 1 20 100 250 500 999999999 999999999 999999999 999999999 999999999 999999999 999999999 121.88 121.88 103.6 103.6 103.6 17 1 20 100 250 500 999999999 999999999 999999999 999999999 999999999 999999999 999999999 121.88 121.88 103.6 103.6 103.6 85 1 20 100 250 500 999999999 999999999 999999999 999999999 999999999 999999999 999999999 121.88 121.88 103.6 103.6 103.6 92 5 25 75 125 275 500 999999999 999999999 999999999 999999999 999999999 999999999 250.17 230.92 196.28 196.28 196.28 470 1 20 100 250 500 999999999 999999999 999999999 999999999 999999999 999999999 999999999 39.24 39.24 33.35 33.35 33.35 125 1 20 100 250 500 999999999 999999999 999999999 999999999 999999999 999999999 999999999 39.24 39.24 33.35 33.35 33.35 <tbody> </tbody>

<tbody>
</tbody>

 

[Joe4]

Can you explain exactly what it is you are trying to do?
You posted some data, which is good. Now can you explain your expected results and the logic behind it?

 

[karcpr]

Hi Joe,
I am working with 250k+ parts. Below I attached what I would have done on excel. I could do this on excel but with the bandwidth, I would really like to do it on access. I can also easily nest some of these items but I wanted to keep it simple. Thank you for your reply.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

Qty

Quantity 01

Quantity 02

Quantity 03

Quantity 04

Quantity 05

Quantity 06

Quantity 07

Quantity 08

Quantity 09

Quantity 10

Quantity 11

Quantity 12

Unit Cost 1

Unit Cost 2

Unit Cost 3

Unit Cost 4

Unit Cost 5

Unit Cost 6

Unit Cost 7

Unit Cost 8

Unit Cost 9

Unit Cost 10

Unit Cost 11

Unit Cost 12

MATCH

match formula

Desired Break

break formula

Next column

next break cost

next break cost formula

Extended current pricing

extended current pricing formula

extended next break

extended next break formula

delta between break 1 and 2

delta between break 1 and 2 formula

% difference

% difference formula

35

1

20

100

250

500

999999999

999999999

999999999

999999999

999999999

999999999

999999999

121.9

121.9

103.6

103.6

103.6

121.88

INDEX(N3:Y3,MATCH(A3,B3:M3,1))

20

INDEX(B3:M3,MATCH(A3,B3:M3,1))

100

INDEX(B3:M3,MATCH(A3,B3:M3,1)+1)

103.6

INDEX(N3:Y3,MATCH(A3,B3:M3,1)+1)

$ 2,437.60

MATCH*Desired_break

$ 10,360.00

Next_column*Next_col_break_cost

$ 7,922.40

Extended_next_break-Extended_Current_Pricing

76%

1-(Extended current pricing/extended next break)

<colgroup><col><col span="5"><col span="7"><col span="5"><col span="4"><col span="3"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

-Chris

 

[Joe4]

What makes this very tough to do in Access is that your data table is not Normalized (working with data that is not Normalized can be very difficult to do in Relational Database like Access, SQL, or Oracle). If you can normalize your data, you can do what you want without having to use VBA.

In a normalized data table, you would not have multiple columns in a single record for each Quantity and Cost. Rather, you would have multiple records, with a single Quantity and Cost column, like this:

ItemNum	Quantity	Cost
01	1	$121.90
02	20	$121.90
03	100	$103.60
04	250	$103.60
05	500	$103.60
06	999999999	$0.00
07	999999999	$0.00
08	999999999	$0.00
09	999999999	$0.00
10	999999999	$0.00
11	999999999	$0.00
12	999999999	$0.00

<tbody>
</tbody>

Let's call this Table1. This is basically the table of the values you want to lookup. Now, let's create another table that has the value we want to lookup, like this:

LookupQuantity
35

<caption> Table2 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Then, we can create a Query using Table2 as the Source. To this Query, we add our LookupQuantity field, and then we add the following calculated fields:

Match_Formula: DMax("Quantity","Table1","Quantity < " & [LookupQuantity])+0
Break_Formula: DLookUp("Cost","Table1","Quantity =" & [Match_Formula])+0

The results of that query give you the values you are looking for.

LookupQuantity	Match_Formula	Break_Formula
35	20	121.9

<caption> Query1 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

 

[karcpr]

Joe,
Unfortunately, I will not have the opportunity to normalize the data. Since this is based on multiple parts which have multiple different dynamic breaks, it is not a possibility.

If it is not able to be done via formula, is there a fairly simplistic VBA code that can achieve this?

 

[Joe4]

The thing to understand about Access, is that it is not simply a replacement for Excel. It is a very different "beast", by nature. Where Excel is a spreadsheet program, Access is a relational database program. As such, they think and work very differently. When looking for things, Access wants to look in a single field, down multiple records (as opposed to across multiple fields in a single record, like you are trying to do).

Just like with Excel, though, you could come up with some complex formulas or VBA code to force it to do a lot of things the hard way. What you want to do can conceivably be done with VBA. For an experienced VBA Programmer, it probably wouldn't be too difficult, though I don't know if I would call it simple. If you have a single table, and the value you are looking up and all the fields you are looking into are contained within each single record (line), I would probably go the route of trying to create UDFs for each value you are trying to look up.

 

[karcpr]

That definitely puts it into perspective. Thank you for your help.