I'd like to know if A2007 has interval keys.

Imagine fldKey as a primary key:
fldKey fldValue
10 A
20 B
30 C
40 D

Any value <=10 should point me to A, values (>10 And <=20) should point to B,
etc.


TIA

Vlado

Vlado,

I'm not sure if this is the kind of thing you mean. But you can make a table with two fields for the start and end values for the range, e.g.
fldValue fldStart fldEnd
A 0 10
B 10 20
C 20 30
D 30 40

Then, you can add this table to a query, where you have a number you want to return the corresponding fldValue, so in the criteria of the query you can put like this...
>[fldStart] And <=[fldEnd]

Just curious, does your fld prefix mean 'field'?

TYVM for your respond, Steve. I know that there' an SQL solution for my problem. I'd appreciate interval keys anyway. ;-) We had interval keys & other goodies in PC FAND (DOS based relational database) very long time ago.

P.S. "fld" was meant as a prefix in table declaration:
fldKey (dbInteger)
fldValue (dbText,1)

You may want to investigate the use of the Partition() function ... it will return a string in the form similar to this:

10: 20

For numbers between 10 and 20 (if the parameters are passed correctly), then you can use string functions to parse out the first value then, you will have the "Parent" key.

I'm not aware of anything like this in any modern database.

SQL would generally be used.

Back here after some time...

I've been investigating Partition function with no luck.
My scenario is:
1) I have lookup tables with a key field and two dates: PlatiOd and PlatiDo (DateStart and DateEnd).
2) Data table (Denik) has a field DATUUP and 9 fields pointing to various lookup tables with PlatiOd and PlatiDo.
3) Each key-field in Denik has its own lookup table.
4) I need to evaluate Denik.DATUUP and a key-field against an appropriate lookup table to get correct information, ie. values of other fields from the lookup table.

Example:


It would be an easy task for a database with interval keys. But I can't make it work in "modern" Access.
Thank you very much for your time in advance.

"SQL query should return something like this"

The SQL solution Steve provided would work in YOUR SQL query. Just a thought.

Thank you very much for your response.
I'm affraid the criteria won't work correctly since I need all records from Denik to be compared against Cis3Uc04.
Maybe I'm missing something.

I've created small demonstration DB, see the attachment. I'm not able to create a query that meets my needs (3 records in Denik should output 3 records in the query). I can't think about DLookups in the query since:
1) there may be >10.000 records in Denik a month
2) there are 9 lookup tables with DateStart and DateEnd (PlatoOd, PlatiDo)
3) there are other lookup tables that join Denik

Hi Vlado

To retrieve the 3 records you need to have your structure somethinmg like the attached

tblDenik - PK - ID_Denik Autonumber
- linked to tblCis3Uc04 - FK - ID_Denik

Hope this helps?

Mike

Edited by: mike60smart on Thu Nov 6 10:09:26 EST 2008.

Hi,

TYVM for your response, Mike. Unfortunatelly, there's M:N relation, ie. M in Cis3Uc04, N in Denik. At the moment I'm trying "nested" query according to a post in one of Czech DB forums: http://forum.builder.cz/read.php?21,2804163. Suppose you speak Czech... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)