Jan-
The easiest thing to do is to add a function to your database that will return all the emails in one column, separated by a character of your choice. Code pasted below my sig.
SELECT Contacts.Contact_ID, LoopAndCombine("TBL", "Contact_ID", "Email_Address", [Contact_ID]) As Emails
FROM Contacts;
That will return all the emails it finds int TBL related to Contact_ID and separated with commas (the default).
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
Function LoopAndCombine(pTablename As String, pIDFieldname As String, pTextFieldname As String, pValueID As Long, Optional pWhere As String = "", Optional pDeli As String = ", ", Optional pNoValue As String = "", Optional pOrderBy As String = "") As Variant
'PARAMETERS
'pTablename --> tablename or queryname to get list from (ie:"qExpenseItemsTotal)
'pIDFieldname --> fieldname to link on (ie: "ExpenseID")
'pTextFieldname --> fieldname to combine (ie: "ItemAndTotal")
'pValueID --> actual value of ID for this iteration ( ie: [ExpenseID])
'pWhere, Optional --> more criteria
'pDeli, Optional --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
'pNoValue, Optional --> value to use if no data (ie: "No Data")
'crystal 5-6-07, modified 090911, John corrected 14-09-09
'strive4peace2008 at yahoo dot com
On Error GoTo Proc_Err
LoopAndCombine = Null
Dim r As dao.Recordset, mAllValues As String, S As String
mAllValues = ""
S = "SELECT [" & pTextFieldname & "] " & " FROM [" & pTablename & "]" & " WHERE [" & pIDFieldname & "] = " & pValueID & IIf(Len(pWhere) > 0, "And " & pWhere, "") & IIf(Len(pOrderBy) > 0, " ORDER BY " & pOrderBy, "") & ";"
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues & Trim(r(pTextFieldname)) & pDeli
End If
r.MoveNext
Loop
If Len(mAllValues) > 0 Then
mAllValues = Left(mAllValues, Len(mAllValues) - Len(pDeli))
LoopAndCombine = Trim(mAllValues)
ElseIf Len(pNoValue) > 0 Then
LoopAndCombine = pNoValue
End If
r.Close
Proc_Exit:
Set r = Nothing
Exit Function
Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " LoopAndCombine"
Resume Proc_Exit
End Function
=================End====================
On Sep 27, 2015, at 5:35 PM, jan.hertzsch@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have a table with three fields
TBL.Contact_ID, TBL.Email_address(Value 1), TBL.Email_address (Value 2).............
Thanks
- TBl.ID
- TBL.Contact_ID
- TBL_Email address
TBL.Contact_ID links to the name and address table and can have multiple entries
TBL.Email_address records ANY email address I get for a contact. We usually get these from sign up sheets and you would be surprised how many email addresses a person has and uses interchangeably.
I tried to set up a crosstab with TBLContact_ID as the rows and TBL.Email_address as columns. It seems to result in one column for each unique email address. How can I generate a query that shows the following?
TBL.Contact_ID, TBL.Email_address(Value 1), TBL.Email_address (Value 2).............
Thanks
_
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar