How To Delete Column-Data Without Query or Macro?

[johnywhy]

hi

i want to delete all the data in a single column.

i do not want to delete the column.

i do not want to use a query or macro.

i want to do this in the worksheet table-view.

any help?

thanks!

 

[pbaldy]

Place cursor in first row, highlighting all data in the desired field, then:

hit delete key
hit down arrow key
hit delete key
hit down arrow key

repeat until you're at the end of the table.

Sorry, I couldn't resist. Your question is a bit like "I want to build a house, but I don't want to use a hammer or saw". An UPDATE query is probably the easiest way to clear the column. I don't know of a way to do it manually other than one at a time. In Access you don't typically work directly in tables as you would with a spreadsheet.

 

[johnywhy]

thanks!

in Access, my clients, especially the non-technical ones, often DO work directly in tables like a spreadsheet.

as a microsoft certified C#/VB/SQL programmer, i also often work directly in tables like a spreadsheet. just because i'm a programmer, that does not mean i want to write code for every thing, if there's a gui way.

microsoft does provide single-cell editing in sheet-mode. they also provide some bulk operations in sheet mode, such as bulk paste. so it's not unreasonable to expect bulk-deletion in sheet mode.

one possible non-code solution is to simply delete the column, and then recreate it. another option is to copy a blank column from access or excel, and then paste the blank cells into the column i want to clear (taking advantage of the bulk-paste). but a normal bulk-delete with the delete key would be most consistent with microsoft standards.

mainly, a non-code solution would be helpful for my non-technical clients and beginner students. i could write them a macro if necessary, but a built-in solution would be nicest.

 

[boblarson]

In "database world" you will find that we discourage working in tables directly because if a user is in the table then there is no limit to what they can do, enter, delete, etc.

A form is used to ensure that data validation occurs both when entering and deleting data. So, tables are not designed with bulk operations in mind because of that rule. You cannot do column level deleting without an update query from SQL Server, or Oracle, or db2, so why expect Access to be different?

Just an FYI.

 

[johnywhy]

there are many times, particularly during development, when unlimited access to the table directly is essential and legitimate.

sometimes the sheet-mode makes an appropriate data-entry screen. data-validation occurs in sheet-mode as well, if you put your validation rules in the backend table, instead of in the entry-form.

i would expect Access to be different, because it is intended to be different-- more user-friendly.

fyi!

 

[johnywhy]

no disrespect, but one of my pet peeves in forums is people who say "you can't do that", or "you shouldn't do that", or simply "i don't know how to do that."

if you don't know, why broadcast your lack of knowledge? it serves no purpose.

"you can't do that" is equivalent to "i don't know how."

"you shouldn't" is inappropriate-- this is not a thread about shoulds and shouldn'ts. it's a how-to. i'm not asking for design philosophy. just nuts and bolts.

thanks

 

[boblarson]

there are many times, particularly during development, when unlimited access to the table directly is essential and legitimate.

sometimes the sheet-mode makes an appropriate data-entry screen. data-validation occurs in sheet-mode as well.

i would expect Access to be different, because it is intended to be different-- more user-friendly.

fyi!

More user friendly doesn't mean it has to sacrifice itself. Yes, there are times when one might use the table directly. But in a production setting that is a huge mistake if it is used and even more so if the data is critical.

If the data isn't all that important and ensuring correct data isn't all that important then having direct access to the tables isn't all that important.

But if the data IS important and ensuring correct and referential integrity is important then direct access to the tables should not happen. Table level validation rules and all just can't protect it well enough nor can it deal with referential integrity completely.

 

[MSAccessRookie]

there are many times, particularly during development, when unlimited access to the table directly is essential and legitimate.

sometimes the sheet-mode makes an appropriate data-entry screen. data-validation occurs in sheet-mode as well.

i would expect Access to be different, because it is intended to be different-- more user-friendly.

fyi!

I amlost agree with the point that you are trying to make, but that is not the main issue taht I want to discuss.

Why not create a Form for the users and display it in DataSheet mode. Most users will probably feel comfortable with it since it looks and feels like an Excel Spreadsheet. Then you have the protection and Data Validation that you need and the Flexibility that they want. Once you have a Form, you might even be able to work some VB Magic to do things like clearing columns the way that you want.

As for your own direct access to the Table ( the point that you were trying to make ), that will not go away just because you make the users access the Table via a Form.

 

