Attn: Alick "Combine three fields into one fields

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Thanks for info

I haven't try it yet but I like ot know if it's possible to create the heading like "Step 1" with notes and then Step 2 with notes and then Step 3 with notes.

Your help would be much appreciated..

Angelo




********************************************

Hi,

You may try update query to combine some fields into one field in the table.

UPDATE Table1 SET c4 = c2 & c3;

Please feel free to reply to the threads if you have any questions or
concerns.



Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.



--------------------
| From: "Bill" <[email protected]>
| Newsgroups: microsoft.public.access.queries
|
| Hi,
| I'm trying to figure it out how to collect other information from three
fields into one fields.
| example:
| Field#1
| Field#2
| Field#3
| New Fileds#4 should show some information from Field#1,#2 and #3. I
would like to see the form show as:
| *********** Field#1 ************
| hello!
| *********** Field#2 ************
| Goodbye
| ************Field#3 ***********
| Your help would be much appreciated.
| *********** The End************************
|
 
Hi Angelo,

The steps are as follows:

1. Create a table with four columns.

Column 1:

Name: c1, data type: autonumber.

Column 2 to column 4's data type are all text.

2. Save the table with name table1.

3. Create a query in design view. (new->design view->OK), close the "show
table" window.
4. Click View->SQL View to design the query
5. Copy and past the query into the query window.
UPDATE Table1 SET c4 = c2 & c3;
6. Save the query.
7. enter some data into the table table1.
8. Run the query. We can see c4 is set to the sum of c2 and c3.

Please feel free to reply to the thread if you have any question or
concerns.





Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.


--------------------
| From: "Bill" <[email protected]>
| Newsgroups: microsoft.public.access.queries
|
| Thanks for info
| I haven't try it yet but I like ot know if it's possible to create the
heading like "Step 1" with notes and then Step 2 with notes and then Step
3 with notes.
| Your help would be much appreciated..
| Angelo
| ********************************************
| Hi,
| You may try update query to combine some fields into one field in the
table.
| UPDATE Table1 SET c4 = c2 & c3;
| Please feel free to reply to the threads if you have any questions or
| concerns.
| Sincerely,
| Alick Ye, MCSD
| Microsoft Online Partner Support
| Get Secure! - www.microsoft.com/security
| This posting is provided "as is" with no warranties and confers no rights.
| --------------------
| | From: "Bill" <[email protected]>
| | Newsgroups: microsoft.public.access.queries
| |
| | Hi,
| | I'm trying to figure it out how to collect other information from three
| fields into one fields.
| | example:
| | Field#1
| | Field#2
| | Field#3
| | New Fileds#4 should show some information from Field#1,#2 and #3. I
| would like to see the form show as:
| | *********** Field#1 ************
| | hello!
| | *********** Field#2 ************
| | Goodbye
| | ************Field#3 ***********
| | Your help would be much appreciated.
| | *********** The End************************
| |
|
 
Hello Alick,

I have a similar problem but it is not so clear cut. I have multiple detail
records associated with a single master record. I am trying to create a RDB
from a flat file and so far I have isolated the master table and the details
table. What I want to do is to take a field from the details
table(CitiesTraveled) which could have 0, 3, 8, 25 records and
combine/concatenate them into 1 field in the master table (CitiesTraveled).
Any ideas?

-Tom
 
Thank you Duane,

I found the Access database and it works great but there is a slight
problem. When I run the query for some reason some of the fields which have
more than 1 NULL value in the details table have multiple commas in them
(e.g. 4 records(3 commas), 6 records(5 commas). The code I used is below.
How do I add code to remove the commas associated with greater than 1 detail
record? P.S. I removed your comments only for quick review. Thanks for your
help

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ")
As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
Pardon me for jumping in.

If I understand your request - you don't want a comma if the value is null -,
then I would add an if statement to the Do While loop.

With rs
...
If Len(Trim(.Fields(0) & vbNullString)) > 0 Then
strConcat = StrConcat & .Fields(0) & pstrDelim
End If
.MoveNext
...

Or alternatively, replace the one line with the following.
strConcat = StrConcat & (.Fields(0) + pStrDelim)

This second choice will still give you commas if the field you are concatenating
contains a zero-length string or spaces.
 
Back
Top