automatic answer based on other field/column entries?

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Is there an expression/datatype/criteria in table or query
to automatically use "yes/no" format based on the date in
a range of identified columns?

Ex.
Name (text)
Attended all monthly meetings? (yes/no)
Jan 04 (date)
Feb 04 (date)
March 04 (date)
....
Nov 04 (date)
Dec 04 (date)
If Jan 04 through May 04 columns all have dates entered,
can the question automatically be answered yes?


Please help, I'm new to access and can't find this in any
books!!
 
Hi Michelle

First, it is probably best to have this kind of information in a query, not
a table. It is good database practice not to store information in a table if
it can be inferred directly from other data in a table (which it can here).

Second, I am worried that you might not have set up your table of dates and
meetings in the best possible way. When you talk about Jan 04 to May 04
columns, does that mean you have a separate column for each meeting? That
isn't the best way to do it. It is better if you have a table (probably
separate from the table you use for names) which simply lists persons and
dates of meetings in this format:

Person MeetingDate
1 4 Jan 04
1 6 Feb 04
1 3 April 04
2 4 Jan 04
2 6 Feb 04
2 4 March 04

The number in the 'person' column is an ID number that links to the primary
key (assuming you have an ID number as your primary key) in your persons
table. If you create a table of persons first, you can add an autonumber
field as a primary key, and then use the lookup wizard while creating your
table of dates to link to that field.

You can then create a query that can figure out how many meetings each
person has been to. It's probably easiest if you count the number of
meetings for each person in a specified date range by using the aggregate
functions in a query to generate a count. You can then use an IIf function
to work out if the person has been to enough meetings.

I realise that this probably all sounds very scary if you are new to Access.
Access does take some getting used to, but once you have learned how to do
things, everything is much easier. Most of the things I have described are
reasonably well described in the Access help files (you should certainly
spend some time reading about queries). Do feel free to post back here if
anything is still confusing you.

Good luck!

Adam
 
Michelle,
No, there isn't a way to do that in the table. You could create a query that
would check to see if the value of all the date fields is not null and then
set the value of attended to yes/no.

However, having said that, let's go to square 1. It looks to me like you are
thinking of your database table as a spreadsheet. When you use the names of
the Month and Year for your column names, you are essentially attempting to
store data in the column name and that's a big no no. Remember that you are
working with a database. You should have a structure like this.

tblMeetingAttendance
MeetingAttendanceID (PK)
AttendeeName
AttendanceDate
.....Other fields you might need to store information in.

Then you would be able to create a query/report that would let you know if
someone attended all meetings. You might also be able to use AttendeeName
and AttendanceDate as a composite Primary Key.

SELECT AttendeeName, Count(AttendanceDate) AS CountOfDates,
IIF(Count(AttendanceDate) = 12, "Yes","No")
From YourTable
Group By AttendeeName;
 
Back
Top