Reverse crosstab using a UNION query; I want to do it better.

[keun]

I start with data that looks like this (I do some cleanup in Excel to get it like this):

ID,RecordID,Field1,Field2,Field3,Field4,Field5
1,ABC_001,this,that,,,
2,GER_056,that,other thing,,,
3,HTR_895,stuff,nothing,more stuff,blah,
4,RTZ_456,that,more stuff,cars,trucks,vans


I turn it into this:
RecordID,Values
ABC_001,that
ABC_001,this
GER_056,other thing
GER_056,that
HTR_895,blah
HTR_895,more stuff
HTR_895,nothing
HTR_895,stuff
RTZ_456,vans
RTZ_456,trucks
RTZ_456,cars
RTZ_456,more stuff
RTZ_456,that

Here is how I get there:

1. Import data into Access, delimit on comma.

2. Run a Union query called qry1Data:

SELECT [RecordID] , [Field1] AS [Values] FROM [tblData] UNION ALL
SELECT [RecordID] , [Field2] AS [Values] FROM [tblData] UNION ALL
SELECT [RecordID] , [Field3] AS [Values] FROM [tblData] UNION ALL
SELECT [RecordID] , [Field4] AS [Values] FROM [tblData] UNION ALL
SELECT [RecordID] , [Field5] AS [Values] FROM [tblData]
ORDER BY [RecordID];

2. Run a SELECT to get rid of nulls:

SELECT qry1Data.RecordID, qry1Data.Values
FROM qry1Data
WHERE (((qry1Data.Values) Is Not Null));

Here is the problem:
I do not know how many "value" fields the original data might have. In this sample we have five, I might have up to 100 or down to one. So I made my UNION query handle five fields. The problem is that if I have less that five my UNION query prompts me for the values of whatever fields I do not have. Is there a way to avoid this prompt? If so I can just make the UNION query handle like 100 fields.

I joined this forum in 2005. I am still a hack.

 

[dhookom]

I would write some DAO code that builds the union query based on looping through the fields collection of the tabledef.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

Thanks, the tabledef is a good place to start.

I joined this forum in 2005. I am still a hack.

 

[dhookom]

I just took a look in my "sandbox" database and found this generic function that might get you started:

Function CreateUnion(strTableName As String, _
        strPKField As String) As String
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Dim strSQL As String
    Dim intFieldNum As Integer
    Set db = CurrentDb
    Set td = db.TableDefs(strTableName)
    For Each fd In td.Fields
        If fd.Name <> strPKField Then
            intFieldNum = intFieldNum + 1
            If Len(strSQL) = 0 Then
                
                strSQL = strSQL & "SELECT [" & strPKField & "], [" & _
                fd.Name & "] as Fld, " & intFieldNum & " As RecNum " & vbCrLf & _
                "FROM [" & strTableName & "] " & vbCrLf & _
                "WHERE [" & fd.Name & "] Is Not Null " & vbCrLf
             Else
                strSQL = strSQL & "UNION ALL " & vbCrLf
                strSQL = strSQL & "SELECT [" & strPKField & "], [" & _
                    fd.Name & "], " & intFieldNum & vbCrLf & _
                    "FROM [" & strTableName & "] " & vbCrLf & _
                    "WHERE [" & fd.Name & "] Is Not Null " & vbCrLf
           End If
        End If
    Next
    CreateUnion = strSQL & ";"
    Set fd = Nothing
    Set td = Nothing
    Set db = Nothing
    
End Function

Running this against the Customers table in Northwind:

