DLookup using multiple criteria?

[Bobp3114]

I have this table:

etc
and I need to look up, say,the figure ($) in ZoneD for the KGWeight say 2.0 and get the result of 39.21
DLookup("[ZoneD]", "tblWeightZonesFedEx", "[KGWeights] = " & Me.txtCalculatingWeight) would be fine if I only had ZoneD but there are approx 20 such zones.
I know which zone I want as I get it from a combo box query (Me.txtFreightIndexNumber = Me.cboChooseProduct.Column(40)), but how do I pass it to the DLookup?
All help appreciated
Bob

 

[plog]

You've incorrectly set up your table. When you feel the need to have a series of fields differentiated by a number or letter suffix(e.g. ZoneA, ZoneB, ZoneC etc), you need to reevaluate. You want your tables to accommodate data vertically (with more records) and not horizontally (with more fields).

TblWeightZonesFedex should have this structure:

ZoneID, auto number, primary key
KGWeights, number, same as in it now
ZoneLetter, text, will hold letter of zone
ZonePrice, currency, will hold actual value now in all those fields

Thats it. Those 4 fields will hold all the data currently in that table. When populated it will look like this:

ZoneID, KGWeights, ZoneLetter, ZonePrice
1, 1.0, A, 27.85
2, 1.0, B, 91.62
3, 1.0, C, 92.92
4, 1.0, D, 27.59
...

Instead of 1 record with 20 fields for all the data of a weight, you will have 20 records.

By doing it this way you can now use the zone value in your criteria of your lookup:

DLookup("[ZonePrice]", "tblWeightZonesFedEx", "[KGWeights] = " & Me.txtCalculatingWeight & " AND [ZoneLetter] = '" & me.txtZone & "'")

---

 

[arnelgp]

is the the column,boChooseProduct.Column(40) starts with 1 as "A"?
if so you can use this:

DLookup("[Zone" & Chr(64 + Me.cboChooseProduct.Column(40)) & "]", "tblWeightZonesFedEx", "[KGWeights] = " & Me.txtCalculatingWeight)

 

[Eugene-LS]

but how do I pass it to the DLookup?

Dim sLookUpField As String, vVal
    
    Select Case Me.cboChooseProduct.Column(40)
        Case 1: sLookUpField = "[ZoneA]"
        Case 2: sLookUpField = "[ZoneB]"
        Case 3: sLookUpField = "[ZoneC]"
        'Case  ... : sLookUpField = "[Zone ...]"
        Case Else: Exit Sub
    End Select

    vVal = DLookup(sLookUpField, "tblWeightZonesFedEx", "[KGWeights] = " & Me.txtCalculatingWeight)

 

[June7]

A combobox with 40+ columns - why?

Exactly what values are in Column(40)?

 

[XPS35]

A combobox with 40+ columns - why?

Because the table is not normalized

 

[arnelgp]

Because the table is not normalized

too early to tell without actually seeing it.
what if he is using a Joined query?

 

[ebs17]

With a table structure like the one @plog shows (unpivot to the source table), not only is the selection easy, but the table can also be easily maintained by the user via an additional user interface => no reprogramming necessary when changes are made.
Database-compliant structures => mostly simple queries

 

[XPS35]

too early to tell without actually seeing it.

Look at post #1 to see the (a) table.

 

[June7]

Posted table is not the table used for combobox RowSource. If it were, DLookup would not be necessary.

 

[Bobp3114]

Hi Guys
i will add a few bits that may help
The data in the table comes from a PDF converted to Excel then into the table

thru to Z

Converted Spreadsheet:

etc

 

[Pat Hartman]

The data in the table comes from a PDF converted to Excel then into the table

It comes to you that way because the matrix version of the data is convenient for humans. @Eugene-LS gave you a solution if you want to keep the unnormalized structure. Why you would do that is beyond me but whatever.

Once the image is converted to Excel, you can very easily write a procedure to take the Excel data and normalize it as you create a current version of the rates. Normalizing the data allows you to also use a single rate table regardless of what service the rate is for. Do you want help with the code to normalize the spreadsheet?

 

[arnelgp]

Look at post #1 to see the (a) table.

the combobox on post#1 is named cboChooseProduct, and i believe refers to product (table/query?) and not the table (which has 15 fields not 40) that is shown on that post.

 

[Bobp3114]

WOW! OK guys this is new to me...trying to understand this normalising process...do I apply it to the spreadsheet?
Can you point me to a good utube etc. Be kind and help with the procedure necessary. I am not a pro so keen to learn
Bob

 

[June7]

That converted spreadsheet is still in Excel?

You could normalize the data with Excel before importing to Access. Excel PowerQuery could possibly accomplish that with an UNPIVOT.

Before trying to restructure data, you should get an understanding of relational database principles and what normalization means.

Will pulling this data from PDF be a regular activity or a one-time event?

 

[Bobp3114]

Data will be pulled from the PDF whenever FedEx changes their rates, which could be yearly or, depending on world volatility every 3 months.
I have a good understanding of rational database but normalization is new

 

[arnelgp]

I have a good understanding of rational database but normalization is new

 

[Gasman]

WOW! OK guys this is new to me...trying to understand this normalising process...do I apply it to the spreadsheet?
Can you point me to a good utube etc. Be kind and help with the procedure necessary. I am not a pro so keen to learn
Bob

Here is a link I keep bookmarked.

Solved Thread 'Normalisation'

Aug 6, 2024

Micron on here posted a few links on another site, the first one being for Normalisation, which Excel users to come to Access have never heard of.
He also posted a good few other links.

Rather than post to his original post (as it is on another site) anymore, I am posting them here and so will refer to this post from now on.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV...

• Gasman
• Replies: 1
• Forum: General

• 

 

[June7]

Normalization is the means to optimize advantages offered by relational database structure. How far to take normalization depends on requirements. "Normalize until it hurts, denormalize until it works."

Your worksheet is very not normalized. Same type of data (weight charge) spread over multiple fields is not normalized. Yes, it's a friendly lookup tool hanging on the wall but for programming not so friendly. Right now you (actually, FedEx) appear to have zones A through Z. What if you need to add another? That would mean modifying table, queries, forms, reports, code. A normalized structure could produce the non-normalized structure when needed - that would be with a CROSSTAB query.

If you keep the non-normalized version, it can be dealt with as shown by posts 3 and 4.