pattern of data

[subs]

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

 

[John W. Vinson]

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.

 

[subs]

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.

thanks i donot have a idea of VB-- Where do i have to enter this
code? and how do i save it in access?