Rows to columns - concatenation

[George]

Dear friends,

Happy New Year to all of you.

Here is my big problem:

I have developed a relational database using access 2003 with the following
structure:
-T_Polygons (PolygonID – text 6 – Primary key)
-T_Data1 (PolygonID and SpeciesID – Primary keys)
-T_Data2 (PolygonID and SpeciesID – Primary keys)
-T_Data3 (PolygonID and SpeciesID – Primary keys)
As you realize, each Polygon has several records to the other related tables.

I want to run a query (perhaps) and give me as column A the polygon ID and
the following columns (0-38 for each related table, or just the 3 first
records) to be the related records of the other 3 tables.

The result should be something like (in columns and not in rows):

PolygonID A12345
Species1FromTable1 = SpeciesA
Species2FromTable1 = SpeciesB
Species2FromTable1 = SpeciesC
Species1FromTable2 = SpeciesA
Species2FromTable2 = SpeciesB
Species2FromTable2 = SpeciesC
Species1FromTable3 = SpeciesA
Species2FromTable3 = SpeciesB
Species2FromTable3 = SpeciesC
Then I will use concatenation in order to concatenate all the above in a
single field.

Thanking you in advance,

GeorgeCY

 

[Duane Hookom]

If I understand correctly "Species1FromTable1" should more accurately be
referred to as "SpeciesID(1) from T_Data1". It doesn't make sense to not use
the actual names from the first part of your question is the last part of
your question.

If I am correct and you have multiple records in the T_Data tables related
to single records in T_Polygons, then you could use the generic concatenate
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[George]

Thanks so much dear friend,

The given link seems to be very promising. Regarding the different data
tables I have, each table represent a different storey of a forest inventory
database and so in each separate data table a different storey is described.
In some cases some species found in the first data table appear also in the
second or even in the third, but in other cases not. So i need to have all
this data in different tables.

Thanks again

Ο χÏήστης "Duane Hookom" έγγÏαψε:

 

[John W. Vinson]

The given link seems to be very promising. Regarding the different data
tables I have, each table represent a different storey of a forest inventory
database and so in each separate data table a different storey is described.
In some cases some species found in the first data table appear also in the
second or even in the third, but in other cases not. So i need to have all
this data in different tables.

No. You do NOT need a separate table for each storey!

Instead, you can use one table with an additional Storey field. Each record
will pertain only to one storey; you can have multiple records (with different
values of Storey) with the same or different species.

Storing data - a storey - in a tablename is simply bad design.

John W. Vinson [MVP]

 

[George]

Yes, you are absolutely right - Thanks a lot.

But, even if i had created a single table for all storeys I would like also
to concatenate many related records into one field.

Thanks again

Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[Duane Hookom]

George,
Do you still need help with the concatenation or have you been able to apply
the function?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[George]

Oh yes,

Thanking you a million Duane !

George

Ο χÏήστης "Duane Hookom" έγγÏαψε:

 

[Duane Hookom]

"Oh yes" to which part of my question? If you still need help, please provide
significant table and field names as well as some sample records and desired
output.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[George]

Dear Duane,

Yes, I got managed to use your module with some modifications to my tables
(My main table has 3 primary keys wich are related to my many table, all of
them are number fields). I then use a query to join all those fields to have
a unique code for each record in my tables.

I have manage to run your module after running make table queries in order
to have this unique code in a single table (number - primary key) and in the
second table derived from another make table query I have added an autonumber
field.

Is there any possibility to use my existing tables (3 primary keys) in order
to run your code?



Ο χÏήστης "Duane Hookom" έγγÏαψε:

 

[Duane Hookom]

There is a SQL statement within the Concatenate function that can specify any
number of fields in a "join". For instance:

Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] ="""
& [FieldA] & """ AND Field1 =" & [Field1] & " AND [FieldDate] =#" &
[FieldDate] & "#")

Or possibly
Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] &
[Field1] & [FieldDate]=""" & [FieldA] & [Field1] & [FieldDate] & """")

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[George]

Dear Duane,

The structure of my tables follows:

T_CompartmentAndPolygons: (ForestID, CompartmentNo, PolygonID all Primary
keys – number)

T_StoreySpecies: (ForestID, CompartmentNo, PolygonID, SpeciesID all Primary
keys – number)


Please, how can I appy your concatenate module using the above structure?

Thanking you so much for your valuable help.

GeorgeC



Ο χÏήστης "Duane Hookom" έγγÏαψε:

There is a SQL statement within the Concatenate function that can specify any
number of fields in a "join". For instance:

Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] ="""
& [FieldA] & """ AND Field1 =" & [Field1] & " AND [FieldDate] =#" &
[FieldDate] & "#")

Or possibly
Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] &
[Field1] & [FieldDate]=""" & [FieldA] & [Field1] & [FieldDate] & """")

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Dear Duane,

Yes, I got managed to use your module with some modifications to my tables
(My main table has 3 primary keys wich are related to my many table, all of
them are number fields). I then use a query to join all those fields to have
a unique code for each record in my tables.

I have manage to run your module after running make table queries in order
to have this unique code in a single table (number - primary key) and in the
second table derived from another make table query I have added an autonumber
field.

Is there any possibility to use my existing tables (3 primary keys) in order
to run your code?



Ο χÏήστης "Duane Hookom" έγγÏαψε:

 

[Duane Hookom]

