I'm having some trouble with a query. I can't figure out what I'm doing wrong and I've been trying to get it for awhile now.

Basically when I run the query just using the one table (Type of Emergency Table) I get:
Query1
Type of Emergency Number of Calls ZipCode
Auto Accident 2 16933
Heart Attack 1 16950
Heart Attack 1 16910
Heart Attack 1 16901
Gunshot Wound 1 16901
Drowning 1 16910
Childbirth 1 16933
Childbirth 1 16901


And the SQL view shows

Which looks like the right data and I just have to add another table [the EMT] table and get the Base for the city from the Zip Code.

The problem is when I try to add the EMT table I get a different number of calls.
Query1 Type of Emergency Number of Calls Zip Code
Auto Accident 8 16933
Childbirth 4 16933
Heart Attack 3 16910
Heart Attack 3 16901
Gunshot Wound 3 16901
Drowning 3 16910
Childbirth 3 16901



I don't know why the numbers are coming out this way.
Does anyone have any clue what is going on. I've tried to do a tutorial with a similar problem and thought I understood but can't get this to work.

I believe you have what is called a "cross product" result set here. What this means is that because of the way you have joined the two tables on zip code that all the records for a given zip in the EMT table is being matched up with all the records that have the same zip in the emergency table. I can't confirm this for you since you haven't shown the EMT table fields.

But for you to help confirm this, remove your group by clause (and the count aggregation) and rerun the query. You will probably see more records than you expect. This query will show you which records from emergency is being linked to what in EMT. You will probably see some records you didn't anticipate being matched together.

Here is a tip for you in the future... when creating queries like this, always link the two tables first, look at all the records being linked and THEN add the group by clause. By adding the group by clause first and then trying to do the link you will have trouble understanding what the numbers exactly mean and to check if they are right.

If you need further help, show us all the fields in question from both tables, especially the EMT table.

Thanks.

Thank You for your help. Your guess was right on the table and the problem is fixed.