Hi,

I am a relative newbie when it comes to database devleopment, and I've got a sticky problem...

I have a table (tblInput)

I have a query (qryImprovements)

qryImprovements is based on tblInput and compares 8 pairs of fields (pre & post) and determines whether it is a postive result or not. If it is positive it gives it a "1" if not then a "0".

So for each record I have a total positive result.

I would like this to be included in my table tblInput as a field "Total Positive Changes" but every time I try to get the lookup wizard says that because the query is based on the table I am using I cannot lookup those figures.

I have worked out a way of getting the figures to display on my form frmInput which is the front end of tblInput but this doesn't feed back to the table the results i.e. the form says "7" but the table is still "0".

I didn't know whether this was a "table" or a "form" forum question so apologies if this could be better placed!

Thanks in advance for any help offered as I am slowly going insane!

Andy

Why do you want to store this value? You already calculate it on your form, so you don't need to store it. It is bad practice to store a value that is dynamic.

Sorry, I forgot to explain that part.

I have a query that picks up the number of records that have 2 or more positive changes.

Which worked on my basic database where an individual had to look at all the data and work out whether or not the increase or decrease was a positive change and then add them up and type them into a field.

I was hoping that I could get all of this done automatically with little or no VB coding (as I don't know any VB).

Thanks

Andy

You don't need VB (VBA actually in Access!)

You are calculating the number of positives in a query. All you need to do is join this query with your basic table in another query, and use this as a basis for your reports, forms etc.

If you really, really, really insist on updating this value in your table, use an update query.

I don't really mind how I do it, although I don't know how to do either of your suggestions!!

All I know is that if I can get the table updated then the query I have at the moment qry2Positives will work again.

I have never used an update query before can you offer any tips in order to get it to work?

At the moment I have created a new query and dragged the qryImprovements onto it showing the Total Positive Changes. And in the "Update to" I have [tblInput].[Total positive results] which is the field I want to update.

I am getting an error "Operation must use an updatable query" and despite using the help wizard I am none the wiser as to what I am doing wrong!

Thanks for your help.

Andy

All I know is that if I can get the table updated then the query I have at the moment qry2Positives will work again. Add the query that calculates the positives to this query (linking the query to the existing table), drop the relevant field into the grid, and remove the one that comes from your table. All should be well.

You have no idea how much stress you have just fixed!!

That was so simple, I had actually got half way there already having qryImprovements linked to qry2Positives and had replaced the actual Total Positive Results field from the table with that from the query but I had NOT changed the count column from the table to the query field.

Once done, it works!!

Thanks again for your help,

Andy:D