473,320 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Concatnate records

21
Hey Guys,
I have a DB with multiple records. (lets take a teacher student example)

Teacher (Tcode, Tname, Taddress)
Student (Scode, Sname, Sgrade)

I want to run a query that will give me
The result such as
QueryResult (TCode, Listof(Sname), Max(Sgrade))

The should be something like this:
ListOf(Sname) = john, sam, jenny, ann
Comam separated list of names

basically the number of records should be the number of Teachers.

I tred using this code i found online but it doesnt work in the sense when the cursor moves onto the 'concatnated field' the values change..

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function Concat2(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then _
  9.             strItems = strItems & ", " & strItem
  10.     Else
  11.         strLastGroup = strGroup
  12.         strItems = strItem
  13.     End If
  14.     Concat2 = strItems
  15. End Function
  16.  
  17.  
  18.  
Sep 15 '10 #1
0 846

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
3
by: CSDunn | last post by:
Hello, I have an Access 2000 Project in which the data comes from a SQL Server 2000 database, and multiple users need to be able to see new records as each user adds records. The users also need...
1
by: Liz | last post by:
I have a table of about 10,000 records where each record has a numeric field named RecIdent. The value of RecIdent starts at 1 and is not sequential. For a given RecIdent, there may be only one...
6
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new...
6
by: Paul T. Rong | last post by:
Dear all, Here is my problem: There is a table "products" in my access database, since some of the products are out of date and stopped manufacture, I would like to delete those PRODUCTS from...
5
by: Grant | last post by:
Hi Is there a way to recover deleted records from a table. A mass deletion has occurred and Access has been closed since it happened Louis
1
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
3
by: Zelalem | last post by:
Hi, I am using VB.6 for developing an application program and come across with the folloing SQL statement that works properly . rs.open"SELECT Count(tblcontrol.id) As FROM tblcontrol WHERE...
4
by: LetMeDoIt | last post by:
Greetings, I'm using ASP to retrieve from MSSQL and I then populate a table. After several months of successfull retrieves, this same code now bombs out. It turns out that if I clear out from...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.