Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Issue with Access Data in SQL Statement

Posted on 2011-03-11
12
Medium Priority
?
310 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
[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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

705 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