Query to update null records (1 Viewer)

Kayleigh

Member
Local time
Today, 16:17
Joined
Sep 24, 2020
Messages
706
Hi,
I have three tables - tblStaff (staff info), tblStaffPosition (list of staff positions) and jtblStaffRoles (assigns each staff member 1+ positions). Currently several staff members have been assigned position/s.
How do I write a query to insert records in jtblStaffRoles to add 'unassigned' to all staff members not currently assigned a role?
Really appreciate your help!
 

Attachments

  • StaffRolesTest.zip
    21 KB · Views: 112

theDBguy

I’m here to help
Staff member
Local time
Today, 09:17
Joined
Oct 29, 2018
Messages
21,358
How do I write a query to insert records in jtblStaffRoles to add 'unassigned' to all staff members not currently assigned a role?
You could try starting out with using the Find Unmatched Query Wizard to list all the members without roles. Then perhaps you could use the resulting query as a base for your APPEND query.
 

plog

Banishment Pending
Local time
Today, 11:17
Joined
May 11, 2011
Messages
11,611
What's assigning "Unassigned" going to accomplish? I really don't think you need to do this. If you truly need to know who is unassigned, just run a query. That query would be this SQL:

Code:
SELECT tblStaff.fldStaffID
FROM jtblStaffRoles RIGHT JOIN tblStaff ON jtblStaffRoles.fldStaffID = tblStaff.fldStaffID
WHERE (((jtblStaffRoles.fldStaffID) Is Null));

However, you can also use the above query to accomplish what you want. Name the above query "Unassigned", then to add records to jtblSTaffRoles you would use this query:

Code:
INSERT INTO jtblStaffRoles ( fldStaffID, fldStaffPositionID )
SELECT Unassigned.fldStaffID, 24 AS PositionID
FROM Unassigned;
 

Kayleigh

Member
Local time
Today, 16:17
Joined
Sep 24, 2020
Messages
706
Reason why I had unassigned is for a form which will assign roles based on list from data source tblStaffPosition. I would like to be able to find any staff members which are not yet assigned - I guess I can program a query to do this as you said.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:17
Joined
Jul 9, 2003
Messages
16,244
In the attached sample file is a database with a new Form. the Form displays the staff details and the roles that they are assigned to. The roles are displayed in a sub-form. If you go to a new record on the main Form, enter a new surname, then it will automatically add "unassigned" and unassigned will appear in the record in the subform.

I used the "insert into" code from my website here:-

INSERT INTO SQL Statement
 

Attachments

  • StaffRolesTest.zip
    320.1 KB · Views: 93
Last edited:

Kayleigh

Member
Local time
Today, 16:17
Joined
Sep 24, 2020
Messages
706
Having serious trouble with implementing solution into my DB. Basically I am not able to refer to subform of subform - have copied structure in file enclosed. (An ongoing issue I am having with my DB:()

NB. Just realised that this is query forum - should have put in forms...
 

Attachments

  • StaffRolesTest_4.accdb
    716 KB · Views: 91

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:17
Joined
Feb 19, 2002
Messages
42,970
Reason why I had unassigned is for a form which will assign roles based on list from data source tblStaffPosition. I would like to be able to find any staff members which are not yet assigned - I guess I can program a query to do this as you said.
The real reason for not having an "unassigned" value is that it is mutually exclusive with the other position values. The business rule is that a person can have 0, 1, or many positions. However, when you throw "unassigned" into the mix, the rule no longer works and you have to manage it programmatically. Don't do it. You can easily find the unassigned people with a query. Don't violate normal forms for something like this.
 

Users who are viewing this thread

Top Bottom