Prev: Extracting a word from a long text field
Next: Combine multiple tables with different date ranges
From: subs on 11 Apr 2010 19:23
I have a table in the below format- For example Customer A and Dzip
678 is being served from ozip 099 twice and 011 once


ozip dzip customer
099 678 A
011 678 A
099 678 A
121 875 B
122 875 B
122 875 B


I need the below format for the above table. the data in the brackets
indicate the no of times ozip appears in the table for a particular
set of dzip,customer

ozip dzip customer
099(2),011(1) 678 A
121(1),122(2) 875 B

Please let me know the query which will give the data in the above
format. Thanks in advance
From: John W. Vinson on 11 Apr 2010 23:02
On Sun, 11 Apr 2010 16:23:34 -0700 (PDT), subs <subbu1678(a)gmail.com> wrote:

>I have a table in the below format- For example Customer A and Dzip
>678 is being served from ozip 099 twice and 011 once
>
>
>ozip dzip customer
>099 678 A
>011 678 A
>099 678 A
>121 875 B
>122 875 B
>122 875 B
>
>
>I need the below format for the above table. the data in the brackets
>indicate the no of times ozip appears in the table for a particular
>set of dzip,customer
>
>ozip dzip customer
>099(2),011(1) 678 A
>121(1),122(2) 875 B
>
>Please let me know the query which will give the data in the above
>format. Thanks in advance

That'll be very difficult or impossible in a query. I think you'll need to
write some VBA code to construct this new string. Let's give it a try
(untested air code):

Public Function Newozip(lngD as Long, strCust As String) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Newozip = ""
Set rs = db.OpenRecordset("SELECT Ozip, Count(*) AS Ct FROM yourtable" _
& " WHERE dzlp = " & lngD & " AND Customer = """ & strCust & """ _
& " GROUP BY ozip;")
Do Until rs.EOF
Newozip = rs!Ozip & "(" & rs!Ct & "),"
rs.MoveNext
Loop
If Len(Newozip) > 0 Then ' trim trailing comma
Newozip = Left(Newozip, Len(Newozip) - 1)
End If
End Function

Then use a query

SELECT Newozip(dzip, customer), dzip, customer FROM yourtable
GROUP BY dzip, customer;

Note that you can't use ozip as both the name of the old field and the new
field.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Extracting a word from a long text field
Next: Combine multiple tables with different date ranges