Exanding Query?

[Steve]

Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to show all the
combinations for each possible object value. Row and column order aren't
important. I've narrowed the problem down to knowing the product of the
variations of each object. I know this would be easy to do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of stuff, but nothing
that works so far. Is it possible to write a VBA function to act like a
query?

 

[Michel Walsh]

Hi


I am completely lost with your example... where your 14 comes from,
among other questions.

To get all the possible mix between table1.f1 and table2.g2, use



SELECT f1, g2
FROM Table1, Table2


nothing more complicated than that, really.




Hoping it may help
Vanderghast, Access MVP

 

[Steve]

Well, it is a bit more complicated that that. If I was trying to get all
combinations of things from multiple columns, and if the number of things I
was trying to get combinations of was constant and know, then I could use a
self join. The two problems with that are a) I have only one column, and the
things I want combinations of are in that column, and b) the number of
things is not constant, although it will be 1, 2 or 3.

My example didn't come through very well, but in the third table in my
earlier message, what looks like ##obj1## is actually three columns that I
know I can make a crosstab out of.

The 14 comes from there being 7 varieties of obj1, and 2 varieties of obj2,
7*2=14. I will have 1, 2, or 3 different objects, and in different ranges
(minval and maxval).

Having ground myself against this for the better part of a day, I'm becoming
convinced it can't be done with queries.

 

[Guest]

I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
.....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
..... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need

 

[Steve]

This is certainly a step in the right direction, but it assumes the number
of object types I have to be static, and they aren't... The source table,
which lists the objects and their min & max values lists them in rows
because I will have multiple kinds, and in multiple combinations. The object
types are actually "Units" "Webs" "Ribbons" and whatever else marketing
comes up with in the future ;-), and sometimes I will need to combine 1 to 6
Units with 1 to 2 Webs, or just 1 to 2 Webs, or 1 to 6 Units with 1 to 2
Webs with 1 to 8 Ribbons... Each set of these dimensions has an id (a key
into a different table)

So, I wanted to start with a source table like:

ID Dimension Minvalue Maxvalue
1 Units 1 4
1 Webs 1 2
2 Ribbons 1 8
3 Units 1 10
3 Webs 1 2
3 Ribbons 1 8

And end up with a table like:

ID Units Webs Ribbons (the columns would be
created automatically depending on what rows are in the first table)
1 1 1 0
1 2 1 0
1 3 1 0
1 4 1 0
1 1 2 0
1 1 2 0
1 2 2 0
1 3 2 0
1 4 2 0
2 0 0 1
2 0 0 2
2 0 0 3
2 0 0 4
2 0 0 5
2 0 0 6
2 0 0 7
2 0 0 8
<additional rows not shown>

Your second query is farther than I got, and if I could figure out some way
to automatically create the columns in that query, it would be the bomb....

I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
.... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need

-----Original Message-----
Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to show all the
combinations for each possible object value. Row and column order aren't
important. I've narrowed the problem down to knowing the product of the
variations of each object. I know this would be easy to do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of stuff, but nothing
that works so far. Is it possible to write a VBA function to act like a
query?


.

 

[Michel Walsh]

Hi,


If you have a table Iotas, one column, Iota, with values from 1 to n ( n =
largest integer you would use), make a first query, q1:

SELECT ID, Dimension, Iota
FROM myTable INNER JOIN Iotas
ON (Iotas.iota BETWEEN myTable.MinValue AND myTable.MaxValue)




Next, q2:


SELECT a.ID, a.Iota As Qt1, a.Dimension As D1, b.Iota As Qt2, b.Dimension As
D2, c.Iota AS Qt3, c.Dimension AS D3
FROM ( q1 As a LEFT JOIN q1 As b ON (a.ID=b.ID) AND (a.Dimension <
b.Dimension))
LEFT JOIN q1 As b ON (a.ID=c.ID) AND (b.ID=c.ID) AND (b.Dimension <
c.Dimension)


Finally, just before the crosstab, we normalize the previous data from q2,
to make q3:


SELECT ID, Qt1 As qty, D1 As thePivot FROM q2
UNION ALL
SELECT ID, Qt2, D2 FROM q2
UNION ALL
SELECT ID, Qt3, D3 FROM q2



