Hi All,
I have a very simple problem. I want to join table a with table b twice, but return all the records from table a.
The problem. table a has a list of systems, lets say 100. Table b list those systems and a number of types of certificates. I want two in particular, "Z1" and "Z2", both of which have a date against them I need. Now, I can do the left Join and get all the records against Z1, but get nothing but errors when trying to do the second join. Can anyone provide some assistance?
Here is my example that works using the access query builder but it does not return all the records. I understand I need to use the AND statement against the On, but how do you do it a second time?
Code:
SELECT u_AllSubs.System, u_AllSubs.SubSystem, u_AllSubs.SubSystemDescription, ul_ZenCerts.[Date Accepted by Client PMT Rep], ul_ZenCerts_1.[Date Accepted by Client PMT Rep]
FROM (u_AllSubs LEFT JOIN ul_ZenCerts ON u_AllSubs.SubSystem = ul_ZenCerts.Subsystem)
LEFT JOIN ul_ZenCerts AS ul_ZenCerts_1 ON u_AllSubs.SubSystem = ul_ZenCerts_1.Subsystem
WHERE (((ul_ZenCerts.[Certificate Template]) Like "*Z85*") AND ((ul_ZenCerts_1.[Certificate Template]) Like "*Z86*"));
As I mentioned, I can get it to work one time using the code below.
Code:
SELECT u_AllSubs.SubSystem, ul_ZenCerts.[Date Accepted by Client PMT Rep]
FROM (u_AllSubs LEFT JOIN ul_ZenCerts ON ((u_AllSubs.SubSystem = ul_ZenCerts.Subsystem) AND ((ul_ZenCerts.[Certificate Template]) Like "*Z85*")));
So does anyone have any advice or even an answer as to how I can achieve all the records from u_Allsubs and the associated info from Certs?