Advanced Query

[Bushido121]

Hello, listed below is an example of what I'm trying to do. Any thoughts?

Example of Table
AccountNum Desc Amount Comments
---------------------------------------
112233344445555 #1 100.00 C#1
112233344446666 #2 150.00 C#2
112233344447777 #3 50.00 C#3
112233344448888 #4 200.00 C#4

AccountNum
11 - Corp
22 - Company
333 - Major
4444 - Minor
etc...

I already have a query that Compresses the data by Company, Major, Minor. It sums the amount field, and takes the first row for Description, and Comments

Example of Query Results
AcctNumber Desc Amount Comments
----------------------------------
223334444 #1 500.00 C#1

Select Distinct (Co-Maj-Min)
First(Description)
Sum(Amount)
First(Comments)

What I need to do is instead of taking the first of the description, and comments fields, is to be able to concatenate everything so I would end up with this:

Result Needed
AcctNumber Description Amount Comments
--------------------------------------
223334444 #1#2#3#4 500.00 C#1C#2C#3C#4

Thanks for anything you can help with.

Jerid

[This message has been edited by Bushido121 (edited 11-13-2001).]

 

[pdx_man]

You can use the & (ampersand) to concatonate the fields into a calculated field:

ConField: [AcctNumber]&[Desc]&[Amount]&[Comments]

or whatever the names of your subtotaled fields are ...

 

[Bushido121]

Thanks, but what I need to do is concatenate the rows in the table for that one field.

Example: All of the Store rows for that one specific account number would be concatenated and all of the descriptions for that account number would be concatenated.

Giving me the AcctNumber, All of the Stores, Sum of the Amount field and All if the descriptions.

I hope this does a better job explaining.

Thanks.

Jerid

 

[pdx_man]

How do you rate your VBA skills? This is going to require some coding. The basic algorithm is:

Function SquishIt (AcctNum as String) as String

Dim SQL_Str, varDesc, varComm as String
Dim curAmt as Currency
Dim dbs as Database
Dim rst as Recordset

Set dbs = CurrentDB
SQL_Str = "Select * From Tbl_My_Table WHERE AccountNum = '" & AcctNum & "'")
set rst = dbs.openrecordset(SQL_Str)

varDesc = ""
varComm = ""
curAmt = 0

Do Until rst.EOF

varDesc = varDesc & rst!Desc
varComm = varComm & rst!Comments
curAmt = curAmt + rst!Amount
rst.movenext

Loop

SquishIt = AcctNum & varDesc & curAmt & varComm
rst.close
dbs.close

End Function

Call this function from a query that has unique Account numbers. Use the DISTINCT keyword in the query or in the queries properties, set the Unique Values to Yes

HTH

[This message has been edited by pdx_man (edited 11-19-2001).]

 

[Bushido121]

Thanks for your help. I was trying to avoid using DAO or ADO just because of the time it takes compared to a query. Just wanted to see if there was a way to do it that I wasn't aware of.

I looks like SquishIt is the only way.

Thanks Again.