Note that I assume you have no more than three items (records) for the same
group (sharing the same ID ) in your original table.

The Crosstab, on q3, groups on ID, PIVOT on ThePivot, and aggregate with
LAST on qty.



Hoping it may help,
Vanderghast, Access MVP

This is certainly a step in the right direction, but it assumes the number
of object types I have to be static, and they aren't... The source table,
which lists the objects and their min & max values lists them in rows
because I will have multiple kinds, and in multiple combinations. The object
types are actually "Units" "Webs" "Ribbons" and whatever else marketing
comes up with in the future ;-), and sometimes I will need to combine 1 to 6
Units with 1 to 2 Webs, or just 1 to 2 Webs, or 1 to 6 Units with 1 to 2
Webs with 1 to 8 Ribbons... Each set of these dimensions has an id (a key
into a different table)

So, I wanted to start with a source table like:

ID Dimension Minvalue Maxvalue
1 Units 1 4
1 Webs 1 2
2 Ribbons 1 8
3 Units 1 10
3 Webs 1 2
3 Ribbons 1 8

And end up with a table like:

ID Units Webs Ribbons (the columns would be
created automatically depending on what rows are in the first table)
1 1 1 0
1 2 1 0
1 3 1 0
1 4 1 0
1 1 2 0
1 1 2 0
1 2 2 0
1 3 2 0
1 4 2 0
2 0 0 1
2 0 0 2
2 0 0 3
2 0 0 4
2 0 0 5
2 0 0 6
2 0 0 7
2 0 0 8
<additional rows not shown>

Your second query is farther than I got, and if I could figure out some way
to automatically create the columns in that query, it would be the bomb....

I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
.... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need

-----Original Message-----
Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to show all the
combinations for each possible object value. Row and column order aren't
important. I've narrowed the problem down to knowing the product of the
variations of each object. I know this would be easy to do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of stuff, but nothing
that works so far. Is it possible to write a VBA function to act like a
query?


.

 

[Steve]

Well, it sure seems like it should work, but when I run q2 Access tells me
"Join Expression not supported". I should note that the last 'AS b' in q2 I
replaced with 'AS c', because it didn't look like you were defining anything
called 'c'....

Hi,


If you have a table Iotas, one column, Iota, with values from 1 to n ( n =
largest integer you would use), make a first query, q1:

SELECT ID, Dimension, Iota
FROM myTable INNER JOIN Iotas
ON (Iotas.iota BETWEEN myTable.MinValue AND myTable.MaxValue)




Next, q2:


SELECT a.ID, a.Iota As Qt1, a.Dimension As D1, b.Iota As Qt2, b.Dimension As
D2, c.Iota AS Qt3, c.Dimension AS D3
FROM ( q1 As a LEFT JOIN q1 As b ON (a.ID=b.ID) AND (a.Dimension <
b.Dimension))
LEFT JOIN q1 As b ON (a.ID=c.ID) AND (b.ID=c.ID) AND (b.Dimension <
c.Dimension)


Finally, just before the crosstab, we normalize the previous data from q2,
to make q3:


SELECT ID, Qt1 As qty, D1 As thePivot FROM q2
UNION ALL
SELECT ID, Qt2, D2 FROM q2
UNION ALL
SELECT ID, Qt3, D3 FROM q2



Note that I assume you have no more than three items (records) for the same
group (sharing the same ID ) in your original table.

The Crosstab, on q3, groups on ID, PIVOT on ThePivot, and aggregate with
LAST on qty.



Hoping it may help,
Vanderghast, Access MVP

This is certainly a step in the right direction, but it assumes the number
of object types I have to be static, and they aren't... The source table,
which lists the objects and their min & max values lists them in rows
because I will have multiple kinds, and in multiple combinations. The object
types are actually "Units" "Webs" "Ribbons" and whatever else marketing
comes up with in the future ;-), and sometimes I will need to combine 1

to

6
Units with 1 to 2 Webs, or just 1 to 2 Webs, or 1 to 6 Units with 1 to 2
Webs with 1 to 8 Ribbons... Each set of these dimensions has an id (a key
into a different table)

