Query loses records?

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello all

I have a table [staffs] with key field 'name' (I know - very bad - but I
inherited this and the application works!), and a table [stafpay] with
linked field 'name'. Each [staffs].name value is in [stafpay] many times.

The following query returns all the expected records:

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This return every record from [staffs] with [staffs].practice=[Forms]![frm x
main]![prac name] - as expected.

BUT when I add a field from [stafpay] some records are omitted from the
query results.
e.g.

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

.... omits those records from [staffs] where there is no coresponding record
in [stafpay] with a 'month name' value of "November 2008". I would have
expected the LEFT JOIN in the query to mean that these records should have
been returned.

Hope someone can help - this is driving me nuts!

Many thanks
Les
 
Hi Leslie,

Looks like exactly what I'd expect: you have a criteria (from the WHERE
clause in your SQL) which says:
stafpay.[month name]="November 2008"
so why would you expect to see any records where this is not true?

The LEFT JOIN will give only records from the stafpay table where there is
an entry in the staffs table (already restricted by the HAVING clause); you
are then restricting those records via your criteria clause.

What exactly are you expecting (or wanting)?

Rob
 
Hello Rob

Many thanks for your reply.
I was wanting to see all the records from [staffs] irrespective of whether
there is a related record in [stafpay] with a 'month name' value of
"November 2008". Presumably the LEFT JOIN doesn't override the criteria on
'month name' - which I had thought it would!

Where there is a related record in [stafpay] with a 'month name' value of
"November 2008", I need the query to return some other fields from that
related record in [stafpay].

Hope that makes it clearer!?

Thanks for your continued help.
Les



Rob Parker said:
Hi Leslie,

Looks like exactly what I'd expect: you have a criteria (from the WHERE
clause in your SQL) which says:
stafpay.[month name]="November 2008"
so why would you expect to see any records where this is not true?

The LEFT JOIN will give only records from the stafpay table where there is
an entry in the staffs table (already restricted by the HAVING clause);
you are then restricting those records via your criteria clause.

What exactly are you expecting (or wanting)?

Rob

Leslie said:
Hello all

I have a table [staffs] with key field 'name' (I know - very bad -
but I inherited this and the application works!), and a table
[stafpay] with linked field 'name'. Each [staffs].name value is in
[stafpay] many times.

The following query returns all the expected records:

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This return every record from [staffs] with
[staffs].practice=[Forms]![frm x main]![prac name] - as expected.

BUT when I add a field from [stafpay] some records are omitted from
the query results.
e.g.

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

... omits those records from [staffs] where there is no coresponding
record in [stafpay] with a 'month name' value of "November 2008". I
would have expected the LEFT JOIN in the query to mean that these
records should have been returned.

Hope someone can help - this is driving me nuts!

Many thanks
Les
Click to expand...
Click to expand...
 
Hi Leslie,

Try adding an additional criteria of "Is Null" to the query, as follows:
SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
OR ((stafpay.MonthName) Is Null))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This is based on your previous SQL that you quoted. Im assuming that it is
simply a test query, since it's not returning any additional fields from the
stafpay table, but it should now do so if you include them in the SELECT
clause.

HTH,

Rob


Leslie said:
Hello Rob

Many thanks for your reply.
I was wanting to see all the records from [staffs] irrespective of
whether there is a related record in [stafpay] with a 'month name'
value of "November 2008". Presumably the LEFT JOIN doesn't override
the criteria on 'month name' - which I had thought it would!

Where there is a related record in [stafpay] with a 'month name'
value of "November 2008", I need the query to return some other
fields from that related record in [stafpay].

Hope that makes it clearer!?

Thanks for your continued help.
Les



Rob Parker said:
Hi Leslie,

Looks like exactly what I'd expect: you have a criteria (from the
WHERE clause in your SQL) which says:
stafpay.[month name]="November 2008"
so why would you expect to see any records where this is not true?

The LEFT JOIN will give only records from the stafpay table where
there is an entry in the staffs table (already restricted by the
HAVING clause); you are then restricting those records via your
criteria clause. What exactly are you expecting (or wanting)?

Rob

Leslie said:
Hello all

I have a table [staffs] with key field 'name' (I know - very bad -
but I inherited this and the application works!), and a table
[stafpay] with linked field 'name'. Each [staffs].name value is in
[stafpay] many times.

The following query returns all the expected records:

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This return every record from [staffs] with
[staffs].practice=[Forms]![frm x main]![prac name] - as expected.

BUT when I add a field from [stafpay] some records are omitted from
the query results.
e.g.

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

... omits those records from [staffs] where there is no coresponding
record in [stafpay] with a 'month name' value of "November 2008". I
would have expected the LEFT JOIN in the query to mean that these
records should have been returned.

Hope someone can help - this is driving me nuts!

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
Hello Rob

OK - I get it I think!
The only problem now is that I'm not in the office until Monday so won't be
able to test your suggestion until then. I will let you know though!

Many thanks
Les

Rob Parker said:
Hi Leslie,

Try adding an additional criteria of "Is Null" to the query, as follows:
SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
OR ((stafpay.MonthName) Is Null))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This is based on your previous SQL that you quoted. Im assuming that it
is simply a test query, since it's not returning any additional fields
from the stafpay table, but it should now do so if you include them in the
SELECT clause.

HTH,

Rob


Leslie said:
Hello Rob

Many thanks for your reply.
I was wanting to see all the records from [staffs] irrespective of
whether there is a related record in [stafpay] with a 'month name'
value of "November 2008". Presumably the LEFT JOIN doesn't override
the criteria on 'month name' - which I had thought it would!

Where there is a related record in [stafpay] with a 'month name'
value of "November 2008", I need the query to return some other
fields from that related record in [stafpay].

Hope that makes it clearer!?

Thanks for your continued help.
Les



Rob Parker said:
Hi Leslie,

Looks like exactly what I'd expect: you have a criteria (from the
WHERE clause in your SQL) which says:
stafpay.[month name]="November 2008"
so why would you expect to see any records where this is not true?

The LEFT JOIN will give only records from the stafpay table where
there is an entry in the staffs table (already restricted by the
HAVING clause); you are then restricting those records via your
criteria clause. What exactly are you expecting (or wanting)?

Rob

Leslie Isaacs wrote:
Hello all

I have a table [staffs] with key field 'name' (I know - very bad -
but I inherited this and the application works!), and a table
[stafpay] with linked field 'name'. Each [staffs].name value is in
[stafpay] many times.

The following query returns all the expected records:

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This return every record from [staffs] with
[staffs].practice=[Forms]![frm x main]![prac name] - as expected.

BUT when I add a field from [stafpay] some records are omitted from
the query results.
e.g.

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

... omits those records from [staffs] where there is no coresponding
record in [stafpay] with a 'month name' value of "November 2008". I
would have expected the LEFT JOIN in the query to mean that these
records should have been returned.

Hope someone can help - this is driving me nuts!

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
Hello Rob

Sorry for the delay in reporting back: things have been a bit busy here!
Your suggestion worked perfectly: but I guess you knew it would?!

Many thanks for your help.
Les

Rob Parker said:
Hi Leslie,

Try adding an additional criteria of "Is Null" to the query, as follows:
SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
OR ((stafpay.MonthName) Is Null))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This is based on your previous SQL that you quoted. Im assuming that it
is simply a test query, since it's not returning any additional fields
from the stafpay table, but it should now do so if you include them in the
SELECT clause.

HTH,

Rob


Leslie said:
Hello Rob

Many thanks for your reply.
I was wanting to see all the records from [staffs] irrespective of
whether there is a related record in [stafpay] with a 'month name'
value of "November 2008". Presumably the LEFT JOIN doesn't override
the criteria on 'month name' - which I had thought it would!

Where there is a related record in [stafpay] with a 'month name'
value of "November 2008", I need the query to return some other
fields from that related record in [stafpay].

Hope that makes it clearer!?

Thanks for your continued help.
Les



Rob Parker said:
Hi Leslie,

Looks like exactly what I'd expect: you have a criteria (from the
WHERE clause in your SQL) which says:
stafpay.[month name]="November 2008"
so why would you expect to see any records where this is not true?

The LEFT JOIN will give only records from the stafpay table where
there is an entry in the staffs table (already restricted by the
HAVING clause); you are then restricting those records via your
criteria clause. What exactly are you expecting (or wanting)?

Rob

Leslie Isaacs wrote:
Hello all

I have a table [staffs] with key field 'name' (I know - very bad -
but I inherited this and the application works!), and a table
[stafpay] with linked field 'name'. Each [staffs].name value is in
[stafpay] many times.

The following query returns all the expected records:

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

This return every record from [staffs] with
[staffs].practice=[Forms]![frm x main]![prac name] - as expected.

BUT when I add a field from [stafpay] some records are omitted from
the query results.
e.g.

SELECT staffs.practice, staffs.ID
FROM staffs LEFT JOIN stafpay ON staffs.name = stafpay.name
WHERE (((stafpay.[month name])="November 2008"))
GROUP BY staffs.practice, staffs.ID
HAVING (((staffs.practice)=[Forms]![frm x main]![prac name]));

... omits those records from [staffs] where there is no coresponding
record in [stafpay] with a 'month name' value of "November 2008". I
would have expected the LEFT JOIN in the query to mean that these
records should have been returned.

Hope someone can help - this is driving me nuts!

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
Back
Top