Simple Access IF query when no join on tables?

Posted on 2011-10-19
Last Modified: 2012-06-27
Hello EE!

I'm having a few problems with a query that I do

I have three tables that is used in a simple weight loss programme

My table is "Names" and contains a long list of people names and their starting weight , "HalfWayWeight" contains a list of names with their corresponding mid way weight and "EndWeight" contains a list of people names and their corresponding end weight.

My query takes the Names table and then joins midway and end weights. However, sometimes there are no mid or end weight listed. What I'm trying to do is the following:

Get a query that gives the following:

Name, StartWeight, MidwayWeight, EndWeight and gives a 0 if it's missing. So far I've been trying this:

StartWeight: [Names].[Weight]
MidwayWeight: IIf([HalfWayWeight].[Weight]>0;[HalfWayWeight].[Weight]];0)

However, this does not work. It does not seem to check if "[HalfWayWeight].[Weight]" is greater than zero if that person is not represented in the MidwayWeight table?

I hope one of you out there have a suggestion on how to do these queries!

Thanks in advance!
Question by:ThomasFoege
    LVL 77

    Expert Comment

    Are ypou saying that you are using 3 different table?  (There is no obvious reason for doing that based on what you have described - why can't all the values be in the same table?)

    If either of the halfway or end tables might not have a record that matches the start record then you must address that through the join type.  Right click on the join line in the query design, and choose Join Type; then pick the option that gives you all Start table records.

    As for the missing weights you can try...
    MidwayWeight: NZ([HalfWayWeight].[Weight],0)

    LVL 61

    Assisted Solution

    You probably need to use a LEFT JOIN to get all names regardless of whther they are matched in the Weight Tables.  

    Something like this:

    SELECT StartWeigh, EndWeight, IIf([HalfWayWeight].[Weight] IS NULL, 0,[HalfWayWeight].[Weight])
    FROM (tblNames LEFT JOIN MidwayWeight ON tblNames.PersonID = MidwayWeight.Person ID) LEFT JOIN EndWeight  ON tblNames.PersonID = EndWeight.PersonID
    LVL 8

    Accepted Solution

    Try this one :
    Select Names.Name, Names.Weight as 'StartWeight', HalfWeight.Weight as 'MidwayWeight', EndWeight.Weight as 'EndWeight' FROM Names LEFT JOIN HalfWeight ON Names.Name = HalfWeight.Name LEFT JOIN EndWeight ON Names.Name = EndWeight.Name
    LVL 44

    Assisted Solution

    I think this is what you need:

    Select Names.Name, Names.Weight as [StartWeight], Nz(HalfWeight.Weight,0) as [MidwayWeight], Nz(EndWeight.Weight,0) as [EndWeight] FROM Names LEFT JOIN HalfWeight ON Names.Name = HalfWeight.Name LEFT JOIN EndWeight ON Names.Name = EndWeight.Name;

    Author Closing Comment


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now