John,
If you cannot do what I've asked I understand. I do appreciate this Access Professionals site, but this is becoming tiring for me. I've asked for some things to work around this problem but what I've received is suggestions to my database which I do not think are necessary. Why my proposed fix cannot be addressed is puzzling to me even though I've expressed to you many times what I would like. Instead, my proposed fix is ignored as improper along with any other suggestion or simplification I've submitted. While you may be able to provide a solution that would be to my liking, I am puzzled as to why you consistently ignore any of my input and insist upon more and more irrelevant data. Thank you for any thought you have provided, but as I have been repeatedly treated as irrelevant I will have to drop this request from this group.
Russ
Russ-
I need the full query and a description of all the tables.
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 21, 2014, at 1:10 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Ok. Assuming your solution is used, how would a query choose/not choose between two date fields based upon the last time served?
Here is a simplified query SQL where I have eliminated all of the extra checks it is doing, such as:
None of those items are relevant to this problem.
- Is this person already assigned with something else this week
- Is this person already assigned for a monthly duty and therefore not available
- Is this person available in the morning
- Does this person have a schedule conflict for this week
- Whether they have volunteered for this duty or not
- Whether they are an active worker or not
INSERT INTO tblRosterWk1 ( RoleName, AssNameID2, RoleID )
SELECT TOP 1 tblRoleList.RoleName, Names.NameID, tblRoleList.RoleID
FROM [Names] INNER JOIN (tblRoleList INNER JOIN tblAssignedRoles ON tblRoleList.RoleID = tblAssignedRoles.AssRoleID) ON Names.NameID = tblAssignedRoles.AssNameID
WHERE (((tblRoleList.RoleID)=17))
ORDER BY tblAssignedRoles.AssDate, Names.NameID;
Russ
On 6/21/2014 9:06 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
Wrong. Note that the "last" date is the greatest date they've ever served for both morning and evening. Once a morning-only person got picked, he or she wouldn't get picked again until everyone else had served on a later date.
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 21, 2014, at 8:58 AM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Just quickly thinking about that it seems it will not work out. If a person, due to their schedule, cannot ever do an evening duty (and I have a couple of them) then their dates would always be the oldest and would always get chosen by the db to do the morning work.
Russ
On 6/21/2014 8:31 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
I completely understand your problem, so you don't need to keep repeating it. You are not understanding that the real solution is NOT to kludge the dates but to fix how you pick the "next" person. Let's say you have four people, and the previous assignments look like this:
Person Morning EveningMike June 1 June 1Mary June 2 June 2Dave June 3 June 3Ann June 4 June 4
You go to assign June 5 (Mike), but he can't do it in the evening, so you assign Mike and Mary:
Person Morning EveningMike June 5 June 1Mary June 2 June 5Dave June 3 June 3Ann June 4 June 4
Now you go to assign the next day, so you need the person who served the longest ago for *either* morning or evening. The last service dates look like this:
Mike: June 5Mary: June 5Dave: June 3Ann: June 4
So, you should pick Dave!
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 21, 2014, at 7:33 AM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Here is the scenario again. On a particular day my db picks someone to do the morning duty and I want that same person to do the evening duty on the same day. Works just fine until that same person is unavailable to do that duty so my db picks the next person in line for the evening duty. Now the db updates both the morning duty person and the evening duty person with the appropriate date. Come next time for morning duty and my db will pick the same person who did it last evening because their morning duty date was not updated, and that is not what I want and that is not what my assignees want or expect. Manipulating these dates to make them equal would solve this problem, but I do not have the skill to do that.
Russ
On 6/21/2014 6:07 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
My point exactly. When you need to assign a duty, you should pick the person who had that duty the longest ago *regardless* of whether it was morning or evening. Someone who did the duty the prior evening should be at the bottom of the list, even if they last time they performed the duty in the morning was two weeks ago.
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 9:31 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Nothing is "artificial". I don't want to assign a person the same duty in the AM because they just had that duty the prior evening. That is how it works right now in certain circumstances when another person cannot fulfill the evening duty. Under my present setup under some circumstances a person would have one duty in the evening and then the exact same duty would be assigned in the morning. They need a break, and the way it is now they do not get one.
Russ
On 6/20/2014 8:34 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
You should change your allocation algorithm to use the latest date for *any* duty. Don't screw up the actual duty dates with an artificial update.
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 5:42 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Did I screw it up that bad? Sorry.
In order to make this simple, I've eliminated nearly all of the tables, fields, queries etc that this db uses. The ones that should be applicable to this mod I've not deleted.
The purpose is when the db assigns a duty it changes the date that the duty was assigned so that each person gets assigned an even amount. The next assignments are made according to when they did that duty last, so those who did that particular duty the longest time ago will be chosen. Because the MorniingDuty and EveningDuty are similar, I never updated the EveningDuty's date but just chose the same person for MorningDuty and EveningDuty. That was ok until some people could not do the EveningDuty. When that happened my db did not give them "credit" for it and did not change their date served. Then the next time came around for MorningDuty they got chosen again because their date was the oldest. To remedy that I thought the easiest way was to even-out the Morning and Evening dates and give them 'credit' for the evening work. The only way to do that was to "even out" the dates from MorningDuty and EveningDuty at each assignment which I need to do at each assignment time.
Russ
On 6/20/2014 4:46 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
Well, you were trying to use something called EveDate that exists in NONE of your tables.
No, I didn't understand your problem at all. I had assumed that the two dates were in the same table and that you could simply look for RoleID 17 or 19. The problem as you have described it is much more complex. Do you find the matching 17 and 19 records based on AssNameID? When I look at RoleID 17 and 19 in the tblRoleList table, I see that these are called "MornDuty" and "EveDuty."
What is the purpose of this table, and why do you want to make sure the date on both records is the later of the two dates defined for 17 and 19?
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 2:45 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John
I'm not sure exactly where it went, I just pressed the Upload a File button. There is a notice in the conversations section about the file being uploaded. From that message there is this sentence:
You can access this file at the URL:
https://groups.yahoo.com/neo/groups/MS_Access_Professionals/files/Roster_be.accdb
Anyway, Duane Hookom sent me a personal message saying no one should have to look at a file I've uploaded. I understand the avoiding spam and viruses, so if you would prefer here is the very slimmed take on what I've uploaded.
3 tables
Names with one field NameID
tblAssignedRoles with the fields: AssNameID, AssRoleID, and AssDate.
tblRoleList with the fields of RoleID, RoleName
I would like to update the AssDate field value of the AssRoleID of 17 to the value of the AssRoleID of 19. In other words, if the AssDate of AssRoleID 17 is 1/3/2013 and the AssDate of AssRoleID 19 is 2/4/2014, I would like to update the date of AssRoleID17 of that person to 2/4/2014.
Sounds confusing, but I think you understood it earlier but just did not have all the data.
Russ
On 6/20/2014 2:11 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
Where did you put your file? I don't see it in 2_Assistance Needed.
How in tblRoleList related to tblAssignedRoles? You need both tables in the query.
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 12:53 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
No, that is in the tblRoleList table.
On 6/20/2014 12:32 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
Does tblAssignedRoles have an EveDuty field? Try using [tblAssignedRoles].[EveDuty] - it may need to be fully qualified.
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 11:37 AM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Using this I always get a "Enter Parameter Value" for EveDuty. I've tried various changes but it keeps popping up.
Russ
On 6/20/2014 10:17 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
Only one WHERE keyword:
UPDATE tblAssignedRoles
SET AssDate = EveDuty
WHERE Eveduty > MornDuty
AND RoleID IN (17, 19);
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 9:31 AM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
My SQL. Probably all screwed up.
UPDATE tblAssignedRoles
SET AssDate = EveDuty
WHERE Eveduty > MornDuty
WHERE RoleID IN (17, 19);
On 6/20/2014 9:17 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
Please post the SQL of your query.
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 8:59 AM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
It might be easy, but I can't get it to go as my query keeps getting error messages when I transition to design view. Can you give me an example please?
On 6/20/2014 8:40 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
Easy. Just add a WHERE clause:
WHERE RoleID IN (17, 19)
John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 20, 2014, at 8:38 AM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
It's a little more complicated than what I imagined. I've got certain RoleIDs to work with and I'm not sure how to incorporate that into the query. I've uploaded a very much trimmed down back end db (Roster be.accdb) that should have all the required fields. I've got to choose just those two fields IDs 17 and 19 out of all the others. The sample db already have the dates set to the same value, but upon operating the front end those values change sometimes. You'll have to change a couple of them manually to test it out.
Russ
On 6/19/2014 9:32 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Russ-
You need TWO update queries:
UPDATE MyTable
SET Morning = Evening
WHERE Evening > Morning;
UPDATE MyTable
SET Evening = Morning
WHERE Morning > Evening;
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Jun 19, 2014, at 8:05 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I would like to build an update query that updates one of two date
fields in a table. I want to update the date of the oldest date field
to the value of the most recent date field. For example, one field
named Morning has a date of 1/5/2000, and the other field named Evening
has a date of 2/5/2000. The new query should check these two fields,
and update whichever one has the oldest date to the value of the date in
the other field. In this particular example, I'll want to update the
Morning field to the value of 2/5/2000. And if the date situation is
reversed, update the other field.
How can I do that?
Russ
------------------------------------
Posted by: Russ <dyspoz2@cox.net>
------------------------------------
------------------------------------
Yahoo Groups Links
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7709 - Release Date: 06/19/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7712 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7712 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7712 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7712 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7712 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7715 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7715 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7715 - Release Date: 06/20/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7717 - Release Date: 06/21/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7717 - Release Date: 06/21/14
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3485 / Virus Database: 3955/7719 - Release Date: 06/21/14
Posted by: Russ <dyspoz2@cox.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (36) |
Tidak ada komentar:
Posting Komentar