Sql query: If a date exists for the record add the last one in.

[munkee]

I have a system where records are created, costs added to the record and also E-mails that are sent using access are logged and attached to the record.

These emails have a date+time sent field attached to them so I can basically look at a record and see any communication history.

At present I produce a query which contains the following information:

NCC_ID    DteReport    DteOccur    DeptRaisedBy    DeptResp    NCStatus    SumOfCostFig
472    03/06/2010    20/05/2010    Service Facility - Heavy Machining    Service Facility - Heavy Machining    Awaiting Closure    £700.00
521    05/07/2010    02/07/2010    Service Facility - Blading    Service Facility - Blading    Awaiting Closure    £1,260.00
522    05/07/2010    25/06/2010    Business Excellence    HS&E    Pending Acceptance    £125.00
524    06/07/2010    09/06/2010    Finance & Commercial    HS&E    Awaiting Closure    £400.00
525    06/07/2010    05/06/2010    Finance & Commercial    Field Service    Action Being Taken    £80.00
536    10/07/2010    08/06/2010    Service Facility - Generator Assembly    Service Facility - Generator Assembly    Initial Notification    £40.00

However, I now want to add the following:

If an E-mail has been sent, I want the last date e.g. The newest E mail date to be attached.

My sql for the above is:

SELECT tbllog.NCC_ID, tbllog.DteReport, tbllog.DteOccur, tbldept_1.Department AS DeptRaisedBy, tbldept.Department AS DeptResp, tblstatus.NCStatus, Sum(tblCosts.CostFig) AS SumOfCostFig
FROM (tblstatus INNER JOIN ((tbllog INNER JOIN tbldept AS tbldept_1 ON tbllog.DeptRaisedBy = tbldept_1.DeptID) INNER JOIN tbldept ON tbllog.DeptResp = tbldept.DeptID) ON tblstatus.NCStatusID = tbllog.NCStatus) INNER JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID
GROUP BY tbllog.NCC_ID, tbllog.DteReport, tbllog.DteOccur, tbldept_1.Department, tbldept.Department, tblstatus.NCStatus;

Quite a long mess due to using the in built wizard. However when I add in the E-mail date field I am only being shown two records, the two records that have had emails sent in the past.

I want to see all records, but only attach the date if there has been an email sent, also the last of these.

Hard to explain and likely confusing if any more information is required don't hesitate to ask.

Thanks,

Chris

PS the set up of my query:

[mseo]

@munkee
hi,
you can use something like: Max(date) you posted your query and mentioned few details about what your problem , please, try to restate the problem, to let us understand your question clearly
hope this helps

[munkee]

Thanks for the reply.

The problem is as soon as I add in the date field from the tblEmail to join it to the query. I only get results returned for records that have an email in the tblEmail.

I want to show both those with dates and those without, if there is a date I want to show the last date, or max like you stated. If there isnt I want it to be blank.

[nico5038]

You'll need to double click the connection line between the existing table and the email table. Then select the option that the existing table is always present and that the email table is optional. An arrow will occur directing to the email table when all is OK.

This is called a LEFT or RIGHT JOIN....

Nic;o)