Need to perform a "lookup and classify" type query

[Beemermonkey]

First off, please be gentle with me, I am a total noob on Access/SQL/etc....

Here's what I'm trying to do.

let's say I have one table with data like this

Class range
Class ValLow ValHigh
----- ---- ----
Class1 0 50
Class2 51 65
Class3 66 77
Class4 78 90
etc.


Data to classify
Entry Val
----- ----
A 10
B 26
C 52
D 85
etc.


I want a query that returns:

Entry Class
----- -----
A Class1
B Class1
C Class2
D Class4


Now I suppose this could be done with an expression with a lot of IIF's, but there are potentially THOUSANDS of classes/ranges in my real data.

I doesn't seem that Access has the concept of a CASE statement, and even if it did, it would be a huge CASE statement.

In Excel this would be trivial using a temporary column and a Vlookup, but I have hundreds of thousands of records.

How can I create a query that will do this classification? I don't even know enough about how to do it to figure out what to google on...

 

[pbaldy]

Not that tricky with the right SQL. If you can post a sample db with a few records I'll throw it together.

 

[Beemermonkey]

That would be awesome

Not that tricky with the right SQL. If you can post a sample db with a few records I'll throw it together.

That's like saying that flying is not that tricky with the right gravity!

Here's a quick sample dataset...

The query would need to return

Values_to_classify.Item & Class_range.Class based on where the Values_to_classify.Value falls withy regard to Class_range.LowVal and Class_range.HighVal

In case the zip does not upload, here's some loose text of the example data:

Class_range
ID Class LowVal HighVal
1 Class1 0 5
2 Class2 6 31
...
33 Class33 1653 1663

Values_to_classify
ID Item Value
1 1 539
2 2 1242
...

800 800 1028

 

[pbaldy]

Paste this into SQL view of a new query and see if it does what you want:

SELECT Values_to_classify.ID, Values_to_classify.Item, Values_to_classify.Value, Class_range.Class
FROM Values_to_classify INNER JOIN Class_range ON Values_to_classify.Value >= Class_range.LowVal
AND Values_to_classify.Value <= Class_range.HighVal;

You realize there are gaps in your ranges (class 2 high is 31, class 3 low is 40)?

 

[pbaldy]

Matter of fact, change it to this, which will reveal the values that will not fall into a class (there are quite a few):

SELECT Values_to_classify.ID, Values_to_classify.Item, Values_to_classify.Value, Class_range.Class
FROM Values_to_classify LEFT JOIN Class_range ON Values_to_classify.Value >= Class_range.LowVal
AND Values_to_classify.Value <= Class_range.HighVal
ORDER BY Values_to_classify.ID

 

[Beemermonkey]

Paste this into SQL view of a new query and see if it does what you want:

SELECT Values_to_classify.ID, Values_to_classify.Item, Values_to_classify.Value, Class_range.Class
FROM Values_to_classify INNER JOIN Class_range ON Values_to_classify.Value >= Class_range.LowVal
AND Values_to_classify.Value <= Class_range.HighVal;

You realize there are gaps in your ranges (class 2 high is 31, class 3 low is 40)?

I didn't notice it, however, it was a q'n'd gen of data in Excel that I just imported into Access.

Thanks for the help. I'll give it a try ASAP and let you know how it works on the real data.

 

[Beemermonkey]

More help needed... Real world intervened!

OK, I need to come back to this as (as usual after jumping into more detail things got more ugly)

It's the same idea, I have a bunch of products that are in possibly multiple catalogs.

I have one table that details how the catalog sections are laid out (pages 1-5 are Category 1, pages 6-15 are Category 2, etc.)

CatalogSect.
CatalogID CatalogName CatalogSection SectionBeg SectionEnd

All fields are stored as text. The SectionBeg and SectionEnd are stored/ formatted like this:
999999 but there is an implied decimal like this 9999.99

and another table that tells me what catalogs, and what pages of those catalogs that a particular item is on.

CatalogPageCurrent.
CatalogID Item PageNumber

All fields are stored as text. The PageNumber is stored/formatted like this:
999999 but there is an implied decimal like this 9999.99

(the decimal format is to allow insertion of special page inserts, etc.)

The idea is that you look up CatalogPageCurrent.PageNumber & CatalogPageCurrent.CatalogID and look up the appropriate section name in CatalogSect

I need a query that spits out:
CatalogPageCurrent.Item CatalogSect.CatalogName CatalogSect.CatalogSection

Note that in live/real data that CatalogPageCurrent.Item may be returned in more than one catalog or more than one category.

Sample dataset is attached

Help...? Please...

 

[pbaldy]

This?

SELECT CatalogPageCurrent.Item, CatalogSect.CatalogName, CatalogSect.CatalogSect
FROM CatalogPageCurrent LEFT JOIN CatalogSect ON (CatalogPageCurrent.PageNumber >= CatalogSect.SectionBeg AND CatalogPageCurrent.PageNumber <= CatalogSect.SectionEnd) AND (CatalogPageCurrent.CatalogID = CatalogSect.CatalogID);

 

[Beemermonkey]

This?

SELECT CatalogPageCurrent.Item, CatalogSect.CatalogName, CatalogSect.CatalogSect
FROM CatalogPageCurrent LEFT JOIN CatalogSect ON (CatalogPageCurrent.PageNumber >= CatalogSect.SectionBeg AND CatalogPageCurrent.PageNumber <= CatalogSect.SectionEnd) AND (CatalogPageCurrent.CatalogID = CatalogSect.CatalogID);

But ow do I do a numeric comparison between items that are stored as text? They have to be stored as text, so I'd magine there's some way of converting them to numbers for the sake of this one query, right?

 

[jc1104]

There are various conversion methods you can use to convert strings.

Example:

intNumber = CInt ( strValue ) where strValue = "123" returns 123
strValue = CStr ( intNumber ) where intNumber = 123 returns "123"

CSng converts to a Single data type
CDbl converts to a Double data type

There are more, just search for one of these in the Help section

Obviously, you will have to work out which one you want and apply it in the correct location(s).

Such as:
CInt(CatalogPageCurrent.PageNumber) >= CInt(CatalogSect.SectionBeg)

 

[pbaldy]

Did my query return incorrect results? Since you have the text values stored with padded zeros, the comparison should still work. Otherwise, try jc's method (given your numbers, you want CLng rather than CInt).

 

[Beemermonkey]

Did my query return incorrect results? Since you have the text values stored with padded zeros, the comparison should still work. Otherwise, try jc's method (given your numbers, you want CLng rather than CInt).

I just had a chance to try it on the live data and it seemed to work. I see what you're talking about with the leading zeros a they sort the same numerically as alphabetically.

Thanks again for your help!