Categorise records based on two fields (text and between two values)

[Stormin]

Hi all,

I am looking to categorise the records in my database based on two fields, say [Rec_Type] and [Rec_Age].

I have my categories in a table like this:

[Rec_Type]	[Age_Min]	[Age_Max]	[Cat_Desc]
TypeFoo		0		12		TypeFoo 0-12 months
TypeFoo		13		24		TypeFoo 13-24 months
TypeMan		0		4		TypeMan 0-4 months
TypeMan		5		8		TypeMan 5-8 months
TypeMan		9		12		TypeMan 9-12 months
TypeMan		13		24		TypeMan 13-24 months
TypeChu		0		24		TypeChu 0-24 months
TypeChu		25		99		TypeChu 25+ months

I am looking to build a SELECT query that can compare Main.[Rec_Type] with Cat.[Rec_Type] along with if Main.[Rec_Age] is between Cat.[Age_Min] & Cat.[Age_Max], and output Cat.[Cat_Desc].

I'm drawing a blank on the first step, can anyone point me in the right direction?

Thanks

 

[plog]

This can be done, but you are going to have to manually edit the SQL code.

First, set up your query using the Design Viewer thingy. Bring in your tables, bring down the fields you want, apply any over-riding criteria you might need. Then use the viewer to make the JOIN between your Rec_Type fields and then JOIN the Age field to both the Age_Min and Age_Max field. Get the query just like you want it and save it.

Next, open it up in SQL view and change the portion of the JOIN that looks something like this:

... ON Main.Rec_Type = Cat.Rec_Type AND Main.Age = Cat.Age_Min AND Main.Age = Cat.Age_Max ...

To this:

... ON Main.Rec_Type = Cat.Rec_Type AND Main.Age >= Cat.Age_Min AND Main.Age <= Cat.Age_Max ...

Do that and your query shoudl work. Unfortunately, the query design viewer thingy can't support this type of JOIN, so you can no longer use it to modify the query and have it maintain that JOIN.

 

[Stormin]

Thanks plog; I knew it would be something like that I was just having an empty brain moment (Friday afternoon effect).

It works, of course