Solved

Issue with Access Data in SQL Statement

Posted on 2011-03-11
12
298 Views
Last Modified: 2012-05-11
Hi.  I have issue with SQL Statement

DoCmd.RunSQL "SELECT [Combined-Tax-Data].LoanID, Sumtax([LoanID]) AS TaxTotal, ConcatVar([LoanID]) AS Explanation INTO temp_deliquency FROM [Combined-Tax-Data] GROUP BY [Combined-Tax-Data].LoanID, Sumtax([LoanID]), ConcatVar([LoanID]);"

Data is truncated: If you see Attached Excel File I'm trying to combine all tax explanation.  Using above SQL statement Data is truncated: you may from the top to "$94.80, HEALTH/EXTR".  Data for $90.87, HEALTH/EXTR
$90.87, HEALTH/EXTR
$505.44, EM REPAIR/NEW
Not included into Combined value.

Purpose was to arrange everything from top as array:

Example:

Explanation
$739.74, RENT STAB;$411.50, RENT STAB;$514.37, RENT STAB;$328.81, RENT STAB;$411.01, RENT STAB;$431.56, RENT STAB;$464.80, EM REPAIR/NEW;$122.52, EM REPAIR/NEW;$141.20, INSP;$129.70, INSP;$94.80, HEALTH/EXTR;$94.80, HEALTH/EXTR;$94.80, HEALTH/EXTR;$94.80,"

Question: How can I change VBA Code or SQL Statement to get full data without truncation.

Current data type is text 255.  I tried to change data type to memo and append data to exising text field with data type "memo".  Not working

Please see file and code data.
Function ConcatVar(sVar As Variant) As String
Dim rs As DAO.Recordset, s As String

Set rs = CurrentDb.OpenRecordset("select * From [Combined-Tax-Data] Where [LoanID]='" & sVar & "'")

    rs.MoveFirst
 
    Do Until rs.EOF
      s = s & rs("[Tax_Explanation]") & ";"
      rs.MoveNext
    Loop

    ConcatVar = Left(s, Len(s) - 1)
End Function

Open in new window

Today-work.xls
0
Comment
Question by:maximyshka
  • 8
  • 4
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35111401
your information is getting truncated due to the Group By clause in your query.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35111430
use VBA codes and recordset to achieve this.
0
 

Author Comment

by:maximyshka
ID: 35111583
Thanks Capricorn.

However, how can I achieve it it using vba code:

Suggestion:

Set rs = CurrentDb.OpenRecordset("SELECT [Combined-Tax-Data].LoanID, Sumtax([LoanID]) AS TaxTotal, ConcatVar([LoanID]) AS Explanation INTO temp_deliquency
FROM [Combined-Tax-Data]
Where [LoanID]='" & sVar & "'"
GROUP BY [Combined-Tax-Data].LoanID, Sumtax([LoanID]), ConcatVar([LoanID]), [Combined-Tax-Data].LoanID)

I changed little query but it is not working.  Main purpose it to put data into array
0
 

Author Comment

by:maximyshka
ID: 35113501
Would you please help me to change SQL statement.  Part of vba code i put above with my changes which are not working
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35113712
upload a copy of the db..
0
 

Author Comment

by:maximyshka
ID: 35133988
Attached, you can find testdb.

See field Combined_Tax_Data.  Would you please combined all explanations for all fiedls Loan # 8009280299-200703.  All fields listed in combined explanation should equal sum listed in the function Sumtax([LoanID]).

Please let me know if you have any questions

TestDB.mdb
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:maximyshka
ID: 35134023
Additional information: please see frm_import.  Button: Update Input File: OnClick Event.  Code is listed there
0
 

Author Comment

by:maximyshka
ID: 35137857
Capricorn, did you see test database which I uploaded for you?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 35138362
test this


TestDB.mdb
0
 

Author Comment

by:maximyshka
ID: 35139121
Would you please tell me how it is working

Thanks, instead of make table query:

DoCmd.RunSQL "SELECT [Combined-Tax-Data].LoanID, Sumtax([LoanID]) AS TaxTotal, ConcatVar([LoanID]) AS Explanation INTO temp_deliquency FROM [Combined-Tax-Data] GROUP BY [Combined-Tax-Data].LoanID, Sumtax([LoanID]), ConcatVar([LoanID]

You open recordset group by loanid
and then use following code:

 With rsTemp
        .AddNew
        !LoanID = rsLoanID!LoanID
        !TaxTotal = SumTax(rsLoanID!LoanID)
        !Explanation = ConcatVar(rsLoanID!LoanID)
        .Update
Would you please tell me how this is working?

You used 2 recordsets and looks like one inside another.  I'm little lost

Dim rsTemp As DAO.Recordset, rsLoanID As DAO.Recordset
Set rsTemp = CurrentDb.OpenRecordset("temp_delinquency")

Set rsLoanID = CurrentDb.OpenRecordset("select LoanId from [Combined-Tax-Data] group by LoanID") -- as I understand you open one recordset and put there unique loan id.
How ccde below is working?
With rsTemp
        .AddNew
        !LoanID = rsLoanID!LoanID
        !TaxTotal = SumTax(rsLoanID!LoanID)
        !Explanation = ConcatVar(rsLoanID!LoanID)
        .Update
0
 

Author Comment

by:maximyshka
ID: 35139291
Based on the debugging window: looks like you add new record, run functions which contantenate records and sum of total value.  All data from function you put into recordset !TaxTotal and !Explanation and With Update you update table.
Then you close both recordsets

2 questions: why it did not work with previous statement?
2nd If I need to group everything also by due date will this work with statement

CurrentDb.OpenRecordset("select LoanId from [Combined-Tax-Data] group by LoanID", DueDate?
0
 

Author Closing Comment

by:maximyshka
ID: 35141740
Capricorn, please answer last my questions. Understanding is important
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now