Duplicate count

[work4ever]

I'm new to access I have attach a sample querry belowThat shows two different counts for HP 198. I know that it is because of the different in the column one named sites. Each site has one to nine randomly used devices and that is what is displayed by the decimal point after the site number. I need to know the total usage of a particular site.

Site # Unit ID Alias # Count of Unit ID # Unit ID
8.4,28.2,39.1 # HP 198 # 6 # 700244
8.4,28.1,34.2,39.1 # HC! SRC BRK # 8 # 704055
8.4,28.1,39.1 # HP 198 # 9 # 700244​

 

[duluter]

I'm having trouble reading your query results. Could you reformat with some kind of identifier between each field?

For example:

Field1 # Field2 # Field3
100 # 423 # 543
345 # 311 # 212
983 # 974 # 121


Also, could we see your SQL?

Duluter

 

[boblarson]

If you use the CODE tags here on the forum, it will keep your formatting.

 

[work4ever]

I'm new to access I have attach a sample querry belowThat shows two different counts for HP 198. I know that it is because of the different in the column one named sites. Each site has one to nine randomly used devices and that is what is displayed by the decimal point after the site number. I need to know the total usage of a particular site.

Site # Unit ID Alias # Count of Unit ID # Unit ID
8.4,28.2,39.1 # HP 198 # 6 # 700244
8.4,28.1,34.2,39.1 # HC! SRC BRK # 8 # 704055
8.4,28.1,39.1 # HP 198 # 9 # 700244​

The database is imported from an excel format
Time # [01/30/09 23:08:27]
Message # Call Activity Update - Start of New Call
Unit ID # 706913
Unit Id Alias # ROBERTS COSO
TG Id # 801096
TG Alias # ROBERTS CO LAW1
Site # 13.3, 25.1
Result or Status # Reason for Busy = No Reason
CallType # Digital,Talkgroup
OL # # 28546350

 

[jal]

Sorry, I haven't got a clue as to what you want help doing.

 

[duluter]

As you mentioned, you are going to have trouble performing an aggregate function if your Site field has extra, variable info to the right of the main site number. Instead of pulling in the entire Site field from the table, you could try pulling in just the main site number. Am I correct in assuming that the main site number is the number that comes before the first comma? If so, then this might work:

SELECT Left([Site],InStr([Site],",")-1) AS SitePart, UnitIDAlias, UnitID, Count(UnitID) AS CountOfUnitID
FROM Table1
GROUP BY Left([Site],InStr([Site],".")-1)


Duluter