Base needs to Auto populate 3 different cities and states when zip code it entered for each?

edit

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.

Hello,

Your question is a number of questions in one. This forum is designed to ask one question at a time. This aids in others looking for answers to their questions.

With regard to:

Do I need to create separate tables for Attorney, client and subject?

In order to avoid duplication of data - Yes. This is a reason for using a relational database.

How do I establish the relationships?

There needs to be a common element. In your particular situation, it's probable that this might be a Case # (another table). Much depends upon a complete analysis & design of what you are attempting - beyond the scope of this forum.

How do I auto populate the cities and state for each one of these (people)?

First the data needs to be present - Records of ALL possible zipcodes along with the associated city and state. Another quite large table. Now with this amount of information you should probably be using a database other than the default HSQLDB embedded. This enters into a topic which can be discussed literally in books rather than an answer here.

Saving the city and state information within each of the three mentioned tables doesn't seem to be necessary. If you have the previously mentioned zipcode table, the data can be retrieved anytime from there.

Watching or reading tutorials using Access is not going to help as Base and Access are quite different in in how to make each perform specific tasks. Deal with Base documentation found here -> LibreOffice Base Handbook.