Find all records not updated this month.

[Guest]

Hi,

I have two tables: AllTitles and TitlesHistory. AllTitles contains the
total of the titles that I update monthly and the TitleHistory has a record
appended each time I update a title and includes the date. I am trying to
create a query that will show me all Titles that have not been updated yet
for the current month. Here's the SQL I have started with:

SELECT AllLists.List, AllLists.[Total Count], AllLists.LastUpdate,
AllLists.DateSent, ListHistory.DateSent,
MonthName(DatePart("m",[listhistory].[datesent])) AS MonthName
FROM ListHistory INNER JOIN AllLists ON ListHistory.List = AllLists.List
WHERE
(((MonthName(DatePart("m",[listhistory].[datesent])))<>MonthName(DatePart("m",Now()))))
ORDER BY MonthName(DatePart("m",[listhistory].[datesent]));

Can anyone give suggestions on the best way to do this?

Thanks in advance.

 

[Jeff Boyce]

Bonnie

Let's see if I can paraphrase...

Your [TitlesHistory] table has one entry for each title updated (title and
date). So you could create a query that shows all titles updated in a given
time period.

Your [AllTitles] table lists each title (once, I assume).

If I understand what you are trying to do, it sounds like you would want to
run an "unmatched" query (there's a wizard to help with that) between your
[AllTitles] table and the query that shows all updated titles in a given
time period.

In English, "show me all the Titles that are not in the list of updated
Titles".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes, that's it. Your are right on. As a matter of fact I've been working on
it and that's how I finally got it too. Thanks for taking the time to help.

Bonnie

Let's see if I can paraphrase...

Your [TitlesHistory] table has one entry for each title updated (title and
date). So you could create a query that shows all titles updated in a given
time period.

Your [AllTitles] table lists each title (once, I assume).

If I understand what you are trying to do, it sounds like you would want to
run an "unmatched" query (there's a wizard to help with that) between your
[AllTitles] table and the query that shows all updated titles in a given
time period.

In English, "show me all the Titles that are not in the list of updated
Titles".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have two tables: AllTitles and TitlesHistory. AllTitles contains the
total of the titles that I update monthly and the TitleHistory has a
record
appended each time I update a title and includes the date. I am trying to
create a query that will show me all Titles that have not been updated yet
for the current month. Here's the SQL I have started with:

SELECT AllLists.List, AllLists.[Total Count], AllLists.LastUpdate,
AllLists.DateSent, ListHistory.DateSent,
MonthName(DatePart("m",[listhistory].[datesent])) AS MonthName
FROM ListHistory INNER JOIN AllLists ON ListHistory.List = AllLists.List
WHERE
(((MonthName(DatePart("m",[listhistory].[datesent])))<>MonthName(DatePart("m",Now()))))
ORDER BY MonthName(DatePart("m",[listhistory].[datesent]));

Can anyone give suggestions on the best way to do this?

Thanks in advance.