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

Minggu, 27 September 2015

RE: [MS_AccessPros] Fixing a crosstab query

Jan,

If you want to do this in a pure SQL solution, create a ranking query [qrnkEmails] with SQL view of:

SELECT TBL.ID, TBL.Contact_ID, TBL.Email_Address, Count(TBL.Email_Address) AS CountOfEmail_Address
FROM TBL INNER JOIN TBL AS TBL_1 ON TBL.Contact_ID = TBL_1.Contact_ID
WHERE TBL.ID>=[TBL_1].[ID]
GROUP BY TBL.ID, TBL.Contact_ID, TBL.Email_Address;

Then create your crosstab based on qrnkEmails:

TRANSFORM First(qrnkEmails.Email_Address) AS FirstOfEmail_Address
SELECT qrnkEmails.Contact_ID
FROM qrnkEmails
GROUP BY qrnkEmails.Contact_ID
PIVOT qrnkEmails.CountOfEmail_Address;

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Sun, 27 Sep 2015 12:46:53 -0600
Subject: Re: [MS_AccessPros] Fixing a crosstab query

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

_