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

[theresa]

Hi, I'm trying to get a field in a table to automatically fill with the result of an Append query but it is not working. The query is run on an Appointments table to get the date of the Clients first appointment and I was hoping to store this date in the Counselling table but It's not working. I also have to figure out how to enter the date of the last appointment if the CounsellingFinished Yes/No field is selected. If anyone has any idea how to do this and could show me where I'm going wrong, that would be great. Here's what I've done so far



and the resulting table has not been updated

[ItsMe]

What happens when you run the Append query, nothing?

What data type is the field "CounselingID" in tblCounseling?

[theresa]

You're right, nothing happens. The CounsellingID is an autonumber (PK), does this make a difference as it's only the DateOfFirstSession and DateCounsellingFinished I wish the queries to update

[ItsMe]

Autonumber fields are, generally, not included in an append query. The fact that you are not getting any warnings after you try to run the Append query indicates additional issues. I would try removing the Append function from the query until after you view the correct information as a SELECT query and or Totals query.

You will need to address the issue involving the Autonumber field. I suspect Non-Normalized data structure is an underlying factor. Consider the relationships between your query and the table you are appending.

[theresa]

It works as a select query but I don't get what you mean by "Non-Normalized data structure is an underlying factor".
This is the relationships between the main tables which I think is pretty normalised, maybe I'm wrong.

[ItsMe]

What I did not notice at the beginning is that the table you are appending to is part of your Append query. It seems that what you want to do is UPDATE existing records, not Append new records to a table.

If you want to append records, remove the CounsellingID field from your Append query.

[theresa]

Thanks for getting back so quicky, I'm burning the midnight oil here and my brain is going to sleep. You're right, I just want to update records that already exist, both queries work so I should use an update query, I only noticed it now you've mentioned it, I researched online and got reference to append query. Should I remove the counsellingID from the update query as I thought it would be used to get the correct records to update

[ItsMe]

You stated that you want to UPDATE existing records in tblCounseling. An Append query will not do this. You could use an UPDATE query, where the Key value "CounsellingID" from tblCounseling equals the key value in tblCounseling.

Another approach is to use a SELECT query as the Recordsource of a form and edit the current record of the form's Recordset.

[theresa]

I don't know which would be best, I'm trying to make this as easy to use as possible as the lady I'm helping out would be quite clueless when it comes to using a database. I need both of these pieces of information for reports she would be doing quarterly and annually. which would you recommend under these circumstances

[ItsMe]

The typical approach is to use a form's recordset to edit existing records. If you are changing the status of several records, you can automate this with code using an action query like UPDATE or using DAO.

[theresa]

I've no knowledge of VBA, would it be something like when a client on the waiting list is allocated to a counsellor, the appointment form is opened and an appointment date is selected, this appointment date is then used to update DateOfFirstSession i.e. when Save button is clicked it also runs the update query

[ItsMe]

If it is just the CURRENT record you are updating/editing, have the user type the value into a bound control. If there are several records to update, you will want an event to fire some VBA. The code can call on an object like an action query or the code can manage the entire process. Automation will require some code. You may be able to incorporate a Macro. You will still need to pass criteria to the UPDATE query from the form. I find Macros can, at times, be as complex as writing basic VBA.

[theresa]

I wondering if I need to record the first/last appointment, maybe I should just use the dates on the query for the report. VBA and Macros would be beyond my ability

[ItsMe]

Calculated data is not usually stored in tables. As you see, Access is about managing the data you store in your tables. You already have the appointment date in your table and there is not a need to enter a calculated version of this date. Calculated data changes over time. Use queries and VBA to retrieve data when you need it.

[theresa]

Thanks, that's what I was thinking, I could call current clients based on the dates of their appointments if they fall within the date parameters on the form and finished clients if the yes/no box is selected in the tblCounselling and the last appointment date is within the date parameters on the form. I'll delete the fields DateOfFirstSession and DateCounsellinEnded in tblCounselling. Do you think this would work?