Access query trouble with 2 tables

I am having trouble with a query

Page 1 of 1

2 Replies - 1521 Views - Last Post: 30 September 2008 - 12:56 AM

#1 raiderspader   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 22-October 07

Access query trouble with 2 tables

Posted 28 September 2008 - 03:35 PM

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
SELECT [Type of Emergency].[Type of Emergency], Count([Type of Emergency].[Type of Emergency]) AS [Number of Calls], [Type of Emergency].[Zip Code]
FROM [Type of Emergency]
GROUP BY [Type of Emergency].[Type of Emergency], [Type of Emergency].[Zip Code]
ORDER BY Count([Type of Emergency].[Type of Emergency]) DESC;


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

SELECT [Type of Emergency].[Type of Emergency], Count([Type of Emergency].[Type of Emergency]) AS [Number of Calls], [Type of Emergency].[Zip Code]
FROM [Type of Emergency] INNER JOIN EMT ON [Type of Emergency].[Zip Code] = EMT.[Zip Code]
GROUP BY [Type of Emergency].[Type of Emergency], [Type of Emergency].[Zip Code]
ORDER BY Count([Type of Emergency].[Type of Emergency]) DESC;


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.

Is This A Good Question/Topic? 0
  • +

Replies To: Access query trouble with 2 tables

#2 Martyr2   User is online

  • Programming Theoretician
  • member icon

Reputation: 5237
  • View blog
  • Posts: 14,031
  • Joined: 18-April 07

Re: Access query trouble with 2 tables

Posted 29 September 2008 - 10:17 AM

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. :)
Was This Post Helpful? 0
  • +
  • -

#3 raiderspader   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 22-October 07

Re: Access query trouble with 2 tables

Posted 30 September 2008 - 12:56 AM

Thank You for your help. Your guess was right on the table and the problem is fixed.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1