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
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
Posted by: Russ <dyspoz2@cox.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (17) |
Tidak ada komentar:
Posting Komentar