Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to reverse fields in a query

Posted on 2003-03-24
17
Medium Priority
?
252 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
  • 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

564 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