?
Solved

how to reverse fields in a query

Posted on 2003-03-24
17
Medium Priority
?
245 Views
Last Modified: 2010-08-05
I have a table with several loans and different descripions. LOan has several rows with description . I need to display each loan in one row and several fields , let say first three.  Order is not important.
Looks like that:
 Table:
loan_id | descr
________|_______
1       | green
1       | red
1       | Yellow
2       | red
2       | white

I need to get a result like this:
1  | green  | red  | Yellow
2  | red    | white|    


0
Comment
Question by:yelemarg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +4
17 Comments
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8197051
does this mean, that you are satisfied with 3 out of e.g. 5 descriptions of a loan with more than 3 descriptions ?
0
 
LVL 28

Expert Comment

by:TextReport
ID: 8197075
The way I do this is through a function. The following function needs to be pasted into a new module.

Then in your query you can create a new field

NewField: GetFieldFromMultiRecords_NumberID([Loan_id],"TableName", "loan_id", "descr")

Cheers, Andrew

Function GetFieldFromMultiRecords_NumberID(plngID As Long, pstrTable As String, pstrIDField As String, pstrMergeField As String) As String
Dim db As DAO.database
Dim rst As DAO.Recordset
Dim strReturnValue As String

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT [" & pstrMergeField & "] FROM " & pstrTable & " WHERE [" & pstrIDField & "]=" & plngID)
    Do While Not rst.EOF
       strReturnValue = strReturnValue & ", " & rst("[" & pstrMergeField & "]")
       rst.MoveNext
    Loop
   
    If Len(strReturnValue) > 0 Then strReturnValue = Mid(strReturnValue, 3)
   
    GetFieldFromMultiRecords_NumberID = strReturnValue
   
End Function
0
 
LVL 28

Expert Comment

by:TextReport
ID: 8197106
Just found my updated version of this that allows you to specify in the one function whether it is a number, text or date field you are using as the ID

NewField: GetFieldFromMultiRecords(1, [Loan_id], "TableName", "loan_id", "descr")

You will need a reference in your module to Microsoft DAO which is in your module select Tools, Reference and ensure Microsoft DAO is selected.

Cheers, Andrew

Function GetFieldFromMultiRecords(pbytKeyType As Byte, pvarID As Variant, pstrTable As String, pstrIDField As String, pstrMergeField As String) As String
Dim db As DAO.database
Dim rst As DAO.Recordset
Dim strReturnValue As String
Dim strWHERE As String

    strWHERE = " WHERE [" & pstrIDField & "]="
    Select Case pbytKeyType
        Case 1: strWHERE = strWHERE & pvarID
        Case 2: strWHERE = strWHERE & Chr(34) & pvarID & Chr(34)
        Case 3: strWHERE = strWHERE & "#" & Format(CDate(pvarID), "MM/DD/YYYY HH:NN:SS") & "#"
    End Select
   
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT [" & pstrMergeField & "] FROM " & pstrTable & strWHERE)
    Do While Not rst.EOF
       strReturnValue = strReturnValue & ", " & rst("[" & pstrMergeField & "]")
       rst.MoveNext
    Loop
   
    If Len(strReturnValue) > 0 Then strReturnValue = Mid(strReturnValue, 3)
   
    GetFieldFromMultiRecords = strReturnValue
   
End Function
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Expert Comment

by:pcunha
ID: 8197233
Create a second Table with the same fiels, and
run the followin procedure


Sub Make_reverse_table()

Dim db As DAO.Database
Dim rs1, rs2 As DAO.Recordset
Dim sqlstr As String
Dim loan_id_tmp As Integer
sqlstr = "SELECT * FROM Table1 order by loan_id"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(sqlstr)
Set rs2 = db.OpenRecordset("Table2")

loan_id_tmp = 0
rs1.MoveFirst

While Not rs1.EOF
If loan_id_tmp <> rs1!loan_id Then
    rs2.AddNew
    rs2!loan_id = rs1!loan_id
    rs2!descr = rs1!descr
    rs2.Update
    rs2.MoveLast
    loan_id_tmp = rs1!loan_id
Else

    rs2.Edit
    rs2!descr = rs2!descr & " | " & rs1!descr
    rs2.Update
   
End If
rs1.MoveNext
Wend

rs1.Close
Set rs1 = Nothing

