The first part of an answer to your question should be on finding multiple municipalities for a given zip code.
So given a table full of addresses you want, eventually, to group on unique zip code entries and list all records the unique CITY entries in each group, right. Unless you are acquiring an actual database with that information already broken out.
I’m going to use a simple database with the Contacts template table.
And begin first with query of Contacts table to show for each zipcode in the table how many uinque cities are included
Will do this with query designer:
Which generates the following SQL select statement
SELECT
"PostalCode" "Zipcode",
COUNT( "City" ) "Cities"
FROM "Contacts"
GROUP BY "PostalCode"
ORDER BY "Zipcode" ASC
---- will come back in a few minutes with the next part of the answer
back!
I saved that query definition as ‘qryZipcodeCities’
Now use query designer to start a second query, wich includes both qryZipcodeCities (added this first) and the Contacts Table and the query definition.
Then linked the column ZipCode in qryZipcodeCities to PostalCode in Contacts.
Now I would like to get a result that would show me each uniqe set of zipcodes with city.
So I click the Distinct Values button on the query designer tool bar.
The SQL select statement produced is this
SELECT DISTINCT
"Contacts"."PostalCode",
"Contacts"."City"
FROM "qryZipcodeCities", "Contacts"
WHERE "qryZipcodeCities"."Zipcode" = "Contacts"."PostalCode"
AND "qryZipcodeCities"."Cities" > 1
But it didn’t do quite what I wanted did it.
— back in a minute
Back
Turns out the query does in fact do what I wanted and what it shows me is that I made a typo when I entered the data in the Contacts table, spelling Gaithersbug vs Gaithersburg.
– so onto the next part of the answer and back in a few
After further comments and realizing I missed what you where really getting at I backed up to the database tables again, creating a Base file using the Firebird database, as an aside.
I’ll start with three tables:
Firm - which has some information about the law firm
Attorney - a table for the attorneys in the firm
Client - the firms clients
Attorneys have a list of clients and each Client has one Attorney, for our simple example.
Here is the single relationship created in Base for the three Tables
Going to make a simple form that displays all the clients for the firm, and uses the relationship to pull the attorney data, including a phone number for the Attorneys office, not the general firm number which is displayed in the header of the form.
That form is based on the following query which builds the multi-line strings for display and later printing. The query designer also automatically created the join in the query, because there was a relationship created between Client and Attorney records.
---- in the morning will come back and use this simple database to generate a mail merge.
