Problem entering result of (Append) query into field in table

[ItsMe]

Deleting the fields DateOfFirstSession and DateCounsellinEnded makes sense. I am not sure I understand or follow the business rules but, using a query to determine the last appointment is a good idea. Using Booleans (Yes/No fields) to change the status of a given appointment is a good idea. You can incorporate various Boolean fields in your queries to avoid retrieving the entire table.

[theresa]

Here's the brief that I've been given by the Counsellor to help her:

When a Client first contacts the service, information is collect from the client such as Name, Address, Phone, DOB, Date of Referral, Presenting Issues, Referral Source and Times/Days available. They are placed on a waiting List until a Counsellor is available.
An Active Counsellor has 4 or 5 Clients at any given time and does not receive a new client until one of their current Clients has finished their Counselling. The Counsellor collects records of their Appointments such as Date, Time, Length of session and if the Client shows up for their Counselling Appointment.
When a Counsellor becomes available, a Client is then selected from the waiting list and given their first Appointment. The Client is seen by the Counsellor through Appointments until the Client is Finished and/or referred elsewhere, which is noted by the Counsellor. When the Client is Finished, they are no longer an active Client for that Counsellor and a new Client is selected from the waiting list.
The Management require the following information both quarterly and annually:

• List of New Clients referred within the Period
• Current Waiting List within the Period
• Current Waiting List based on Category and Age within the Period
• Length of time on Waiting List
• New Counselling Clients within the Period (Current Clients)
• Counselling hours provided within the Period grouped by Counsellor
• Number of Clients finished within the Period
• Number of Client 'No Shows' within the Period grouped by Counsellor

The Management would also like the database to warn if a Client has 3 previous consecutive 'No Shows'.

I think I'm going to have to use more Booleans (Yes/No fields) as I having trouble getting the waiting list at the moment

[ItsMe]

It all goes back to Normalization. Consider the players and consider the activities. Create tables for the players to reside in and create tables to log events. Use PK's and FK's to create relationships between said tables.

[theresa]

Thanks so much for all your help. I think I've bitten off more than I can chew but I'll do my best to help her out. I'm sure I'll be back with other questions as I get through each query

[ItsMe]

While developing a DB it is common to revisit the data structure. That is why it is important to have the relations Normalized before too much has been done with regards to queries and forms. You do not want to paint yourself into a corner where you need to rebuild the application part (or public interface part) of your RDBMS.

[theresa]

Very true, I thought I had done that but left those two fields as I thought it would be easier to run queries, forms etc if I had those dates stored. So far all the queries are working but I've been unable to find a method to warn if a Client has 3 previous consecutive 'No Shows' or 'Cancelled by Client'. Don't think I'll be able to do it as Access doesn't recognise previous records. I'm about to start creating the forms and reports for this database so hope to have it ready for her tomorrow. This is the second time I've created this database for her as she didn't want an Appointments table as the Counsellors sort this out themselves, I told her that it wouldn't be able to produce accurate reports on the information she required which she didn't get until she saw the finished product. She thought it would only take me a few hours to do it for her but it's taken days as I don't have any more than a basic knowledge of Access