So, I wanted to start with a source table like:

ID Dimension Minvalue Maxvalue
1 Units 1 4
1 Webs 1 2
2 Ribbons 1 8
3 Units 1 10
3 Webs 1 2
3 Ribbons 1 8

And end up with a table like:

ID Units Webs Ribbons (the columns would be
created automatically depending on what rows are in the first table)
1 1 1 0
1 2 1 0
1 3 1 0
1 4 1 0
1 1 2 0
1 1 2 0
1 2 2 0
1 3 2 0
1 4 2 0
2 0 0 1
2 0 0 2
2 0 0 3
2 0 0 4
2 0 0 5
2 0 0 6
2 0 0 7
2 0 0 8
<additional rows not shown>

Your second query is farther than I got, and if I could figure out some way
to automatically create the columns in that query, it would be the bomb....

I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
.... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need


-----Original Message-----
Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on
this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I
can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to
show all the
combinations for each possible object value. Row and
column order aren't
important. I've narrowed the problem down to knowing the
product of the
variations of each object. I know this would be easy to
do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of
stuff, but nothing
that works so far. Is it possible to write a VBA
function to act like a
query?


.

 

[Michel Walsh]

Hi,


You are right about the alias. Jet may not like the parenthesis like
they are.

You can also do it graphically, for the most part. Bring q1 3 times in a
new query, Join ID and Dimension from two tables, join ID and Dimension from
the first and the third, facultative, join ID from the second to the third
too. Edit in the SQL view, change the = between the Dimension to < (at that
point, you can't switch back in design view). Complete the SELECT clause
appropriately. That would make your query q2. Jet is sometimes very strange
in the parenthesis it wants around its joins... when that occur, I try back
in graphical mode, then finish editing the SQL...





Hoping it may help,
Vanderghast, Access MVP

Well, it sure seems like it should work, but when I run q2 Access tells me
"Join Expression not supported". I should note that the last 'AS b' in q2 I
replaced with 'AS c', because it didn't look like you were defining anything
called 'c'....

Hi,


If you have a table Iotas, one column, Iota, with values from 1 to n ( n =
largest integer you would use), make a first query, q1:

SELECT ID, Dimension, Iota
FROM myTable INNER JOIN Iotas
ON (Iotas.iota BETWEEN myTable.MinValue AND myTable.MaxValue)




Next, q2:


SELECT a.ID, a.Iota As Qt1, a.Dimension As D1, b.Iota As Qt2,

b.Dimension

As
D2, c.Iota AS Qt3, c.Dimension AS D3
FROM ( q1 As a LEFT JOIN q1 As b ON (a.ID=b.ID) AND (a.Dimension <
b.Dimension))
LEFT JOIN q1 As b ON (a.ID=c.ID) AND (b.ID=c.ID) AND (b.Dimension <
c.Dimension)


Finally, just before the crosstab, we normalize the previous data from q2,
to make q3:


SELECT ID, Qt1 As qty, D1 As thePivot FROM q2
UNION ALL
SELECT ID, Qt2, D2 FROM q2
UNION ALL
SELECT ID, Qt3, D3 FROM q2



Note that I assume you have no more than three items (records) for the same
group (sharing the same ID ) in your original table.

The Crosstab, on q3, groups on ID, PIVOT on ThePivot, and aggregate with
LAST on qty.



Hoping it may help,
Vanderghast, Access MVP

1

to

6
Units with 1 to 2 Webs, or just 1 to 2 Webs, or 1 to 6 Units with 1 to 2
Webs with 1 to 8 Ribbons... Each set of these dimensions has an id (a key
into a different table)

So, I wanted to start with a source table like:

ID Dimension Minvalue Maxvalue
1 Units 1 4
1 Webs 1 2
2 Ribbons 1 8
3 Units 1 10
3 Webs 1 2
3 Ribbons 1 8

And end up with a table like:

ID Units Webs Ribbons (the columns would be
created automatically depending on what rows are in the first table)
1 1 1 0
1 2 1 0
1 3 1 0
1 4 1 0
1 1 2 0
1 1 2 0
1 2 2 0
1 3 2 0
1 4 2 0
2 0 0 1
2 0 0 2
2 0 0 3
2 0 0 4
2 0 0 5
2 0 0 6
2 0 0 7
2 0 0 8
<additional rows not shown>

Your second query is farther than I got, and if I could figure out

some
way

to automatically create the columns in that query, it would be the bomb....




I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
.... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need


-----Original Message-----
Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on
this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I
can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to
show all the
combinations for each possible object value. Row and
column order aren't
important. I've narrowed the problem down to knowing the
product of the
variations of each object. I know this would be easy to
do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of
stuff, but nothing
that works so far. Is it possible to write a VBA
function to act like a
query?


.

 

[Michel Walsh]

Hi,


I just got a flash about what Jet does not like. You have to split the
query q2 in two, to remove any ambiguity.


SELECT a.ID, a.Iota As Qt1, a.Dimension As D1, b.Iota As Qt2, b.Dimension As
D2
FROM q1 As a LEFT JOIN q1 As b ON (a.ID=b.ID) AND (a.Dimension
<b.Dimension)


make it q2a


SELECT q2a.*, c.Iota As Qt3, c.Dimension As D3
FROM q2a LEFT JOIN q1 As c
ON (q2a.ID=c.ID) AND (q2a.D2< c.Dimension)


make it as q2



Vanderghast, Access MVP

Hi,


You are right about the alias. Jet may not like the parenthesis like
they are.

You can also do it graphically, for the most part. Bring q1 3 times in a
new query, Join ID and Dimension from two tables, join ID and Dimension from
the first and the third, facultative, join ID from the second to the third
too. Edit in the SQL view, change the = between the Dimension to < (at that
point, you can't switch back in design view). Complete the SELECT clause
appropriately. That would make your query q2. Jet is sometimes very strange
in the parenthesis it wants around its joins... when that occur, I try back
in graphical mode, then finish editing the SQL...





Hoping it may help,
Vanderghast, Access MVP

Well, it sure seems like it should work, but when I run q2 Access tells me
"Join Expression not supported". I should note that the last 'AS b' in

q2

I
replaced with 'AS c', because it didn't look like you were defining anything
called 'c'....

( n
=

b.Dimension

combine

1

to

2

Webs with 1 to 8 Ribbons... Each set of these dimensions has an id

(a
key

into a different table)

So, I wanted to start with a source table like:

ID Dimension Minvalue Maxvalue
1 Units 1 4
1 Webs 1 2
2 Ribbons 1 8
3 Units 1 10
3 Webs 1 2
3 Ribbons 1 8

And end up with a table like:

ID Units Webs Ribbons (the columns

would
be

created automatically depending on what rows are in the first table)
1 1 1 0
1 2 1 0
1 3 1 0
1 4 1 0
1 1 2 0
1 1 2 0
1 2 2 0
1 3 2 0
1 4 2 0
2 0 0 1
2 0 0 2
2 0 0 3
2 0 0 4
2 0 0 5
2 0 0 6
2 0 0 7
2 0 0 8
<additional rows not shown>

Your second query is farther than I got, and if I could figure out some
way
to automatically create the columns in that query, it would be the
bomb....




I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
.... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need


-----Original Message-----
Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on
this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I
can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to
show all the
combinations for each possible object value. Row and
column order aren't
important. I've narrowed the problem down to knowing the
product of the
variations of each object. I know this would be easy to
do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of
stuff, but nothing
that works so far. Is it possible to write a VBA
function to act like a
query?


.

 

[Steve]

Ok - I'll give it a try. One thing though, what do you mean by
'facultative'?

Hi,


You are right about the alias. Jet may not like the parenthesis like
they are.

You can also do it graphically, for the most part. Bring q1 3 times in a
new query, Join ID and Dimension from two tables, join ID and Dimension from
the first and the third, facultative, join ID from the second to the third
too. Edit in the SQL view, change the = between the Dimension to < (at that
point, you can't switch back in design view). Complete the SELECT clause
appropriately. That would make your query q2. Jet is sometimes very strange
in the parenthesis it wants around its joins... when that occur, I try back
in graphical mode, then finish editing the SQL...





Hoping it may help,
Vanderghast, Access MVP

Well, it sure seems like it should work, but when I run q2 Access tells me
"Join Expression not supported". I should note that the last 'AS b' in

q2

I
replaced with 'AS c', because it didn't look like you were defining anything
called 'c'....

( n
=

b.Dimension

combine

1

to

2

Webs with 1 to 8 Ribbons... Each set of these dimensions has an id

(a
key

into a different table)

So, I wanted to start with a source table like:

ID Dimension Minvalue Maxvalue
1 Units 1 4
1 Webs 1 2
2 Ribbons 1 8
3 Units 1 10
3 Webs 1 2
3 Ribbons 1 8

And end up with a table like:

ID Units Webs Ribbons (the columns

would
be

created automatically depending on what rows are in the first table)
1 1 1 0
1 2 1 0
1 3 1 0
1 4 1 0
1 1 2 0
1 1 2 0
1 2 2 0
1 3 2 0
1 4 2 0
2 0 0 1
2 0 0 2
2 0 0 3
2 0 0 4
2 0 0 5
2 0 0 6
2 0 0 7
2 0 0 8
<additional rows not shown>

Your second query is farther than I got, and if I could figure out some
way
to automatically create the columns in that query, it would be the
bomb....




I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
.... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need


-----Original Message-----
Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on
this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I
can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to
show all the
combinations for each possible object value. Row and
column order aren't
important. I've narrowed the problem down to knowing the
product of the
variations of each object. I know this would be easy to
do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of
stuff, but nothing
that works so far. Is it possible to write a VBA
function to act like a
query?


.

 

[Steve]

Hmmm...still no go. Here is what I have:

SELECT Query1.ID, Query1.N AS Qt1, Query1.Dimension AS D1,
Query1_1.Dimension AS D2, Query1_1.N AS Qt2, Query1_2.Dimension AS D3,
Query1_2.N AS Qt3
FROM (Query1 LEFT JOIN Query1 AS Query1_1 ON (Query1.ID = Query1_1.ID) AND
(Query1.Dimension < Query1_1.Dimension)) LEFT JOIN Query1 AS Query1_2 ON
(Query1.ID = Query1_2.ID) AND (Query1_1.ID = Query1_2.ID) AND
(Query1_1.Dimension < Query1_2.Dimension);

Query1 = a in your example
Query1_1 = b
Query1_2 = c

"N" is what you were calling Iota...

Hi,


You are right about the alias. Jet may not like the parenthesis like
they are.

You can also do it graphically, for the most part. Bring q1 3 times in a
new query, Join ID and Dimension from two tables, join ID and Dimension from
the first and the third, facultative, join ID from the second to the third
too. Edit in the SQL view, change the = between the Dimension to < (at that
point, you can't switch back in design view). Complete the SELECT clause
appropriately. That would make your query q2. Jet is sometimes very strange
in the parenthesis it wants around its joins... when that occur, I try back
in graphical mode, then finish editing the SQL...





Hoping it may help,
Vanderghast, Access MVP

Well, it sure seems like it should work, but when I run q2 Access tells me
"Join Expression not supported". I should note that the last 'AS b' in

q2

I
replaced with 'AS c', because it didn't look like you were defining anything
called 'c'....

( n
=

b.Dimension

combine

1

to

2

Webs with 1 to 8 Ribbons... Each set of these dimensions has an id

(a
key

into a different table)

So, I wanted to start with a source table like:

ID Dimension Minvalue Maxvalue
1 Units 1 4
1 Webs 1 2
2 Ribbons 1 8
3 Units 1 10
3 Webs 1 2
3 Ribbons 1 8

And end up with a table like:

ID Units Webs Ribbons (the columns

would
be

created automatically depending on what rows are in the first table)
1 1 1 0
1 2 1 0
1 3 1 0
1 4 1 0
1 1 2 0
1 1 2 0
1 2 2 0
1 3 2 0
1 4 2 0
2 0 0 1
2 0 0 2
2 0 0 3
2 0 0 4
2 0 0 5
2 0 0 6
2 0 0 7
2 0 0 8
<additional rows not shown>

Your second query is farther than I got, and if I could figure out some
way
to automatically create the columns in that query, it would be the
bomb....




I have a feeling that you need to create records for Obj1
and Obj2

Obj1 4
Obj1 5
....
Obj1 9
Obj1 10
and
Obj2 1
Obj2 2

then combine them like this:
Obj1 4 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 1
Obj1 5 Obj2 2
Obj1 6 Obj2 1
Obj1 6 Obj2 2
etc
Obj1 10 Obj2 1
Obj1 10 Obj2 2

You need a table tblNumbers, one field Number, values 1
to 10, and a tblObjects.

Tables used in the example:
tblObjects:
Object MinVal MaxVal
Obj1 4 10
Obj2 1 2

tblNumbers:
Number
1
2
3
.... ommitted numbers
9
10


First we need a query, query qryObjectsExpanded, like
this:

SELECT tblObjects.Object, tblNumbers.Number,
tblObjects.MinVal, tblObjects.MaxVal
FROM tblObjects, tblNumbers
WHERE (((tblNumbers.Number) Between [MinVal] And
[Maxval]));

qryObjectsExpanded returns this:

Object Number MinVal MaxVal
------------------------------
Obj2 1 1 2
Obj2 2 1 2
Obj1 4 4 10
Obj1 5 4 10
Obj1 6 4 10
Obj1 7 4 10
Obj1 8 4 10
Obj1 9 4 10
Obj1 10 4 10

Then we do the following query, qryObjectsExpanded_Final:

SELECT qryObjectsExpanded.Object AS Obj1,
qryObjectsExpanded.Number AS Obj1Val,
qryObjectsExpanded_1.Object AS Obj2,
qryObjectsExpanded_1.Number AS Obj2Val
FROM qryObjectsExpanded, qryObjectsExpanded AS
qryObjectsExpanded_1
WHERE (((qryObjectsExpanded.Object)="Obj1") AND
((qryObjectsExpanded_1.Object)="Obj2"));

which returns 14 records:

Obj1 Obj1Val Obj2 Obj2Val
-------------------------------
Obj1 4 Obj2 1
Obj1 5 Obj2 1
Obj1 6 Obj2 1
Obj1 7 Obj2 1
Obj1 8 Obj2 1
Obj1 9 Obj2 1
Obj1 10 Obj2 1
Obj1 4 Obj2 2
Obj1 5 Obj2 2
Obj1 6 Obj2 2
Obj1 7 Obj2 2
Obj1 8 Obj2 2
Obj1 9 Obj2 2
Obj1 10 Obj2 2

If this all make sense and you need more help, contact me
at e-mail address
Dejan*Mladenovic@EQAO*com
(* means .)
if you need


-----Original Message-----
Here is what I am trying to do:

I have a table, with rows like this:

Object minval maxval
obj1 4 10
obj2 1 2

I would like to make a query/set of queries based on
this table that will
yield:

obj1 obj2
4 1
5 1
6 1
<rows not shown>
4 2
5 2
6 2
<rows not shown>
10 2

The last query would obviously be a crosstab, and if I
can get it to the
intermediate form of:

RowHead Object X
1 obj1 4
2 obj1 5
3 obj1 6
4 obj1 7
5 obj1 8
6 obj1 9
7 obj1 10
8 obj1 4
9 obj1 5
10 obj1 6
11 obj1 7
12 obj1 8
13 obj1 9
14 obj1 10
1 obj2 1
2 obj2 1
3 obj2 1
4 obj2 1
5 obj2 1
6 obj2 1
7 obj2 1
8 obj2 2
9 obj2 2
10 obj2 2
11 obj2 2
12 obj2 2
13 obj2 2
14 obj2 2


I would have it licked. Basically, the table needs to
show all the
combinations for each possible object value. Row and
column order aren't
important. I've narrowed the problem down to knowing the
product of the
variations of each object. I know this would be easy to
do in a table, but
for various reasons I don't want to create a new table.

Is it possible to make such a query? I've tried a lot of
stuff, but nothing
that works so far. Is it possible to write a VBA
function to act like a
query?


.