SQL to send field values to one record in multiple fields

[Fred Morris]

I have a query that has one field with multiple records. I would like to
create a new query that has the record values in one record with multiple
fields.

For example, I want to go from:

Fieldname
a1
b2
c3
d4

To:

Newfield1 Newfield2 Newfield3 Newfield4
a1 b2 c3 d4

Any help would be appreciated.

 

[Ofer Cohen]

Check help on CrossTab Query, you can use the query wizard to create one.

 

[Fred Morris]

Thanks, but I tried a CrossTab Query, and it doesn't do what I want it to do.
It transforms the values in the original field to fieldnames for the query.
I need to get the values in the original field to be values in a single
record.

 

[Duane Hookom]

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" & [FieldName]
& "'");

 

[Fred Morris]

Thanks Duane! This does what I want it to do with one exception. The new
field names appear as Field1, Field10, Field11, ... Field2, Field3, Field4
.... when there are ten or more records in the original query. This means the
second record goes into Field10, the third record goes into Field11, etc. I
would like the second record to go to Field2, etc. Any ideas on how to solve
this problem?

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" & [FieldName]
& "'");

--
Duane Hookom
MS Access MVP

Thanks, but I tried a CrossTab Query, and it doesn't do what I want it to
do.
It transforms the values in the original field to fieldnames for the
query.
I need to get the values in the original field to be values in a single
record.

 

[Duane Hookom]

They values go into fields based on their value. The columns appear in alpha
order from left to right but Field2 will contain a value that is less than
Field11.

If you can't figure this out, come back with your actual data.
--
Duane Hookom
MS Access MVP

Thanks Duane! This does what I want it to do with one exception. The new
field names appear as Field1, Field10, Field11, ... Field2, Field3, Field4
... when there are ten or more records in the original query. This means
the
second record goes into Field10, the third record goes into Field11, etc.
I
would like the second record to go to Field2, etc. Any ideas on how to
solve
this problem?

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'");

--
Duane Hookom
MS Access MVP

Thanks, but I tried a CrossTab Query, and it doesn't do what I want it
to
do.
It transforms the values in the original field to fieldnames for the
query.
I need to get the values in the original field to be values in a single
record.

:

Check help on CrossTab Query, you can use the query wizard to create
one.


--
HTH, Good Luck
BS"D


:

I have a query that has one field with multiple records. I would
like
to
create a new query that has the record values in one record with
multiple
fields.

For example, I want to go from:

Fieldname
a1
b2
c3
d4

To:

Newfield1 Newfield2 Newfield3 Newfield4
a1 b2 c3 d4

Any help would be appreciated.

 

[John Spencer]

Duane,

If the only thing the user needs is to reorder the field presentation, couldn't
the poster just change the Pivot statement to include leading zeroes? Or is
that what you were suggesting?

