dougv.com « The Web home of Doug Vanderweide

Recently asked on Yahoo! Answers:

SQL / Access query?

I have a 1-table client database which, as well as all the usual name / address info, includes client ‘˜visit’™ fields. Each client may have between 1 and 5 visits per week (Mon to Fri) and each visit can be at a different time, duration and frequency (weekly, fortnightly or monthly) e.g.

Cust XVisit 1 Day1 Hrs1 Time1 Freq1
Visit 2Day2 Hrs2 Time2 Freq2
Visit 3Day3Hrs3 Time3 Freq3etc’¦

Cust YVisit 1 Day1 Hrs1 Time1 Freq1
Visit 2Day2 Hrs2 Time2 Freq2
Visit 3Day3Hrs3 Time3 Freq3etc’¦

I want to generate a report that shows monthly visits – but not details of weekly or fortnightly calls the same customer may have. E.g. If Cust X has 1 weekly visit on a Mon, a fortnightly visit every other week on a Wed – and a visit on the last Fri of each month, I only want to see details of the Fri/monthly visit.

Hope this makes sense!!
Any assistance very much appreciated

My initial answer to the question prompted a reply from the questioner, who noted my suggested SQL query returned all visits for the client, and who also asked about relational databases and expressed some confusion.

I’ll explain why the questioner’s current design is bad, how to make the proper relationships and demonstrate how to best generate queries such as this user wants.

Quick aside: For the record, my original SQL was:

SELECT *
FROM TABLE
WHERE Freq1 = 'monthly' OR Freq2 = 'monthly' OR Freq3 = 'monthly' OR Freq4 = 'monthly' OR Freq5 = 'monthly'

Now that I think about it, of course this query will return all records for the client — including weekly and fortnightly visits — provided he had a monthly visit at some point. What I should have done was UNION ALL five different queries, so that only the monthly visits and associated details were shown:

SELECT Customer, Visit1, Day1, Hrs1, Time1,  Freq1 FROM visits_original WHERE Freq1 = 'monthly' 
UNION ALL SELECT Customer, Visit2, Day2, Hrs2, Time2,  Freq2 FROM visits_original WHERE Freq2 = 'monthly'
UNION ALL SELECT Customer, Visit3, Day3, Hrs3, Time3,  Freq3 FROM visits_original WHERE Freq3 = 'monthly'
UNION ALL SELECT Customer, Visit4, Day4, Hrs4, Time4,  Freq4 FROM visits_original WHERE Freq4 = 'monthly'
UNION ALL SELECT Customer, Visit5, Day5, Hrs5, Time5,  Freq5 FROM visits_original WHERE Freq5 = 'monthly'

That will produce the results he wanted; in the sample database, run the query “visits_original_get_monthly_visits” to see the results of the statement above.

Now, back to the point.

The Original Table Design

The original table looks something like this. (I don’t actually have the database in question, so I’ve reconstructed the table as best I understand it from the questioner’s description):

The problem with this design is that it places, into one record, data that actually encompasses many events and properties. Any time you’re placing more than one event or thing in a single database record, that’s a pretty sure sign your design is wrong.

In our table, visit1 and visit5 are not the same event. They’re both visits that the same person had, true, but they take place at different times, for different durations and for different purposes. Because each visit is a single event within the larger scope of all the visits a customer has, we should be recording those events individually, not alongside each other in a single record.

Why? Because databases are designed to have many records that all store the same structured data. That is, we can have many, many visits, so long as each visit consist of specific, fixed-type columns: time, duration, purpose, type, etc.

If we lump all the visits together in a single record, we lose the ability to treat each visit as an individual event we can evaluate on its own merit. Instead, we have to examine all five visits in the customer record, looking for the details we want.

If we can break each visit out into its own record, and somehow link all the visits together by remembering who had the visit, then we can not only select, update and delete an individual visit more easily, we can more easily group them together based on criteria.

The Customers Table

To get started down the right path, we start with a simple customers table:

Notice that the table is very basic. It contains a primary key column — CustomerID — the customer’s first and last name, his date of birth and Social Security number. In short, it contains only information about a customer that occurs once.

We all only have one first name, last name, birthday and one Social Security Number. It’s common for names, especially family names, to change. It is possible, but unlikely, your SSN may change, and conceivable, but highly unlikely, your birthday may change.

But no matter what, you’ll never have more than one first name, last name, SSN or birthday.

So, for each record in this table, and each column of each record, we know we’re referencing a unique thing — at the record (object) level, you; at the property (column) level, your last name, your birthday, etc.

One object per record, one property per column. That’s our goal.

The Addresses Table

In our example, we may have more than one address per customer, so we put all the addresses in the address table. Each address will have only one street, city, state and ZIP Code, and each address will be of only one type — home, business, etc. — but each customer may have more than one address.

The addresses table therefore gives us our first look at relationships and keys. In relational databases, relationships are the links between records in one table and records in another table. The way those relationships are established is through keys.

The addresses tables contains a primary key named AddressID. In relational databases, a primary key is a column that you are setting up to uniquely identify each record.

The value in a primary key column will never be the same for any two records. You could have 10 million records with the same value in a text column, such as a name or state, but your primary key column will always have a different value for each row. That way, you can always get to a specific record, because it will always have something about it that’s different from every other record in the database: its primary key.

The addresses table also contains a foreign key column — the CustomerID column. In relational databases, a foreign key identifies a record in another table.

So, for each address a customer has, we create a new record in the address table. Each address has its own primary key, and each address also has a foreign key that relates that address to an entry in the customer table.

Examples Of Using Database Relations

Because we’ve broken out our addresses into a different table, we not only can have many addresses for one person, we can also only get those addresses that meet certain criteria.

For example, suppose we want the home addresses only for all our customers.

The type of address is stored in the addresses table’s AddressType column (each address can only have one type: home or business). So, we select the records in the address table that have Home as the value of its AddressType, then join back to it all the records in the customers table that match the addresses table’s CustomerID foreign key.

Let’s look at some data to better illustrate this point. Suppose we have the following customers:

Let’s also suppose you have the following addresses:

OK, let’s get all the home addresses from the database. To do that, we call this SQL:

SELECT c.CustomerID, c.CustomerFirstName, c.CustomerLastName, a.AddressStreet, a.AddressCity, a.AddressState, a.AddressZIP
FROM customers c
INNER JOIN addresses a ON a.CustomerID = c.CustomerID
WHERE a.AddressType = 'Home'

What this query says is, “Find all the records in the addresses table that have the value of ‘Home’ in the AddressType column. Next, get all the records from the customers table that match the address records you just got, on the basis of the values in the CustomerID columns in both tables matching one another.”

Our query will return two records:

We get these two results because even though Bob Jones has two entries in the address table, we specifically asked only for home addresses. And we don’t get any results for Jane Doe because she doesn’t have any entries in the address database.

Hopefully, you can begin to see the benefits of using relationships. We’ll reinforce the benefit of doing so by now dealing with visits.

The New Visits Table Design

The visits table feels much like the addresses table: We have one record per visit and one column per unique attribute of the visit.

For example, since Access can record both the date and the time of a visit in a single Date/Time column, we’ve eliminated the time column from the original table design. Each visit will be its own record. And we’ll restrict the visit frequency entry to match only weekly, fortnightly or monthly, based on a drop-down list we create from another table, as we did with the AddressType field in the addresses table.

So, if you wanted to record a monthly visit for John Public, you would enter a record for them that looks like this:

And now, you should begin to see where querying the database to get all monthly appointments becomes a lot easier. Let’s suppose these are the records you wind up with for the visits table:

Let’s go ahead and write a query that will return all monthly visits:

SELECT c.CustomerID, c.CustomerFirstName, c.CustomerLastName, c.CustomerDOB, v.VisitDateTime, v.VisitLength, v.VisitPurpose
FROM customers c
INNER JOIN visits v ON v.CustomerID = c.CustomerID
WHERE v.VisitFrequency = 'Monthly'
ORDER BY c.CustomerLastName, c.CustomerFirstName, v.VisitDateTime

This query returns three rows: Two for Jane Doe and one for John Public, since Jane Doe had two monthly visits in the visits table and John Public had one. Bob Jones isn’t in the list because although he has two entries in the visits table, one is a weekly visit and the other, fortnightly, so they are excluded.

And that’s the power of a relational database: By using one record per thing, and linking those records together by relationships, we can store all kinds of information — much more than would be possible by putting everything into a single database record with many, many columns that have to be searched, just to find information a properly designed database can quickly get.