sql - MS Access Query to output all values and treat unavailable values as zero -


i've created query ms access 2010 intended take fuel types (all values) each date. have query relationship below: figure1) , have following 6 values fuel type: diesel #2, msfo, ulsd, biodiesel, used oil, heat recovery.

i'm trying output fuel delivery each fuel type each date regardless if there's fuel delivered date (figure2 , figure3). i'm getting output below figure4.

i have tried change relationships such values on tbl_fueltype output. gave me output on figure above. i've tried entering criteria specific fuel type (e.g. "ulsd") if there's no data day, output null values, don't want. see criteria below figure5 i've tried program flow functions such iif , switch still getting null values. there easy way without having go table , filling out values zero's different fuel types on tbl_fueldelivery? sql view follows: 

select tbl_fueldelivery.datelog, sum(nz([tbl_fueldelivery].[f_fo_gal_gross],0)) fuel_delivery_gross, sum(nz([tbl_fueldelivery].[f_fo_gal_net],0)) fuel_delivery_net, tbl_fueltype.fueltype  tbl_fueltype left join tbl_fueldelivery on tbl_fueltype.id = tbl_fueldelivery.fueltype  group tbl_fueldelivery.datelog, tbl_fueltype.fueltype  order tbl_fueldelivery.datelog;  ​

what need add query table dates. can have separate table dates entered, such calendar table or have derived tbl_fueldelivery this:

select distinct tbl_fueldelivery.datelog  tbl_fueldelivery;

now, need cross join table tbl_fueltype. access not natively support cross joins, you'll have use workaround: add cross join comma separated table clause:

select a.datelog, tbl_fueltype.fueltype, tbl_fueltype.id (select distinct tbl_fueldelivery.datelog tbl_fueldelivery) a,  tbl_fueltype;

the query above give fuel types dates. can save new query (let's call alldatesfuels). now, need join query:

select  alldatesfuels.datelog,  sum(nz([tbl_fueldelivery].[f_fo_gal_gross],0)) fuel_delivery_gross,  sum(nz([tbl_fueldelivery].[f_fo_gal_net],0)) fuel_delivery_net,  alldatesfuels.fueltype alldatesfuels left join tbl_fueldelivery on alldatesfuels.id = tbl_fueldelivery.fueltype , alldatesfuels.datelog = tbl_fueldelivery.datelog group alldatesfuels.datelog, alldatesfuels.fueltype order alldatesfuels.datelog; ​

-

Comments

Post a Comment

Popular posts from this blog

java - OnDragListener Fires Several Times? -

i have several layouts; each containing textview . if user drags textview unto textview , i'm going execute method swapfamily() it fires several times, depending on number of families . familyitemlayout custom linearlayout string attribute attached , add'tl getters , setter attribute. however, issue that, private class ondropfamily implements view.ondraglistener { @override public boolean ondrag(view view, dragevent event) { textview txtdragged = (textview) event.getlocalstate(); textview txttarget = (textview) view; string familydragged = ((familyitemlayout) txtdragged.getparent()).getfamily(); string familytarget = ((familyitemlayout) txttarget.getparent()).getfamily(); switch (event.getaction()) { // signals start of drag , drop operation. case dragevent.action_drag_started: break; // signals view drag point has entered bounding box of view. case ...
Read more

spring - "Dispatcher has no subscribers" during startup -

i facing problem happens , hard reproduce. therefore have problems creating compelling test case. our setup looks this: spring integration spring boot rabbitmq listener custom bus manages transaction aware messages the setup not newest anymore , lot of code replaced more idiomatic spring wiring. though exception getting when service started: dispatcher has no subscribers, failedmessage=genericmessage [payload=com.foobar.service.greencheckout.message.greenorderpropertyaddedmessage@2ba0efb2, headers={id=f750a792-6b01-16d3-8206-6e553a03f8fa, type=com.foobar.service.greencheckout.message.greenorderpropertyaddedmessage, amqp_deliverymode=persistent, timestamp=1505797680967}], failedmessage=genericmessage [payload=com.foobar.service.greencheckout.message.greenorderpropertyaddedmessage@2ba0efb2, headers={id=f750a792-6b01-16d3-8206-6e553a03f8fa, type=com.foobar.service.greencheckout.message.greenorderpropertyaddedmessage, amqp_deliverymode=persistent, timestamp=1505797680967...
Read more

c# - ajax - How to receive data both html and json from server? -

i have referenced this topic , doesn't solve problem. i'm using mvc 5 c#. have used code receive data server data type json . $.ajax({ url: "/mycontroller/myaction", type: "post", datatype: "json", success: function (data) { if (data.result) { alert('successfull'); } else { alert(data.ex); } } }); and controller code: [httppost] public actionresult myaction() { try { return json(new { result = "true", ex = "" }); } catch (exception e) { return json(new { result = "false", ex = e.message }); } } i use way data type html: $.ajax({ url: "/mycontroller/myaction", type: "post", datatype: "html", success: function (data) { $(".mydiv").append(data); } }); and controller should be: [httppost] public actionresult myaction() { return partialview("_...
Read more