Convert One Column Data to Multiple Columns Data

[Rob K]

I have a Query I am trying to Build that has the Following data:

Name WO Days Old
AD K-12345 10
AD K-12346 23
AD K-12368 02
AD K-12351 05
AD K-12386 20

What I am needing to is move the Days old Data to three Colums based off of
the Days old

Name WO 1-5 days 6-12 days 13+ Days
AD K-12345 10
AD K-12346 23
AD K-12368 02

Something like that if it is possible.

I have searched for this and could not find any other topics like this, Any
help will be appreciated. Thanks for your time.

 

[Jerry Whittle]

Two questions:

1. Where does Days Old come from? Is it a value in a table or something
computed from a date field.

2. Could you ever have something like this, and, if so, how should it look?

AD K-12345 10
AD K-12345 09

 

[Jerry Whittle]

Create a query something like below. The 01 and 06 are not mistakes. If you
leave them 1 and 6, you will have a sort problem across the columns. Put in
the proper table name.

SELECT RobK.Name,
RobK.WO,
RobK.[Days Old],
IIf([Days Old] Between 1 And 5,"01-05 days",IIf([Days Old] Between 6 And
10,"06-10 days","13+ days")) AS OldDays
FROM RobK;

After getting the above query to run, create a crosstab query based upon it.
Make sure to change the "qryRobK" to the name of the query above:

TRANSFORM Max(qryRobK.[Days Old]) AS [MaxOfDays Old]
SELECT qryRobK.[Name], qryRobK.[WO]
FROM qryRobK
GROUP BY qryRobK.[Name], qryRobK.[WO]
ORDER BY qryRobK.[Name], qryRobK.[WO]
PIVOT qryRobK.[OldDays];

Note: If the Days Old field is left null, zero, or a negative number, it
will show up in the 13+ days column.

 

[Rob K]

1. It is a Value in a table.
2. No there will not be any Duplcate WO as you have listed

 

[Rob K]

Thank You for your help Jerry.

I used your First Query you showed and was able to get the info I needed.
Here is what I used.

SELECT WIPCLSIF.RESPAR, WIPCLSIF.WONO, WIPCLSIF.DAYS,
IIf([WIPCLSIF.DAYS] Between 0 And 5,[Days]) AS [0-5 Days],
IIf([WIPCLSIF.DAYS] Between 6 And 12,[Days]) AS [6-12 days],
IIf([WIPCLSIF.DAYS] Between 13 And 999,[days]) AS [13+ Days]

What does the Crosstab query you have listed Do?

Create a query something like below. The 01 and 06 are not mistakes. If you
leave them 1 and 6, you will have a sort problem across the columns. Put in
the proper table name.

SELECT RobK.Name,
RobK.WO,
RobK.[Days Old],
IIf([Days Old] Between 1 And 5,"01-05 days",IIf([Days Old] Between 6 And
10,"06-10 days","13+ days")) AS OldDays
FROM RobK;

After getting the above query to run, create a crosstab query based upon it.
Make sure to change the "qryRobK" to the name of the query above:

TRANSFORM Max(qryRobK.[Days Old]) AS [MaxOfDays Old]
SELECT qryRobK.[Name], qryRobK.[WO]
FROM qryRobK
GROUP BY qryRobK.[Name], qryRobK.[WO]
ORDER BY qryRobK.[Name], qryRobK.[WO]
PIVOT qryRobK.[OldDays];

Note: If the Days Old field is left null, zero, or a negative number, it
will show up in the 13+ days column.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a Query I am trying to Build that has the Following data:

Name WO Days Old
AD K-12345 10
AD K-12346 23
AD K-12368 02
AD K-12351 05
AD K-12386 20

What I am needing to is move the Days old Data to three Colums based off of
the Days old

Name WO 1-5 days 6-12 days 13+ Days
AD K-12345 10
AD K-12346 23
AD K-12368 02

Something like that if it is possible.

I have searched for this and could not find any other topics like this, Any
help will be appreciated. Thanks for your time.