Russ-
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
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
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (20) |
Tidak ada komentar:
Posting Komentar