Re: Delete query date field pulling more than one month

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I don't think there's going to be a way of doing it without getting a bit
elaborate. Nothing wrong with that though.

I *think* I now have a handle on what you're trying to do, and even if I
don't then hopefully what I'm about to suggest will give you a general
method that you can fine tune anyway.

From your posted set of sample data (which I'm assuming is called
tblMtdYtdAllRolling) I'd do something like this:

Query Q1 - will come up with the earliest date stamp for each month/year.
SELECT tblMtdYtdAllRolling.Year, tblMtdYtdAllRolling.Month,
Min(tblMtdYtdAllRolling.DateEntered) AS MinOfDateEntered
FROM tblMtdYtdAllRolling
GROUP BY tblMtdYtdAllRolling.Year, tblMtdYtdAllRolling.Month;

Query Q2 - links that data back to tblMdtYtdAllRolling to produce the
related IDs.
SELECT tblMtdYtdAllRolling.ID
FROM Q1 INNER JOIN tblMtdYtdAllRolling ON (Q1.MinOfDateEntered =
tblMtdYtdAllRolling.DateEntered) AND (Q1.Month = tblMtdYtdAllRolling.Month)
AND (Q1.Year = tblMtdYtdAllRolling.Year);

Query Q3 - just deletes those IDs
DELETE tblMtdYtdAllRolling.ID
FROM tblMtdYtdAllRolling
WHERE (((tblMtdYtdAllRolling.ID) In (select * from Q2)));

As I say, you might need to do some tuning on that. A couple of things that
might be necessary:

Limit records to delete to only those with a data stamp before the 5th. You
can do that kind of thing by including an additional calculated field onto
Q1 Day([DateEntered]) and applying a criteria (e.g. <5) to that.

This general technique can work in two different ways: either by coming up
with a list of IDs that you want to delete (as in the example above), or a
list of IDs that you want to keep (in which case the criteria on Q3 would be
'Not In' instead of just 'In'.

The last delete query (Q3 above) obviously needs to be applied to an
updatable source. It may be that you work out the relavent IDs to either
delete or keep from a non-updatable query, but then use the base table to
delete from in Q3.

How does that look?


"Bill Fischer via AccessMonster.com" <u16683@uwe> wrote in message
news:5c09583ebda1e@xxxxxx
Exactly, I have to test some more, but this:

WHERE DMin("DateEntered","tblMtdYtdAllRolling","[DateEntered]>=DateSerial
(Year(Date()),Month(Date()),1)")

Seems to be working, althought I don't think the criteria needs to be this
elborate.

Thanks again Rob!!!


Rob Oldfield wrote:
Still not 100% certain what you're after. With your sample data, you
want
to leave the latest Jan record, and the latest Feb as well?

Rob,

[quoted text clipped - 29 lines]

Bill Fischer

--
Message posted via http://www.accessmonster.com


.

Relevant Pages

  • Re: Delete query date field pulling more than one month
    ... Rob I really appreciate the time you have put into this headache of mine. ... I hope you can help me with this IIf syntax for one query. ... FROM Q1 INNER JOIN tblMtdYtdAllRolling ON (Q1.MinOfDateEntered = ... Query Q3 - just deletes those IDs ...
    (microsoft.public.access.queries)
  • Re: Heres one for ya.
    ... You could also try nesting a select statement in the criteria like this; ... If you want to filter out IDs ... then EXCLUDE that ID from a query. ...
    (comp.databases.ms-access)
  • Re: Delete query
    ... Assuming that there are no relationships between the two IDs, ... query for table A with the following criteria for ID: ... A and B. Each table has a numeric key field that is ...
    (comp.databases.ms-access)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)