Zero Lines

[TajikBoy]

Hi Guys,

Its me again.......

I have written below SQL to list all contracts with invoices to run a report, but unfortunately, it only results contracts with invoices attached to it, but I need the zero ones listed too

How do I achieve this?

SELECT tblProjectContracts.ProjectName, tblProjectContracts.AreaName, tblProjectContracts.Type, tblProjectContracts.Vendor, tblProjectContracts.CtrValue, tblProjectContracts.VOValue, tblProjectContracts.LPOValue, tblProjectContracts.Reference, tblProjectContracts.AreaBudget, tblProjects.ProjectBudget, tblProjectInvoices.InvTotal
FROM (tblProjects RIGHT JOIN tblProjectContracts ON tblProjects.ID = tblProjectContracts.ProjectName) RIGHT JOIN tblProjectInvoices ON tblProjectContracts.id = tblProjectInvoices.Vendor
GROUP BY tblProjectContracts.ProjectName, tblProjectContracts.AreaName, tblProjectContracts.Type, tblProjectContracts.Vendor, tblProjectContracts.CtrValue, tblProjectContracts.VOValue, tblProjectContracts.LPOValue, tblProjectContracts.Reference, tblProjectContracts.AreaBudget, tblProjects.ProjectBudget, tblProjectInvoices.InvTotal;

Thanks in advance,

Erol

 

[Ranman256]

you want an OUTER join. dbl-click the join line,
set Show ALL records in Contracts, SOME records in Invoices.

this should show you all your values.

 

[MajP]

SELECT tblprojectcontracts.projectname,
       tblprojectcontracts.areaname,
       tblprojectcontracts.type,
       tblprojectcontracts.vendor,
       tblprojectcontracts.ctrvalue,
       tblprojectcontracts.vovalue,
       tblprojectcontracts.lpovalue,
       tblprojectcontracts.reference,
       tblprojectcontracts.areabudget,
       tblprojects.projectbudget,
       tblprojectinvoices.invtotal
FROM   (tblprojects
        RIGHT JOIN tblprojectcontracts
                ON tblprojects.id = tblprojectcontracts.projectname)
       RIGHT JOIN tblprojectinvoices
               ON tblprojectcontracts.id = tblprojectinvoices.vendor
GROUP  BY tblprojectcontracts.projectname,
          tblprojectcontracts.areaname,
          tblprojectcontracts.type,
          tblprojectcontracts.vendor,
          tblprojectcontracts.ctrvalue,
          tblprojectcontracts.vovalue,
          tblprojectcontracts.lpovalue,
          tblprojectcontracts.reference,
          tblprojectcontracts.areabudget,
          tblprojects.projectbudget,
          tblprojectinvoices.invtotal;

 

[TajikBoy]

Thank you guys....... I need to buy you guys few beers