rs2.Close
Set rs2 = Nothing

Set db = Nothing

End Sub
0
 
LVL 6

Expert Comment

by:TheAmigo
ID: 8197459
A crosstab query will get you something pretty close to that.  Give this a try:

TRANSFORM Count(Loan.Loan_ID) AS CountOfid
SELECT Loan.Loan_ID
FROM Loan
GROUP BY Loan.Loan_ID
PIVOT Loan.descr;

Is that what you want?
0
 

Expert Comment

by:pcunha
ID: 8197576
That gives something like:

Loan_ID     a     b     c     d     e
1     1     1     1          
2                    1     1

That's not what yelemarg asked for...
0
 
LVL 6

Expert Comment

by:TheAmigo
ID: 8198405
The output from the crosstab query is different (same idea but slightly different format), however, it's more useful.

Looking at the results of the crosstab, it's quite easy to select a column (e.g. "red") and sort on it.  This lets you quickly see all the loans that have (or don't have) "red" as one of their descriptions.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8198464
I think it depends on whether yelemarg needs the results to be in different fields or in a single, concatenated field.

For a single, concatenated field, TextReport's solution will work (or pcunha's, if you prefer the temp table method although this will require managing the contents of the table whenever the query is needed), or if multiple fields are required, TheAmigo's crosstab is probably the best bet.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8198474
A point about the crosstab - they're more difficult to use to create forms and reports unless you know the field header names beforehand (which in this case would each unique value of descr). If you have more than 255 possible descr's, you will need to look at the single field method.
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8198754
i did a solution to a simular problem, where the output was required as a file not a table
the output has exactly the form you want
fileds are seperated by a Tab
to have it as a table, you can reimport it

***********************************************
Private Sub Output_Click()
Dim varFile As Variant
Dim strFile, strText, lastField, lid As String
Dim i,uct As Integer

varFile = FreeFile
strFile = "your path and filename.txt"
uct = DCount("[descr]", "LOan")
DoCmd.GoToRecord , , acFirst

Open strFile For Append As #varFile
lid = Str$(loan_id) ' convert to string
lid = right(lid;len(lid)-1) 'to get rid of the blank
strText = lid & Chr(9) 'add a Tab
lastField = lid
For i = 1 To uct
If lid = lastField Then GoTo ContN  'no new user, append desc
Print #varFile, strText 'write loan_id & desc to file
lid = Str$(loan_id) ' convert to string
lid = right(lid;len(lid)-1) 'to get rid of the blank
strText = lid & Chr(9) 'start new user
lastField = lid
ContN:
strText = strText & desc & Chr(9)  'append desc
DoCmd.GoToRecord
Next i
Print #varFile, strText 'write last loan_id & desc sto file

Close #varFile
End Sub
****************************************************

there should be a more elegant way to do this
i give it as a general idea to get to a output with seperate fields and no empty fields in between as you get from the crosstab
if you have the loan_id as string in the table, it will help already a little bit
 
0
 

Expert Comment

by:pcunha
ID: 8201560
yelemarg,

If you want to use the reverse for one record at time, and speed isn't important the TextReport solution is the best one.

If you want to reverse all you records and make a quick full analyse of your table, my solution is more efficient
0
 

Author Comment

by:yelemarg
ID: 8202870
Thank you all, I am still trying to get it work. Crosstab will not help since I will get different column names each time the desc is changing.
I need it to create a report to show first three desc belong to one loan (these are three separate fields, sorry it is not "|").  I need to write a code that goes thru each loan, look on desc and place it to desc1, then go to next desc for the same loan and if it is different from desc1, place it on next field in a new table desc2, ... same with desc3. If it is more then three different, I don't care. If it is only one  for each loan, desc2 and desc3 will be blank.  I didn' code for a while.  Feel very rusty doing that. I can use help.
Thank you
0
 
LVL 28

Accepted Solution

by:
TextReport earned 2000 total points
ID: 8206886
Ok taken from the loan table above you can do this with the following table, data and queries.

Table and Data
LoanID     Loan_Desc
1     Green
1     Red
1     Yellow
1     zzz
2     Orange
2     Green

qryLoanID_1
SELECT Loan.LoanID AS ID, Min(Loan.Loan_Desc) AS DESCR
FROM Loan
GROUP BY Loan.LoanID
WITH OWNERACCESS OPTION;

