Only updating a field once when running same query multiple times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Contro - 07 Dec 2005 13:26 GMT

Hi guys!

Kind of hard to put in a subject header for this.

Basically, I want to run a query so that it picks up which records have
today's date in a specified field. But when the query is run again, it
will not pick up that record again.

How do you go about this?

I've had a few thoughts on it: is it possible to create a query that
sets a field to true once the query has been run via an update, or
would this not work as it's separate from the select statement i.e.
access would complain that it's not a valid statement or somesuch
(apologies for not yet trying this out; I meant to, but didn't get
chance this morning, and won't have enough time to post this message
this afternoon)?

Have you guys any ideas about how best to tackle this? Your help, as
always, would be hugely appreciated!

Many thanks in advance.

Contro.

Reply to this Message

John Spencer - 07 Dec 2005 21:37 GMT

You can run a SELECT query and immediately after run an UPDATE query. That
would be two separate queries.

You might use a DateTime field as your tracking field and do this the other
way around. Run the Update of the datetime field using NOW(). Then do your
select query based on that datetime field. Obviously you would need to
store the value of NOW() when you ran the update query (or you could just
grab the maximum value of the field).

UPDATE YOURTABLE
SET LastChecked = NOW()
WHERE LastChecked is Null and YourOtherField = Date()

SELECT * FROM YourTable
WHERE YourOtherField=Date() and
LastChecked = DMax("LastChecked","YourTable","YourOtherField=Date()")

You could probably get away with
DMAX("LastChecked","YourTable")

> Hi guys!
>
[quoted text clipped - 20 lines]
>
> Contro.

Reply to this Message

Contro - 08 Dec 2005 15:48 GMT

That's great, thank you very much! I've got it working now, although I
didn't use the DMAX function, as I wasn't sure what this does. What
does it do exactly, and why would it be a good idea to use it in this
example (I have not used it, and all seems to work okay so far...but
obviously if some problems might occur due to not using it, then I'd
prefer to stick it in now)?

Thanks again for your help!

Oh, and while you are here...I don't suppose you know if it's possible
to implement an autonumber facility inside a report (so each report has
a unique report number just like primary keys do)?

Contro.

> You can run a SELECT query and immediately after run an UPDATE query. That
> would be two separate queries.
[quoted text clipped - 40 lines]
> >
> > Contro.

Reply to this Message

John Spencer - 08 Dec 2005 18:49 GMT

DMax gets the latest (largest) date value from the table, so you will always
get the latest set of records marked. If your query is working without it,
just leave it out for now. If things start misbehaving, you can add it back
in.

> Oh, and while you are here...I don't suppose you know if it's possible
> to implement an autonumber facility inside a report (so each report has
> a unique report number just like primary keys do)?

As far as I know there is no way to do this without storing data in a table
every time you run the report and then grabbing that. You might be able to
just use the LastChecked Date for this. You are setting the date and time
just before you run the query.

If you want to do this generally - for all reports - then I would build a
table to hold the data I want/need. Do you want to have a one-up number for
each specific type of report or do you want a one-up number for every report
ever printed?

> That's great, thank you very much! I've got it working now, although I
> didn't use the DMAX function, as I wasn't sure what this does. What
[quoted text clipped - 54 lines]
>> >
>> > Contro.

Reply to this Message