pattern of data

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
0
subs
4/11/2010 11:23:34 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
690 Views

Similar Articles

[PageSpeed] 44

On Sun, 11 Apr 2010 16:23:34 -0700 (PDT), subs <subbu1678@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]
0
John
4/12/2010 3:02:56 AM
Reply:

Similar Artilces: