Delete Field Value in a Tbl

[Mac1206]

I have a Table (A) which I need to delete a field value based off another filed in that table..Delete Field_Value = YES WHERE Field_Maryland = Toys Example:

Field_Maryland Field_Value
Toys Yes

So if my Field_Maryland = TOYS, DELETE Field_Value = YES...Thanks in Advance

 

[Mac1206]

No problem! Glad I was able to help!

With action queries (like Update, Append, Delete), I find the easiest way to do it is to first build a simple Select Query that returns all the records you want to take the action to.
Once you have that, then just change the query type, make the change to the appropriate field.

Question, I have to fields which I need to calculate within my table A...Min_Frieght - Full_Frieght for my Freight_Discount column, do I need to do an Update query if those 3 fields already exist in the table...

 

[Joe4]

Question, I have to fields which I need to calculate within my table A...Min_Frieght - Full_Frieght for my Freight_Discount column, do I need to do an Update query if those 3 fields already exist in the table...

Not quite clear what you are trying to do, but if you have calculations which can be calculated using other records/values in your table, those calculations should be done in a query, and not stored in a table.

The general rule of thumb to ensure database integrity and keep your database dynamic is to never stored any amount which can easily be calculated on-the-fly.

 

[Mac1206]

Not quite clear what you are trying to do, but if you have calculations which can be calculated using other records/values in your table, those calculations should be done in a query, and not stored in a table.

The general rule of thumb to ensure database integrity and keep your database dynamic is to never stored any amount which can easily be calculated on-the-fly.

Gotcha, so I will create the query by building an expression for Freight_Discount and then update my table with those values...Thanks

 

[Joe4]

Gotcha, so I will create the query by building an expression for Freight_Discount and then update my table with those values

Not quite. Most of the time, you do NOT need to update the table at all.
If it is a calculation that can be performed at any time when needed, there is not point in storing the resulting calculation to a table, and it is actually usually a bad idea to do so.

To put it in Excel terms, let's say that in cell C1 we had the formula:
=A1+B1
So this adds A1 and B1. We COULD overwrite the formula with the hard-coded value of that calculation, but why? That is usually a bad idea.
If we were to do that, and at some point somebody changes A1 or B1, C1 is no longer correct (since it is hard-coded and not a dynamic formula).
The same logic/philosophy works in Access. Don't hard-code calculations unless you have a good reason to.

There are exceptions to this rule (trying to capture a historical record at a specific point in time), but those are few and far between, usually.

 

[Mac1206]

Not quite. Most of the time, you do NOT need to update the table at all.
If it is a calculation that can be performed at any time when needed, there is not point in storing the resulting calculation to a table, and it is actually usually a bad idea to do so.

To put it in Excel terms, let's say that in cell C1 we had the formula:
=A1+B1
So this adds A1 and B1. We COULD overwrite the formula with the hard-coded value of that calculation, but why? That is usually a bad idea.
If we were to do that, and at some point somebody changes A1 or B1, C1 is no longer correct (since it is hard-coded and not a dynamic formula).
The same logic/philosophy works in Access. Don't hard-code calculations unless you have a good reason to.

There are exceptions to this rule (trying to capture a historical record at a specific point in time), but those are few and far between, usually.

OK, so as of now, I need to place a value in C1 in my table which = B1-A1...How should I go about that?

 

[Joe4]

Why does it need to be on the Table?

A very popular misconception that many new Access users have is that they think that every needs to be on the Table level, but that is not the case. Pretty much anything that you use a Table for (data source for a Form, Report, or Export), you can also use a Query for. So you simply create a query and do the calculation there.

If you are still unsure, let us know exactly what you are using this for (what you are trying to ultimately accomplish).

 

[Mac1206]

We have a table in Excel which take hours to calculate and we are trying to reduplicate that table in Access to save time, that's my task by higher authority...lol...They want to see it in the table...

 

[Joe4]

They want to see it in the table...

Who, exactly? Most users wouldn't even know the difference if they are viewing the results of a table or a query (the data is presented the same way). And I find that most of them don't even really understand or know what they are asking. They often say things like that because they simply do not know any better. Is there some specific reason WHY they think it needs to be this way?

In general, users should NEVER have access to the underlying tables - they can mess too many things up.
Databases should typically be menu-driven, and all data should be presented to users via Forms and Reports (sometimes Queries). And as we discussed, Queries can be used to be the data sources of any Form or Report.

These best practices are there for a reason - and can save you a lot of heartache. Let's say you decide to use an Update Query and stored the calculated values in a Table. Now, let's say that someone updates the underlying data. Now, those calculated values are incorrect. And what often happens? They blame the programmer saying the database is broken, unreliable, or doesn't work. Trust me, I have seem this play out!

 

[xenou]

For the record, you can update a table in this way:
UPDATE MyTable SET [C] = -[A]

What Joe is saying is you could just as easily use a select query in a table that has only B and A:
SELECT [A], , -[A] AS C FROM MyTable

The end result is the same.

 

[Joe4]

The end result is the same.

Just to clarify, they are the same at that point in time, but is not dynamic. So if the underlying data changes, they will NOT be the same.