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

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.

--
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.

"George" <(E-Mail Removed)> wrote in message
news:8F7B19B4-50C5-489C-A132-(E-Mail Removed)...
> 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

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.
>
> 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.
>
> --
> 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.
>
> "George" <(E-Mail Removed)> wrote in message
> news:8F7B19B4-50C5-489C-A132-(E-Mail Removed)...
> > 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
>
>

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

Perhaps someone else can make a suggestion.

--
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.

"George" <(E-Mail Removed)> wrote in message
news:4600814F-CA20-4849-95B8-(E-Mail Removed)...
> 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.
>>
>> 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.
>>
>> "George" <(E-Mail Removed)> wrote in message
>> news:8F7B19B4-50C5-489C-A132-(E-Mail Removed)...
>> > 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)

Get well soon dear friend.

Ο χÏ�ήστης "Allen Browne" Î*γγÏ�αψε:

> Sorry George: I'm down with the 'flu at present, not able to think straight.
>
> Perhaps someone else can make a suggestion.
>
> --
> 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.
>
> "George" <(E-Mail Removed)> wrote in message
> news:4600814F-CA20-4849-95B8-(E-Mail Removed)...
> > 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.
> >>
> >> 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.
> >>
> >> "George" <(E-Mail Removed)> wrote in message
> >> news:8F7B19B4-50C5-489C-A132-(E-Mail Removed)...
> >> > 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)
>
>