Probably something like:
SELECT *, concatenate("SELECT SpeciesID FROM T_StoreySpecies WHERE ForestID
= " & ForestID & " AND CompartmentNo = " & CompartmentNo & " AND PolygonID =
" & PolygonID ) As SpeciesIDs
FROM T_CompartementAndPolygons;

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Dear Duane,

The structure of my tables follows:

T_CompartmentAndPolygons: (ForestID, CompartmentNo, PolygonID all Primary
keys – number)

T_StoreySpecies: (ForestID, CompartmentNo, PolygonID, SpeciesID all Primary
keys – number)


Please, how can I appy your concatenate module using the above structure?

Thanking you so much for your valuable help.

GeorgeC



Ο χÏήστης "Duane Hookom" έγγÏαψε:

There is a SQL statement within the Concatenate function that can specify any
number of fields in a "join". For instance:

Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] ="""
& [FieldA] & """ AND Field1 =" & [Field1] & " AND [FieldDate] =#" &
[FieldDate] & "#")

Or possibly
Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] &
[Field1] & [FieldDate]=""" & [FieldA] & [Field1] & [FieldDate] & """")

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Dear Duane,

Yes, I got managed to use your module with some modifications to my tables
(My main table has 3 primary keys wich are related to my many table, all of
them are number fields). I then use a query to join all those fields to have
a unique code for each record in my tables.

I have manage to run your module after running make table queries in order
to have this unique code in a single table (number - primary key) and in the
second table derived from another make table query I have added an autonumber
field.

Is there any possibility to use my existing tables (3 primary keys) in order
to run your code?



Ο χÏήστης "Duane Hookom" έγγÏαψε:

"Oh yes" to which part of my question? If you still need help, please provide
significant table and field names as well as some sample records and desired
output.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Oh yes,

Thanking you a million Duane !

George

Ο χÏήστης "Duane Hookom" έγγÏαψε:

George,
Do you still need help with the concatenation or have you been able to apply
the function?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Yes, you are absolutely right - Thanks a lot.

But, even if i had created a single table for all storeys I would like also
to concatenate many related records into one field.

Thanks again

Ο χÏήστης "John W. Vinson" έγγÏαψε:

The given link seems to be very promising. Regarding the different data
tables I have, each table represent a different storey of a forest inventory
database and so in each separate data table a different storey is described.
In some cases some species found in the first data table appear also in the
second or even in the third, but in other cases not. So i need to have all
this data in different tables.

No. You do NOT need a separate table for each storey!

Instead, you can use one table with an additional Storey field. Each record
will pertain only to one storey; you can have multiple records (with different
values of Storey) with the same or different species.

Storing data - a storey - in a tablename is simply bad design.

John W. Vinson [MVP]

 

[George]

You are amazing my friend ))

I did it!!!


Thanks a lot

GeorgeC

Ο χÏήστης "Duane Hookom" έγγÏαψε:

Probably something like:
SELECT *, concatenate("SELECT SpeciesID FROM T_StoreySpecies WHERE ForestID
= " & ForestID & " AND CompartmentNo = " & CompartmentNo & " AND PolygonID =
" & PolygonID ) As SpeciesIDs
FROM T_CompartementAndPolygons;

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Dear Duane,

The structure of my tables follows:

T_CompartmentAndPolygons: (ForestID, CompartmentNo, PolygonID all Primary
keys – number)

T_StoreySpecies: (ForestID, CompartmentNo, PolygonID, SpeciesID all Primary
keys – number)


Please, how can I appy your concatenate module using the above structure?

Thanking you so much for your valuable help.

GeorgeC



Ο χÏήστης "Duane Hookom" έγγÏαψε:

There is a SQL statement within the Concatenate function that can specify any
number of fields in a "join". For instance:

Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] ="""
& [FieldA] & """ AND Field1 =" & [Field1] & " AND [FieldDate] =#" &
[FieldDate] & "#")

Or possibly
Concatenate("SELECT FirstName & ' ' & LastName FROM tblA WHERE [FieldA] &
[Field1] & [FieldDate]=""" & [FieldA] & [Field1] & [FieldDate] & """")

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Dear Duane,

Yes, I got managed to use your module with some modifications to my tables
(My main table has 3 primary keys wich are related to my many table, all of
them are number fields). I then use a query to join all those fields to have
a unique code for each record in my tables.

I have manage to run your module after running make table queries in order
to have this unique code in a single table (number - primary key) and in the
second table derived from another make table query I have added an autonumber
field.

Is there any possibility to use my existing tables (3 primary keys) in order
to run your code?



Ο χÏήστης "Duane Hookom" έγγÏαψε:

"Oh yes" to which part of my question? If you still need help, please provide
significant table and field names as well as some sample records and desired
output.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Oh yes,

Thanking you a million Duane !

George

Ο χÏήστης "Duane Hookom" έγγÏαψε:

George,
Do you still need help with the concatenation or have you been able to apply
the function?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Yes, you are absolutely right - Thanks a lot.

But, even if i had created a single table for all storeys I would like also
to concatenate many related records into one field.

Thanks again

Ο χÏήστης "John W. Vinson" έγγÏαψε:

The given link seems to be very promising. Regarding the different data
tables I have, each table represent a different storey of a forest inventory
database and so in each separate data table a different storey is described.
In some cases some species found in the first data table appear also in the
second or even in the third, but in other cases not. So i need to have all
this data in different tables.

No. You do NOT need a separate table for each storey!

Instead, you can use one table with an additional Storey field. Each record
will pertain only to one storey; you can have multiple records (with different
values of Storey) with the same or different species.

Storing data - a storey - in a tablename is simply bad design.

John W. Vinson [MVP]