473,840 Members | 1,607 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Combine multiple records into one

I have a table with multiple records in it. I am being forced to
combine multiple records into one record in order to be uploaded to a
website, and I only get one record per client. How can I
systematically combine multiple records into 1?

The table looks like this

Client ID Service Status
1 Doc Pending
1 Test Pending
1 Fix Complete
2 Fix Pending
2 Other Complete
3 Doc Complete
3 Test Complete
3 AA Complete
3 Mail Pending

I need 1 record for Client 1, one for Client 2, one for Client 3.

Any suggestions?

Thanks in advance,
Laura

Feb 27 '06 #1
4 3689
musicloverlch wrote:
I have a table with multiple records in it. I am being forced to
combine multiple records into one record in order to be uploaded to a
website, and I only get one record per client. How can I
systematically combine multiple records into 1?

The table looks like this

Client ID Service Status
1 Doc Pending
1 Test Pending
1 Fix Complete
2 Fix Pending
2 Other Complete
3 Doc Complete
3 Test Complete
3 AA Complete
3 Mail Pending

I need 1 record for Client 1, one for Client 2, one for Client 3.


Use VBA to concatenate the records into one string.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Feb 27 '06 #2
I tried the Module available for doing this at the AccessWeb site below
(using Access 2000)

http://www.mvps.org/access/modules/mdl0008.htm

but, unfortunately, I get either a "Complie Error: User-defined type
not defined".

I really need something similar myself, but can't figure out what I
need to fix.

Tino

Feb 28 '06 #3
See:
http://www.mvps.org/access/bugs/bugs0031.htm
Bugs: Access 2000: Type Mismatch or User Defined Type Not Defined
Author(s)
Dev Ashish
When you create a new database in Access 2000, the default data
access library used is ADO. That is, ADO will be selected under
Tools/References in VBE instead of DAO. Converted databases still retain DAO
as the default library and ADO will not be selected under References.
The "Type Mismatch" error occurs as the global class Recordset is
evaluated as ADODB.Recordset instead of the DAO.Recordset, while the "User
Defined Type Not Defined" error is generated at compile time because there
is no Database object in ADO.

To fix this problem, uncheck the ADO reference and check DAO 3.6
Library as a new reference.

You may still receive this error message when referring to DAO
Recordsets if you have both ADO and DAO selected under References. This is
because the ADO reference is listed above DAO in the References dialog and
hence, is taking precedence at runtime when Access tries to resolve your
data types. To move the DAO reference above the ADO one in the dialog,

a.. Uncheck the ADO reference.
b.. Close the References dialog.
c.. Open the dialog again.
d.. Check the ADO reference.
e.. Close the dialog.
If you are working with a database in which both ADO and DAO
references are selected, the best way is to always fully qualify your data
types. For example:

Dim rsCustomers as ADODB.Recordset
Dim rsInvoices as DAO.Recordset

For more details on which references to use in your application, view
this article.

Article ID: 197110
ACC2000: References to Set When Working with Microsoft Access

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
<Ti*****@gmail. com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .
I tried the Module available for doing this at the AccessWeb site below
(using Access 2000)

http://www.mvps.org/access/modules/mdl0008.htm

but, unfortunately, I get either a "Complie Error: User-defined type
not defined".

I really need something similar myself, but can't figure out what I
need to fix.

Tino

Feb 28 '06 #4
Stephen,

You are an answer to my prayers!!
no
It works perfectly now.

Thanks!
Ti

Feb 28 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
7107
by: mark | last post by:
Access2000 How do I write a query that combines the CTC field from each record below into one record? I need to concatenate the CTC field with a separator, like below: BattID VehicleID STDATE STTIME CTC LKO500HF 00000000 10/27/2003 4:13:51 AM 4 LKO500HF 00000000 10/27/2003 5:13:51 AM 5 LKO500HF 00000000 10/27/2003 10:13:51 AM 6 LKO500HF 00000000 10/27/2003 11:13:51 AM 4
1
23361
by: mojo | last post by:
We have set up an Access database with tables x,y, & z where we would like to have multiple people entering data into a table x. Each person has an identical copy of the database on their PC's. When the person is done entering the data into table x, we want them to e-mail their database to one person who will combine the data from table x into a single database. What's the easiest method to combine the tables into a single table? ...
2
2054
by: BeanCounterCPA | last post by:
I have a database that has multiple records for a single individual. For example, for a database that has the following fields for each loan. Member Loan Int # # Rate Balance 11 1 .06 10,000 22 1 .07 10,000 22 2 .05 5,000
14
2422
by: imani_technology_spam | last post by:
I have the following table; CREATE TABLE ( IDENTITY (1, 1) NOT NULL , (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOT NULL , CONSTRAINT PRIMARY KEY CLUSTERED ( ,
5
3007
by: jhutchings | last post by:
Hello everyone, I have a database where I collect shipment data from various tables. However, I have a problem. Whenever I want to see shipping data for orders that were set to ship on or before a certain date (in this case January 30th) the database will return 2 rows for an order as you can see below. Order ID: Line: Due Date: Qty: Ship Qty: Part #: Shipped: 141285 1 1/30/2006 31 10 S15F-55
8
22199
by: Mark Welch | last post by:
I am at a complete loss on how to do this. I am using Microsoft Access 2000, to implement a preliminary version of a reasonably modest relational database. I have two Access Tables (imported from MS Excel spreadsheets), and I need to somehow get the data for a "Person_ID" brought over from one to the other. In Table_A, there are many records for each person, and the first and last name are included, but the Person_ID field is empty....
4
2586
werks
by: werks | last post by:
Hello experts could someone tell me how can i combine multiple records into one value. Sample in MS Access: (Lastname & "" & "," & " " & Firstname) AS Name How can i do this in MySQL?..tnx in advance -- Kenneth "Better Than Yesterday"
1
3421
by: bluereign | last post by:
Thank you for your assistance. I am a novice looking to JOIN and append or combine records from 2 current Tables into 2 new Tables named below. I have been able to JOIN Tables with the script below. My problem is if I don’t use the WHERE clause in my script below, the script can query up to 3 records with the same “LoanNo” due to the fact that there can be up to 3 “TypeCodes” in each Table (Borrower, Co-Borrower 1 and Co-Borrower 2...
3
5653
by: klbachrodt | last post by:
Hi all - I've been browsing this forum for a while now, looking for help, and finally decided to join and ask my question, since I'm not terribly good at writing SQL queries in Access. Here is my dilemma: Table U contains 1 field (Field F, primary key = no dupes) Table N contains 50 fields (including Field F, same as in Table U) * Field F in Table N contains multiple records, with dupe values Would like to create a new Table (or just...
0
9860
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9699
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10660
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10301
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9440
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7838
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7023
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3138
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.