|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 2nd, 2008, 02:31 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Total count for crosstab query
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; 
|
|
#2
July 2nd, 2008, 03:53 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Total count for crosstab query
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;
|
|
#3
July 2nd, 2008, 04:19 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Total count for crosstab query
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;
|
|
#4
July 2nd, 2008, 05:10 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Total count for crosstab query
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;
|
|
#5
July 2nd, 2008, 06:43 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Total count for crosstab query
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;
|
|
#6
July 2nd, 2008, 08:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Total count for crosstab query
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. -- KARL DEWEY Build a little - Test a little "HOCSMGR" wrote: 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;
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
