Crosstab calculating query

[Dinger_80]

Ok So I admit when it comes to crosstab queries I am pretty much clueless. I have gotten a few to work in the past and have a general idea of the mechanics. That being said, I am trying to pull some metrics for the year from the database I designed. What I am attempting to do with this particular query is calculate how many days were spent on each product. The main table is TestRequestTable. It is how the other two tables, FinishedTestRequestTable and CatalogNumberTable are related. Currently the crosstab query returns the number of days spent on each catalog number. However it seems to return the highest number of days spent on each catalog number. I want it to total up the number of days from all testing spent on each product. If possible to ignore records where there isn't a results date. This could be because someone is still working on it and hasn't finished it yet. Here is what I have so far. Any help would be appreciated.

TRANSFORM Count(DateDiff("d",[PrepStartDate],[ResultsDate])) AS CatalogNumber
SELECT "Days on Products" AS Title
FROM (TestRequestTable INNER JOIN FinishedTestRequestTable ON TestRequestTable.FinishedRelateField = FinishedTestRequestTable.RID) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID
WHERE (((TestRequestTable.IsSubmitted)=True))
GROUP BY "Days on Products"
PIVOT CatalogNumberTable.CatalogNumber;

 

[JHB]

You need to supply sample data, with properly named tables and columns and then data showing the desired output derived from the sample data. Somebody will then hopefully help you.
Also posting your database with some sample data + name of the query and the result you want could also help.

 

[Dinger_80]

I got the query working. I changed the Count for Sum and it gave the desired results.

TRANSFORM Sum(DateDiff("d",[PrepStartDate],[ResultsDate])) AS CatalogNumber
SELECT "Days on Products" AS Title
FROM (TestRequestTable INNER JOIN FinishedTestRequestTable ON TestRequestTable.FinishedRelateField = FinishedTestRequestTable.RID) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID
WHERE (((TestRequestTable.IsSubmitted)=True))
GROUP BY "Days on Products"
PIVOT CatalogNumberTable.CatalogNumber;