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

**mseo** · Jul 15 '10, 07:51 PM

Originally posted by 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:

Code:

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:

Code:

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:

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** · Jul 15 '10, 08:52 PM

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** · Jul 15 '10, 09:08 PM

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)

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

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

Comment

Comment

Comment