Setting a Yes/No based on Null data in multiple fields (1 Viewer)

mcinta19

New member
Local time
Today, 11:08
Joined
Jan 16, 2018
Messages
7
I am new to Access and I have to create a database that tracks the progress of incoming members for an organization. One of the things I need to keep track of is whether or not they have completed all 4 of the mandatory orientation sessions.

I have a table Tbl_Orientation that contains a date field for each orientation (Orient1 through 4).
I would like to create a query that sets a yes/no field as true if each of the 4 orientation date fields have an entry and false if any one of them is null.

Does anyone know how I would go about doing this?
 

MarkK

bit cruncher
Local time
Today, 08:08
Joined
Mar 17, 2004
Messages
8,178
If you have four "sibling" things, like your orientations and their dates, it is a table design flaw to store them all in the same row. If this data was in separate rows it would be much easier to run summaries like Avg() and Count(). Use search terms "database normalization" for lots more info, and save yourself a ton of time.
hth
Mark
 

isladogs

MVP / VIP
Local time
Today, 15:08
Joined
Jan 14, 2017
Messages
18,186
First of all your table structure isn't normalised
You should have at least 2 tables:

1. tblMembers - MembersID (PK), FirstName, Lastname etc ... and Completed(Yes/No)
2. tblOrientation - OrientID (PK), MembersID, OrientNo, OrientDate

A new record is added to the 2nd table each time a member completes an orientation - up to 4 records per member

Link the 2 tables using a 1 to many relationship
Create a query to count the number of Orients done - call it qryCountOrient

Code:
SELECT tblOrientation.MembersID, Count(tblOrientation.OrientNo) AS CountOfOrientNo
FROM tblOrientation
WHERE (((tblOrientation.OrientDate) Is Not Null))
GROUP BY tblOrientation.MembersID;

NOTE: the WHERE line isn't needed if you are sure a date will always be entered

Now create an update query based on that
Code:
UPDATE DISTINCTROW tblMembers INNER JOIN qryCountOrient ON tblMembers.MemberID = qryCountOrient.MembersID SET tblMembers.Completed = True
WHERE (((qryCountOrient.CountOfOrientNo)=4));

That's it!

The Completed field is false by default dso nothing needs to be done if count<4

See example database attached
 

Attachments

  • Mcinta19.accdb
    448 KB · Views: 47

Mark_

Longboard on the internet
Local time
Today, 08:08
Joined
Sep 12, 2017
Messages
2,111
More to the point regarding what the others have posted...

If you can calculate the value from existing data, why have a "Yes/No" for it? Simply calculate it when needed and save yourself some problems.

For example, what happens if a user fills in orientation dates on the wrong person? You code will set the Yes/No to YES for that person. The underlying data gets fixed, so now you either have to manually go back in and correct the data OR you have to put in as much code (or more) than you would to count up the orientations to fix your data.

Unless there is a pressing need for a value like this to be saved your best served by calculating these results when you need them.
 

Users who are viewing this thread

Top Bottom