qryLoanID_2
SELECT Loan.LoanID AS ID, Min(Loan.Loan_Desc) AS Descr
FROM Loan
WHERE (((Loan.Loan_Desc) Not In (select descr from qryLoanID_1 where id=[loanid])))
GROUP BY Loan.LoanID
WITH OWNERACCESS OPTION;

qryLoanID_3
SELECT Loan.LoanID AS ID, Min(Loan.Loan_Desc) AS Descr
FROM Loan
WHERE (((Loan.Loan_Desc) Not In (select descr from qryLoanID_1 where id=[loanid]) And (Loan.Loan_Desc) Not In (select descr from qryLoanID_2 where id=[loanid])))
GROUP BY Loan.LoanID
WITH OWNERACCESS OPTION;

Now you can join these queries in the final query
SELECT qryLoanID_1.ID, qryLoanID_1.DESCR AS Desc1, qryLoanID_2.Descr AS Desc2, qryLoanID_3.Descr AS Desc3
FROM (qryLoanID_1 LEFT JOIN qryLoanID_2 ON qryLoanID_1.ID = qryLoanID_2.ID) LEFT JOIN qryLoanID_3 ON qryLoanID_2.ID = qryLoanID_3.ID
WITH OWNERACCESS OPTION;

and this produces the results
ID     Desc1     Desc2     Desc3
1     Green     Red     Yellow
2     Green     Orange    

Cheers, Andrew
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8207027
I agree, Text Reports method is exact, what you wanted.

As I have already mentioned, I had a simular question but with much more collums, I did an other method with writing an intermediate file. For your interest i post it here. I build the example with TextReports Field Names:

***************************************
Private Sub Output_Click()
Dim varFile As Variant
Dim strFile, strText, lastField As String
Dim i, uct, llim, lsw As Integer

varFile = FreeFile
strFile = "loans" & Str$(Format(Date, "yymmdd")) & Str$(Format(Time, "hhmmss")) & ".txt"
uct = DCount("[Loan_Desc]", "Loan")
llim = 3
lsw = 0
DoCmd.GoToRecord , , acFirst

Open strFile For Append As #varFile
strText = "loan_id;desc1;desc2;desc3"
Print #varFile, strText
strText = LoanID
lastField = LoanID
For i = 1 To uct
 If LoanID = lastField Then GoTo ContN  'no new LoanID, append Desc
 Print #varFile, strText 'write LoanID & Desc to file
 strText = LoanID  'start new LoanID
 lastField = LoanID
 lsw = 0
ContN:
 If lsw > 2 Then GoTo NextRec
 strText = strText & ";" & Loan_Desc 'append Desc
 lsw = lsw + 1
NextRec:
 
DoCmd.GoToRecord
Next i
Print #varFile, strText 'write last LoanID & Desc to file
Close #varFile
DoCmd.TransferText acImportDelim, , "LOanTrans", strFile, True

End Sub
******************************************
0
 

Author Comment

by:yelemarg
ID: 8211174
Thank you all.  I am still looking thru the code.  I learned a lot from you. The best solution in my case was from TextReport.  I still have to now how to accept his answer so he'll get points.  I am definetly going to use yor help again.  Thanks, you saved me.
0
 

Author Comment

by:yelemarg
ID: 8211186
Thank you Andrew.  I may use your help again in a future.
0
 

Expert Comment

by:leaded
ID: 8925331
Hi, I have been facing the same problem for a while and developed a solution:
 
My table in Excel basically looks like a crosstab query:

Supplier     Question    Jury1   Jury2  Jury3
a                 1                  4          ......
a                 2                  5          ......
b                 1                  6
b                 2                  2
c                 1                   3
c                 2                  2

The solution involves an identity matrix (1s on the diagonal, 0s otherwise) with with rows for each jury member Jury_i and columns with coefficients Jury_iCoeff:

              Jury1_Coeff   Jury2_Coeff
Jury1           1                      0
Jury2           0                      1

Then in a query I carry out a matrix multiplication. Note: [Jury1]*[Jury1_coeff]+[Jury2]*[Jury2_coeff]+[Jury3]*[Jury3_Coeff]...

This works and is fast enough, even with many thousands of records. BUT you have to create the Identity-Matrix and the names of the fields and write the matrix multiplication and so on.

This may also be helpful.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question