Total count for crosstab query

[HOCSMGR]

I am working on a query to show the vendor_name and the total number of
pieces the user completed for a certain date. I am pulling the info i need
but having problem with the calculation for the totals.

TRANSFORM Sum(sku_information.item_type) AS SumOfitem_type
SELECT vendor_information.vendor_name, testing_information.check_in
FROM users, (testing_information INNER JOIN sku_information ON
testing_information.sku_number = sku_information.sku_number) INNER JOIN
vendor_information ON sku_information.vendor = vendor_information.db_key
WHERE (((testing_information.check_in) Between #6/1/2008# And #6/30/2008#))
GROUP BY vendor_information.vendor_name, testing_information.check_in
PIVOT users.long_name;

 

[KARL DEWEY]

You did not relate 'users' to anything.
Why are you bringing 'sku_number' into this?

Is this what you expect to get as results?
Vendor Check_in Joe Bill Sam
Acme 1/1/08 0 5 4
Acme 2/4/08 1 2 0
Bard's 1/1/08 5 3 2
Bard's 3/12/08 4 9 7

 

[HOCSMGR]

those are the results I am looking for.

the sku-information and testing_information tables are both related to each
other by sku number. I assume this is why the inner join was pulled in for
sku number.

 

[KARL DEWEY]

You did not relate 'users' to anything.
How are the users related? The users have to be related to the other tables
in some manner.

 

[HOCSMGR]

Users is now related to tested_by on the Testing_information table. Here is
the current code:

TRANSFORM Sum(sku_information.item_type) AS SumOfitem_type
SELECT vendor_information.vendor_name, testing_information.check_in
FROM sku_information INNER JOIN vendor_information ON sku_information.vendor
= vendor_information.db_key, users INNER JOIN testing_information ON
users.row_id = testing_information.tested_by
WHERE (((testing_information.check_in) Between #6/1/2008# And #6/30/2008#))
GROUP BY vendor_information.vendor_name, testing_information.check_in
PIVOT users.long_name;

 

[KARL DEWEY]

Based on what I see in your SQL I get the following related tables/fields --

sku_information.item_type - sku_information.vendor --
vendor_information.db_key - vendor_information.vendor_name

testing_information.check_in - testing_information.tested_by -- users.row_id
- users.long_name

But the item being tested is not related to who tested it.