Update to specific Date!

[MarkNie]

Hi All

This is a follow up on the Count Records in an update query.

What I am trying to do is once I can get the count function working right, I would like to update this to a tbl with a date field which is automatic. It should update only the records on todays date and not any previous dates.

This is for stats and I need to run the query at least 3 times a day as the stats change through the day. So that at the end of the day I have the latest stats without having multiple date entries in my table. Thus it should only update todays date.

If you know of anyway that this is possible that would be great.

Thanks again
Mark

 

[AlexCuse]

Sounds like you want to place a condition on your update query like

where DateField = date()

To do this in query designer, add the date field on the table to update (but don't set anything to update to) and for Criteria enter the following:

Date()

Hope this helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MarkNie]

Hi AlexCuse

That seems to work but it is selecting the empty record where you input a new record in the table and not the actual record needed.

Any Idead.

Thanks for your Help!!

Mark

 

[AlexCuse]

It should select any record where DateField is populated with today's date (Provided DateField is a date/time column)

Will this return anything?

where datefield = Date() AND

someotherfield is not null

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MarkNie]

Nope still not working, still giving me the empty record value.

Is there anyway that you can use the Max function in an Update query?

Thanks for you help!!

Mark

 

[lespaul]

Do you have any records in your table that have today's date?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MarkNie]

Yip I do..

Still nothing coming up.

Mark

 

[lespaul]

If the point is to always have the most recent information, why don't you just add a timestamp field to your table so when you get the new information you just have to find the ones with the most recent time instead of having to do awkward updates?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MarkNie]

Hi Lespaul

The problem I have is that I have to keep the records of a whole month. So that is why I need to get it into a table but I also don't want there to be duplicate dates I just want the most recent data copied/updated.

I got it right to append the latest update for the day by inserting the Max expression in the date field, but when I change it to an update query the option for the Max expression falls away. Do you know if it is possible to insert code into the SQL to get this right.

I have thought of maybe creating a delete query with the Max expression so that I can run that query and then the append query which will get ride of the duplicate date. But when I change the append query to a delete query I also loose the option for deleting the Max Date or even Date() or Now().

Thanks
Regards
Mark

 

[lespaul]

why don't you show us the SQL you are trying to run and what the table looks like (example data).

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MarkNie]

Hi lespaul

Here is the code, I haven't inserted the Max option as I can't find one in the update query.

Code:
UPDATE ytblPerformanceSummary SET ytblPerformanceSummary.[PERFORMANCE Summary of Deadlines] = [ytblMISDailyPerformance]![PERFORMANCE Summary of Deadlines], ytblPerformanceSummary.[Performance Cumulative Target] = [ytblMISDailyPerformance]![Performance Cumulative Target], ytblPerformanceSummary.[Performance Number Deadlines per Day] = [ytblMISDailyPerformance]![Performance Number Deadlines per Day], ytblPerformanceSummary.[Performance Number Deadlines Signed Off] = [ytblMISDailyPerformance]![Performance Number Deadlines Signed Off], ytblPerformanceSummary.[Performance Deadlines met] = [ytblMISDailyPerformance]![Performance Deadlines met], ytblPerformanceSummary.[Performance Deadlines missed] = [ytblMISDailyPerformance]![Performance Deadlines missed], ytblPerformanceSummary.[Performance Deadlines Met Percentage] = [ytblMISDailyPerformance]![Performance Deadlines Met Percentage]
WITH OWNERACCESS OPTION;

The table looks like this:
Record 1
Fields Data
PERFORMANCE Summary of Deadlines 29/09/2006 13:30:12
Performance Cumulative Target 395
Performance Number Deadlines per Day 0
Performance Number Deadlines Signed Off 394
Performance Deadlines met 203
Performance Deadlines missed 192
Performance Deadlines Met Percentage 0

Record 2
Fields Data
PERFORMANCE Summary of Deadlines 29/09/2006 14:35:12
Performance Cumulative Target 395
Performance Number Deadlines per Day 0
Performance Number Deadlines Signed Off 395
Performance Deadlines met 204
Performance Deadlines missed 192
Performance Deadlines Met Percentage 0

Thanks
Mark

 

[lespaul]

and you want to replace record 1 with record 2?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MarkNie]

Thats right.

But I have to get the max date from the table to replace record number 2.

 

[AlexCuse]

try

where left(datefield, len(datefield) - 9) = format(Date(), "dd/mm/yyyy")

Your date field is not in the standard access date format.

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[MarkNie]

Sorry will the code be like this

where left(PERFORMANCE Summary of Deadlines, len(PERFORMANCE Summary of Deadlines) - 9) = format(Date(), "dd/mm/yyyy")

or is it exactly like you wrote..

 

[AlexCuse]

It will be as you wrote it, only with brackets around the field names. In the future it would be a good idea to not have spaces in your field names, as this necessitates brackets in all queries.

Hope this helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[PHV]

You want only the latest stats for each day ?
DELETE FROM ytblPerformanceSummary
WHERE [PERFORMANCE Summary of Deadlines] Not In (SELECT Max([PERFORMANCE Summary of Deadlines]) FROM ytblPerformanceSummary GROUP BY CLng(ytblPerformanceSummary));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkNie]

Hi PHV

Thanks for that it is working fine the only thing that I am getting ia a text box asking me to enter a value, so if I enter 1 then it seems to be working fine, is there maybe something wrong with my code.

Code:

DELETE ytblPerformanceSummary.[PERFORMANCE Summary of Deadlines]
FROM ytblPerformanceSummary
WHERE (((ytblPerformanceSummary.[PERFORMANCE Summary of Deadlines]) Not In (SELECT Max([PERFORMANCE Summary of Deadlines]) FROM ytblPerformanceSummary
GROUP BY CLng(ytblPerformanceSummary))))
WITH OWNERACCESS OPTION;

Thanks for your help...
Regards
Mark

 

[PHV]

Sorry for the typo:
DELETE FROM ytblPerformanceSummary
WHERE [PERFORMANCE Summary of Deadlines] Not In (SELECT Max([PERFORMANCE Summary of Deadlines]) FROM ytblPerformanceSummary GROUP BY CLng([!][PERFORMANCE Summary of Deadlines][/!]));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkNie]

Hi PHV

Sorry I should have seen that myself anyway, but it works so thanks alot this has taken a while to get right but hey I guess you do it step by step hey.

Just a follow up question, is there anyway that you could do this according to todays date? As the code above deletes all date except the largest right. I know this is a long shot..

Thanks alot for all your help much appreciated !!!!
Cheers
Mark