fill in the blanks query

[Guest]

a library with nearly 10000 books (or series) in 80 Book cases
each book has a uniqe id number
i have a library index table with 3 columns

From Serias To Serias BookCase#
1 3 1
4 5 2
6 7 4
8 8 1
9 9 4
10 14 3
15 16 2

i need for each serias the exact bookcase number
the rasult should be like this

Serias BookCase#
1 1
2 1
3 1
4 2
5 2
6 4
7 4
8 1
9 4
10 3
11 3
12 3
13 3
14 3
15 2
16 2

 

[Tom Ellison]

Dear Aron:

Could it be as simple as this:

SELECT DISTINCT [From Serias], [BookCase#]
FROM [library index]
ORDER BY [From Serias]

Tom Ellison

 

[Guest]

No
This would not fill out the blanks

Dear Aron:

Could it be as simple as this:

SELECT DISTINCT [From Serias], [BookCase#]
FROM [library index]
ORDER BY [From Serias]

Tom Ellison

a library with nearly 10000 books (or series) in 80 Book cases
each book has a uniqe id number
i have a library index table with 3 columns

From Serias To Serias BookCase#
1 3 1
4 5 2
6 7 4
8 8 1
9 9 4
10 14 3
15 16 2

i need for each serias the exact bookcase number
the rasult should be like this

Serias BookCase#
1 1
2 1
3 1
4 2
5 2
6 4
7 4
8 1
9 4
10 3
11 3
12 3
13 3
14 3
15 2
16 2

 

[Tom Ellison]

Dear Aron:

I believe I can see what you want.

The values of [From Serias] of 2, 3, 5, 7, 11, 12, 13, and 14 are missing.
Using the largest existing [From Serias] less than each of these values, you
want to retrieve the [BookCase#] associated with that. Is this correct.

If you do not have it, you will need a table that provides these values.
The query must have a source from which to provide them. A LEFT JOIN from
that to the [library index] will have NULL for [BookCase#] which can be
filled by a subquery that finds the next lower value of [From Serias] which
can then locate the [BookCase#] associated with it. Isn't that the one you
want?

SELECT N.Number, Nz(L.[BookCase#],
(SELECT L1.[BookCase#]
FROM [library index] L1
WHERE L1.[From Serias] =
(SELECT MAX(L2.[From Serias])
FROM [library index] L2
WHERE L2.[library index] < L.[library index]))
AS [BookCase]
FROM AllNumbers N
LEFT JOIN [library index] L
ON L.[From Serias] = N.Number
ORDER BY N.Number

This presumes you supply the table AllNumbers with the column Number and
rows for each value you want in the query results.

A query like this often won't work with Access Jet. If you experience this,
it can be divided into two queries that probably will work. I would need a
sample database with the [library index] table and the AllNumbers table in
order to work this out for you. We'll arrange that if you need it.

Tom Ellison

No
This would not fill out the blanks

Dear Aron:

Could it be as simple as this:

SELECT DISTINCT [From Serias], [BookCase#]
FROM [library index]
ORDER BY [From Serias]

Tom Ellison

a library with nearly 10000 books (or series) in 80 Book cases
each book has a uniqe id number
i have a library index table with 3 columns

From Serias To Serias BookCase#
1 3 1
4 5 2
6 7 4
8 8 1
9 9 4
10 14 3
15 16 2

i need for each serias the exact bookcase number
the rasult should be like this

Serias BookCase#
1 1
2 1
3 1
4 2
5 2
6 4
7 4
8 1
9 4
10 3
11 3
12 3
13 3
14 3
15 2
16 2

 

[Michel Walsh]

Hi,


If you have a driver table, Iotas, one field, Iota, with values from 0 to,
say, 999, then


SELECT myTable.[From Serias]+ Iotas.Iota, [BookCase#]
FROM myTable INNER JOIN Iotas ON Iotas.Iota <=myTable.[ToSerias] -
myTable.[From Serias]


should do.


Hoping it may help,
Vanderghast, Access MVP

No
This would not fill out the blanks

Dear Aron:

Could it be as simple as this:

SELECT DISTINCT [From Serias], [BookCase#]
FROM [library index]
ORDER BY [From Serias]

Tom Ellison

a library with nearly 10000 books (or series) in 80 Book cases
each book has a uniqe id number
i have a library index table with 3 columns

From Serias To Serias BookCase#
1 3 1
4 5 2
6 7 4
8 8 1
9 9 4
10 14 3
15 16 2

i need for each serias the exact bookcase number
the rasult should be like this

Serias BookCase#
1 1
2 1
3 1
4 2
5 2
6 4
7 4
8 1
9 4
10 3
11 3
12 3
13 3
14 3
15 2
16 2

 

[Guest]

Can you explan me this query so i can tweak it a little bit
this query covers only 85% of the book serias

Hi,


If you have a driver table, Iotas, one field, Iota, with values from 0 to,
say, 999, then


SELECT myTable.[From Serias]+ Iotas.Iota, [BookCase#]
FROM myTable INNER JOIN Iotas ON Iotas.Iota <=myTable.[ToSerias] -
myTable.[From Serias]


should do.


Hoping it may help,
Vanderghast, Access MVP

No
This would not fill out the blanks

Dear Aron:

Could it be as simple as this:

SELECT DISTINCT [From Serias], [BookCase#]
FROM [library index]
ORDER BY [From Serias]

Tom Ellison


a library with nearly 10000 books (or series) in 80 Book cases
each book has a uniqe id number
i have a library index table with 3 columns

From Serias To Serias BookCase#
1 3 1
4 5 2
6 7 4
8 8 1
9 9 4
10 14 3
15 16 2

i need for each serias the exact bookcase number
the rasult should be like this

Serias BookCase#
1 1
2 1
3 1
4 2
5 2
6 4
7 4
8 1
9 4
10 3
11 3
12 3
13 3
14 3
15 2
16 2

 

[Michel Walsh]

Hi,

A Cartesian join produces the mix of all records of one table with all
records of the second table. So, if tableA has one field f1 with 3 records,
with values 10, 20, 30; while tableB, one field g1, two records with values
1 and 2, then the join:


SELECT tableA.f1, tableB.g1
FROM tableA, tableB

produces 3*2 = 6 records:

10 1
10 2
20 1
20 2
30 1
30 2


An inner join does LOGICALLY the same, but keep only those mix that
evaluates the ON clause to True. You are NOT limited to comparison with =,
any logical expression that is a Boolean result (true, false, null) can do.
As example:


SELECT tableA.f1, tableB.g1
FROM tableA INNER JOIN tableB ON tableA.f1 >= 10*tableB.g1


10 1
20 1
20 2
30 1
30 2


note that (10 2) is not present anymore since the ON clause, tableA.f1 >=
10*tableB.g1, is false with f1=10 and g1=2.

That was the theory.



In OUR actual case, the second table, Iotas, has values from 0 to 999, so,
if a mix is such that startingSequence + iota > endingSequece, then, we
DON'T want to keep that "mix" between the table of sequences and the table
iotas. So our ON clause is :

ON StartingSequence + iota <= endingSequence

or, simple mathematical arrangement,

ON iota <= EndingSequence-StartingSequence


which could allow the use of the index on Iotas.iota if there is one, while
the first formulation is unlikely to allow it. (A column involved in a
computed expression, like + or -, is unlikely to see its index used).

Everything else is just question of syntax, or to accommodate the
presentation.


StartingSequence+Iota will supply the sequence:


StartingSequence+0
StartingSequence+1
StartingSequence+2
....up to...
StartingSequence+m = EndingSequence




Hoping it may help,
Vanderghast, Access MVP

Can you explan me this query so i can tweak it a little bit
this query covers only 85% of the book serias

Hi,


If you have a driver table, Iotas, one field, Iota, with values from 0
to,
say, 999, then


SELECT myTable.[From Serias]+ Iotas.Iota, [BookCase#]
FROM myTable INNER JOIN Iotas ON Iotas.Iota <=myTable.[ToSerias] -
myTable.[From Serias]


should do.


Hoping it may help,
Vanderghast, Access MVP

No
This would not fill out the blanks

:

Dear Aron:

Could it be as simple as this:

SELECT DISTINCT [From Serias], [BookCase#]
FROM [library index]
ORDER BY [From Serias]

Tom Ellison


a library with nearly 10000 books (or series) in 80 Book cases
each book has a uniqe id number
i have a library index table with 3 columns

From Serias To Serias BookCase#
1 3 1
4 5 2
6 7 4
8 8 1
9 9 4
10 14 3
15 16 2

i need for each serias the exact bookcase number
the rasult should be like this

Serias BookCase#
1 1
2 1
3 1
4 2
5 2
6 4
7 4
8 1
9 4
10 3
11 3
12 3
13 3
14 3
15 2
16 2

 

[Guest]

Thanks a million - it works wonderful

Hi,

A Cartesian join produces the mix of all records of one table with all
records of the second table. So, if tableA has one field f1 with 3 records,
with values 10, 20, 30; while tableB, one field g1, two records with values
1 and 2, then the join:


SELECT tableA.f1, tableB.g1
FROM tableA, tableB

produces 3*2 = 6 records:

10 1
10 2
20 1
20 2
30 1
30 2


An inner join does LOGICALLY the same, but keep only those mix that
evaluates the ON clause to True. You are NOT limited to comparison with =,
any logical expression that is a Boolean result (true, false, null) can do.
As example:


SELECT tableA.f1, tableB.g1
FROM tableA INNER JOIN tableB ON tableA.f1 >= 10*tableB.g1


10 1
20 1
20 2
30 1
30 2


note that (10 2) is not present anymore since the ON clause, tableA.f1 >=
10*tableB.g1, is false with f1=10 and g1=2.

That was the theory.



In OUR actual case, the second table, Iotas, has values from 0 to 999, so,
if a mix is such that startingSequence + iota > endingSequece, then, we
DON'T want to keep that "mix" between the table of sequences and the table
iotas. So our ON clause is :

ON StartingSequence + iota <= endingSequence

or, simple mathematical arrangement,

ON iota <= EndingSequence-StartingSequence


which could allow the use of the index on Iotas.iota if there is one, while
the first formulation is unlikely to allow it. (A column involved in a
computed expression, like + or -, is unlikely to see its index used).

Everything else is just question of syntax, or to accommodate the
presentation.


StartingSequence+Iota will supply the sequence:


StartingSequence+0
StartingSequence+1
StartingSequence+2
....up to...
StartingSequence+m = EndingSequence




Hoping it may help,
Vanderghast, Access MVP

Can you explan me this query so i can tweak it a little bit
this query covers only 85% of the book serias

Hi,


If you have a driver table, Iotas, one field, Iota, with values from 0
to,
say, 999, then


SELECT myTable.[From Serias]+ Iotas.Iota, [BookCase#]
FROM myTable INNER JOIN Iotas ON Iotas.Iota <=myTable.[ToSerias] -
myTable.[From Serias]


should do.


Hoping it may help,
Vanderghast, Access MVP


No
This would not fill out the blanks

:

Dear Aron:

Could it be as simple as this:

SELECT DISTINCT [From Serias], [BookCase#]
FROM [library index]
ORDER BY [From Serias]

Tom Ellison


a library with nearly 10000 books (or series) in 80 Book cases
each book has a uniqe id number
i have a library index table with 3 columns

From Serias To Serias BookCase#
1 3 1
4 5 2
6 7 4
8 8 1
9 9 4
10 14 3
15 16 2

i need for each serias the exact bookcase number
the rasult should be like this

Serias BookCase#
1 1
2 1
3 1
4 2
5 2
6 4
7 4
8 1
9 4
10 3
11 3
12 3
13 3
14 3
15 2
16 2