Updating to a table in the same date!!

[MarkNie]

Hi All

Hope you are doing well!!!

I seem to be stuck and am not sure if this is in anyway possible..

I have a bunch of queries which I have combined into a update query which populates a table. This is for Stats and I do not want to have duplicate dates (this field is Date()) in the table, but want it to be possible to run the update query numeris times a day as records get signed off during the day.

I have tried to state in my data type in the table not to allow duplicate values in the date field but then I can't change the information that gets appended more than once.
Any help or suggestions will be greatly appreciated.

Here is the update code I am using:

INSERT INTO ytblPerformanceSummary ( [Performance Cumiltive Target], [Performance Number Deadlines per Day], [Performance Number Deadlines Signed Off], [Performance Deadlines met], [Performance Deadlines missed] )
SELECT Count(yqryNoDeadlinePerDay0.CountOfPortfolioCode) AS CountOfCountOfPortfolioCode, Count(yqryNoDeadlines1.CountOfPerfDate) AS CountOfCountOfPerfDate, Count(yqryNoDeadlinesSignedOff2.CountOfPerfDate) AS CountOfCountOfPerfDate1, Count(yqryNoDeadlineswMet3.CountOfPerfDate) AS CountOfCountOfPerfDate2, Count(yqryNoDeadlineswMissed4.CountOfPerfDate) AS CountOfCountOfPerfDate3
FROM ((((tblTracking LEFT JOIN yqryNoDeadlinePerDay0 ON tblTracking.PortfolioCode = yqryNoDeadlinePerDay0.PortfolioCode) LEFT JOIN yqryNoDeadlines1 ON tblTracking.PortfolioCode = yqryNoDeadlines1.PortfolioCode) LEFT JOIN yqryNoDeadlinesSignedOff2 ON tblTracking.PortfolioCode = yqryNoDeadlinesSignedOff2.PortfolioCode) LEFT JOIN yqryNoDeadlineswMet3 ON tblTracking.PortfolioCode = yqryNoDeadlineswMet3.PortfolioCode) LEFT JOIN yqryNoDeadlineswMissed4 ON tblTracking.PortfolioCode = yqryNoDeadlineswMissed4.PortfolioCode
WITH OWNERACCESS OPTION;

Thanks again for any help provided.
Kind Regards
Mark

 

[Golom]

Your query doesn't show any DATE field. I assume that the "Date" field to which you refer is being inserted as a result of a default value for some other field not shown in the INSERT query.

You might make the default for that field "Now()" rather than "Date()" which would allow multiple inserts during a day.

 

[MarkNie]

HI Golom

Yeah the date field is generated automatically in the table when the record is appended.

I changed the Date() to Now() which works, but what I am trying to do is not have the same date in twice, is there anyway of just updating the values in the fields with the append query. I know you can do it with the Update query but then I can't assign the "count" to the fields.

Thanks for you help!!

Kind Regards
Mark