Combine records in a single record

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
0
Utf
7/23/2007 9:26:06 AM
access 16762 articles. 3 followers. Follow

4 Replies
1024 Views

Similar Articles

[PageSpeed] 53

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" <George@discussions.microsoft.com> wrote in message
news:8F7B19B4-50C5-489C-A132-A79763F7DC49@microsoft.com...
> 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 

0
Allen
7/23/2007 9:37:09 AM
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" <George@discussions.microsoft.com> wrote in message
> news:8F7B19B4-50C5-489C-A132-A79763F7DC49@microsoft.com...
> > 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 
> 
> 
0
Utf
7/25/2007 4:40:00 AM
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" <George@discussions.microsoft.com> wrote in message
news:4600814F-CA20-4849-95B8-3D7F1BF4D523@microsoft.com...
> 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" <George@discussions.microsoft.com> wrote in message
>> news:8F7B19B4-50C5-489C-A132-A79763F7DC49@microsoft.com...
>> > 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) 

0
Allen
7/27/2007 9:18:46 AM
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" <George@discussions.microsoft.com> wrote in message
> news:4600814F-CA20-4849-95B8-3D7F1BF4D523@microsoft.com...
> > 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" <George@discussions.microsoft.com> wrote in message
> >> news:8F7B19B4-50C5-489C-A132-A79763F7DC49@microsoft.com...
> >> > 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) 
> 
> 
0
Utf
7/30/2007 4:22:00 AM
Reply:

Similar Artilces:

ADDING NEW RECORDS IN EXCEL 2000
IN ACCESS, THERE IS AN ASTERISK WHICH YOU CHECK FOR ADDING NEW RECORDS. CLICKING THIS ASTERISK BRINGS YOU RIGHT TO THE BOTTOM OF THE DATA LIST WHERE YOU CAN ENTER A NEW RECORD. WHERE IS THERE A SIMILAR QUICK ICON OR LOCATION FOR ADDING A NEW RECORD IN EXCEL 2000? ( OTHER THAN SCROLLING ALL THE WAY DOWN TO THE BOTTOM OF THE LIST OF ALL RECORDS )? Hi first lease turn off your CAPS-Lock: Difficult to read and considered as shouting in newsgroups For your question: AFAIK this is a new feature of Excel 2003 -- Regards Frank Kabel Frankfurt, Germany CHASPILLS@COMCAST.NET wrote: > IN...

Deleting Records
This is too simple but I'm having a lot of trouble trying to do this. I can create a recordset using DoCmd.ApplyFilter Sql (Sql contains the Sql statement) I can delete a single record using DoCmd.DomenuItem acFormBar, acEditmenu, 8, acmenuVer70 I want to delete all the records in the recordset but I can't make it happen. I tried DoCmd.RunSql, and everything else I can think of. I'm doing something wrong. Can anyone give me a bit of code showing how to delete all the records in the recordset. Thanks The simplest way to do this would be to execute a DELETE query statement. It...

Including records with null values in report
I'm buliding an access database for a small business. I have a parent table which is related to 4 child tables. The four child tables are subforms in my form design. I created a query which pulls data from all 5 tables (parent and children included) using the Q wizard and used this for my report design. Here I realized that if all the tables have data in them, the report works fine. However, if one of the child tables contain null in it's primary key field (i.e no data was entered in that table at all) the whole of that record including data from the parent and child ta...

