asked on

Simple Access IF query when no join on tables?

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!

peter57r

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)

SOLUTION