Solved

Issue with Access Data in SQL Statement

Posted on 2011-03-11
12
301 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 120

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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, 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…

789 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