That is
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY Null
PIVOT "Newfield" & Format(DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'"),"00");

They values go into fields based on their value. The columns appear in alpha
order from left to right but Field2 will contain a value that is less than
Field11.

If you can't figure this out, come back with your actual data.
--
Duane Hookom
MS Access MVP

Thanks Duane! This does what I want it to do with one exception. The new
field names appear as Field1, Field10, Field11, ... Field2, Field3, Field4
... when there are ten or more records in the original query. This means
the
second record goes into Field10, the third record goes into Field11, etc.
I
would like the second record to go to Field2, etc. Any ideas on how to
solve
this problem?

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'");

--
Duane Hookom
MS Access MVP

Thanks, but I tried a CrossTab Query, and it doesn't do what I want it
to
do.
It transforms the values in the original field to fieldnames for the
query.
I need to get the values in the original field to be values in a single
record.

:

Check help on CrossTab Query, you can use the query wizard to create
one.


--
HTH, Good Luck
BS"D


:

I have a query that has one field with multiple records. I would
like
to
create a new query that has the record values in one record with
multiple
fields.

For example, I want to go from:

Fieldname
a1
b2
c3
d4

To:

Newfield1 Newfield2 Newfield3 Newfield4
a1 b2 c3 d4

Any help would be appreciated.

 

[Duane Hookom]

That would resolve all issues unless there are more hidden that we don't
know about.

--
Duane Hookom
MS Access MVP

Duane,

If the only thing the user needs is to reorder the field presentation,
couldn't
the poster just change the Pivot statement to include leading zeroes? Or
is
that what you were suggesting?

That is
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY Null
PIVOT "Newfield" & Format(DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'"),"00");

They values go into fields based on their value. The columns appear in
alpha
order from left to right but Field2 will contain a value that is less
than
Field11.

If you can't figure this out, come back with your actual data.
--
Duane Hookom
MS Access MVP

Thanks Duane! This does what I want it to do with one exception. The
new
field names appear as Field1, Field10, Field11, ... Field2, Field3,
Field4
... when there are ten or more records in the original query. This
means
the
second record goes into Field10, the third record goes into Field11,
etc.
I
would like the second record to go to Field2, etc. Any ideas on how to
solve
this problem?

:

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'");

--
Duane Hookom
MS Access MVP

Thanks, but I tried a CrossTab Query, and it doesn't do what I want
it
to
do.
It transforms the values in the original field to fieldnames for the
query.
I need to get the values in the original field to be values in a
single
record.

:

Check help on CrossTab Query, you can use the query wizard to
create
one.


--
HTH, Good Luck
BS"D


:

I have a query that has one field with multiple records. I would
like
to
create a new query that has the record values in one record with
multiple
fields.

For example, I want to go from:

Fieldname
a1
b2
c3
d4

To:

Newfield1 Newfield2 Newfield3 Newfield4
a1 b2 c3 d4

Any help would be appreciated.

 

[Fred Morris]

John and Duane: Thanks for your help. John's suggestion reorders the field
presentation to Newfield10, Newfield11, Newfield12, ..., Newfield01,
Newfield02, Newfield03, ..., Newfield09. Is there any way to get from:

Fieldname
a1
b2
c3
d4
e5
f6
g7
h8
i9
j10
k11
l12
m13
n14
o15
p16

To:

Newfield01 Newfield02 Newfield03 ...Newfield09 Newfield10...Newfield16
a1 b2 c3 ... i9 j10
... p16 ?

That would resolve all issues unless there are more hidden that we don't
know about.

--
Duane Hookom
MS Access MVP

Duane,

If the only thing the user needs is to reorder the field presentation,
couldn't
the poster just change the Pivot statement to include leading zeroes? Or
is
that what you were suggesting?

That is
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY Null
PIVOT "Newfield" & Format(DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'"),"00");

They values go into fields based on their value. The columns appear in
alpha
order from left to right but Field2 will contain a value that is less
than
Field11.

If you can't figure this out, come back with your actual data.
--
Duane Hookom
MS Access MVP

Thanks Duane! This does what I want it to do with one exception. The
new
field names appear as Field1, Field10, Field11, ... Field2, Field3,
Field4
... when there are ten or more records in the original query. This
means
the
second record goes into Field10, the third record goes into Field11,
etc.
I
would like the second record to go to Field2, etc. Any ideas on how to
solve
this problem?

:

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'");

--
Duane Hookom
MS Access MVP

Thanks, but I tried a CrossTab Query, and it doesn't do what I want
it
to
do.
It transforms the values in the original field to fieldnames for the
query.
I need to get the values in the original field to be values in a
single
record.

:

Check help on CrossTab Query, you can use the query wizard to
create
one.


--
HTH, Good Luck
BS"D


:

I have a query that has one field with multiple records. I would
like
to
create a new query that has the record values in one record with
multiple
fields.

For example, I want to go from:

Fieldname
a1
b2
c3
d4

To:

Newfield1 Newfield2 Newfield3 Newfield4
a1 b2 c3 d4

Any help would be appreciated.

 

[Duane Hookom]

This query works exactly like your intended display:
TRANSFORM First(qryWithOneField.FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & Format(DCount("*","qryWithOneField","FieldName<='" &
[FieldName] & "'"),"00");

The order is exactly the same as you want. You can specify the columns and
order by setting the Column Headings property.
--
Duane Hookom
MS Access MVP

John and Duane: Thanks for your help. John's suggestion reorders the
field
presentation to Newfield10, Newfield11, Newfield12, ..., Newfield01,
Newfield02, Newfield03, ..., Newfield09. Is there any way to get from:

Fieldname
a1
b2
c3
d4
e5
f6
g7
h8
i9
j10
k11
l12
m13
n14
o15
p16

To:

Newfield01 Newfield02 Newfield03 ...Newfield09 Newfield10...Newfield16
a1 b2 c3 ... i9 j10
... p16 ?

That would resolve all issues unless there are more hidden that we don't
know about.

--
Duane Hookom
MS Access MVP

Duane,

If the only thing the user needs is to reorder the field presentation,
couldn't
the poster just change the Pivot statement to include leading zeroes?
Or
is
that what you were suggesting?

That is
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY Null
PIVOT "Newfield" & Format(DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'"),"00");

Duane Hookom wrote:

They values go into fields based on their value. The columns appear in
alpha
order from left to right but Field2 will contain a value that is less
than
Field11.

If you can't figure this out, come back with your actual data.
--
Duane Hookom
MS Access MVP

Thanks Duane! This does what I want it to do with one exception.
The
new
field names appear as Field1, Field10, Field11, ... Field2, Field3,
Field4
... when there are ten or more records in the original query. This
means
the
second record goes into Field10, the third record goes into Field11,
etc.
I
would like the second record to go to Field2, etc. Any ideas on how
to
solve
this problem?

:

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'");

--
Duane Hookom
MS Access MVP

message
Thanks, but I tried a CrossTab Query, and it doesn't do what I
want
it
to
do.
It transforms the values in the original field to fieldnames for
the
query.
I need to get the values in the original field to be values in a
single
record.

:

Check help on CrossTab Query, you can use the query wizard to
create
one.


--
HTH, Good Luck
BS"D


:

I have a query that has one field with multiple records. I
would
like
to
create a new query that has the record values in one record
with
multiple
fields.

For example, I want to go from:

Fieldname
a1
b2
c3
d4

To:

Newfield1 Newfield2 Newfield3 Newfield4
a1 b2 c3 d4

Any help would be appreciated.

 

[Fred Morris]

Thanks Duane,
You are correct. I was using numeric data (1-16) to test the query, and my
field names were coming up as Field01, Field10, Field 11, etc. When I went
back and added the alpha character to the data, everything worked as intended.

Thanks, again!

This query works exactly like your intended display:
TRANSFORM First(qryWithOneField.FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & Format(DCount("*","qryWithOneField","FieldName<='" &
[FieldName] & "'"),"00");

The order is exactly the same as you want. You can specify the columns and
order by setting the Column Headings property.
--
Duane Hookom
MS Access MVP

John and Duane: Thanks for your help. John's suggestion reorders the
field
presentation to Newfield10, Newfield11, Newfield12, ..., Newfield01,
Newfield02, Newfield03, ..., Newfield09. Is there any way to get from:

Fieldname
a1
b2
c3
d4
e5
f6
g7
h8
i9
j10
k11
l12
m13
n14
o15
p16

To:

Newfield01 Newfield02 Newfield03 ...Newfield09 Newfield10...Newfield16
a1 b2 c3 ... i9 j10
... p16 ?

That would resolve all issues unless there are more hidden that we don't
know about.

--
Duane Hookom
MS Access MVP

Duane,

If the only thing the user needs is to reorder the field presentation,
couldn't
the poster just change the Pivot statement to include leading zeroes?
Or
is
that what you were suggesting?

That is
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY Null
PIVOT "Newfield" & Format(DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'"),"00");

Duane Hookom wrote:

They values go into fields based on their value. The columns appear in
alpha
order from left to right but Field2 will contain a value that is less
than
Field11.

If you can't figure this out, come back with your actual data.
--
Duane Hookom
MS Access MVP

Thanks Duane! This does what I want it to do with one exception.
The
new
field names appear as Field1, Field10, Field11, ... Field2, Field3,
Field4
... when there are ten or more records in the original query. This
means
the
second record goes into Field10, the third record goes into Field11,
etc.
I
would like the second record to go to Field2, etc. Any ideas on how
to
solve
this problem?

:

You can use a crosstab with SQL like:
TRANSFORM First(FieldName) AS FirstOfFieldName
SELECT Null AS IgnoreMe
FROM qryWithOneField
GROUP BY Null
PIVOT "Newfield" & DCount("*","qryWithOneField","FieldName<='" &
[FieldName]
& "'");

--
Duane Hookom
MS Access MVP

message
Thanks, but I tried a CrossTab Query, and it doesn't do what I
want
it
to
do.
It transforms the values in the original field to fieldnames for
the
query.
I need to get the values in the original field to be values in a
single
record.

:

Check help on CrossTab Query, you can use the query wizard to
create
one.


--
HTH, Good Luck
BS"D


:

I have a query that has one field with multiple records. I
would
like
to
create a new query that has the record values in one record
with
multiple
fields.

For example, I want to go from:

Fieldname
a1
b2
c3
d4

To:

Newfield1 Newfield2 Newfield3 Newfield4
a1 b2 c3 d4

Any help would be appreciated.