[boblarson]

"you shouldn't" is inappropriate-- this is not a thread about shoulds and shouldn'ts. it's a how-to. i'm not asking for design philosophy. just nuts and bolts.

thanks

No, that's where you are dead wrong. We do not just blindly give someone a gun with which to shoot themself. We try to explain that you should not aim the gun at your head while it is loaded with bullets because you will end up with serious damage if you pull the trigger.

If you can't accept that we here want to make sure that you don't pull the trigger on a loaded gun then perhaps you should exit gracefully, go where you have "yes men" and pull the trigger.

 

[MSAccessRookie]

no disrespect, but one of my pet peeves in forums is people who say "you can't do that", or "you shouldn't do that", or simply "i don't know how to do that."

if you don't know, why broadcast your lack of knowledge? it serves no purpose.

"you can't do that" is equivalent to "i don't know how."

"you shouldn't" is inappropriate-- this is not a thread about shoulds and shouldn'ts. it's a how-to. i'm not asking for design philosophy. just nuts and bolts.

thanks

As observations toward your points:

I try not to be too hard on responders, because not Everyone knows everything. Some of us know some things while others know other things, and we all try to help out where we can.

Sometimes "you can't do that" does means "you can't do that", and sometimes "you can't do that" means "Access can't do that". Access is not capable of handling everything that we need, but on occasion, we are able to find ways to use Non-Access tools to assist the process.

This is a point that many of the people here will disagree with entirely. "you shouldn't" is rarely inappropriate. While this Particular thread may not be about shoulds and shouldn'ts, one of the specific purposes of the Forum in general is to assist people in learning and using better Access Programming Techniques ( shoulds and shouldn'ts ). While it is also true that not all people are asking for design philosophy, some people have benefitted from it in the past when they took the time to understand. After all, design philosophy is part of the nuts and bolts of MS Access.

 

[johnywhy]

bob, you're a little hostile. i think your metaphor about shooting myself in the head is a little off. also, you keep saying "we"-- If you're referring to the community of professional database developers in the database universe, then i am one. therefor, have a little respect for a fellow experienced professional.

again, this table is in development mode-- i'm not going to build an entry form for a quick database used for testing, which may never be used by end-users.

msaccess-rookie, if a table has data-validation on the back-end table, then a form is not needed to enforce those rules. re "you can't do that", in my years of pro experience, i usually find a way to accomplish something that someone told me was impossible. i do see your point about the value of comments about design philosophy, especially for beginners. i did not realize this forum was directed mainly to beginners. my mistake.

 

[Endre]

The easiest way I know of is to use an empty field in your table - create one if you don't have it already. When you view the table you will have an entire column empty, and simply select the entire column by clicking in the column heading, then a simple copy, select the entire column you want to "delete", and paste the empty values. Voila.

 

[johnywhy]

thanks Endre!

 

[boblarson]

wow, bob, you're extremely hostile.

Only to the extent that you are also such.

and i think your metaphor is way way off. also, you keep saying "we". if you're referring to the community of professional database developers in the database universe,

No, I mean on this forum.

then i am one. therefor, have a little respect for a fellow experienced professional. your tone is very condescending.

I tend to get that way when someone starts calling me names and saying that what I'm saying is not applicable when it is. So, anyway, I feel that this is heading off in a bad direction so I will not respond further in this thread or any others of yours.

again, this table is in development mode-- i'm not going to build an entry form for a quick database used for testing, which may never be used by end-users.

Should you have stated this up front then this thread would never have taken the path that it took. Your initial question - go re-read it - said nothing of this so we were left to assume that you were trying to do this in a live environment and your second post kept on with the "my clients might need to do this" and so there - live environment - not a good idea. A development environment - something completely different.

msaccess-rookie, if a table has data-validation on the back-end table, then a form is not needed to enforce those rules.

That is an erroneous statement because validation rules are not always able to accomplish the appropriate level of validation required OR they make it so cumbersome to the user because they squawk AFTER the update is attempted instead of catching things BEFORE they try it.

So, anyway, I'll leave you to it. Have fun. Good day.

 

[DCrake]

Sorry to butt in but Jonnywhy, can I just say that, Yes, you can employ data validation at table level, however, there may be validation checks that simply cannot be employed at table level.

I never use table level validation so coming up with an example may not be quite right, but lets say the column of data that you want to delete is one entity in an equation for a calculated field.

