Hi Guys,
Any help would be appreciated for the following issue I am having in Access 2003.
Table1
ID Drug
1 Aspirin
1 Paracetamol
2 Loratidine
2 Omeprazole
Needed:
ID Drug
1 Aspirin,Paracetamol
2 Loratidine, Omeprazole
Sounds simple yet complicated for someone with my lack of knowledge. I am using Access 2003 so any help would be appreciated. Thank you.
6 1494
There is no row concat function. The only way to do it in Access is to create a VBA function.
Concatenation can be accomplished with the & operator. - Dim StringA, StringB, StringC as string
-
StringA="Cat"
-
StringB="Tail"
-
StringC=StringA & "," & StringB
Result is StringC="Cat,Tail"
Jim
NeoPa 32,556
Expert Mod 16PB
Personally, I prefer to use Nested Loops and Recordsets for similar type Projects, as in: - Dim MyDB As DAO.Database
-
Dim rstUniqueIDs As DAO.Recordset
-
Dim rst As DAO.Recordset
-
Dim strDrugs As String
-
-
Set MyDB = CurrentDb
-
Set rstUniqueIDs = MyDB.OpenRecordset("SELECT DISTINCT [ID] FROM Table1 ORDER BY [ID]", dbOpenForwardOnly)
-
-
Debug.Print "ID Drug"
-
Debug.Print String(30, "-")
-
-
With rstUniqueIDs
-
Do While Not .EOF
-
Set rst = MyDB.OpenRecordset("SELECT [Drug] FROM Table1 WHERE [ID] = " & ![Id], dbOpenForwardOnly)
-
Do While Not rst.EOF
-
strDrugs = strDrugs & rst![Drug] & ","
-
rst.MoveNext
-
Loop
-
Debug.Print Format$(![Id], "000") & " " & Left$(strDrugs, Len(strDrugs) - 1)
-
strDrugs = ""
-
.MoveNext
-
Loop
-
End With
-
-
rstUniqueIDs.Close
-
rst.Close
-
Set rstUniqueIDs = Nothing
-
Set rst = Nothing
OUTPUT: - ID Drug
-
------------------------------
-
001 Aspirin,Paracetamol
-
002 Loratidine,Omeprazole
Thank you guys... I will try out the options above. I need to run the query/script to have the desired effect for 1500+ unique records, but currently has about 6000+ rows displayed! Unsure if this is achievable but thank you for your replies.
@rndruk
Should be achievable, but may take a little time.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Marek Lewczuk |
last post by:
Hello,
As I'm in the middle of the migration process form mysql to pg I found
that there is no CONCAT function which is available in mysql. Can
anybody tell me how to implement this function using...
|
by: Wayne Aprato |
last post by:
I have several Access 2003 mde databases. When I try to open them in
Access 2002 I get the following error:
"The Visual Basic for Applications project in the database is
corrupt."
...
|
by: Ottar |
last post by:
I've made a program sorting incomming mail in public folder.
The function runs every minute by using the form.timer event.
In Access XP it runs for weeks, no problem. Access 2003 runs the same...
|
by: Steve D |
last post by:
Does anyone know if this can be done?
|
by: deko |
last post by:
Is it possible to develop an Access app in Access 2003 that will run on
Access 2000? Is it just a matter of selecting "Access 2000" from the
Default File Format drop down list on the Advanced Tab...
|
by: nfrodsham |
last post by:
In Microsoft's help literature, it states:
"You can filter out non-unique rows by using the DISTINCT option of an
aggregate function"
I am trying to do this in Access 2003 with the COUNT...
|
by: Kelii |
last post by:
Hi all,
(WinXP Pro SP2, Access 2003)
I'm using Dev Ashish's fRefreshLinks function from AccessWeb
(http://www.mvps.org/access/tables/tbl0009.htm) to relink tables in my
front end to one of...
|
by: =?Utf-8?B?Sm9lbA==?= |
last post by:
Hello:
Anyone have used or know how to calculate the quartile in a data using
access 2003?.
I already have used this function in excel.
if this function doesn´t exist, any idea of how to...
|
by: geolemon |
last post by:
I developed an Access database on my laptop, which has Access 2003.
The workstations in our office have Office 2007 installations, which did not include Access (Student and Teacher edition...
|
by: Lysander |
last post by:
My collegue had to buy a new laptop that came with Office 2007 already installed.
She had Access 2003 installed on top, in a different directory.
None of our 2003 databases will run on her...
|
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: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |