advice on a database design please

[Sheilac]

Hi all;

I’m a newbie here and it looks as if I might be able to get a bit of advice on what I think should be a fairly simple project that would actually have some value in my real world if I managed to get it working.

I have successfully set up a web site with a MySQL database and I can connect to it using some simple PHP code, so that part is out of the way. The challenge will be to build on it.

Here is the background. I would like to build a database to record attendance at a variety of activities that are organized into categories. The first name and last name of the person attending, the activity category such as Outdoor Sports, Cultural or Intellectual and the actual activity is really all that is required.

Examples of activities by category might be as follows:

Category = Outdoor Sports (Activities = Archery, Swimming, Tennis)
Category = Cultural (Art, Music, Drama)
Category = Intellectual (Lectures, Seminars, Discussion Groups)

The categories are fairly static, however the actual activities are somewhat dynamic in that new ones are regularly introduced and some are dropped from the program.

Reports that would be desired might be as follows:

• All activities attended by a person between two dates, or for the current or last month, etc.
• Persons who attended a particular activity.
• Persons who attended activities in a particular category.
• Count of attendance for a particular activity between two dates.
• Count of attendance for a particular category between two dates.
• And probably a whole lot of goodies that I haven’t thought about yet.

Some database maintenance requirements would be:

• Add, delete or modify a person (First name, Last Name)
• Add, delete or modify an activity, and possibly a category.

For the record, there could be a few hundred people in the program, possibly about 10 categories of activities, and approximately 50 unique activities. Occasionally an activity might be scheduled more than once on a particular day, so I will need to think about how to deal with that situation.

Currently entering the data is a tedious task, and I am thinking that I would like to set up a data entry form where the user could use drop down list boxes to select the person who attended with a mouse click, another drop down box for the category that would then offer a list of specific activities. It would be handy if the date that the activity could be entered by means of a calendar. I actually built something like this using VBA in Excel, so it will be an interesting challenge for me to see if I can make it happen in PHP.

So to the question on the design.

I’m thinking that I should have a “person” table that contains the first and last name of the person and a master_id field. Then a table that has the date and a master_id field that would be the same as and linked to the master_id in the person table. Then the same idea with the category table that would have a master_id field linking back to the “person” table’s master_id. And the same design for the activity table.

Table = person
Fields = id (index), master_id. first_name, last_name

Table = date_time
Fields – id (index), master_id, activity_date, activity_time

Table = category
Fields = id (index), master_id, activity_category

Table = activity
Fields = id (index), master_id, activity

I would like to have some confidence that I am on reasonably solid ground with this database design before I get to far along with this. This is the only design that I can come up with that I think will satisfy the reports that I would like to extract from the database. However I know very little about this, hence my pleas for some help.

Any advice or suggestions would be mighty welcome, and if you could point me to some PHP code and a MySQL database that might behave somewhat like I am after, it always helps my learning process.

I hope that this all makes sense.

Thanks for the time that you are taking to help me.

Sheila

[almostbob]

My only answer, dont use any of the sql date formats, text dates are tedious to manipulate, they have to be converted to numeric for any operation which takes al lot of time and processing
a timestamp takes 10bytes stores date and time in one column and can be mined for any part of the date and time, and need only be converted once on input
a text date time is
january 1 2009 10:45.22am (25 bytes) not a lot on 1 row, but a lot on a million rows
speed test,
examine 10000 text records of dates
to find dates between December 1 2008 at 12:15pm and 1 January 2009
echo $date;
or
examine 10000 TIMESTAMPs >1228133700 and <1230768000
echo date(d m y, $timestamp)

bit over passionate 'bout this ... :)

[timothybard]

I don't see a need for the master_id. Could you explain it further? The problem I have, for example, is that the date and time of an activity is not related to the people.

Here is the design that comes to my mind:
person: id (primary key), first_name, last_name
category: id (primary key), category_name
activity: id (primary key), category_id (foreign key), activity_name
session: id (primary key), activity_id (foreign key), date, time
attendance: id (primary key), session_id (foreign key)

This design allows you to track each category, which activities are in each category, what times each session of the activity is at and who attended each session of the activity.

[Sheilac]

Thanks for the advice timothybard. The master-id thing came from a PHP MySQL database example for an address book that I found in Sam’s Teach Yourself PHP, MySQL and Apache, which seemed to have some elements that behaved somewhat like I am looking for in this project. Clearly I don’t know much about this stuff however I have six books on the table here beside me as I try to pick up some knowledge on the subject.

I think that the way that the address book PHP works is to insert the name of the person into the table and then extract it again using a piece of code that looks like this:

master-id = mysql_inseret_id ( );

which is then used in other tables. What I am getting from your advice is that I don’t really need this field but instead can rely on the foreign key to make the associations.

I confess that I don’t fully understand your suggested design using a session, so I will ramble on in the direction that I was headed and maybe you will be able to extract enough information to get me on track.

I did consider using something like a “session” or an “activity event” as the core to all of this and I’m thinking that this is what you are pointing me toward. I opted to see if I couldn’t make it work with just the 4 tables. As stated this project is simply something that has the potential to improve significantly on a spreadsheet application that is currently used, and my interest is more in gathering some knowledge about databases and PHP (or something similar) than coming up with the most eloquent solution, so I am delighted to try a few things that might contribute to my education. And I have the luxury of lots of time. It is always more interesting for me to work on something that has a direct application in my world than exclusively textbook examples.

On further reflection, I decided to add two more fields. The first would be a field called “status” in the “person” table that would serve to indicate whether the member was current or lapsed. The other additional field would be a “comments” field that would live in the “activities” table. I’m not sure if this would actually get a lot of use, however it just seems like a good idea at the time.

Considering the relationship between the tables, here is what I came up with;

A person can participate in many activities and any activity could be attended by many persons. Therefore it is a many-to-many relationship requiring an association table.

A “category” can have many activities, however an activity can only belong to one category, hence a one to many relationship.

The “date and time” issue does perplex me. I am going with the idea that a date may include many activities and that an activity may be repeated on many dates so a many-to-many relationship with an association table.

Here is what the table and field design might look like then:

person: - id (primary key), master_id, first_name, last_name, status
category: - id (primary key), master_id (foreign key), category_name
activity: - id (primary key), master_id (foreign key), activity_name, comments
date_time: - id (primary key), master_id (foreign key), date, time

On to what I gather is the next phase or normalization. My take on it is that the 1st normal form conditions are satisfied. As for the 2nd normal form, this is getting a little blurry, but I think that the above complies as does the 3rd normal form, which isn’t quite so blurry.

So to summarize, I would be happy to use your ideas, since they are undoubtedly much better than mine, given the distinct lack of exposure to the subject, but it does beg the question “Would what I have got going here actually work, albeit crudely?” or would it flop.

My thanks also to “almostbob” for his comments. Performance isn’t really an issue for this particular application. There will not be a huge number of records in the database for a long while, and the number of users will be limited to a handful of interested parties who will simply print off reports. That brings up another subject, but at the pace that I am going, it could be a while before I get to that.

Thanks to all,

Sheila

[timothybard]

The activity table that I suggested is simply the date_time table you had but with the addition of the activity id to track for which activity the date and time was for.

You mention that there is a many-to-many relationship between people and activity and a one-to-many relationship between category to activity. I agree with those two relationships; however, I do not see how they are implemented in your design. For example, in your design, I do not see how you track which activities are in each category.

[Sheilac]

Thanks again for your help. Evidently this is the place where I fall flat on my face. I guess that my thinking was that a foreign key in the activities table linked to the categories table would accomplish the many-to-one relationship between the two.

I am clearly in over my head at this point, and am not even sure of the questions that I need to ask to clarify this. A good reason to try to come up with a pencil and paper design before I try to implement it.

This may not be a question for this forum, however since it is relevant I will pose it anyway and risk the ire of the purists. In MS Access I know that the associations are established through a graphical interface where the user connects the tables and keys by dragging connecting lines that indicate the various associations. I am planning on parking this is a MySQL environment and I haven’t been able to figure out how the relationships are done using it.

As well I believe that I will need an association table to establish the many-to-many situations and I am wondering how to set this up too.

Just thinking out loud here and illustrating how little I know about this yet, so your help is greatly appreciated, and I apologize for being so dense.

So with your proposed design copied here:

person: id (primary key), first_name, last_name
category: id (primary key), category_name
activity: id (primary key), category_id (foreign key), activity_name
session: id (primary key), activity_id (foreign key), date, time
attendance: id (primary key), session_id (foreign key)

The relationships would be:

A person can attend many activities and activities could be attended by many persons. (many-to-many)
A category can have several activities but an activity can only belong to one category (one-to-many)
A session could be attended by many persons and many persons could attend a session (many-to-many)
An attendance would be associated with only one person and only one person would be associated with an attendance (one-to-one)

Have I got this right? Do I seem confused?

Thanks a bunch.

Sheila

[Sheilac]

I have taken timothybard’s design and attempted to render it into an entity relationship diagram. If someone who understands where he was going with this could be kind and let me know if the drawing incorporates what he had had in mind I would be very grateful. I suspect that I am a long way from getting this right yet.

(It seems that you have to click on the image to view it in the original size.)

As I understand it, the attendance table is simply a starting point to represent the fact that a person attended a particular activity that belonged to a particular category on a date at a time. It is a vehicle to organize the tables so that the user could construct any type of report combination.

Some of the reports that I can envision are:List of active persons (or list of all persons active and lapsed) between two dates
List of all activities and their date and time ordered by category between two dates
List of all categories
List of all of the activities that were attended by a particular person between two dates
List of all of the categories that were attended by a particular person between two dates
Number of activities that were attended by a particular person between two dates
List of all the persons who attended a particular activity on a particular date
Number of persons who attended a particular activity on a particular date.
I’m thinking that if I can come up with these reports, I should be able to get at anything.

I have been trying to do enough research to find out how to set up the entity relationships in MySQL and I am arriving at the conclusion that there is no equivalent of the graphic interface as is found in MS Access and that it is accomplished through the SQL query language. This must get quite fancy! The ISP where this will be hosted while I gain my education offers C-Panel and phpMyAdmin, but I have found no helpful shortcuts there to setting relationships. So some guidance on this theme would be really valuable as well.

I think that I should be OK with figuring out how to implement the associative tables, but it would be nice to have some confirmation that the relationships are proper before I wade in deeper.

Thanks for any help in furthering my education.

Sheila

[timothybard]

It looks like you have been busy and made good progress. Before I go in to anything else, I noticed that the design I suggested is missing one crucial field.
The attendance table should have three fields: (1) Id, (2) session_id and (3) person_id
The design I posted originally was missing the person_id.

You mention the relationships tool in MS Access. MySQL does have features to handle the same function. Here is a link to a page showing how to define relationships in MySQL via phpMyAdmin. Please note that the primary purpose of defining the relationships is for referential integrity; at a minimum, defining the relationships with these tools is not necessary.

As far as the drawing you put together... you show a relationship between the category table and the attendance table; that relationship should not be there. Here are all the relationships you should have:
1) category to activity, one-to-many
2) activity to session, one-to-many
3) session to attendance, one-to-many
4) attendance to person, many-to-one

Notice that I don't mention any many-to-many relationships. When creating databases, many-to-many relationship are created by using an intermediate table and has two many-to-one relationships. For example, there is a many-to-many relationship between session and person; each person can attend many sessions and each sessions can have many people. However, when creating the database, I we used an attendance table as the intermediate table and created two many-to-one relationships from it to the session and person tables.

Lastly, if you notice that I don't respond to your posts, please send me a private message. I always try to respond to posts that are within threads I've posted on previously, but some times I overlook new posts.

[Sheilac]

Once again thank you timothybard. It looks like you may have taken on my education all on your own. I hope that you can stick with me a while longer.

There is a lot that I still don’t understand, however I am encouraged by your help. I think that it may actually be coming together for me. And of course I am still chewing through a few books on the subject, but still only know enough to be dangerous.

I did another entity relationship chart based on your last advice and re-organized it somewhat to park the attendance table between the person table and the session table so that visually for me it looks like an associative (intermediate) table.

One issue that is unresolved in my mind is the relationship between the session and the activity. It is true that an activity could have many sessions (or start times) however it is also true that session could have many activities that start at the same time. I’m not sure if this idea is accommodated in what we have so far, or even if it matters.

I decided on this project because it seemed to have enough complexity that I would learn a lot from undertaking it, however on the other side I figured “How difficult can it be?” Four items to track and fairly straightforward and clear relationships. Shouldn’t be too tough. But as we work through this I am discovering a lot of nuances that impact the design, however I think that when I am done, I should have the confidence to take on something a bit heavier.

