Update query to add dats to table NOT overwrite!

[Reece92]

Update query to add data to table NOT overwrite!

Hi guys!

Im having an issue with an update query and looking for some help, I am pretty new to access so please bare that in mind

The problem: I have a table that is updated from an update query which gets its data from a table, which gets its data from a form. I have set the query to only update the current record, this is done by a macro when the user exits the input box. The problem arises when a user goes to visit past record, I would like it to not overwrite the old data with the new data. If the query was only allowed to ADD data and not overwrite then this would fix the problem!

Any ideas people?

My SQL code is as follows:

UPDATE [Run Info], [Sieve Weights] SET [Run Info].[315 Tare] = [Sieve Weights]![315 Tare], [Run Info].[250 Tare] = [Sieve Weights]![250 Tare], [Run Info].[200 Tare] = [Sieve Weights]![200 Tare], [Run Info].[160 Tare] = [Sieve Weights]![160 Tare], [Run Info].[100 Tare] = [Sieve Weights]![100 Tare], [Run Info].[75 Tare] = [Sieve Weights]![75 Tare], [Run Info].[50 Tare] = [Sieve Weights]![50 Tare], [Run Info].[BD Tube Tare] = [Sieve Weights]![BD Tube]
WHERE ((([Forms]![1L Input form]![ID No])=[Run Info]![ID No]));

Thanks in advance

Reece

 

[plog]

The problem: I have a table that is updated from an update query which gets its data from a table, which gets its data from a form...this is done by a macro when the user exits the input box

That's quite the Rube Goldberg Machine you have there. How come such a convoluted process? Why not have the user load the existing record they want and just let them update it directly?

Actually, looking at the names of the fields in your table, I can sort of understand why it has to be so complex--I think your table structure is improper. Whenever you start storing values in your table/field names (e.g 315 Tare, 250 Tare, XXX Tare) you are probably doing it wrong.

My advice is to post your table structure, along with a plain english explanation of what real world information/process this database represents.

 

[Reece92]

Thanks for the response


I need all of my data in 1 table as the database has a lot of calculated fields ( I know that this is a sin in the database world). The sieve tare weights change over time, but the old data is valid for that record so I would like it to keep the old value!

What do you mean by table structure, how can I post this? my database has 2 tables. one stores the sieve weights, which are imputed from a form. The reason I don't want to make the user enter the sieve weights is because he would have to do this 3 times a day, and the sieves are only reweighted once a month.

This is my first thread here, so sorry if its a bit frustrating!

Oh there are 7 sieves, that's why there is so many different tare weights

 

[plog]

sieve tare rates mean nothing to me. The frequency with which data changes provides almost no help in understanding what you are trying to accomplish with your database.

I know about databases in general, not about what your data represents. To bridge that gap you need to explain what your database represents in real world terms. Don't use database jargon (tables, macros, queries, etc.). Explain your process as if I was a complete newbie to your organization.

If nothing else you can post your structure like this:

Table1NameHere
Field1NameHere, DataTypeHere, ExplanationHere
Field2NameHere, DataTypeHere, ExplanationHere
...

Table2NameHere
...

 

[Reece92]

ahh I see! sorry

table 1: sieve weights:

315 tare: entered by the user once a month (number): the weight of the 315 micrometre sieve
250 tare: entered by the user once a month (number): the weight of the 250 micrometre sieve
200 tare: entered by the user once a month (number): the weight of the 200 micrometre sieve
160 tare: entered by the user once a month (number): the weight of the 160 micrometre sieve
100 tare: entered by the user once a month (number): the weight of the 100 micrometre sieve
75 tare: entered by the user once a month (number):the weight of the 75 micrometre sieve
50 tare: entered by the user once a month (number):the weight of the 50 micrometre sieve

This data is stored in only one row, so when the sieve weights change the old sieve weight is lost on this table but it is stored in table 2 once the update query is run.

Table 2 : Run info

315 Tare: This one of the fields that is updated by the query.
250 Tare: This one of the fields that is updated by the query.
200 Tare: This one of the fields that is updated by the query.
160 Tare: This one of the fields that is updated by the query.
100 Tare: This one of the fields that is updated by the query.
75 Tare: This one of the fields that is updated by the query.
50 Tare: This one of the fields that is updated by the query.

The tare weights have to be stored on the run info table as they are used in a calculated field (the weight of the sieve (tare weight) is taken away from the weight of the sieve + the sample, This gives us the mass of sample on each sieve).

I tried to use a query instead of calculated fields but I got an error on my final calculation, cant remember exactly what is was... something about memory?

Finally the ID No is an auto number which is my primary key. the WHERE statement is so that the update query only updates the record that is shown in the 1L input form.

I hope this helps!

 

[plog]

Why are you not doing this in Excel? You are gaining no database benefits by this being in Access. For one, your tables have more fields than needed to store this in a database. Most importantly, it doesn't seem like you are retaining data, this seems to be calculator of sorts and that's it. You process current data and then for the next run you discard all past data. Wouldn't it be beneficial to have historical information to compare runs? You properly configure your database and that's possible.

As for your tables, both are inproperly structured. Your tables should add rows instead of columns to accomodate data. That means the 350 part of [350 tare] should be a value in a record, not in the name of a field. sieve weights structure should look like this:

SieveWeights
weight_ID, autonumber, primary key
weight_TareSize, number, will hold size of tare (e.g. 315, 250, 200, etc.)
weight_Value, number, will hold weight

Additionally, you might put a date field in there which will allow you to store all the previous sieve weights for historical purposes (What was the sieve weight a year ago?).

Run info has similiar problems. This one will probably take 2 tables, Runs and SampleSizes

Runs
run_ID, autonumber, primaray key
run_Date, date, date of run
run_Notes, text, description or any notes about run

SampleSizes
sample_ID, autonumber, primarky key
ID_run, number, foreign key to Runs
sample_TareSize, number, will hold size of tare (e.g. 315, 250, 200, etc.)
sample_Weight, number, will hold weight of sample

And, again you've omitted an explanation of what your data represents in real world terms, so I made some assumptions and took liberties in naming. But I think that structure is closer to what you need than what you currently have.