×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Calcualtion in View
2

Calcualtion in View

Calcualtion in View

(OP)
Hi

I have a view which as a calculation for one field

dbo.OrderHeader.TotalVolume * dbo.Delivery.LineCost

However, I have just noticed we have times where something may be collected so there would not be amounts in the fields. It is therefore leaving these lines out of the the query result.

I have tried something like

Select isnull (dbo.OrderHeader.TotalVolume * dbo.Delivery.LineCost,0)

But it still not returning the ones missing. Could anyone advise how I get them to appear please.

Many thanks

RE: Calcualtion in View

There is no mecahnuusm suppressing results which are 0 or NULL, so the exclusion must result from the WHERE clause or you need an outer insted of an inner join. So in short, only the whole query would give us an insight about what's to be mended.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Calcualtion in View

(OP)
HI

Here is the code as it is, for whatever reason it excludes VehicleName called own collection. All the rest come in. My assumption was that the line that calculates (dbo.OrderHeader.TotalVolume * dbo.Delivery.LineCost) would be null or 0 for own collection. Thanks




CODE --> sql

SELECT         dbo.JourneyHeader.JourneyDate, dbo.Vehicle.Name, dbo.JourneyHeader.DriverName, dbo.JourneyHeader.VehicleName, 
                      dbo.JourneyHeader.JourneyNumber, dbo.JourneyHeader.TotalVolume, dbo.JourneyLine.DropNumber, dbo.OrderHeader.DeliveryAddress, 
                      dbo.Customer.Name AS [Customer Name], dbo.Customer.County, dbo.Customer.PostCode, dbo.JourneyHeader.NoOfStops, dbo.OrderHeader.udfSalesOrderOTIF, 
                      dbo.JourneyHeader.NoOfPacks, dbo.JourneyHeader.udfLoader1, dbo.JourneyHeader.udfLoader2, dbo.JourneyHeader.TransportCost, dbo.JourneyHeader.Reference, 
                      dbo.DeliveryArea.Name AS DeliveryArea, dbo.OrderHeader.TotalVolume * dbo.Delivery.LineCost AS BistrackTotalCost
FROM         dbo.JourneyHeader INNER JOIN
                      dbo.JourneyLine ON dbo.JourneyHeader.JourneyID = dbo.JourneyLine.JourneyID INNER JOIN
                      dbo.Vehicle ON dbo.JourneyHeader.VehicleID = dbo.Vehicle.VehicleID INNER JOIN
                      dbo.OrderHeader ON dbo.JourneyLine.OrderID = dbo.OrderHeader.OrderID INNER JOIN
                      dbo.Customer ON dbo.OrderHeader.CustomerID = dbo.Customer.CustomerID INNER JOIN
                      dbo.DeliveryArea ON dbo.OrderHeader.DeliveryAreaID = dbo.DeliveryArea.DeliveryAreaID INNER JOIN
                      dbo.Delivery ON dbo.DeliveryArea.DeliveryAreaID = dbo.Delivery.DeliveryAreaID AND dbo.Vehicle.VehicleTypeID = dbo.Delivery.VehicleTypeID
ORDER BY dbo.JourneyHeader.JourneyNumber 

RE: Calcualtion in View

Are you missing the entire row or just the calculated value? If the entire row, it suggests there is not a matching record in the "child" table (possibly Delivery). In this case you would need to use a LEFT JOIN rather than INNER JOIN.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Calcualtion in View

(OP)
HI

I think the Left Join as worked. Just testing it a bit more but is bringing them through.

Thanks

RE: Calcualtion in View

Yes, as there is no where caluse the only eason you get no value is becaue INNER JOIN not matching something.

Just notice when you would have records with 0 in them, INNER JOIN would also work, the join conditions are only about IDs, not asking anything about TotalVolume or LineCost.
Both from the way the query is written and from the terms OrderHEADER and Delivery I would conclude Delivery records are missing for some Orderheaders and not the other way around, it's also logic not any order will have a delivery, that only happens as last step.

Whenever you have such a hierarchy and on top of that a chronology meaning some data will only be added later, but you still want to calculate some cost or income summary, you LEFT JOIN table, in which you expect data to be inserted at a löater stage.

The join will not put in 0 for Linecost, but NULL, because a missing match can't give a result. In an overall SUM a NULL will be handled as 0, though more strictly speaking you would also get a NULL if any single summand or factor or concatgenated value of any expression is NULL.

So very strictly programming you would do a LEFT JOIN of the Delivery table and use ISNULL(dbo.Delivery.LineCost,0) instead of dbo.Delivery.LineCost as a factor of dbo.OrderHeader.TotalVolume * ISNULL(dbo.Delivery.LineCost,0)

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Calcualtion in View

Great explanation Olaf.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close