The reason I decided to meet you all (1 Viewer)

[Scribtor]

Got a problem here!





Table Events containing
( E_Name,Start_Date,End_Date)


Table Participants containing
(Event_Name,Participant_Name,Arrival_Date,Quitting_Date)


E_Name is a unique index for its table
(Event_Name&Participant_Name) is a unique index for their table


Start_Date and End_Date can only be one for one E_Name


My problem is the following:

I need to have a query running update in the following manner

UPDATE Customers
SET DBZ=DBZ+(round(X/30));


Clarification:
DBZ= Number column in the Customers table
(Not all Customers are Participants, but all Participants are Customers)

X=DateDiff("n",date2,date1)




date2 should compare a Participants' quitting_date to events' end_date and select the smaller of the two dates
date1 should compare a Participants' arrival_date to events' start_date and select the bigger of the two dates



Plain English (compare, maybe I missed something):
Participants come and go as they please
Events start and end at set dates
I need to measure how long everyone stayed, AND
1. If you're late, your timer starts on arrival_date
2. If you bail early, your timer ends on quitting_date
3.Maximum measured is end_date-start_date

The result that meets all conditions should be returned as a number
(preferably a number representing the minutes of date calculation)


So far, I got as far as:

:banghead:

 

[Guus2005]

a few observations
1 - Apparently you have posted this question somewhere else? Given the odd font. It is what a simple copy and paste does.
2 - You want to update a table with process data. You dont store process data.
3 - i don't know what you want and it seems to me that you haven't tried anything.
4 - provide a sample database and a question, don't expect a complete solution. The further you get in solving this, the more help you can expect.

HTH

 

[The_Doc_Man]

FYI: As Guus2005 notes, we are sensitive to what is called "cross-posting." If you have done that, we should know about it so we don't waste time if we determine that you already have an answer. However, such cross-posting is allowed with proper disclosure.

OK. having gone through the obligatory chastisement with a wet noodle:

Part of your solution MIGHT include building a query using IIF functions to compare the two candidate starting dates and the two candidate ending dates. The IIF functions could then choose which one to use for each case. So look up IIF Function to see how it is used. Here's a link:

https://support.office.com/en-us/article/iif-function-32436ecf-c629-48a3-9900-647539c764e3

you would use the IIF in a query by using the "AS field-name" syntax (because otherwise the field would be called EXPRn where n depends on how many other expressions are to the left of that field in the same query). Then your query could refer to its own data with an expression that gives you the difference using "real-start - real-end AS stay-time" - or you could make one query to just do the IIFs and build a layered query on top of that one to compute and reformat the differences.

A further note: Since differences are zero-based, if a person arrives and leaves on the same day, you might well see a zero. If you count same-day arrival and departure as ONE day then you need to adjust your formula to add 1 to the differences.

 

[Scribtor]

Thanks for observing that I copy-pasted this problem from somewhere else, when in truth, I simply like how everyone nearly forgot about Palatino font.

No, I wrote this post with my 10 fingers.
Yes, I posted a similar problem to StackOverflow, nearly a week ago, but no solution so far.
The reason why I didn't post any of my attempts is simple: there were too many to count, and I literally lost my compass, so up is left, and right is down, down is up and up is right.


Now, in all honesty, for the past few days, I DID get some progress so this can be shortened a bit now:

I figured out how to get the DateDiff I need, with a select statement, but now, I'm stuck on converting the DateDiff values I produced in a select statement to act as a part of update query.
(I could be missing something too easy and obvious, go easy on me)

Query that does this is attached (One might appreciate being able to see line spacing to better understand code)


The main objective, by any means necessary, is to get the result as a number, so I might take the integer part of its' division by 30 as my final result of points by which each and every Customer that Participated in an Event designated by the form control will be awarded.
All the Participants that my query relates minutes_stayed to come from the Customers table so there ought to be a filter for update, so only those that participated will receive the update.
Preffered option is to have it built so the end-user that will enter data and monitor everything only has to click a simple button

 

[Cronk]

Instead of using sub queries, why not join the two tables in the query?


Incidentally DateDiff("n", date1, date2) gives the number of minutes between two date/times. Use "d" if you want days.

 

[Scribtor]

The minute interval is a requirement and i fixed that part already...

UPDATE Customer
INNER JOIN tmp
ON Customer.Customer_name= tmp.Participant
SET DBZ=DBZ+tmp.Pts


why does this produce an error about non updateable queries in access???
tmp is a name for saved query that produced datediff and related Participants (all correct and error-free)
also, how to stick the calculated Pts that are the result of DateDiff to DBZ in the update?

:banghead::banghead::banghead::banghead::banghead:

 

[CJ_London]

why does this produce an error about non updateable queries in access???

probably something in your tmp query - see these links for more info

http://allenbrowne.com/ser-61.html
http://rogersaccessblog.blogspot.com/2009/11/this-recordset-is-not-updateable-why.html

note that this applies to nested/stacked queries as well

 

[Scribtor]

SELECT DateDiff
("n",
IIF([Arrival Date]<=(SELECT Start_Date FROM Events WHERE Descriptive_Name='First_recording'),(SELECT Start_Date FROM Events WHERE Descriptive_Name='First_recording'),[Arrival Date]),
IIF([Quitting Date]>=(SELECT End_Date FROM Events WHERE Descriptive_Name='First_recording'),(SELECT End_Date FROM Events WHERE Descriptive_Name='First_recording'),[Quitting Date])) AS pts,Participant
FROM Event_Participants;


this is tmp




DateDiff returns an integer, right?


