last date

[acidrain9]

i have a databe set up that holds contact info, as well as notes on each contact
the notes are added and the date is captured to the database table

2 separate questions

1) if each contact has say 5 notes is it possible to pull from that table
of notes the last date a note was added for a customer?

2) also is it possible to display contacts in another datagrid that shows
only those contacts that have not
had a note added in say 30 days?

[Michael Fesser]

.oO(acidrain9)

>i have a databe set up that holds contact info, as well as notes on each contact
> the notes are added and the date is captured to the database table
>
> 2 separate questions
>
> 1) if each contact has say 5 notes is it possible to pull from that table
>of notes the last date a note was added for a customer?
>
> 2) also is it possible to display contacts in another datagrid that shows
>only those contacts that have not
> had a note added in say 30 days?

Both can be done, but it depends on your table structure and what
informations are available from them (date etc.). You should have at
least two tables, one for the contacts, another for the notes. Then it
should be pretty easy to get the informations you want in 1) and 2).

Micha

[acidrain9]

i do have 2 tables set up, here is the structure:

TBL_CONTACTS
ID
Name
PhoneNumber
NoteID

TBL_NOTES
NoteID
NoteDate
Note

i added the NoteDate field to the datagrid but it displays each contact more
than once if it has multiple notes
how do i get it to capture only the most current date and not all of them....

then how can i set up a datgrid to dislay contacts that have not had a note
added to them in say 30 days

[CMBergin]

You could have just used the contact ID in the notes table....
But -
If you just want the date of the last note:
SELECT C.ID, C.Name, C.PhoneNumber, (SELECT MAX(N.NoteDate) FROM TBL_NOTES N
WHERE N.NoteID=C.ID)
FROM TBL_CONTACTS C
ORDER BY C.Name

To show contacts with no recent notes:
SELECT ID, Name, PhoneNumber
FROM TBL_CONTACTS
WHERE NoteID NOT IN (SELECT NoteID FROM TBL_NOTES WHERE NoteDate >=
DATEADD(d,-30,GETDATE())
ORDER BY Name

"acidrain9" <webforumsuser@macromedia.com> wrote in message
news:cvvse8$9s8$1@forums.macromedia.com...

> i do have 2 tables set up, here is the structure:
>
> TBL_CONTACTS
> ID
> Name
> PhoneNumber
> NoteID
>
> TBL_NOTES
> NoteID
> NoteDate
> Note
>
> i added the NoteDate field to the datagrid but it displays each contact

more

> than once if it has multiple notes
> how do i get it to capture only the most current date and not all of

them....

>
> then how can i set up a datgrid to dislay contacts that have not had a

note

> added to them in say 30 days
>