ACCESS FEED: Re: [MS_AccessPros] Fixing a crosstab query

Minggu, 27 September 2015

Re: [MS_AccessPros] Fixing a crosstab query

Jan-

Your design sounds fine. It's a good relational design that allows you to do a "pivot" like this with a bit of code.

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

http://www.viescas.com/

(Paris, France)

On Sep 27, 2015, at 8:46 PM, Jan Hertzsch jan.hertzsch@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks. Is there a better way to have designed this?

Sent from my iPhone

On Sep 27, 2015, at 10:18 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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
http://www.viescas.com/
(Paris, France)

================Function Start (Create a Module======================
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.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

_