Need some help with Count.

[TJunior]

Hi there,
I am haveing some problems using the Count function. What I am attempting to do is output a list of recorded equipment with the total beside each piece e.g. Equip1 = 3, Equip2 = 5 etc.

This is what I have so far:-

Set objconn = Server.CreateObject("ADODB.Connection&quot
strconn = "DSN=Worry-not; Database=tdotcom Database;"
strconn = strconn & "UID=;PWD=;"
objconn.Open strconn

Set objRS = Server.CreateObject("ADODB.Recordset&quot

strQ = "SELECT Location, Count(tbl_Parts.PartDescript) AS Records "
strQ = strQ & "FROM tbl_CustomerLocations, tbl_Parts, tble_Customers, tbl_EquipCfg, EquipCfgParts "
strQ = strQ & "WHERE tble_Customers.recid = '" & request.cookies("passes2&quot & "' "
strQ = strQ & "AND tbl_CustomerLocations.CustomerId = tble_Customers.recid "
strQ = strQ & "AND tbl_EquipCfg.LocationId = tbl_CustomerLocations.recId "
strQ = strQ & "AND tbl_EquipCfg.recid = tbl_EquipCfgParts.EquipCfgId "
strQ = strQ & "AND tbl_EquipCfgParts.PartId = tbl_Parts.recid "
strQ = strQ & "GROUP BY tbl_Parts.PartDescript "
strQ = strQ & "ORDER BY tbl_Parts.PartDescript DESC "

objRS.Open strQ

Please can you shed some light on where my problem lies. Thank you.

 

[derren]

Hi

"SELECT Location, Count(tbl_Parts.PartDescript) AS Records FROM tbl_CustomerLocations, tbl_Parts, tble_Customers, tbl_EquipCfg, EquipCfgParts WHERE
tble_Customers.recid='value' AND tbl_CustomerLocations.CustomerId=tble_Customers.recid AND tbl_EquipCfg.LocationId = tbl_CustomerLocations.recId AND tbl_EquipCfg.recid=tbl_EquipCfgParts.EquipCfgId AND tbl_EquipCfgParts.PartId=tbl_Parts.recid
GROUP BY tbl_Parts.PartDescript
ORDER BY tbl_Parts.PartDescript DESC "

Nice short SQL string... Your code is fine, and perfectly formed on first glance, but I suspect your count values are all far too high. You are creating a huge great big table of information with all your "wheres" which will dulpicate entries which is then being grouped together by the smallest value.

First, try "GROUP BY location" and see what that yields, but I suspect it goes deeper than that. Tell us more about the results you are getting and explain why the "where" clause is so complex (how all your tables are linked).

Derren
[The only person in the world to like Word]

 

[TJunior]

I have tried 'GROUP BY Location' but this results in outputing each bit of equipment and the numnber 1 beside it, when what it should do is show the total number of each piece of equipment.

The issue with the number 'Where' clauses is due to the initial database design, this means that this is the only way to retreive the information I require. I know it is very complex, but what can I do. I did suggest redesigning the DB, but the guys in the big seats talk in a total different language to me. Well I really do need to get this sorted and am really confussed as why I'm getting the results I am. Any suggestions?

 

[Dynapen]

2 things.

1. You don't have to reference a table in the from clause unless you are selecting something from it. You can still reference it in the where clause without listing it in the from clause.

2. The reason you were getting a count of 1 is that you were grouping by the count. What you actually want it to group by the location and not the description. That will give you a count of the number of pieces of equipment at a given location. The code below should work, just add in the name of the table that the location field comes from.


"SELECT table_name.Location, Count(tbl_Parts.PartDescript) AS Records FROM tbl_part, (whatever_table_location_is_in) WHERE
tble_Customers.recid='value' AND tbl_CustomerLocations.CustomerId=tble_Customers.recid AND tbl_EquipCfg.LocationId = tbl_CustomerLocations.recId AND tbl_EquipCfg.recid=tbl_EquipCfgParts.EquipCfgId AND tbl_EquipCfgParts.PartId=tbl_Parts.recid
GROUP BY table_name.location
ORDER BY tbl_Parts.PartDescript DESC "

 

[TJunior]

I have tried what you suggested:-

strQ = "SELECT Location, Count(tbl_Parts.PartDescript) AS Records "
strQ = & strQ "FROM tbl_Parts, tbl_CustomerLocations "
strQ = & strQ "WHERE tble_Customers.recid = '" & request.cookies("passes2&quot & "' "
strQ = & strQ "AND tbl_CustomerLocations.CustomerId = tble_Customers.recid "
strQ = & strQ "AND tbl_EquipCfg.LocationId = tbl_CustomerLocations.recId "
strQ = & strQ "AND tbl_EquipCfg.recid = tbl_EquipCfgParts.EquipCfgId "
strQ = & strQ "AND tbl_EquipCfgParts.PartId = tbl_Parts.recid "
strQ = & strQ "GROUP BY Location "
strQ = & strQ "ORDER BY PartDescript DESC "

But when I run the page I get the following error:-

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/Project1_Local/EquipmentCfg.asp, line 18, column 7
strQ = & strQ "FROM tbl_Parts,
tbl_CustomerLocations "
------^

What's happening here do you know?

 

[TJunior]

SORRY forget that error. I have a better one:-

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column name 'tbl_Parts.PartDescript' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
/Project1_Local/EquipmentCfg.asp, line 28

With the corrected SQL statement:-

strQ = "SELECT tbl_CustomerLocations.Location, Count(tbl_Parts.PartDescript) AS Records "
strQ = strQ & "FROM tbl_Parts, tbl_CustomerLocations, tble_Customers, tbl_EquipCFG, tbl_EquipCFGParts "
strQ = strQ & "WHERE tble_Customers.recid = '" & request.cookies("passes2&quot & "' "
strQ = strQ & "AND tbl_CustomerLocations.CustomerId = tble_Customers.recid "
strQ = strQ & "AND tbl_EquipCfg.LocationId = tbl_CustomerLocations.recId "
strQ = strQ & "AND tbl_EquipCfg.recid = tbl_EquipCfgParts.EquipCfgId "
strQ = strQ & "AND tbl_EquipCfgParts.PartId = tbl_Parts.recid "
strQ = strQ & "GROUP BY tbl_CustomerLocations.Location "
strQ = strQ & "ORDER BY tbl_Parts.PartDescript DESC "