This for the named event 'First_recording' which is ofc dummy data returns 260 for 3 of the Participants, and 215 for the fourth...when not restricted by the Event Start_Time, they return 300,271,264 and 215

The idea:
1. People who come early and stay after, don't earn anything by THIS query (but by others that are separate from this)
2. People who come late OR leave early should be penalized
3. Minimum time measured is 1min, and maximum is always
DateDiff("n",Events.Start_Time,Events.End_Time)
4. The "n" is a requirement, because once I get my integer, it needs to be divided by 30, and then the integer portion of it extracted from, and finally multiplied by 3
(The request is that Participants earn 3pts every 30min of activity, and that minutes measure include real presence)




Database is, luckily, design phase and not deployed yet, so I'm open to other solutions for this

 

[The_Doc_Man]

A query becomes non-updateable when there is a function or other non-trivial element in a field. This is so because if you now tried to store a record using the SELECT query, there is no place to put any data for that computed field. I.e. no place to do the write-back.

 

[Scribtor]

How do I get to do this then??

 

[isladogs]

Try UPDATE DISTINCTROW ....

 

[CJ_London]

Database is, luckily, design phase and not deployed yet, so I'm open to other solutions for this

a database design principle is (with a very few exceptions) to not store calculated values. Reason being that calculated values can change. I see nothing that makes this an exception so just calculate in a query as and when required

 

[Scribtor]

Try UPDATE DISTINCTROW ....

How would that change anything, and how would I put that together in syntax?

a database design principle is (with a very few exceptions) to not store calculated values. Reason being that calculated values can change. I see nothing that makes this an exception so just calculate in a query as and when required

I meant, suggestions for other work-arounds for that minute interval, instead of in a query?

This system will contain multitude of Events, and up to 100 different participants for each event, where the end-code or end-sql that calculates what the above stated DateDiff does executes at the click of a button on a form

 

[CJ_London]

I meant, suggestions for other work-arounds for that minute interval, instead of in a query?

any basis will require the same basic calculation and a query will be the fastest way of doing it. but if you are struggling to get an update query to be updateable, providing you have a primary key in the table you want to update, change your query to a make table query to make a temporary table (include the PK and the value you want to update) then another update query based on the temp table.

However still say you should calculate on the fly

Alternatives for the minute interval is to use a udf with the parameters for the datediff function, do your datediff in the udf and update it with the returned calculated value - however still basically the same calculation and unlikely to work since your query will be basically the same so still won't be updateable

other option is to run a sub to step through a recordset and run multiple update queries there - but likely to be quite slow compared with a query although your volumes are small so perhaps won't be noticeable.

principle would be

open your destination table recordset
stepping through each record, execute another recordset to get the values from your tmp query
update the record with this value
move to next record

As to whether your tmp query can be improved, probably, but without knowing your table structure I wouldn't like to suggest a solution other than you investigate cartesian queries - but it still won't make your query updateable

good luck with your project

 

[isladogs]

How would that change anything, and how would I put that together in syntax?

UPDATE DISTINCTROW is the SQL equivalent for Unique Records =yes.
It might not change anything but it's worth a try

UPDATE DISTINCTROW Customer
INNER JOIN tmp
ON Customer.Customer_name= tmp.Participant
SET DBZ=DBZ+tmp.Pts

It obviously won't help if your tmp query needs altering

 

[Scribtor]

Project update:

This is the VBA I came up with after nearly two days of
:banghead:

Dim rs As DAO.Recordset
Dim qry, points, pp As String
Dim pts As Integer

pp = Me.Form.cbo_query_picker.Value
Set rs = CurrentDb.OpenRecordset("SELECT DateDiff('n',IIf([Arrival Date]<=(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Arrival Date]),IIf([Quitting Date]>=(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Quitting Date])) AS pts, Event_Participants.Participant, Event_Participants.[Event Name] FROM Event_Participants")
rs.MoveFirst
Do Until rs.EOF = True
    pts = Int(rs("pts").Value / 30) * 3
    qry = "update players set dkp = dkp + "
    qry = qry & Format(pts, "0")
    qry = qry & " where [player name] = '"
    qry = qry & rs("Participant") & "'"
    CurrentDb.Execute (qry)
    Debug.Print qry
    rs.MoveNext
Loop
rs.Close

I tried using to use the form control address directly, but the compiler kept blowing up in my face

This resolves the issue for now, but I would like to learn a few things from this:


As I'm pretty sure this code can be better, how and where am I to improve upon it?
Although it satisfied the needs of this project, I know this can be better written.

 

[Scribtor]

Dim rs As DAO.Recordset
Dim qry, points, pp As String
Dim pts As Integer

pp = Me.Form.cbo_query_picker.Value
Set rs = CurrentDb.OpenRecordset("SELECT DateDiff('n',IIf([Arrival Date]<=(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Arrival Date]),IIf([Quitting Date]>=(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Quitting Date])) AS pts, Event_Participants.Participant, Event_Participants.[Event Name] FROM Event_Participants")
rs.MoveFirst
Do Until rs.EOF = True
pts = Int(rs("pts").Value / 30) * 3
qry = "update customers set points = points + "
qry = qry & Format(pts, "0")
qry = qry & " where [Customer name] = '"
qry = qry & rs("Participant") & "'"
CurrentDb.Execute (qry)
Debug.Print qry
rs.MoveNext
Loop
rs.Close

This is the code I used to eventually scramble what I wanted from access to do, but here's my final question, when the project is complete and deployed, working properly:

Is there any way to improve upon this code, in any way? There must be a way so I don't have to assign 4 times what qry is, among other things

 

[isladogs]

Post 16 was moderated. Posting here to trigger email notifications