sql - Access Query - Select count distinct per record on multiple tables -


i have query (concatenated inside vb string): 

select a.table_no tbl, count(b.table_no) total_call_count,        avg(timevalue(c.service_time) * 86400) total_time table_button_link a, button_log b, service_time_log c  b.table_no = c.table_no   , format(b.transaction_datetime,""dd/mm/yyyy"") = '" & date.now.tostring("d") & "' "  , b.type <> 2 , b.table_no = a.table_no , c.table_no = a.table_no  group a.table_no

i want count tbl_no table button_log returns this:

|tbl_no  | total_call_count | total_time | |   1    |      4661        |   7.2134   | |   2    |       5          |     3      | |   3    |      576         |   9.33333  |

my desired output this: 

|tbl_no  | total_call_count | total_time | |   1    |       5          |   7.2134   | |   2    |       1          |     3      | |   3    |       3          |   9.33333  |

button_log looks this:

| id | table_no | type | transaction_datetime    | | 1  |   1      |  1   | 28/07/2017 10:41:34  | | 1  |   1      |  2   | 28/07/2017 10:42:22  | | 2  |   1      |  1   | 28/07/2017 10:42:25  | | 3  |   2      |  1   | 24/07/2017 10:41:47  |

table_button_link this:

| id | table_no | button_no | | 1  |   1      |   1       | | 2  |   2      |   2       |

and service_time_log :

| id | table_no | service_time | transaction_date       | | 1  |    1     |  00:00:04    | 28/07/2017 10:42:22 | | 1  |    2     |  00:00:05    | 28/07/2017 10:50:43 |

i think counts records. tried using count(distint b.table_no) guess ms access can't that. how achieve this? please help. thanks.


Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more