Past Help almost working, Just a little most assistance

[KayJen]

I am working on an accident program where the streets can be entered as A and B or as B and A, (beyond my control comes from accident investigators), I have the query working up to this point to count them, however, they are now listed twice, please help one more time with this ever present problem:

The query prompts for year and month which for this example is 2002, January. The query returns the following example:

Assembly St Elmwood Ave Park St 5819
Park St Elmwood Ave Assembly St 5819
More accident streets are present here...

How can I remove the number, (AutoNumber which was suggested in previous post). For January 2002, I should have 5 various accident locations, however, I have 10 since all accidents repeat twice. Any step by step directions would be greatly appreciated.

Thanks Again,
Sonny

 

[shannonp]

hmmmm, maybe what you could do is this (taking your example into account):

[MyTable]
AddressA AddressB AddressC myID
===============================
Assembly St Elmwood Ave Park St 5819
Park St Elmwood Ave Assembly St 5819

First, make a query based ONLY on myID (in your example it would be 5819). Make sure its totalled (GROUP BY). FOr this example save it as [MyID_Grouped]

Secondly, make a new query and add [MyID_Grouped] and [MyTable], then link these via MyID.

Lastly add MyID from [MyID_Grouped] and all other relevant fields from [MyTable]. Toggle Totals on (GROUP BY) where [MyID] should be 'GROUP BY' and [AddressA], [AddressB] and [AddressC] are 'FIRST'.

The only downside to this is that if the two identical records contain different values where it will only show the first incedence of 5819.

 

[KayJen]

I think my example is not clear, what I'm looking for is Assembly St and Elmwood Ave = 5
or it could say
Elmwood Ave and Assembly St = 5

The chart will show something like below:

Assembly St & Elmwood Ave ------------ 5
Park St & Main St -------------------- 4
Elmwood Ave & Sumter St -------------- 2

It was suggested that I use Autonumber to eliminate any duplicates this did not work but I kept the Autonumber hoping to find a better solution. Hope this helps...

Sonny