Awkward query!

[PayeDoc]

Hello All

I have a table [months], with fields 'month name' (key field), 'bacs date'
(plus a few other fields), and I have a table [alarm schedule] fields 'month
name', and 'alarm_ID' (plus a few other fields). In table [alarm schedule]
there are many records with the same value in 'month name' - but with
different 'alarm_ID' values. I need a query that will return all records
from table [months] where there is a non-null value in field 'bacs date' but
where there is no corresponding field in table [alarm schedule] with an
'alarm_ID' value of 4.

i.e. if there is a record in [months] with a 'month name' value of "November
2008" where 'bacs date' = "04/11/08", but none of the records in [alarm
schedule] that have a 'month name' value of "November 2008" have a
'alarm_ID' value of 4, the query must return the record from [months] with
the 'month name' value of "November 2008" and 'bacs date' = "04/11/08".

I hope someone can help me with this, as I can't seem to get the results I
need!

Many thanks
Leslie Isaacs

 

[PayeDoc]

Hello MGFoster

Many thanks for your reply.

Your query almost works, except that it returned a record from [months] for
which there IS a corresponding record in [alarm schedule]
i.e. in table [months] there is a record with 'month name' = "November 2008"
and 'bacs_IR_submission_date' = "14/12/08", but there is also a record in
table [alarm schedule] with a 'month name' = "November 2008" and 'alarm ID'
= 4 (the field I originally referred to as 'bacs date' is in fact called
'bacs_IR_submission_date')

I should also add that I had to 'group' the query, as before I did so it
returned records for each row in [months] with the same 'month name' values,
whereas I only want 1 record per 'month name' value.

The query I now have is
SELECT M.[month name], M.bacs_IR_submission_date
FROM months AS M LEFT JOIN [alarm schedule] AS A ON M.[month name] =
A.[month name]
WHERE (((A.alarm_ID)<>4))
GROUP BY M.[month name], M.bacs_IR_submission_date
HAVING (((M.bacs_IR_submission_date) Is Not Null));

Hope you can help.

Many thanks
Les

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not too clear on your requirements. Perhaps this:

SELECT *
FROM [months] As M LEFT JOIN [alarm schedule] As A
ON M.[month name] = A.[month name]
WHERE A.alarm_id <> 4
AND M.[bacs date] IS NOT NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSRsKdIechKqOuFEgEQLiUACgtWzCjyUof7TPrCTYfqaDwzwOPsUAniFQ
fYavN9nT6fwnEJNzWKozQD3t
=cA9f
-----END PGP SIGNATURE-----

Hello All

I have a table [months], with fields 'month name' (key field), 'bacs date'
(plus a few other fields), and I have a table [alarm schedule] fields 'month
name', and 'alarm_ID' (plus a few other fields). In table [alarm schedule]
there are many records with the same value in 'month name' - but with
different 'alarm_ID' values. I need a query that will return all records
from table [months] where there is a non-null value in field 'bacs date' but
where there is no corresponding field in table [alarm schedule] with an
'alarm_ID' value of 4.

i.e. if there is a record in [months] with a 'month name' value of "November
2008" where 'bacs date' = "04/11/08", but none of the records in [alarm
schedule] that have a 'month name' value of "November 2008" have a
'alarm_ID' value of 4, the query must return the record from [months] with
the 'month name' value of "November 2008" and 'bacs date' = "04/11/08".

I hope someone can help me with this, as I can't seem to get the results I
need!

Many thanks
Leslie Isaacs