Expressions in queries - need all values to give any results

[mike_980]

Hi guys, just wondering if anyone has ever had this problem and how you got around it.



When I am creating a query with an expression eg. quantity1 + Quantity2 the query does not give any results unless both hold a value. I would like the query to display the results even if quantity2 is a null value is this possible?

[Bob Fitz]

Take a look at the Nz() function.

EDIT
Maybe something like:
Nz(quantity1,0) + Nz(Quantity2,0)

[mike_980]

Thanks I will take a look now

[mike_980]

No luck i'm afraid, these are the two expressions i tried in the same query. Just as before they only display results if there is a value in Quantity_spread and Quantity_N_spread.

If the value for Quantity_spread is null, I would like the first formula to display 0, thereby making the second formula add 0 to quantity_N_spread or the other way around.

Expr1: Nz([Quantity_spread],"0")*Nz([Ammonium_N],"0")

Expr2: Nz([Expr1],0)+Nz([Quantity_N_Spread],0)

[Bob Fitz]

Have you tried:
Expr1: Nz([Quantity_spread],0)*Nz([Ammonium_N],0)
Expr2: Nz([Expr1],0)+Nz([Quantity_N_Spread],0)
or even:
Expr2: (Nz([Quantity_spread],0)*Nz([Ammonium_N],0))+Nz([Quantity_N_Spread],0)

[mike_980]

Ok I'm trying just with the first expression for now until I get it working. The only value that it is possible for there to be no value for is "Quantity_spread".

It is possible for a destination to have a value in the "quantity_N_spread" but not the "Quantity_spread" - both are in seperate tables as you see. but if this is the case then the query shows no values

Excuse the fact that quantity_N_spread is duplicated, that's now fixed

[Bob Fitz]

Perhaps this would work but I’m not certain:

Remove field tbl_spreading.Destination_ID from the query and replace it with field tbl_destination.Destination_ID

[Dal Jeanis]

1) There's currently no linkage of any kind to the tbl_Results table. If you have more than one record in there, you're likely to get a useless result.
2) If you have just two different kind of things being spread, you might consider having a single tbl_Spreading with two different types of records, and have the record type on the tbl_spreading linked to the appropriate tbl_results record. That could simplify the query a lot.

Discounting that potential change, the following SQL should return one record per destination, with the sums of all quantity amounts in tbl_spreading and tbl_spreading_fert. If the resulting amounts look right, then you can use this query as the basis of your calculation.

SELECT
   TD.Destination_ID,
   TD.Crop_ID,
   TD.Spreading_Area,
   TS.QuantityS,
   TF.QuantityF
FROM 
    ( (SELECT TD2.Destination_ID, Sum(NZ(TS2.Quantity_Spread)) AS QuantityN
       FROM tbl_Destination As TD2 LEFT JOIN tbl_Spreading AS TS2
       ON TD2.Destination_ID = TS2.Destination_ID
       GROUP BY TD2.Destination_ID) AS TS
    INNER JOIN
      (SELECT TD3.Destination_ID, Sum(NZ(TF3.Quantity_N_Spread)) AS QuantityF
       FROM tbl_Destination As TD3 LEFT JOIN tbl_Spreading_fert AS TF3
       ON TD3.Destination_ID = TF3.Destination_ID
       GROUP BY TD3.Destination_ID) AS TF
    ON TF.Destination_ID = TS.Destination_ID
    )
    INNER JOIN
    tbl_Destination As TD 
    ON TD.Destination_ID = TS.Destination_ID;

[mike_980]

Dal Jeanis - I have no idea how that SQL works as I have no clue when it comes to SQL however i copied that into the SQL view of a new query and changed "quantityS" to "quantityF" which I guess was a typo and it worked perfectly! I can't thankyou enough I have been pulling my hair out for days!

[mike_980]

1) There's currently no linkage of any kind to the tbl_Results table. If you have more than one record in there, you're likely to get a useless result.

There is only ever going to be a single record in there - that is where the user inputs the latest slurry analysis results to show how much of each chemical the slurry contains and then the calculations run from the single record.

I guess I could join the spreading tables together and it may work better thanks for the suggestion - as you will pribably have guessed i'm certainly no expert when it comes to access and it takes me a while to get things working right because i have only ever built 2 or 3 basic databases before but i'm keen to learn as it is such a useful tool!

[mike_980]

Ok, sorry to be a pain but there are a few tiny little details not working as planned that I have just found,

1. from table_spreading the quantity in there is the volume of slurry spread but we are only interested in the nitrogen content in that slurry (ammonium_N + Nitrate_N which are kept in tbl_results)
the equation for this is in the screenshot I posted before.

2. I only want the query to show the current years spreading results (july 1st -June 30th) as we get a new allowance on july 1st so what we spread the previous year is not a concern. For example, if i entered that we spread slurry on a field in 2012 it would not show in the query.


Thanks everyone for your help so far!


Edit:
Sorry for the repeated posts! I have solved the first one though and added another column which gives a total N/ha that the field has had, it just needs adapting now to make it only show the current year, can anyone help?

SELECT TD.Destination_ID, TD.Crop_ID, TD.Spreading_Area, TF.QuantityF, ([Ammonium_N]+[Nitrate_N])*[QuantityN]/[Spreading_area] AS Slurry_N, [QuantityF]+[Slurry_N] AS Expr1
FROM tbl_results, ((SELECT TD2.Destination_ID, Sum(NZ(TS2.Quantity_Spread)) AS QuantityN FROM tbl_Destination AS TD2 LEFT JOIN tbl_Spreading AS TS2 ON TD2.Destination_ID = TS2.Destination_ID GROUP BY TD2.Destination_ID) AS TS INNER JOIN (SELECT TD3.Destination_ID, Sum(NZ(TF3.Quantity_N_Spread)) AS QuantityF FROM tbl_Destination AS TD3 LEFT JOIN tbl_Spreading_fert AS TF3 ON TD3.Destination_ID = TF3.Destination_ID GROUP BY TD3.Destination_ID) AS TF ON TS.Destination_ID = TF.Destination_ID) INNER JOIN tbl_Destination AS TD ON TS.Destination_ID = TD.Destination_ID;

[Dal Jeanis]

Okay, inside the two subselects, you need to limit the dates of spreadings that are being summed.

Here's the first one - and I'll let you set the proper dates and adapt the method to do the second one.

 (SELECT TD2.Destination_ID, 
  Sum(NZ(TS2.Quantity_Spread)) AS QuantityN 
  FROM tbl_Destination AS TD2 
  LEFT JOIN 
  tbl_Spreading AS TS2 
  ON TD2.Destination_ID = TS2.Destination_ID 
  WHERE (TS2.Date_of_spreading IS BETWEEN #01/01/2013# AND #12/31/2013#)
 GROUP BY TD2.Destination_ID) AS TS