Query dilemma

[Blackwidow]

Ok I have been asked to design a form for certificates for example Attendance, Effort, Attainment with check boxes for people to select depending on the certificates they are producing, that is done the problem I have is the queries I need to run to get it to work! And I'm so confused now between my append queries and update queries I'm probably gonna explain this all wrong!

I have two tables

tblcertificates holds Admission Number (Unique) and about 20 fields with different types of certificates which are all yes/no fields.

tblCertificateLog holds Admission Number (Number)(Duplicates ok as people will receive several over the time) Certificate Awarded and Certificate Date.


When they check the box "Attendance" against the people's names that are to get certificates I then want them to press a button and it will automatically fill in on a seperate table "Attendance Certificate" under Certificate Awarded and current date under Certificate Date and then clear the checkbox on tblcertificates ready for the next time they need to use it.

However to add to complication several people may be inputting different certificates and I only want it to uncheck the boxes of the "Attendance".

I know I have to use queries and probably an update and an append query but I'm not sure how... I feel like I have cotton wool in my head I know the answer I just cant find it right now!

[Mile-O]

Originally Posted by Blackwidow

tblcertificates holds Admission Number (Unique) and about 20 fields with different types of certificates which are all yes/no fields

NO!

That is not the way to build a table of certificates. It should be two fields at the least: CertificateID, and CertificateName. No wonder you are having query problems.

[Mile-O]

Here's an example of what I mean:

[Blackwidow]

I dont think I explained properly they need the check box because the certificates are run from the database as well through a report.. and as they arent all done at the same time and you might have to do 56 Effort certificates in the morning and maybe 23 Effort ones in the afternoon, so after the ones in the morning are run an update query could be run to clear the checkbox and when they do the afternoon ones they can select the box...

I had it set up similar to yours with the added field of a checkbox. But what I am trying to do is for them to check the box and the rest to be done automatically instead of them having to select certificate from dropdown and then inputting the date and then checking the checkbox.. which is more time consuming when you have hundreds to do at a time than just checking the one box..