Retreive Records via Webservice Sample
Hello all, I am not a programmer, but I am trying to find examples of how to retreive records using webservices and display on an external web page. I am aware of the external connector license. Does anyone know good examples? You find a lot of introductory articles and samples on my site. Ronald maintains the CRM blog world (http://ronaldlemmen.blogspot.com/2006/03/crm-blog-world.html) and there are at least three search engines you can use to search these blogs: My own site (the "CRM Search" in the tool bar) Matt Wittemann's site: http://icu-mscrm.blogspot.com/2007/05...

Recording my macro #2
Gord & Julie... Sorry for not responding sooner but I had some difficulty logging in. Just wanted to say thanx for your ideas. I'm heading out tomorrow t pick up Excel VBA for Dummies....it'll probably suit *me* just fine :confused -- shboo ----------------------------------------------------------------------- shboom's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1474 View this thread: http://www.excelforum.com/showthread.php?threadid=26671 ...

Forms & Table Records
I crated an inventory management database, to monitor items in locations. I have a form to move items between location. I have form for sold items. I have one form to add inventory and one to remove inventory. All of the forms except the form that removes the items works ok. I have a table to hold transactions. A table to hold locations. tion box My issue is when I close the form to remove items, it changes the drop down list of location. What ever location I used last is duplicated to the first item on the location combo box list. None of the other forms I use does this. I would appre...

Parameter Query returns no records
I created a select query to pull the records from a table. The query pull all of the fields, including an autonumber field. When I run the select query, all of the fields and records are returns. I then created a parameter for this query. The parameter was in the Taxpayer Name field, using the criteria [What Taxpayer Name do you want?]. When I run the query again, the criteria dialog opens and I enter the taxpayer. The query returns no records. I tried using CAPS LOCK on, off and with and without the wildcard *. The query is set to look in anypart of the field. I also tried the method ...

Open a record on a form depending on the record of another form
I have 2 forms: A client entry form and a another form with an included subform. Both forms have common fields from the client table: clientid name and dob. The subform has the fields clientid and attendance date. I have no problem with the 2nd form with its subform as the clientid syncs without any problem. As it is opened by an event procedure from the client entry form with the VB code: DoCmd.OpenForm "frm_client_dates", acNormal I would like it to go to the current record in the client entry form to save people from doing a search and from them putting data int...

Select TOP 4 Records For Each Group
I have a query that I need to select the TOP 4 records for each distintive group. The layout: 2 Columns of data(Charge and ICV). Charge is an integer and ICV is a single. I need to get the TOP 4 ICVs for each charge. How do I set Grouping and/or Summing to do this? Thank You See: Subquery basics: TOP n records per group at: http://allenbrowne.com/subquery-01.html#TopN -- 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. "Tirelle" <Tirelle@disc...

how to backup Single Mailbox
i need help im using backup exec 9.0,i wanna backup Single Mailbox ,how to do that. thanks Use exmerge and save it to pst. -- Sincerely, D�j� Ak�m�l�f�, MCSE MCSA MCP+I www.akomolafe.com www.iyaburo.com Do you now realize that Today is the Tomorrow you were worried about Yesterday? -anon "ayaz" <anonymous@discussions.microsoft.com> wrote in message news:16f3101c4181d$2b5d0090$a301280a@phx.gbl... > i need help > im using backup exec 9.0,i wanna backup Single > Mailbox ,how to do that. > thanks > ...

Double V. Single Help
Why is 'ans1' true if double and false for single? Module Module1 Sub Main() Dim x As Double / Single Dim y As Double / Single Dim ans1 As Boolean Dim ans2 As Boolean x = 0.3000000000000001 y = Math.Round(x, 2) ans1 = y = 0.3 ans2 = Math.Round(y, 2) = 0.3 End Sub End Module Thanks Sub Main() Dim x As Double, y As Double Dim a As Single, b As Single Dim ansX As Boolean, ansY As Boolean Dim ansA As Boolean, ansB As Boolean x = 0.3: y = Math.Round(x, 2) a = 0.3: b ...

Report from Record In Form
Question: I have a form which provides details on projects my company has been awarded. I have also formulated a report to provide the same details in a printed form. Is there a way to create a button on the form to print the report using the information currently displayed (or the information for the current record), as opposed to having to exit the form and then open the report and print it. Any help would be greatly appreciated. Thank you, Tom Pratt On Wed, 28 Feb 2007 14:36:30 -0500, FIECA wrote: > Question: I have a form which provides details on projects my company h...

Count of records where checkbox 1 is unchecked, checkbox 2 is chec
Hello, I have a table with several check boxes. I'd like to place in the report footer a count of the number of records where both conditions exist: checkbox 1 is checked checkbox 2 is unchecked. Thanks in advance for your help. Ellen EllenM wrote: >I have a table with several check boxes. I'd like to place in the report >footer a count of the number of records where both conditions exist: >checkbox 1 is checked >checkbox 2 is unchecked. Here's one way: =Sum(IIf(checkbox1 And Not checkbox2, 1, 0)) -- Marsh MVP [MS Access] Perfect! ...

FindFirst sticking with first record?
I have a combo box that queries from a table of people to get a list of names, ordered lastname, firstname, peopleID. There are several people in this list that have the same last name, and no matter which person I choose with that last name, Access chooses the first person with that last name. (So effectively, if I have Smith Jon 1, Smith Jane 8, Smith Harold 9074395, it'll always stick with Smith Harold 9074395.) The VBA behind this is: Private Sub Combo26_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs...

Reassign Records & Associated CRM e-mail
I understand the concept of reassigning records but my question is about the e-mail CRM collects. We do not have the e-mail router/connector installed at this point. I am researching the benefits. If I reassign records & they are assigned to a new user will the history of all e-mail conversations with the accounts be available if they were tracked in CRM 3.0? In other words when the old user is deleted and their e-mail box is gone are all the e-mails that were collected with the CRM application and stored in the database still available as searchable records in CRM? This way the ne...

Any ideas? Automatically record % of income in tithe ledger...
SUMMARY: I'd like to select all (or some) income types, apply a certain %, and record that value in a pseudo-account / ledger to see how much I'd like to be giving my LORD this month. It might be easier to think of it as an I-owe-you notepad, if you're not familiar with tithing. Any ideas? I've searched online help, discussion boards, the net, etc with no success. I will be incredibly grateful for any help. DETAIL: Using Money 2007 Deluxe (currently trial edition while I evaluate). I currently use a really complicated Excel spreadsheet I made. It's all manual -...

Outlook client 'View in CRM' does not open the record
My 'View in CRM' button is not opening the CRM record. Pop-up blocker is turned off. Any other ideas? Thank you very much! Suzy ...

Combo box filtering records in a subfrm
Any help is appreciated! I have a combo box on a main form to search for orders by part number on a subform. The part number can end with a U or a UA dependent on how it was purchased. I want to enter a part number, ie...12345...and see ALL orders including part numbers 12345U or 12345UA....I've seen some discussions on this but none have helped my situation or I'm not thinking clearly. I know its really really easy, just can't think! I have this combo box on a different form and the code I'm using works great, however, now I'm dealing with a subform, or...

Home Inventory
I seem to be having a problem amending the information in my important records - amendments are made but when I close Money and go back into it the some of the original records are back - I want to delete some emergency contacts etc but they keep on coming back!! Can anyone help this idiot? In microsoft.public.money, Mike wrote: >I seem to be having a problem amending the information in my important >records - amendments are made but when I close Money and go back into >it the some of the original records are back - I want to delete some >emergency contacts etc but they keep on...

Selecting particular records
Hi I have an excel sheet with dialog box having default values.I wan particular records from the sheet when I click any value in the dialo box.Like when I click the first value it should show me the 1-5 records and for the second value it should show next 50 records in th sheet.I would appreciate if someone helps me. Thank -- Message posted from http://www.ExcelForum.com Perhaps you could add some code to the button that will run an AutoFilter or Advanced Filter on the worksheet, using a start and end value from your dialog box. Record the steps as you apply a filter manually, and you&...

"Too many records" refresh problem with pivot tables
I've got a couple of spreadsheets that are getting an error message of 'too many records to complete operation' when users try to refresh the pivot table in Excel 2003. They do refresh correctly in Excel 2002. Any thoughts or suggestions? ...

Moving database files from single Mail server to a CCR setup.
I would like to setup a CCR environment with two new blades. Currently, I have my databases on a single blade. I would like to create the cluster and then import the databases from the "older" hardware. Is there an easy way to do this? Hi, You will need to create the CCR cluster and move the mailboxes to this cluster. Leif "Maav" <marc@lrsddotnet.donotspam> wrote in message news:EC4D1090-C4BB-49E4-9C97-9F3C368A4E4A@microsoft.com... >I would like to setup a CCR environment with two new blades. Currently, I > have my databases on a single blade. I ...

'04
I have a single deposit transaction that Money will not let me edit. I can edit the transaction above it and below it. The only thing that is more bizzar is the fact that it does not show up in some of the Reports and if it does it is shown as an unassigned transaction even though it is categorized. I tried repairing the Money datafile but it did not work. Any other ideas? I had orginially setup the transaction as a recurring deposit for my paycheck and I would have never noticed the transaction missing from the Report if it had not been my salary. It is also missing all my wi...

Journal no longer records events
My Outlook 98 Journal has been recording Word and Excel events for years and suddenly it has stopped. I may have inadvertently put in a rule, but I cant find anything. Any suggestions would be appreciated. ...

OneNote Recordings not playing back
Anyone have any idea why on certain occasions my OneNote recordings will not play back? The file size is indicative of a recording being made, but only dead silence is played back. This seems to be an intermittent problem. Some of my recordings play back just fine, others don't...no rhyme or reason why this is the case...from what I can tell. Same Problem Here. I opened the file with notepad and I just see alot of stranger empty spaces. Normally, audio files have just character, not spaces! So the file was not recorded, but is there, with size of a normal audio file f...