? CreateUnion("Customers","CustomerID")
SELECT [CustomerID], [CompanyName] as Fld, 1 As RecNum 
FROM [Customers] 
WHERE [CompanyName] Is Not Null 
UNION ALL 
SELECT [CustomerID], [ContactName], 2
FROM [Customers] 
WHERE [ContactName] Is Not Null 
UNION ALL 
SELECT [CustomerID], [ContactTitle], 3
FROM [Customers] 
WHERE [ContactTitle] Is Not Null 
UNION ALL 
SELECT [CustomerID], [Address], 4
FROM [Customers] 
WHERE [Address] Is Not Null 
UNION ALL 
SELECT [CustomerID], [City], 5
FROM [Customers] 
WHERE [City] Is Not Null 
UNION ALL 
SELECT [CustomerID], [Region], 6
FROM [Customers] 
WHERE [Region] Is Not Null 
UNION ALL 
SELECT [CustomerID], [PostalCode], 7
FROM [Customers] 
WHERE [PostalCode] Is Not Null 
UNION ALL 
SELECT [CustomerID], [Country], 8
FROM [Customers] 
WHERE [Country] Is Not Null 
UNION ALL 
SELECT [CustomerID], [Phone], 9
FROM [Customers] 
WHERE [Phone] Is Not Null 
UNION ALL 
SELECT [CustomerID], [Fax], 10
FROM [Customers] 
WHERE [Fax] Is Not Null 
UNION ALL 
SELECT [CustomerID], [TotalFreight], 11
FROM [Customers] 
WHERE [TotalFreight] Is Not Null 
UNION ALL 
SELECT [CustomerID], [Categories], 12
FROM [Customers] 
WHERE [Categories] Is Not Null 
;

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If you want a generic function to use in a query, I assume you have a table or query of the form
Payee name, payment value, and payment date. Something like

[tt]
ID	PayeeID	PaymentValue	PaymentDate
1	Payee1	($10,000.00)	1/1/1992
2	Payee1	$2,750.00	3/1/1992
3	Payee1	$4,250.00	10/30/1992
4	Payee1	$3,250.00	2/15/1993
5	Payee1	$2,750.00	4/1/1993
6	Payee2	($20,000.00)	11/3/2014
7	Payee2	$4,250.00	1/1/2015
8	Payee2	$6,250.00	2/2/2015
9	Payee2	$5,250.00	4/1/2015
10	Payee2	$6,000.00	7/1/2015
[/tt]

then maybe you have a table of payee names.

[tt]
PayeeID
Payee1
Payee2
[/tt]

You could build a function to use in a query. You would have to provide a lot of information about the table or query. The name of the table/query, name of the payment,date, and payee fields, etc.

Once give that information the function could pass the payments, and payment dates to the xcel function for a specific payee.

Public Function XIRR_Wrapper(Payments() As Currency, Dates() As Date, Optional GuessRate As Double = 0.1)
   'Must add a reference to the Xcel library. Use Tools, References, Microsoft Excel XX.X Object Library
   XIRR_Wrapper = Excel.WorksheetFunction.XIRR(Payments, Dates, GuessRate)
End Function
Public Function AccessXIRR(Domain As String, PaymentField As String, DateField As String, PK_Field As String, PK_Value As Variant, Optional PK_IsText As Boolean = False, Optional GuessRate As Double = 0.1) As Double
  
  'Assumes you have a table or query with a field for the Payee, the Payment, and the date paid.
  Dim Payments() As Currency
  Dim Dates() As Date
  Dim rs As dao.Recordset
  Dim strSql As String
  Dim I As Integer
  
  If PK_IsText Then PK_Value = "'" & PK_Value & "'"
  strSql = "SELECT " & PaymentField & ", " & DateField & " FROM " & Domain & " WHERE " & PK_Field & " = " & PK_Value & " ORDER BY " & DateField
  'Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  'Fill Payments and dates
  ReDim Payments(rs.RecordCount - 1)
  ReDim Dates(rs.RecordCount - 1)
  Do While Not rs.EOF
    Payments(I) = rs.Fields(PaymentField).Value
    Dates(I) = rs.Fields(DateField).Value
    I = I + 1
    rs.MoveNext
  Loop
  'For I = 0 To rs.RecordCount - 1
  '  Debug.Print Payments(I) & " " & Dates(I)
  'Next I
  AccessXIRR = XIRR_Wrapper(Payments, Dates, GuessRate)
End Function

Then you could call this from a query a like this

SELECT tblPayees.PayeeID, AccessXIRR("TblOne","PaymentValue","PaymentDate","PayeeID",[PayeeID],True,0.2) AS XIRR
FROM tblPayees;

Where you data is in TblOne, the field names are PaymentValue, PaymentDate, and PayeeID. Payee id is the field to group on, and it is a Text Field. Your guess is 20%

[tt]
PayeeID	XIRR
Payee1	0.373362535238266
Payee2	0.247556680440903
[/tt]

 

[dhookom]

MajP,
I think your post got posted to the wrong post.

Duane
Hook'D on Access
MS Access MVP