Combine records in a single record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George - 23 Jul 2007 10:26 GMT

Dear friends,

I have two tables, e.g. Table1 (NumberOfPlot - Primary key)

Table2 (NumberOfPlot - foreign key, Species), which are related as One to
Many.

I need to create a query (?) to show in a single record the NumberOfPlot and
only the first 3 related records (species) in the following format, e.g.

ColumnA:121 (which is the plot number)
ColumnB: Pine, PineB, PineN (the first 3 related records of that plot)

Thanking you in advance

GeorgeCY

Reply to this Message

Allen Browne - 23 Jul 2007 10:37 GMT

You will need to write a function to concatenate the values from the TOP 3
related records.

Here's a starting point:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

You will need to modify it so the SQL statement selects just the TOP 3.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Dear friends,
>
[quoted text clipped - 13 lines]
>
> GeorgeCY

Reply to this Message

George - 25 Jul 2007 05:40 GMT

Dear Allen, Thanks a lot for the reply,

I have made some modifications on the structure of mmy database and I cannot
make this task. Here is the new layout of tables:

Tbl_CompartmentsAndPolygons: ForestID, CompNo and PolygonNo are primary keys.

Tbl_MainSpecies: ForestID, CompNo and PolygonNo and SpeciesID are primary
keys.

I would appreciate a lot if you could help me to use the given module in
order to achieve my task.

Thanks again,

GeorgeCY

Ο χρήστης "Allen Browne" έγγραψε:

> You will need to write a function to concatenate the values from the TOP 3
> related records.
[quoted text clipped - 23 lines]
> >
> > GeorgeCY

Reply to this Message

Allen Browne - 27 Jul 2007 10:18 GMT

Sorry George: I'm down with the 'flu at present, not able to think straight.

Perhaps someone else can make a suggestion.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Dear Allen, Thanks a lot for the reply,
>
[quoted text clipped - 44 lines]
>> > ColumnA:121 (which is the plot number)
>> > ColumnB: Pine, PineB, PineN (the first 3 related records of that plot)

Reply to this Message

George - 30 Jul 2007 05:22 GMT

Get well soon dear friend.

Ο χρήστης "Allen Browne" έγγραψε:

> Sorry George: I'm down with the 'flu at present, not able to think straight.
>
[quoted text clipped - 48 lines]
> >> > ColumnA:121 (which is the plot number)
> >> > ColumnB: Pine, PineB, PineN (the first 3 related records of that plot)

Reply to this Message