Update query help

[guinness]

I have a resourcing database that lists the activities of a team of trainers and allows team managers to schedule work to the team. I have one member of the team however who does not work a Friday. I don’t want to have to schedule RDO (Rostered Day Off) every week so would like to run an update query that updates her activity every Friday to RDO.

I have a temporary query (Query1) that has the date of every Friday between now and January 2020
For every date on Query1 I would like to append a record on a table, tblResourcing that updates the following fields:

Start_Date – the date from Query1
End_Date – Same as start date
Duration = “All Day”
Trainer_Name = “Trainer Name”
Team = “Operational North”
Main_Activity = “RDO”
Activity = “RDO:”

I used to know how to do this but can’t remember for the life of me. Can anyone help?

 

[almahmood]

Try this:

Activity = IIF(Format([Start_Date],"ddd")="Fri","RDO","YourSecondChoce")

 

[Minty]

You'll need an insert into query https://msdn.microsoft.com/en-us/vb...-using-access-sql?f=255&MSPPError=-2147217396

 

[arnelgp]

use Insert Query against tblResoursing table:

Insert Into tblResourcing(Start_Date, End_Date,
Duration,Trainer_Name,Team,Main_Activity,
Activity) SELECT Query1.[Date],Query1.[Date],
"All Day","Trainer Name","Operational North",
"RDO","RDO" FROM Query1;

***
REPLACE [DATE] with the date field in Query1
REPLACE "Trainer Name" with the correct trainer name (enclosed in quote)

 

[guinness]

Try this:

Activity = IIF(Format([Start_Date],"ddd")="Fri","RDO","YourSecondChoce")

Thanks Almahood. I only want to add the record for one member of staff though. Your method would change every Friday to show RDO for everyone....I think

 

[guinness]

use Insert Query against tblResoursing table:

Insert Into tblResourcing(Start_Date, End_Date,
Duration,Trainer_Name,Team,Main_Activity,
Activity) SELECT Query1.[Date],Query1.[Date],
"All Day","Trainer Name","Operational North",
"RDO","RDO" FROM Query1;

***
REPLACE [DATE] with the date field in Query1
REPLACE "Trainer Name" with the correct trainer name (enclosed in quote)

I'm going to try this just now. I take it an 'insert' query is just an append query?

 

[Minty]

Cross posted here : http://www.utteraccess.com/forum/index.php?showtopic=2045744&hl=

Please have a read here https://www.excelguru.ca/content.php?184

 

[guinness]

use Insert Query against tblResoursing table:

Insert Into tblResourcing(Start_Date, End_Date,
Duration,Trainer_Name,Team,Main_Activity,
Activity) SELECT Query1.[Date],Query1.[Date],
"All Day","Trainer Name","Operational North",
"RDO","RDO" FROM Query1;

***
REPLACE [DATE] with the date field in Query1
REPLACE "Trainer Name" with the correct trainer name (enclosed in quote)

Thank you. That has worked

 

[guinness]

Now I just need to reverse engineer the code so I can understand it in future. Thanks everyone

 

[arnelgp]

That means if somebody announced early that he has double posts he is spared.
And will not point you to:

Please have a read here https://www.excelguru.ca/content.php?184

I it is more worst than the ignorance of others.

 

[almahmood]

Thanks Almahood. I only want to add the record for one member of staff though. Your method would change every Friday to show RDO for everyone....I think

Use another IIF() to specify a staff like this

Activity = IIF([Staff]="YourSpecificStaff",IIF(Format([Start_Date],"ddd")="Fri","RDO","YourSecondChoce"),"YourSecondChoce")