Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Query / Expression :total of multiple feilds

Posted on 2003-11-10
1
Medium Priority
?
2,279 Views
Last Modified: 2008-02-01
This should be simple looking to sum up all of these route times but when i run the query the total feild is empty why and how do I fix it ????

here is the query>

SELECT route.RouteType, route.RouteName, route.UnitType, [route]![Shear]+[route]![Amada]+[route]![Brake]+[route]![Spot]+[route]![Router]+[route]![Buff]+[route]![Powder]+[route]![Weld]+[route]![Deburr]+[route]![ReShear]+[route]![Radius]+[route]![Insert]+[route]![Drill]+[route]![CutoffSaw]+[route]![Notcher]+[route]![StoneSaw]+[route]![Hinge Emboss] AS totaltime, route.Shear, route.Amada, route.Brake, route.Spot, route.Router, route.Buff, route.Powder, route.Weld, route.Deburr, route.ReShear, route.Radius, route.Insert, route.Drill, route.CutoffSaw, route.Notcher, route.StoneSaw, route.[Hinge Emboss], *
FROM route;
0
Comment
Question by:cmmonline
1 Comment
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 300 total points
ID: 9717367
if ANY of the fields that are prt of the total is a NULL, then the Sum will result in a NULL value.

This looks like a very bad design, having all of those fields in the same record.

but since that is what you have, then you MUST account for the possible NULL values, using the NZ function:

SELECT route.RouteType, route.RouteName, route.UnitType, NZ([route]![Shear],0)+nz([route]![Amada],0)+nz([route]![Brake],0)+nz([route]![Spot],0)+nz([route]![Router],0)+nz([route]![Buff],0)+nz([route]![Powder],0)+nz([route]![Weld],0)+nz([route]![Deburr],0)+nz([route]![ReShear],0)+nz([route]![Radius],0)+nz([route]![Insert],0)+nz([route]![Drill],0)+nz([route]![CutoffSaw],0)+nz([route]![Notcher],0)+nz([route]![StoneSaw],0)+nz([route]![Hinge Emboss],0) AS totaltime, route.Shear, route.Amada, route.Brake, route.Spot, route.Router, route.Buff, route.Powder, route.Weld, route.Deburr, route.ReShear, route.Radius, route.Insert, route.Drill, route.CutoffSaw, route.Notcher, route.StoneSaw, route.[Hinge Emboss], *
FROM route;

a MUCH better and safer design would be to have a separate RouteTimes Table, :

RouteTime
------------------
RouteType
RouteStep
RouteTime

the Route Steps could then be either Strings like "ReShear", "Radius","Insert" etc, or else, if theses steps might change in the future, the have another table (Steps), and use the Primary key from that Table in the RouteTime Table.


The NZ function returns the value of the Field if it is NOT Null, and the value of the second argument (0 in the above) is the field IS NULL.

AW
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question