Vertical to Horizontal

[NetworkTrade]

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.

 

[Duane Hookom]

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;

 

[NetworkTrade]

appreciate your time to make the reply. I believe the 'First' parameter
limits the results as it isn't working the way needed.

To convert from:
A 1
A 2
A 3
B f
B 2
B p

To: A 1 2 3
B f 2 p


Kind of a pain for this to be driven by the need to fit onto a label....


--
NTC

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
--
Duane Hookom
Microsoft Access MVP

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.

 

[Duane Hookom]

If all you need is "fit onto a label" then use the generic concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP

appreciate your time to make the reply. I believe the 'First' parameter
limits the results as it isn't working the way needed.

To convert from:
A 1
A 2
A 3
B f
B 2
B p

To: A 1 2 3
B f 2 p


Kind of a pain for this to be driven by the need to fit onto a label....


--
NTC

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
--
Duane Hookom
Microsoft Access MVP

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.

 

[NetworkTrade]

thanks for this. This should work. Downloaded and it works fine stand
alone. Imported into application the 2 sample tables, module and first name
query example... but have found I have a data mismatch. In my case the
"FamID" must be alpha text... have changed definition in table but is
stopping at

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

am a little puzzled at the moment....

--
NTC

If all you need is "fit onto a label" then use the generic concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP

appreciate your time to make the reply. I believe the 'First' parameter
limits the results as it isn't working the way needed.

To convert from:
A 1
A 2
A 3
B f
B 2
B p

To: A 1 2 3
B f 2 p


Kind of a pain for this to be driven by the need to fit onto a label....


--
NTC

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
--
Duane Hookom
Microsoft Access MVP


:

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.

 

[Duane Hookom]

You should provide your syntax so we can help. I don't see where you have
provided table or field names or data types. These are all significant points.
--
Duane Hookom
Microsoft Access MVP

thanks for this. This should work. Downloaded and it works fine stand
alone. Imported into application the 2 sample tables, module and first name
query example... but have found I have a data mismatch. In my case the
"FamID" must be alpha text... have changed definition in table but is
stopping at

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

am a little puzzled at the moment....

--
NTC

If all you need is "fit onto a label" then use the generic concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP

appreciate your time to make the reply. I believe the 'First' parameter
limits the results as it isn't working the way needed.

To convert from:
A 1
A 2
A 3
B f
B 2
B p

To: A 1 2 3
B f 2 p


Kind of a pain for this to be driven by the need to fit onto a label....


--
NTC


:

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
--
Duane Hookom
Microsoft Access MVP


:

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.

 

[NetworkTrade]

ah well in respect of MVP help I always start cautious. Am only using your
sample. Works fine of course. But FamID can no longer be defined as a number
in my case. So am changing only this parameter, otherwise everything is
exactly the same.

In tblFamily: Change FamID from number to text, remove as key (and add a new
ID autonumber key field)

In tblFamMem: Change FamID from number to text

In your sample with your data, this will throw the error: Data Mismatch and
then the debug is stopped at: rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

and so am puzzled as to the dependency on FamID being defined as number -
not able to resolve
--
NTC

You should provide your syntax so we can help. I don't see where you have
provided table or field names or data types. These are all significant points.
--
Duane Hookom
Microsoft Access MVP

thanks for this. This should work. Downloaded and it works fine stand
alone. Imported into application the 2 sample tables, module and first name
query example... but have found I have a data mismatch. In my case the
"FamID" must be alpha text... have changed definition in table but is
stopping at

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

am a little puzzled at the moment....

--
NTC

If all you need is "fit onto a label" then use the generic concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP


:

appreciate your time to make the reply. I believe the 'First' parameter
limits the results as it isn't working the way needed.

To convert from:
A 1
A 2
A 3
B f
B 2
B p

To: A 1 2 3
B f 2 p


Kind of a pain for this to be driven by the need to fit onto a label....


--
NTC


:

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
--
Duane Hookom
Microsoft Access MVP


:

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.

 

[Duane Hookom]

In my example, the code expression would be changed to:

FirstNames: Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =""" &
[FamID] & """")
--
Duane Hookom
Microsoft Access MVP

ah well in respect of MVP help I always start cautious. Am only using your
sample. Works fine of course. But FamID can no longer be defined as a number
in my case. So am changing only this parameter, otherwise everything is
exactly the same.

In tblFamily: Change FamID from number to text, remove as key (and add a new
ID autonumber key field)

In tblFamMem: Change FamID from number to text

In your sample with your data, this will throw the error: Data Mismatch and
then the debug is stopped at: rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

and so am puzzled as to the dependency on FamID being defined as number -
not able to resolve
--
NTC

You should provide your syntax so we can help. I don't see where you have
provided table or field names or data types. These are all significant points.
--
Duane Hookom
Microsoft Access MVP

thanks for this. This should work. Downloaded and it works fine stand
alone. Imported into application the 2 sample tables, module and first name
query example... but have found I have a data mismatch. In my case the
"FamID" must be alpha text... have changed definition in table but is
stopping at

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

am a little puzzled at the moment....

--
NTC


:

If all you need is "fit onto a label" then use the generic concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP


:

appreciate your time to make the reply. I believe the 'First' parameter
limits the results as it isn't working the way needed.

To convert from:
A 1
A 2
A 3
B f
B 2
B p

To: A 1 2 3
B f 2 p


Kind of a pain for this to be driven by the need to fit onto a label....


--
NTC


:

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
--
Duane Hookom
Microsoft Access MVP


:

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.

 

[NetworkTrade]

yes. that's it. much thanks. wasn't even thinking about the quotations....
--
NTC

In my example, the code expression would be changed to:

FirstNames: Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =""" &
[FamID] & """")
--
Duane Hookom
Microsoft Access MVP

ah well in respect of MVP help I always start cautious. Am only using your
sample. Works fine of course. But FamID can no longer be defined as a number
in my case. So am changing only this parameter, otherwise everything is
exactly the same.

In tblFamily: Change FamID from number to text, remove as key (and add a new
ID autonumber key field)

In tblFamMem: Change FamID from number to text

In your sample with your data, this will throw the error: Data Mismatch and
then the debug is stopped at: rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

and so am puzzled as to the dependency on FamID being defined as number -
not able to resolve
--
NTC

You should provide your syntax so we can help. I don't see where you have
provided table or field names or data types. These are all significant points.
--
Duane Hookom
Microsoft Access MVP


:

thanks for this. This should work. Downloaded and it works fine stand
alone. Imported into application the 2 sample tables, module and first name
query example... but have found I have a data mismatch. In my case the
"FamID" must be alpha text... have changed definition in table but is
stopping at

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

am a little puzzled at the moment....

--
NTC


:

If all you need is "fit onto a label" then use the generic concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP


:

appreciate your time to make the reply. I believe the 'First' parameter
limits the results as it isn't working the way needed.

To convert from:
A 1
A 2
A 3
B f
B 2
B p

To: A 1 2 3
B f 2 p


Kind of a pain for this to be driven by the need to fit onto a label....


--
NTC


:

I would first create a column that can be used to rank the records within
each group. For instance to rank emplees within [Title] groups in the
Northwind sample MDB, you would use SQL like:

=== qselRankTitle ================
SELECT Employees.Title, Employees.LastName,
Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you could create a crosstab based on the above query:
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
--
Duane Hookom
Microsoft Access MVP


:

am putting this in Reports area because the need is being driven by
production of Labels with limited space....probably is a query question
though...

The data is normalized ok and query/table results normally are like this:

A 01
A 01a
A 02
B 01
B 02

I think you get the idea. But to get it onto the necessary label the
results need to be:

A 01 01a 02
B 01 02

there is only going to be 3 rows ever i.e. A B C.....and can be 0 to 10
values for each row...

Would welcome thoughts on dealing with this need. Maybe a crosstab? Thanks
in advance.