Total count for crosstab query
in [Queries]
|
From: HOCSMGR on 2 Jul 2008 09:31 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;
From: KARL DEWEY on 2 Jul 2008 10:53 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 -- KARL DEWEY Build a little - Test a little "HOCSMGR" wrote: > 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; > > >
From: HOCSMGR on 2 Jul 2008 11:19 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" wrote: > 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 > > -- > KARL DEWEY > Build a little - Test a little > > > "HOCSMGR" wrote: > > > 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; > > > > > >
From: KARL DEWEY on 2 Jul 2008 12:10 >> 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. -- KARL DEWEY Build a little - Test a little "HOCSMGR" wrote: > 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" wrote: > > > 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 > > > > -- > > KARL DEWEY > > Build a little - Test a little > > > > > > "HOCSMGR" wrote: > > > > > 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; > > > > > > > > >
From: HOCSMGR on 2 Jul 2008 13:43 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" wrote: > >> 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. > -- > KARL DEWEY > Build a little - Test a little > > > "HOCSMGR" wrote: > > > 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" wrote: > > > > > 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 > > > > > > -- > > > KARL DEWEY > > > Build a little - Test a little > > > > > > > > > "HOCSMGR" wrote: > > > > > > > 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; > > > > > > > > > > > >
|
Next
|
Last
Pages: 1 2 Prev: memo field in the select query Next: Checkbox & Textbox Used As Criteria For A Query |