Access contact management template

[damaruk]

I have used the contact management template in the design of my database. part of that has a form for contact date & subject. As each contact for each individual person is added as a new line in the table it is causing duplication of data when I do a query.

What I want to do is do a search on contact date ie) less than 11/09/2000 the query bringsw back all the previous contacts before that date for the customer. What I want to do is a query whos last contact was before 11/09/2000 ie0 the query stops at the most recent contact date rather than going back further. I hope that this garble can be understood!

Can anyone help

thanks

mark [sig][/sig]

 

[DougP]

Paste the SQL code from your Query here. [sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[damaruk]

sorry dont know what SQL code is

I am using <#11/09/00# as the parameter to get the data

regards

Mark [sig][/sig]

 

[damaruk]

found it

SELECT Contacts.ContactID, Contacts.Title, Contacts.FirstName, Contacts.LastName, Contacts.Dear, Contacts.Address, Contacts.Town, Contacts.County, Contacts.PostalCode, Contacts.Region, Contacts.CustomerType, Contacts.CompanyName, Contacts.Position, Contacts.WorkPhone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.FaxNumber, Contacts.[Email Address], Contacts.LastMeetingDate, Contacts.[Enquiry Source], Contacts.BusinessType, Contacts.[Sales Executive], Contacts.Website, Calls.CallDate
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Calls.CallDate)<#9/11/2000#));
[sig][/sig]

 

[DougP]

Try this

SELECT Contacts.ContactID, Contacts.Title, Contacts.FirstName, Contacts.LastName, Contacts.Dear, Contacts.Address, Contacts.Town, Contacts.County, Contacts.PostalCode, Contacts.Region, Contacts.CustomerType, Contacts.CompanyName, Contacts.Position, Contacts.WorkPhone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.FaxNumber, Contacts.[Email Address], Contacts.LastMeetingDate, Contacts.[Enquiry Source], Contacts.BusinessType, Contacts.[Sales Executive], Contacts.Website, Max(Calls.CallDate) AS MaxOfCallDate
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
GROUP BY Contacts.ContactID, Contacts.Title, Contacts.FirstName, Contacts.LastName, Contacts.Dear, Contacts.Address, Contacts.Town, Contacts.County, Contacts.PostalCode, Contacts.Region, Contacts.CustomerType, Contacts.CompanyName, Contacts.Position, Contacts.WorkPhone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.FaxNumber, Contacts.[Email Address], Contacts.LastMeetingDate, Contacts.[Enquiry Source], Contacts.BusinessType, Contacts.[Sales Executive], Contacts.Website
HAVING (((Max(Calls.CallDate))<#9/11/00#));
^-----------------------------

Note the Having at the end And the MAX command, this is the largest or latest date Max(Calls.CallDate) AS MaxOfCallDate
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[djgolds]

I have a similar problem. I've set up a master table that has my customer names and I've created a table for contacts. I want to be able to do a query to find (for example) all customers that I have not talked with in the last 3 months, and I would also like to do a query to determine the last date of contact. Any help would be appreciated!