Query to Split field.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,
From the table below:

Field_1 Field_3 Field_4
XXX_1 0 ZZZ_1
XXX_1 1 ZZZ_2
XXX_1 2 ZZZ_3
XXX_2 0 ZZZ_4
XXX_2 1 ZZZ_5
XXX_2 2 ZZZ_6





I want to generate the table:

Field_1 If field_3=0 If field_3=1 If field_3=2
XXX_1 ZZZ_1 ZZZ_2 ZZZ_3
XXX_2 ZZZ_4 ZZZ_5 ZZZ_6

Thanks in advance,
 
I'll admit that I don't understand your logic to the problem, but it doesn't
affect the outcome of my answer.

Left(), Right(), and Mid() are all string functions that help you extract
information from string. Examine them further in the Help file.
 
Clark,
I meant, I want to split the column "Field_4" into three columns according
to the values in "Field_3"
 
I'm afraid I'm a lot like Steve and don't quite follow what you want to
accomplish. However, the following query will get the results you want based
on the data you displayed.

SELECT DISTINCT A.Field_1,
(SELECT X.Field_4
FROM tblYourTable AS X
WHERE X.field_3 = 0
AND X.field_1 = A.Field_1) AS Field_4A,
(SELECT Y.Field_4
FROM tblYourTable AS Y
WHERE Y.Field_3 = 1
AND y.field_1 = a.field_1) AS Field_4B,
(SELECT Z.Field_4
FROM tblYourTable AS Z
WHERE Z.Field_3 = 2
AND z.field_1 = a.field_1) AS Field_4C
FROM tblYourTable AS A;
 
Sounds a lot like the "tally" component of a "rank and tally" query.

SELECT Field1,
MAX( Iif([Field3]=0, [Field4], null ) AS IfField3Is0,
MAX( Iif([Field3]=1, [Field4], null ) AS IfField3Is1,
MAX( Iif([Field3]=2, [Field4], null ) AS IfField3Is2,
GROUP BY Field1

David Atkins, MCP
 
Ooops, obviously didn't finish the query (forgot the FROM and had a spare
comma at the end of the last select item).

SELECT Field1,
MAX( Iif([Field3]=0, [Field4], null ) AS IfField3Is0,
MAX( Iif([Field3]=1, [Field4], null ) AS IfField3Is1,
MAX( Iif([Field3]=2, [Field4], null ) AS IfField3Is2
FROM tblYourTableBelow
GROUP BY Field1

.... Grey


GreySky said:
Sounds a lot like the "tally" component of a "rank and tally" query.

SELECT Field1,
MAX( Iif([Field3]=0, [Field4], null ) AS IfField3Is0,
MAX( Iif([Field3]=1, [Field4], null ) AS IfField3Is1,
MAX( Iif([Field3]=2, [Field4], null ) AS IfField3Is2,
GROUP BY Field1

David Atkins, MCP

Rubianob said:
Hi All,
From the table below:

Field_1 Field_3 Field_4
XXX_1 0 ZZZ_1
XXX_1 1 ZZZ_2
XXX_1 2 ZZZ_3
XXX_2 0 ZZZ_4
XXX_2 1 ZZZ_5
XXX_2 2 ZZZ_6





I want to generate the table:

Field_1 If field_3=0 If field_3=1 If field_3=2
XXX_1 ZZZ_1 ZZZ_2 ZZZ_3
XXX_2 ZZZ_4 ZZZ_5 ZZZ_6

Thanks in advance,
Click to expand...
Click to expand...
 
Back
Top