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:
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: