[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Simple Access IF query when no join on tables?

Posted on 2011-10-19
Medium Priority
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

ID: 36991731
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

mbizup earned 498 total points
ID: 36991733
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

Accepted Solution

VipulKadia earned 501 total points
ID: 36991801
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

GRayL earned 501 total points
ID: 36996172
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

ID: 37923115

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 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