UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Remove Obsolete Delete From An Existing Table, Access 2013    
 
   
punster
post Feb 28 2018, 11:10 AM
Post#1



Posts: 227
Joined: 10-February 03



Not sure if what I want to do is possible. Willing to use either a query or VBA code if it is. Will try to break it down to basic situation. Have an existing data base that has a "master" (Table A) table. On Table A each record has up to 20 categories (20 different fields on the table) to which that particular record can be assigned. The valid categories are stored in a second (Table B) table. The business user has recently requested that some of the categories from Table B be removed as they are no longer valid. How can I remove those no longer valid categories from any record in Table A that contains that value? Needless to say I do not want to delete any records from Table A, just clear the fields that contain categories that are no longer valid. If that is doable, I know I am really pushing my luck, but is there a way that I could write code to "bounce" the remaining up to 20 categories so that there is not a blank one in the middle of the list?
Go to the top of the page
 
theDBguy
post Feb 28 2018, 11:15 AM
Post#2


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Not sure I follow. If you know which category is being removed and what to "blank" it out, then you should be able to simply execute an UPDATE query. For example:

UPDATE TableA SET CategoryName=Null

However, having (up to) 20 different categories as fields in a table is not considered a good design. Can you describe your table structure in more detail and explain the business model your database is emulating?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Feb 28 2018, 11:28 AM
Post#3



Posts: 951
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If each record in Table A is only allowed to be associated with one category at a time....then having 20 category fields in Table A is unnecessary. However, if the records in Table A can be associated with several different categories at the same time....then I can understand why you have the 20 category fields there (though I have been rather severely reprimanded and told that this usage breaks the rules of normalization).

Either way, it is still pretty straight forward to do as DBGuy said. Only problem is that a record in Table A may end up with no association to any category...but if that is ok with your app then...fine.

But on a related chain of thought.....if the obsolete category has simply received a new Description but still applies to the same records....then just update the description in the category table and the new description should automatically pop up when each rec in Table A is accessed. That is one of the beautiful benefits of indirect reference. And just another reason why numbers/ids are usually better for keys.
This post has been edited by zaxbat: Feb 28 2018, 11:34 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
projecttoday
post Feb 28 2018, 11:36 AM
Post#4


UtterAccess VIP
Posts: 9,703
Joined: 10-February 04
From: South Charleston, WV


If you clear deleted categories, you will not have a history of past activities. Your old reports won't work (especially if they use inner joins).

Type in the layouts of the tables.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
punster
post Feb 28 2018, 11:51 AM
Post#5



Posts: 227
Joined: 10-February 03



Guess I didn't make myself clear enough. The purpose of Table A is to show all the possible categories (up to 200) that could exist for each record that is in Table A (currently about 300 records). So as zaxbat said in the "however" the records in Table A can be associated with several different categories. Understood about normalization, but since the table is so small it was not originally set up that way and not sure it would be worth effort to change at this point. And the obsolete categories are being completely removed - not replaced. So now to follow-up with the suggestion from theDBguy - removing the obsolete name from Table B is no problem. The problem is that on Table A on any record it could occur in any one of the 20 fields on the record that hold the category. How do I match to the correct one to blank it out.
Go to the top of the page
 
GroverParkGeorge
post Feb 28 2018, 11:57 AM
Post#6


UA Admin
Posts: 32,830
Joined: 20-June 02
From: Newcastle, WA


No. Either way the proper design is NOT to have multiple fields each with a variation of a common data point. This is what is known as a Repeating Group. It is colloquially referred to as a "Spreadsheet Style" table.

If there is a one-to-many relationship, the proper design is two tables, one for the "parent" or "master" record and the related table for the "child" or "detail" records.

If the relationship is many to many, the proper design is three tables, one each for the two "parent" records and a related junction table to establish the multiple relationships.

Either way, the problem here is a direct result of trying to make a "spreadsheet" style table work over the long run. Day one, it makes for an easier interface design. Eventually, it wears out its welcome.

Also, updating descriptions, as you suggest, destroys historical data. Probably not what the client really wants to have happen.

One more thought. It's technically a violation of normalization to create repeating groups. But that's not the reason we object to it. THat would be formulaic and pedantic. The reason we object to it is that it creates extra work, and endangers data cleanliness and accuracy. Just like jay walking. It's not the act of crossing the street at unmarked locations that's bad. It's the act of crossing the street in such a way as to endanger yourself and drivers.

The rule exists because of the problem. The problem isn't the rule itself.
This post has been edited by GroverParkGeorge: Feb 28 2018, 12:04 PM

--------------------
Go to the top of the page
 
GroverParkGeorge
post Feb 28 2018, 11:59 AM
Post#7


UA Admin
Posts: 32,830
Joined: 20-June 02
From: Newcastle, WA


Here's why it matters even with a small table, right? You're here with a problem because the table design makes it a problem.

I strongly urge you to fix that next.

Here's some help.
Here's some more help.


--------------------
Go to the top of the page
 
projecttoday
post Feb 28 2018, 12:21 PM
Post#8


UtterAccess VIP
Posts: 9,703
Joined: 10-February 04
From: South Charleston, WV


It is worth the effort.

Type in the layouts of the tables or post the database if you can.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
zaxbat
post Feb 28 2018, 12:30 PM
Post#9



Posts: 951
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


GroverParkGeorge is right on the mark in this. Anytime you try to represent an infinite collection of possibilities (categories: 300 and rising???) with a finite mask (20 slots max) you are heading down a dead-end path. Future requirements are likely to force you to address this anyway. Might as well address it now.

Just make a new table that holds the chosen categories for each record in table A. They will link back to their record in table A and will not be limited to 20...they can be infinite (or as high as SQL allows--a whole bunch)

Will be significant changes in your forms though...that will be the harder part. Oh, and if more than one client is using your app...then you will need a conversion routine to automate the conversion of the data.
This post has been edited by zaxbat: Feb 28 2018, 12:33 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
theDBguy
post Feb 28 2018, 12:36 PM
Post#10


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


We might be able to normalize the tables but still use the old forms by using a Crosstab query as a data source. But eventually, the forms should use a form/subform setup.

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 09:07 AM