help with 2 queries on a table, selecting correct records 1

[Juddy58]

Hi have a table tblCompanyFollowUps which contains the following fields
fldFollowUpID - Primary Key
fldCompanyID
fldFollowUpName
fldFollowUpDate
fldNoMoreFollowUps
fldNotes

Basically the table tracks followups for organisations we provide services to.
A company can have many follow up records.
What im trying to do is set up 2 queries.

The first is to select the record for the next followup date for each company.
eg say I have the following records
fldFollowUpID fldCompanyID fldFollowUpName fldFollowUpDate fldNoMoreFollowUps fldNotes
1 333 Manual 1/1/04 False Test Notes
2 333 test Name 1/2/04 false testnotes
3 333 testname2 2/4/04 false morenotes
4 333 testname3 23/9/04 true notes
5 222 testname 4/5/04 false notes
6 222 test 4/6/04 true testnotes

say todays date is 20/3/04
i would like the query to return
3 333 testname2 2/4/04 false morenotes
5 222 testname 4/5/04 false notes

these are the next followups to occur for each organisation after todays date.

I also need a query to show the last time we were in contact with each organisation, basically
show the highest date value which is less than todays date.
eg with the sample data provided and todays date being 20/3/04 the query would return
2 333 test Name 1/2/04 false testnotes
this is the last time we conducted a follow up for company 333

no record would be returned for company 222 as they didn't have a follow up before 20/3/04

I have had a play around with grouping and selecting the max date, but cant seem to get either query working correctly.
Any help with creating these two queries would be greatly appreciated.
Thanks
Justin

 

[FoxProProgrammer]

Hey, Justin:

Here's your first Query. If DtToday is on a form, use the syntax Forms!YourFormName.DtToday instead of [DtToday]I'm still thinking about the second one.

SELECT tblCompanyFollowUps.*
FROM tblCompanyFollowUps
WHERE (((tblCompanyFollowUps.fldNoMoreFollowUps)=False) AND ((tblCompanyFollowUps.fldFollowUpDate)>[DtToday]));

dz
dzaccess@yahoo.com

 

[dhookom]

[blue]First[/blue][tt]
SELECT tblCompanyFollowUps.*
FROM tblCompanyFollowUps
WHERE (((tblCompanyFollowUps.fldFollowUpID)=
(SELECT Top 1 fldFollowUpID
FROM tblCompanyFollowUps f
where f.fldCompanyID = tblCompanyFollowUps.fldCompanyID
AND f.fldFollowUpDate>Date() ORDER BY fldFollowUpDate)));
[/tt]
[blue]Second[/blue][tt]
SELECT tblCompanyFollowUps.*
FROM tblCompanyFollowUps
WHERE (((tblCompanyFollowUps.fldFollowUpID)=(SELECT Top 1 fldFollowUpID FROM tblCompanyFollowUps f where f.fldCompanyID = tblCompanyFollowUps.fldCompanyID AND f.fldFollowUpDate<#3/20/2004# ORDER BY fldFollowUpDate Desc)));[/tt]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FoxProProgrammer]

Second Query - If I understood you correctly, I think this does what you are looking for.

SELECT tblCompanyFollowUps.*
FROM tblCompanyFollowUps
WHERE (((tblCompanyFollowUps.fldFollowUpDate)=(SELECT Max(fldFollowUpDate) FROM tblCompanyFollowUps WHERE fldFollowUpDate<[DtToday])));

Best regards,



dz
dzaccess@yahoo.com

 

[dhookom]

I see I left the date hard coded in my second query. This should be replaced with Date(). It would help next time if the sample records corresponded better with Date() rather than "say todays date is 20/3/04".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Juddy58]

Thanks alot dhookom and foxpro programmer, im going to look at this tomorrow, and will let you know how i go.
I thought the solution might have had something to do with running another query within the critera of the main query.
Thanks
Justin

 

[Juddy58]

Thank for the help everyone, i ended using dhookoms suggestion and it worked fine.
Thanks
Justin