Solved

Issue with Access Data in SQL Statement

Posted on 2011-03-11
12
300 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

803 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