Union Query / Totals Query / Duplicate Results

  • Thread starter Thread starter Michael.Laing
  • Start date Start date
M

Michael.Laing

I am tracking accidents locations and have fields Street01
and Street02

I am trying to track the number of accidents at
intersections on Street01/Street02 and Street02/Street01.

A totals query gives the following results:
First Street/Second Street 150
Second Street/First Street 73
Third Street/Forth Street 140
Forth Street/Third Street 46

I constructed a union query that totals the two but it
give me the results twice:
First Street/Second Street 223
Second Street/First Street 223
Third Street/Forth Street 186
Forth Street/Third Street 186

I can't seem to create a query/report that will give a
total of the two possibilities only once.

Any Ideas????
 
Would you please give us all your fields so that we can get a better idea on
how your tables are designed. Generally, a table would not have fields such
as Street01, Street02, etc.
 
The table consists of about 100 fields, but for this
problem, there is a primary key - CrashNumber,
PrimaryStreet (Street01) and IntersectingStreet (Street02).

In trying to work out a solution, I have placed placed the
the two fields: PrimaryStreet and IntersectingStreet into
a child table and related it back to the Main table. I
still get the same problem in the query when I use a
totals query for the two fields.
 
Back
Top