I am hoping that when I move on to trying to create an SQL query to yield the kind of reports that I listed above, it will become clearer to me how the relationships work.

I didn’t find the link that you mentioned in your last post leading to the method to accommodate the relationship assignment using phpMyAdmin and would like to take a look at it please. I have spent hours searching for something that might enlighten me on the subject but failed come up with anything meaningful to me.

I take it from your comments that this part is not crucial to the ability to generate SQL queries anyway, and that whatever is necessary is somehow accommodated in the SQL language.

I am assuming that the next step will be to set up the tables in MySQL and then to create and test a few queries.

Then it is on to part “B” to try to figure out some PHP code to pull it all together.

Thanks again.

Sheila

[Sheilac]

After searching a bit more I discovered an example that might may lead me to addressing the relationship matter in MySQL, so I thought that I might take a run at creating the SQL code to create the tables based on the new entity relationship diagram that I posted in the thread above.

CREATE TABLE IF NOT EXISTS person
(
id INT(11) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
status ENUM('Current','Lapsed') NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS attendance
(
id INT(11) NOT NULL AUTO_INCREMENT,
session_id INT(11) NOT NULL,
person_id INT(11) NOT NULL,
PRIMARY KEY  (id),
FOREIGN KEY (person_id) REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (session_id) REFERENCES session(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS session
(
id INT(11) NOT NULL AUTO_INCREMENT,
activity_id INT(11) NOT NULL,
date date NOT NULL,
time time NOT NULL,
PRIMARY KEY  (id),
FOREIGN KEY (activity_id) REFERENCES activity(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS activity
(
id INT(11) NOT NULL AUTO_INCREMENT,
actiivity_name VARCHAR(30) NOT NULL,
category_id INT(11) NOT NULL,
notes VARCHAR(500) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (category_id) REFERENCES category(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS category
(
id INT(11) NOT NULL AUTO_INCREMENT,
category_name VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);

I am presuming that I can just ignore the things like TYPE=INNODB or MyISAM items and the CHARSET=utf8 parameters that I discovered in the example and that the table creation will default to something that will work for me.

I need to do a little research on the "ON UPDATE CASCADE ON DELETE CASCADE" thing to find out what that ia all about or if I need to include it at all.

I went on to load this into the SQL panel in phpMyAdmin and was successful in creating the tables.

The issue that I would appreciate some confirmation is that I have the foreign keys correct in the "attendance", "session", and "activity" tables.

Did I manage to get it right?

Thanks again,

Sheila

[timothybard]

With the design I had in mind, there is only a one-to-many relationship from activity to session. For example, I am a physics lab instructor. For each physics lab course (activity), I teach 13 classes (sessions) each semester; however, during each class, I teach only for one course. In your case, there certainly my be a many-to many relationship between activity and session; in that case, you would need an intermediate table between the two.

You did find the code I was referring to concerning defining relationships in MySQL. The purpose of this code is for referential integrity. Referential integrity is basically the act of having MySQL remove data that refers to records that no longer exist. For example, in our case, suppose a person has been attending a session for several weeks. For each session the person attended, he/she will have one attendance record. If you then delete the person from your database, then MySQL will look through the attendance table and delete all the records that correspond to that person. If MySQL did not do this, then you would have attendance records in your database that no longer refer to a person.

Everything you have so far looks good. The items you mentioned that were in template that you did not use in your code can be ignored; basically, your goal at the time was to simply create the tables, so if they were created and look good, then you did everything ok.

[Sheilac]

Since several activities often start at the same time, e.g. tennis and archery may both commence at 10:00AM on Monday, I think that I may need to add that associative table between the two.

Here is the new entity relationship diagram that I came up with. I presume that I only need two fields that would contain the primary keys of the session and the activities tables. i.e. the associative table does not require a primary key.

Below is the SQL code to create that table.

CREATE TABLE IF NOT EXISTS session_activity
(
session_id INT(11) NOT NULL,
activity_id INT(11) NOT NULL,
FOREIGN KEY (session_id) REFERENCES session(id) ON UPDATE CASCADE ON DELETE CASCADE
FOREIGN KEY (activity_id) REFERENCES activity(id) ON UPDATE CASCADE ON DELETE CASCADE,
);

Could you please give me some assurance that I have this right? Is there anything that I would need to modify in the other tables?

I guess the next step will be to enter some dummy data and then try to create a couple of SQL query statements to come up with the kind of reports that I would like to see. Thanks for all of you help. It is much appreciated. I can read a bunch of examples in books and on the Internet, and of course they all make sense. However when it comes to attempting a ‘real-world” problem, there are a lot of holes in my knowledge and you have provided me with a great deal of direction. Thanks for all of your time.

Sheila

[timothybard]

Please note that there should be a one-to-many relationship from activity to session_activity and a one-to-many relationship from session to session_activity. The code you supplied looks correct, but the diagram you drew looks as if the relationships are pointing in the wrong direction.

Even though the tennis and archery start at the same time, there is not a need for the many-to-many relationship from activity to session. In that case, you would simply have two activity records (one for tennis and one for archery) and two session records (one to correspond to tennis and the other to correspond to archery). The case where you would need a many-to-many relationship is if two activities are covered in one session. For example, if there is an activity to teach CPR and there is an activity to teach first-aid, then if there is a session to cover both subjects, you would need a many-to-many relationship; in this case, the same people are in the same session and are covering two activities.

Therefore, unless the session is both covering tennis and archery for the same people, then a many-to-many relationship is not needed.

However, I think there may be a misunderstanding on my part about what a session is. Is a session a set time, such as 8:00 - 9:00 on Monday? I was thinking of a session as a class period that met at a certain time but not necassary within a specific block of time. Do you see the difference? In either case, I don't see a need for the session_activity table.

Since several activities often start at the same time, e.g. tennis and archery may both commence at 10:00AM on Monday, I think that I may need to add that associative table between the two.

Here is the new entity relationship diagram that I came up with. I presume that I only need two fields that would contain the primary keys of the session and the activities tables. i.e. the associative table does not require a primary key.

[ATTACH]11500[/ATTACH]

Below is the SQL code to create that table.

CREATE TABLE IF NOT EXISTS session_activity
(
session_id INT(11) NOT NULL,
activity_id INT(11) NOT NULL,
FOREIGN KEY (session_id) REFERENCES session(id) ON UPDATE CASCADE ON DELETE CASCADE
FOREIGN KEY (activity_id) REFERENCES activity(id) ON UPDATE CASCADE ON DELETE CASCADE,
);

Could you please give me some assurance that I have this right? Is there anything that I would need to modify in the other tables?

I guess the next step will be to enter some dummy data and then try to create a couple of SQL query statements to come up with the kind of reports that I would like to see. Thanks for all of you help. It is much appreciated. I can read a bunch of examples in books and on the Internet, and of course they all make sense. However when it comes to attempting a ‘real-world” problem, there are a lot of holes in my knowledge and you have provided me with a great deal of direction. Thanks for all of your time.

Sheila

[Sheilac]

Ahhh…

I was thinking of a session as simply a “start time” for the activity since it contained only a single date and single time, and that is all that is required in this instance, although I could envision applications for a similar design where it might be necessary to think of it more in terms of a college “class’. In my application activities would always be unique and we would never see two combined, so, I think that we are on the same page with this.

Although I would like to possibly try to develop that variation of this project with the session_activity idea at some time in the future, I will try not to confuse myself too much for the present time. My understanding is that I should be able to revert back to the earlier code that I used to create the tables and the entity relationship diagram with five tables and all should behave as expected.

I do hope that you can stick with me while I try to come up with the SQL code to furnish some of the reports that I listed. I will give you a short rest while I try to build something in PHP to enable me to add some data to the tables. I have something that is already working that is quite close, so I am hoping that it will be a small step for me.

As usual, many thanks.

[timothybard]

If the only change you made was to insert the session_activity table, then you should be able to just drop that table; however, if it is easier to start fresh, you can drop all the tables and create the tables again with the code you used earlier.

As far as bulding queries... I think you mentioned MS Access in the past. One tool I use for building queries in php is the query builder in MS Access. If you create the query graphically with the MS Access query builder, then you can view the SQL statement of the query and, with minor changes, use it in php / MySQL. Once you become more familiar with sql statements, it may be faster to just write them from scratch than to use the MS Access query builder, but while learning, it was a useful tool.

[Sheilac]

Thanks again for the guidance.

I decided not to add that last associative table (session_activity) until I was a bit more certain that I was on the right track, so we are “good-to-go”.

As well I decided to stick with the theme here while I have your interest and attention, and evidently that of a few others as evidenced by the 363 views. So, I took the easy way and used the “Insert” method to get some dummy data into the tables, assuming that there is nothing tricky about accomplishing it that way.

I think that we established earlier that there is nothing more that I need to do to create the relationships between the tables.

I will take your advice and build a mirror set in MS Access and attempt to use query builder to help me to come up with the desired results for reports.

For those who are playing along, here are some screen captures from phpMyAdmin of the data in the tables that I will be experimenting with.

person

attendance

session

activities

categories

A few other issues will be getting in the way of letting me spend much time on this for a day or two, but I expect that I will be back and seeking advice before I see the end of this phase of the project,

As always, mighty grateful for your help.

Sheila

[timothybard]

Just wanted to let you know that I did look over those screen shots and everything looks good to me.

[Sheilac]

My apologies for dropping out for a while. I was buried in another project and needed to focus, and finally got away on a short vacation as well. Enough with the excuses.

I did take some time to try to figure out how to make a SQL query work and have had no success at all. It remains a big mystery to me.

If it is not too much trouble, an example of a SQL statement that would yield something like all of the activities that Tweety Bird attended between the dates of 01/09/2009 and 04/09/2009 might be just enough to unlock this for me and reveal the secret.

I hate when I bump into these things that are probably so obvious, but completely stump me. However, I usually learn by seeing examples, so perhaps that will get me on track.

Once again, thanks for your help.

[Sheilac]

I just discovered that I could attach a zip file to a thread. It may be that I don't have the relationships correct, and hence causing my grief.

Here is the Access database containing the dummy data that I am working with.

activities db.zip

[timothybard]

Here is a SQL statement to produce the results you asked for:

SELECT person.first_name, person.last_name, activity.activity_name, session.date
FROM ((person LEFT JOIN attendance ON person.id = attendance.person_id) LEFT JOIN [session] ON attendance.session_id = session.id) LEFT JOIN activity ON session.activity_id = activity.id
WHERE (((person.first_name)="Tweety") AND ((person.last_name)="Bird") AND ((session.date) Between #9/1/2009# And #9/4/2009#));

Please note that in MS Access, you must surround your dates with pound signs. For your date range, you mentioned 1/9/2009 and 4/9/2009. Is this in MM-DD-YYYY or DD-MM-YYYY? Using 1/9/2009 and 4/9/2009 did not result in any records for me, so I used the dates 9/1/2009 and 9/4/2009 instead.

[Sheilac]

Once again, Thanks. The query is exactly what I requested. I haven’t tried it in MySQL as yet, as I decided that while I am experimenting, Access is much easier to work with locally on my machine.

It is clear that I need to find a tutorial on learning the SQL language and take a few “baby” steps and hope that the secrets are revealed to me. So far the books that I have been reading and on-line tutorials that I have found have failed to turn on the lights.

The LEFT and RIGHT JOIN items are a big mystery.

I been attempting to add the “category” to the resultant table and so far I am failing miserably.

I confess that I do get frustrated when I bump into these hurdles. It is certainly good brain exercise for me, but sometimes I wonder if I shouldn’t find another interest. However, I won’t be giving up! Since I apparently lack the natural talent, perhaps perseverance will win the day.

You have been extremely helpful in bringing me a long distance from, where I began and I am very grateful.

[timothybard]

As far as building queries, once I know what result I'm interested in, I start with one table as my 'base' table. In the query I provided to you, I used 'people' as the base query.

From there, I try to find out which tables I need to be displayed in the result. For the query I provided to you, I knew that the activity table would be needed because you needed the activity name in the result.

Next, I try to identify any intermediate tables, such as the session table and the person session table.

Lastly, I start with the base table (people table in this case) and building my relationships from that table to the next table until I get to the last table.

The left and right joins simple determine which direction the one-to-many relationships go; it determines which table is the 1 table and which table is the many table. This is where the MS Access query builder can be helpful.

[Juan_8]

Write an SQL statement to display the customer number, city of destination, and shipping date for all shipments with order number above 2.