reduce values and column

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

How can i reduce value, and put them correct column ?

SELECT DISTINCT Table1.color AS V, Table1.color AS G
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));
Table1 id, color
in V only green, in G only yellow, in table color are same number

thx
 
Marco said:
How can i reduce value, and put them correct column ?

SELECT DISTINCT Table1.color AS V, Table1.color AS G
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));
Table1 id, color
in V only green, in G only yellow, in table color are same number
Click to expand...

Guessing

SELECT Table1.color, 'V' as color_code
FROM Table1
WHERE Table1.color Like "*yellow*"
UNION ALL
SELECT Table1.color, 'G' as color_code
FROM Table1
WHERE Table1.color Like "*green*"
 
Guessing

SELECT Table1.color, 'V' as color_code
FROM Table1
WHERE Table1.color Like "*yellow*"
UNION ALL
SELECT Table1.color, 'G' as color_code
FROM Table1
WHERE Table1.color Like "*green*"
Click to expand...
Now i have yellow and green in the same column (color).
How can i put yellow and green in a separate column, one in color and the
other in color_code ?

thx
 
Your question is unclear.

PERHAPS what you want is

SELECT DISTINCT
IIF(Table1.color Like "*yellow*",table1.color,null) AS V,
IIF(Table1.color Like "*Green*",table1.color,null) AS G
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));

or do you want

SELECT DISTINCT
IIF(Table1.color Like "*yellow*","V",null) AS V,
IIF(Table1.color Like "*Green*","G",null) AS G
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));

OR do you want

SELECT DISTINCT
IIF(Table1.color Like "*yellow*",table1.color,IIF(Table1.color Like
"*Green*",table1.color,null)) AS V,
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));

and there are a couple more variations I can think of.

Post a couple sample output records if none of the above answer your problem.
That way we might have a better idea of what you want.
 
John Spencer (MVP) said:
Your question is unclear.

PERHAPS what you want is

SELECT DISTINCT
IIF(Table1.color Like "*yellow*",table1.color,null) AS V,
IIF(Table1.color Like "*Green*",table1.color,null) AS G
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));

or do you want

SELECT DISTINCT
IIF(Table1.color Like "*yellow*","V",null) AS V,
IIF(Table1.color Like "*Green*","G",null) AS G
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));

OR do you want

SELECT DISTINCT
IIF(Table1.color Like "*yellow*",table1.color,IIF(Table1.color Like
"*Green*",table1.color,null)) AS V,
FROM Table1
WHERE (((Table1.color) Like "*yellow*" Or (Table1.color) Like "*green*"));

and there are a couple more variations I can think of.

Post a couple sample output records if none of the above answer your
problem.
That way we might have a better idea of what you want.
Click to expand...
sample table:
id color
1 red
2 green
3 yellow
4 green
5 blu
6 yellow
query:
V G
green yellow
green yellow
i desire separate result , in column V all records green and in G all
records yellow.

thx
 
Marco said:
sample table:
id color
1 red
2 green
3 yellow
4 green
5 blu
6 yellow
query:
V G
green yellow
green yellow
i desire separate result , in column V all records green and in G all
records yellow.
Click to expand...

But you have green yellow on the same row in your results - they came
from different rows in the base table! How do I know which yellow to
pair with which green?
 
But you have green yellow on the same row in your results - they came
from different rows in the base table! How do I know which yellow to
pair with which green?
Click to expand...
The colors in the original table are in a field text where there are other
records with many not important data , but to every green color record the
successive record is yellow.
 
You still are unclear on what you expect to see in the returned row set. Please
give us an example of what you expect to see returned.

For instance, do you expect the record id to be returned in either the V or G
column depending on the value in the color field. Something like the following?

V G
2
3
4
6
 
sample table:
idtbl color
1 red
2 green1
3 yellow1
4 blu
5 blu
6 green2
7 yellow2
8 red
9 blu
10 green3
11 yellow3
....
query:
IDqry V G
1 green1 yellow1
2 green2 yellow2
3 green3 yellow3
....

green1 idtbl+1 = yellow1 idtbl, ...
Perhaps this explains better
thx
 
Marco said:
sample table:
idtbl color
1 red
2 green1
3 yellow1
4 blu
5 blu
6 green2
7 yellow2
8 red
9 blu
10 green3
11 yellow3
...
query:
IDqry V G
1 green1 yellow1
2 green2 yellow2
3 green3 yellow3
...

green1 idtbl+1 = yellow1 idtbl, ...
Perhaps this explains better
Click to expand...

I'm still not 100% sure but I _think_ what is required is the order
relative to idtbl of the occurrence (qryID) of yellow and green
respectively.

With Jet lacking the FULL OUTER JOIN syntax (the UNION workaround with
the derived tables including subqueries would look horrid!), I opt to
use a Sequence table (the standard trick of having a table of
incrementing integers). You may have to change the % wildcard character

SELECT [Sequence].i AS IDqry,
Greens.color AS V, Yellows.color AS G
FROM (
[Sequence] LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'green%' AND T1.idtbl >= idtbl
) AS occurance_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'green%'
) AS Greens ON [Sequence].i = Greens.occurance_nbr
) LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'yellow%' AND T1.idtbl >= idtbl
) AS occurance_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'yellow%'
) AS Yellows ON [Sequence].i = Yellows.occurance_nbr
WHERE Greens.color IS NOT NULL
AND Yellows.color IS NOT NULL

Should the V=Green and G=Yellow correlation names (aliases) be the
other way round?
 
sample table:
idtbl color
1 red
2 green1
3 yellow1
4 blu
5 blu
6 green2
7 yellow2
8 red
9 blu
10 green3
11 yellow3
...
query:
IDqry V G
1 green1 yellow1
2 green2 yellow2
3 green3 yellow3
...

