query

[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

 

[Duane Hookom]

There is a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/...cd8e1c3d-336z-cc55ecz3-3f5ef4zf-91d7zc13.html.

Your Concatenate() syntax might be something like:
SELECT Concatenate("SELECT Ozip & '(' & Count(Ozip) & ')' FROM subbu1678
WHERE dzip='" & Dzip & "' AND Customer='" & Customer & "' GROUP BY ozip") as
Ozips, dzip, Customer
FROM subbu1678
GROUP BY Concatenate("SELECT Ozip & '(' & Count(Ozip) & ')' FROM subbu1678
WHERE dzip='" & Dzip & "' AND Customer='" & Customer & "' GROUP BY ozip"),
dzip, Customer;

 

[subs]

There is a generic concatenate function that should work athttp://www.rogersaccesslibrary.com/forum/generic-function-to-concaten....

Your Concatenate() syntax might be something like:
SELECT Concatenate("SELECT Ozip & '(' & Count(Ozip) & ')' FROM subbu1678
WHERE dzip='" & Dzip & "' AND Customer='" & Customer & "' GROUP BY ozip") as
Ozips, dzip, Customer
FROM subbu1678
GROUP BY Concatenate("SELECT Ozip & '(' & Count(Ozip) & ')' FROM subbu1678
WHERE dzip='" & Dzip & "' AND Customer='" & Customer & "' GROUP BY ozip"),
dzip, Customer;

When i run the query, i am getting an error - Undefined concatenate
function. Should i write a module

 

[John W. Vinson]

When i run the query, i am getting an error - Undefined concatenate
function. Should i write a module

You need to *download* the concatenate function from the webpage Duane
suggested:

There is a generic concatenate function that should work at
http://www.rogersaccesslibrary.com/...cd8e1c3d-336z-cc55ecz3-3f5ef4zf-91d7zc13.html.

Copy and paste it into a new Module; compile it and save it (using some name
different than Concatenate, the names of modules and of the procedures they
contain must be different).