So the use of form validation is more appropriate and controlable.

You opening question semed to indicate that you wanted to allow users access directly to tables, which in general IS frowned upon. There was no mention that this was a developer question. And BTW you can select a range of rows and columns using the Shift+Tab keys to block out a section of data to delete.

This thread was being writtten prior to reading #12 onwards.

 

[johnywhy]

bob, i'm sorry if you thought i was calling you names, i did not mean to. i hope that we can exchange tips and Q&A in the future.

also, you're right, i did say clients, so i can understand why people got that impression. but in this case, the client is working with me on development of a system, that she is then going to roll out to her clients. so, she's co-developing with me-- we're working on the backend together.

 

[MSAccessRookie]

wow, bob, you're extremely hostile. and i think your metaphor about shooting myself in the head is way off. also, you keep saying "we"-- If you're referring to the community of professional database developers in the database universe, then i am one. therefor, have a little respect for a fellow experienced professional.

again, this table is in development mode-- i'm not going to build an entry form for a quick database used for testing, which may never be used by end-users.

msaccess-rookie, if a table has data-validation on the back-end table, then a form is not needed to enforce those rules. re "you can't do that", in my years of pro experience, i usually find a way to accomplish something that someone told me was impossible. i do see your point about the value of comments about design philosophy, especially for beginners. i did not realize this forum was directed mainly to beginners. my mistake.

While I agree that limited data validation is available at a Table level, It can never be as complete as what is available via VB when using a Form. In addition, content validation, which is often more of a problem, is not as readily or completely available.

This Forum is not only for beginners, it is for people at all levels who wish to get advice from the group of professionals that are members here. Take time to view the Archives, or to peruse the many programming tools available in the library, and you will see what I mean.

 

[DCrake]

One of the problems that long time members encounter is reading posts from brand new members, whose initial post is somewhat misleading. This can go two ways. Either that the new member is a complete newbe that knows ery little about Access or conversely a knowledgeable Access person who by some reason or another posts their question in such a manner as to protray themselves as a newbee.

I think the latter applies here.

 

[johnywhy]

dcrake and msrookie, i think it's important to clarify the difference between data validation and data integrity.

my design philosophy is that user-friendly form-level validation should be used to prevent non-technical end-users from entering bad data, but table-level rules, such as type and size, are the ultimate protectors of data-integrity. i think using a size-unlimited "text" type for all fields is highly risky.

user-entry is not the only way data gets into a database-- data might be loaded via some backend code, stored procedures, a 3rd-party front-end that some end-user decides to use, or some type of import. in those cases, your form-level validation will not protect you.

i've spent many hours on many jobs scrubbing invalid data, because the db designer didn't bother with field-level rules or form-validation.

i use both where end-users are involved, but in a pinch (for example, in situations where end-users are other team-members who don't need to be molly-coddled) i will at least use field-level validation, because that will reliably reject bad data, and it will bubble-up error messages to the user-interface (or to whatever code or import process is running).

incidentally, msrookie, i believe Access has pretty rich back-end validation features, such as input masks, date and number range, flexible validation expressions, and comparison rules to other fields, as well as the usual size and type. You can even put a custom validation message in the field definition. I believe the Access architecture is intended to more closely integrate front-end validation with the back-end rules then a traditional db server.

"The easiest and fastest way to apply a validation rule to a form is to first add the rule to the underlying table field, and then use the automated form-creation tools that Access provides to create a form.... a control can have a different validation rule than the table field to which the control is bound. When a conflict develops between validation rules, the rule defined for the table field takes precedence."
http://office.microsoft.com/en-us/access-help/create-a-validation-rule-to-validate-data-in-a-field-HA010096312.aspx

 

[dfenton]

On the question of SHOULD, I say it's my duty to steer someone away from what is, in my opinion, a bad idea. I would almost never say "you shouldn't do that" and offer no explanation -- I'd almost always explain what the downsides are of the direction being recommended against.

In many cases, problems are caused precisely by the fact that someone is doing something in a way they probably shouldn't. In those kinds of cases, workarounds may be impossible, or incredibly convoluted, whereas addressing the original mistake can make the problem go away entirely.

A forum in which people Jesuitically answer only the question asked is one that I'd lose interest in very quickly. It's not helpful and people would never really learn how to be better Access programmers.