green1 idtbl+1 = yellow1 idtbl, ...
Perhaps this explains better
Click to expand...

I'm still not 100% sure but I _think_ what is required is the order
relative to idtbl of the occurrence (qryID) of yellow and green
respectively.

With Jet lacking the FULL OUTER JOIN syntax (the UNION workaround with
the derived tables including subqueries would look horrid!), I opt to
use a Sequence table (the standard trick of having a table of
incrementing integers). You may have to change the % wildcard character

SELECT [Sequence].i AS IDqry,
Greens.color AS V, Yellows.color AS G
FROM (
[Sequence] LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'green%' AND T1.idtbl >= idtbl
) AS occurance_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'green%'
) AS Greens ON [Sequence].i = Greens.occurance_nbr
) LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'yellow%' AND T1.idtbl >= idtbl
) AS occurance_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'yellow%'
) AS Yellows ON [Sequence].i = Yellows.occurance_nbr
WHERE Greens.color IS NOT NULL
AND Yellows.color IS NOT NULL
Click to expand...

It is "little" complicated for me...
Sequence is a new table like ?
id i
1 1
2 2
3 3

Marco is sample table color ?
Should the V=Green and G=Yellow correlation names (aliases) be the
other way round?
Click to expand...

No Green is Verde > V and Yellow is Giallo > G


thx
 
Marco said:
It is "little" complicated for me...
Sequence is a new table like ?
id i
1 1
2 2
3 3
Click to expand...

Yes, although you only need the one column. It's a standard technique
to have a table of integers to JOIN to in order to build a sequence.
Mine goes up to 65536 because I built it in Excel and imported it :)
Marco is sample table color ?
Yes.


No Green is Verde > V and Yellow is Giallo > G
Click to expand...

Capito :)
 
Marco said:
I'm still not 100% sure but I _think_ what is required is the order
relative to idtbl of the occurrence (qryID) of yellow and green
respectively.

With Jet lacking the FULL OUTER JOIN syntax (the UNION workaround with
the derived tables including subqueries would look horrid!), I opt to
use a Sequence table (the standard trick of having a table of
incrementing integers). You may have to change the % wildcard character

SELECT [Sequence].i AS IDqry,
Greens.color AS V, Yellows.color AS G
FROM (
[Sequence] LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'green%' AND T1.idtbl >= idtbl
) AS occurance_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'green%'
) AS Greens ON [Sequence].i = Greens.occurance_nbr
) LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'yellow%' AND T1.idtbl >= idtbl
) AS occurance_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'yellow%'
) AS Yellows ON [Sequence].i = Yellows.occurance_nbr
WHERE Greens.color IS NOT NULL
AND Yellows.color IS NOT NULL
Click to expand...

It is "little" complicated for me...
Sequence is a new table like ?
id i
1 1
2 2
3 3

Marco is sample table color ?
Should the V=Green and G=Yellow correlation names (aliases) be the
other way round?
Click to expand...

No Green is Verde > V and Yellow is Giallo > G


thx
Click to expand...
I have a problem with %, and now i see result, but i have multiple
row-record.
The structure is ok. idqry, V, G
green1 is in order to indicate the first valid value in may sample table
and yellow1 the same.
If i write only green and yellow in table like original, it returs 9 records
in query with only 3 id.
The correct answer is three, record (1) idqry 1 V green (idtbl 2) G yellow
(idtbl 3), record (2) idqry 2 V green (idtbl 6) G yellow (idtbl 7), record
(3) idqry 2 V green (idtbl 10) G yellow (idtbl 11).
I do not know like explaining better.
sample table, tblsample:
idtbl color
1 red
2 green
3 yellow
4 blu
5 blu
6 green
7 yellow
8 red
9 blu
10 green
11 yellow
12 orange
....
query, qryresult :
IDqry V G
1 green yellow (idtbl 2) (idtbl 3)
2 green yellow (idtbl 6) (idtbl 7)
3 green yellow (idtbl 10) (idtbl 11)
 
Marco said:
The structure is ok.
Click to expand...

Oops, I see a problem. My bad. I'll resort to the full outer join
workaround (replace % with *)

SELECT Greens.occurrence_nbr,
Greens.color AS V, Yellows.color AS G
FROM (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'green%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'green%'
) AS Greens LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'yellow%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'yellow%'
) AS Yellows ON Greens.occurrence_nbr = Yellows.occurrence_nbr
UNION
SELECT Yellows.occurrence_nbr,
Greens.color AS V, Yellows.color AS G
FROM (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'green%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'green%'
) AS Greens RIGHT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'yellow%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'yellow%'
) AS Yellows ON Greens.occurrence_nbr = Yellows.occurrence_nbr
 
Oops, I see a problem. My bad. I'll resort to the full outer join
workaround (replace % with *)

SELECT Greens.occurrence_nbr,
Greens.color AS V, Yellows.color AS G
FROM (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'green%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'green%'
) AS Greens LEFT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'yellow%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'yellow%'
) AS Yellows ON Greens.occurrence_nbr = Yellows.occurrence_nbr
UNION
SELECT Yellows.occurrence_nbr,
Greens.color AS V, Yellows.color AS G
FROM (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'green%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'green%'
) AS Greens RIGHT OUTER JOIN (
SELECT T1.idtbl, T1.color, (
SELECT COUNT(*)
FROM Marco
WHERE color LIKE 'yellow%' AND T1.idtbl >= idtbl
) AS occurrence_nbr
FROM Marco AS T1
WHERE T1.color LIKE 'yellow%'
) AS Yellows ON Greens.occurrence_nbr = Yellows.occurrence_nbr
Click to expand...
It's a litte slow , but works fine.
thx
 
Back
Top