Convert 5 Update queries into one?

[sdel_nevo]

Hi everyone



I have 5 Update queies that i would like to combine into one if its possible

the five queries i have are these

DueCheckQuery
UPDATE UNEXData SET UNEXData.OVERDUE = "Not Over Due"
WHERE (((UNEXData.duedate)>=Date()));

Due Today
UPDATE UNEXData SET UNEXData.OVERDUE = "Due Today"
WHERE (((UNEXData.DUEDATE)=Date()));
DueNextWeekQuery
UPDATE UNEXData SET UNEXData.OVERDUE = "Due Next Week"
WHERE (((Year([DUEDATE])*53+DatePart("ww",[DUEDATE]))=Year(Date())*53+DatePart("ww",Date())+1));

OverDueQuery
UPDATE UNEXData SET UNEXData.OVERDUE = "OVER DUE"
WHERE (((UNEXData.duedate)<=Date()));

ThisWeekQuery
UPDATE UNEXData SET UNEXData.OVERDUE = "Due This Week"
WHERE (((Year([DUEDATE])*53+DatePart("ww",[DUEDATE]))=Year(Date())*53+DatePart("ww",Date())));This basically changes the text in a feild called "OVERDUE" based on the date of the feild "DUEDATE"

Is it possible to combine all these into a single query? i was thinking about using IIF, but im not sure how to do this

These querys run when my main form opens, if i am able to combine these into a single query am i correct in thinking that their will be a performance gain?

Kind regards

Steve

[JoeM]

These querys run when my main form opens, if i am able to combine these into a single query am i correct in thinking that their will be a performance gain?

I am not so sure about that, especially since they all have different criteria. You could loop through the whole recordset and check each one, but I doubt that would be more efficient.

You could put all 5 Update Queries into a single Macro or Procedure, and run it all at once from there.

[sdel_nevo]

Hi JoeM

Many thanks for the reply
i will try the procedure route and see how i get on.

many thanks

Steve

[orange]

Just curious WHY these are update queries. Seems they could be Select queries if you wanted to know Due/overdue status based on Dates.
But we really don't know much about your business or application.

[sdel_nevo]

Hi Orange

the feild "OverDue" is updated based on the "DueDate" feild, each time the main form opens, these querys run and the overdue feild is updated.
the duedate is the customers duedue date which never changes.

not sure if a select would do, as the textfeild "OverDue" needs to be updated

Kind regards

Steve

[ssanfu]

I have to agree with Orange. I would use select queries since the field "OverDue" is a calculated field.

not sure if a select would do, as the textfeild "OverDue" needs to be updated

Why does the textfeild "OverDue" need to be updated if a select query will provide the same data?

[sdel_nevo]

Hi ssanfu

I'm not sure i understand how a select query would help me as i needed to update the text in the feild "overdue" to either "Due Today", "Due Next Week", Due This Week", "Order Late" perhaps i am not understanding

what i have done that has seemed to help alot is to make sure these querys only run once per day, when the form opens the following code runs this has certainly improved the performace of the app


'Find the last date this query ran, only runs once a day
'if the date has changed run the following queries

'Lookup the last date the querys ran

Dim LastRunUpdateQuery As String
LastRunUpdateQuery = DMax("UnexQueryDate", "UnexDueDateQueryLastRun")

'the the date the querys ran is less that todays date, run them

If LastRunUpdateQuery < Date Then
MsgBox "The Systems Due Date Check Queries Have Been Run, This Only Happens The First Time This Form Opens"
DoCmd.SetWarnings False

DoCmd.OpenQuery "UNEXOverDueQuery", acViewNormal
DoCmd.OpenQuery "UNEXThisWeekQuery", acViewNormal
DoCmd.OpenQuery "UNEXNextWeekQuery", acViewNormal
DoCmd.OpenQuery "UNEXDueTodayQuery", acViewNormal


'Then Closes The Queries Before The Form Closes

DoCmd.Close acQuery, "UNEXOverDueQuery"
DoCmd.Close acQuery, "UNEXthisWeekQuery"
DoCmd.Close acQuery, "UNEXNextWeekQuery"
DoCmd.Close acQuery, "UNEXDueTodayQuery"


'then insert the date into the UnexDueQueryDateLastRun table

DoCmd.RunSQL "INSERT INTO UnexDueDateQueryLastRun ([UnexQueryDate])" & "VALUES(date())"
DoCmd.GoToControl "search"
DoCmd.SetWarnings True

Else

'if the date has not changed go to the search feild
'MsgBox "System Has Already Been Updated"
DoCmd.GoToControl "search"

End If

Kind Regards

Steve

[orange]

sdel_nevo,

The point Steve and I are making is that there is no need to update a field to identify OverDue etc.
You can determine, via logic, whether or not something is Due and when without ever updating a field.
It seems the update is using a calculated field, and we try to avoid calculated fields.

[JoeM]

To follow up on what Steve and Orange are saying, the general rule of thumb in Access is NOT to store anything that can be easily calculated "on-the-fly". Storing calculations like that violates the rules of Data Normalization and can undermine the data integrity and the dynamic nature of a database.

See this for the Rules of Normalization: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[sdel_nevo]

Hi Orange

Many thanks for getting back, now i understand and will adapt my design accordingly.

Hi JoeM
many thanks for the link i will study that, i bet i have lots to change now

many many thanks for the time you have taken helping me

Kind regards

Steve

[JoeM]

many thanks for the link i will study that, i bet i have lots to change now

My first "real database" I created many years ago, I had the same issue, and had to re-design the whole thing to normalize it. It was a pain-in-the-neck, but it was so much better and easier to work with when I finished. If it isn't designed properly, it is easy to program yourself into a corner and seemingly easy tasks can become difficult to perform.

[sdel_nevo]

Hi JoeM

im loving access much more than i thought i would, but i think a redesign will be on the cards, i will study the PDF link and start the changes required.
just when i think i have something nailed i turns out i took the wrong approach

i love a learning curve i just hope it doesn't get to steep

many thanks for your help
Kind regards
Steve