none
What is the best practice to overwrite a table with new data, but retain calculated columns? RRS feed

  • Question

  • I have a question regarding the best philosophical way to be able to import new data into a single table, and still have a macro easily recreate fields in that table when I need to.  Here are some particulars: 

    The scenario is where I have a table fruit and vegetable items (for simplicity) with a single fieldname called FoodItem.  I manually create a second column called FoodType.  I also have a named macro (associated with that table) that looks through all the records and assigns the corresponding value in the FoodType column to either fruit or vegetable, depending on the item.  

    My original table might be:

    FoodItem

    Bananas

    Broccoli

    My macro would assign values in a 2nd column (not in the original table) to values of fruit and vegetable, for the bananas and broccoli, respectively.  This is not my application, but it gets the point across.  My issue is not with the above, but the fact that I will be periodically importing new data into the table, and wanting to overwrite the existing data.  Let's say I'm trying out new rule sets or maybe I see that I don't have a rule for oranges, and so I need to add that to my code. 

    Here are 2 issue that I see: If I use a named macro that is linked to the above table, then when I import new data and overwrite the table's contents, it will also delete the named macro.  So, that doesn't appear to be a viable solution.  My only thought there was to keep the raw, imported data in one table and then have another table (which doesn't get overwritten) that contains the named macro.  The named macro in the 2nd table will then modify the FoodType column in the first table.  It seems like an unnecessary step, but I think it will certainly work. 

    The second issue is that when after I import new data, I need to manually add the 2nd fieldname of FoodType since it will be deleted after I import new data.  That's not a pain if I'm just added one calculated column, but it might be if I have several columns to add.  I thought I would just add the fieldname creation in the data macro, but I see I can create a record, but it doesn't look like I can create a field.  Just wondering if there is a better way to do the above.

    My other option is to write a VBA macro that will not be bound to any table.  Then, I can import new data at will and then just run the macro when I need to, in order to both create the field (which I'm assuming I can do in VBA) and populate the records in the FoodItem column (which I know I can do). 

    In another thread, a reviewer commented that VBA code is easier to search through and maintain than named macros, so I'm assuming the overall majority will say that I should code all my needed functionality in VBA code.  Just wondering if there was a simple way to do it in a named macro, but I suspect not.

    Thanks,

    Scotty81

    Monday, November 7, 2016 3:06 PM

Answers

  • Hi Scotty,

    Not sure you understood what I suggested. For example, let's say you imported five records to a table called tblFood and created a named macro called mcrFood, which is associated to tblFood.

    Now, let's say you need to modify the macro to add new rules and import new data. If you import the data into tblFood, I am guessing (since you didn't specify how you're actually importing the data) Access is deleting tblFood first and then creating a new table called tblFood again with the new data. Is it correct? If so, your concern is the macro you created called mcrFood is now gone as well.

    If so, here's what I was saying... Let's say you have five records in tblFood and created a macro called mcrFood attached to this table. When you need to update the rule and import new data, you can perform the following steps intead:

    1. Use a DELETE query to "empty" tblFood (this has the effect of not deleting the table but removes all the old data and also keep the macro intact)

    2. Use an APPEND query to "add" the new data into tblFood (if you cannot APPEND the new data directly into tblFood, then yes, you can import the new data to tblFoodTemp and then APPEND the data from tblFoodTemp into tblFood and then delete tblFoodTemp when you're done)

    3. At the end of the day, you'll have the old table (tblFood) with the macro (mcrFood) intact but contains the new data

    Hope it makes sense...

    Monday, November 7, 2016 6:02 PM

All replies

  • Hi Scotty,

    Sounds like you're importing the new data into a new table overwriting the old one because it has the same name. If you want to keep the old table, then you should not delete it and just import the data into it by using an APPEND query. Of course, you'll have to delete the old data first by using a DELETE query. Where is the new data coming from?

    Hope it helps...

    Monday, November 7, 2016 4:41 PM
  • Hi DBGuy,

    I do understand that if I wanted to keep the old data, I could just import the new data into a differently named table.  The issue is that I don't want to keep the old data; I just want to apply a continually revised macro to it to assign the items to different categories.  I'm doing this for cost assignment purposes so I'm assigning food items (per my made up example) to vegetables, let's say and I'm reviewing it with my client.  But, he might say, "hey look at all these items in the vegetable category.  Maybe we should go a bit more granular and make new rules to assign "winter vegetables" and "summer vegetables" or assign categories such as color of the vegetables (e.g. green vegetables)." 

    So, if that were the case, I'd 1) delete my rule to assign all vegetable food to the vegetable category, 2) add new rules for whatever new level of granularity my client wants and 3) reimport the table (but keep the rules) and review the results again. 

    Even if I imported to different tables, like tblFood1, tblFood2, etc. for my different imports, the issue I see is that if I used a named macro, it is bound to the table I first associated it with.  So, when I import my data into tblFood2, I'd have to re-associate my named macro to the new table.  I'm not sure if one can do that.  Thus, I see myself forced into a VBA solution.  I'm just wondering if I'm missing something that would allow me to preserve the option to use data macros (instead of VBA macros) should I want to.

    Hope that more detailed example makes sense.  The application is not foods, but the work process of continually reviewing how finely we have and need to "cut the data" into various categories is real.

    Thanks,

    Scotty81

    Monday, November 7, 2016 5:44 PM
  • Hi Scotty,

    Not sure you understood what I suggested. For example, let's say you imported five records to a table called tblFood and created a named macro called mcrFood, which is associated to tblFood.

    Now, let's say you need to modify the macro to add new rules and import new data. If you import the data into tblFood, I am guessing (since you didn't specify how you're actually importing the data) Access is deleting tblFood first and then creating a new table called tblFood again with the new data. Is it correct? If so, your concern is the macro you created called mcrFood is now gone as well.

    If so, here's what I was saying... Let's say you have five records in tblFood and created a macro called mcrFood attached to this table. When you need to update the rule and import new data, you can perform the following steps intead:

    1. Use a DELETE query to "empty" tblFood (this has the effect of not deleting the table but removes all the old data and also keep the macro intact)

    2. Use an APPEND query to "add" the new data into tblFood (if you cannot APPEND the new data directly into tblFood, then yes, you can import the new data to tblFoodTemp and then APPEND the data from tblFoodTemp into tblFood and then delete tblFoodTemp when you're done)

    3. At the end of the day, you'll have the old table (tblFood) with the macro (mcrFood) intact but contains the new data

    Hope it makes sense...

    Monday, November 7, 2016 6:02 PM
  • I have a question regarding the best philosophical way to be able to import new data into a single table, and still have a macro easily recreate fields in that table when I need to.


    Why a macro?  This can be done relationally. You have a unary relationship type between Categories and Foods.  At present you are modelling the relationship type by a foreign key in Foods.  While the use of a table to model a binary, ternary etc. relationship type is common knowledge, it's not always realised that a unary relationship type can be modelled in the same way.  This is usually done to avoid semantically ambiguous Null foreign keys, but can also be used here.  The relationship type would be modelled by a table like this:

    FoodCategories
    ….Food
    ….Category

    Each column is a foreign key referencing the primary key of Foods and Categories respectively.  The Food column is of course a candidate key, so unlike the use of such a table to model a binary relationship type the primary key is not a composite one of the two columns, as this is now a trivial key, but is the Food column alone.

    When the Foods table is repopulated it will then simply be necessary to edit the FoodCategories
    Table to assign each Food to whatever Category you wish, and to insert rows into FoodCategories
    for any foods not already represented in the table.

    While an enforced relationship type between Categories and FoodCategories can be maintained, that between Foods and FoodCategories would need to be dropped when Foods is repopulated, if new foods are represented in the imported data.  The relationship can then be recreated and enforced once the necessary rows have been inserted into FoodCategories to avoid any violation of referential integrity.  Once the relationship has been dropped new rows for the new foods, with Nulls in the Category column, can easily be appended to FoodCategories by executing a simple INSERT INTO statement.  Values can be inserted into the Category column as part of the process of editing the table to reflect the new 'rules'.

    Ken Sheridan, Stafford, England

    Monday, November 7, 2016 7:00 PM
  • Hi DBGuy,

    In reading your latest response, I now understand what you mean in being able to clear the table, but not delete it.  That will work.  As someone who works extensively in Excel, but is a newbie when it comes to Access, I wasn't thinking of the DELETE query as an option.  I did try that and it certainly works.

    Hi Ken,

    I read your reply and even though I was able to follow most of it, I think I still need some sort of macro.  Here's why, but feel free to correct me if I'm wrong.

    In actuality, my dataset has about 500 rows of data and I am applying about 100 rules to it.  And my rules are not 1:1 in that if the food is Bananas, then the Food Type is fruit.  It is more fuzzy, and includes things like If my food item contains the word 'banana', then set the corresponding value in the FoodType column to Fruit.  So, I need to run through all 100 rules for all the 500 records.  It would therefore assign the FoodType value as Fruit for the following items: Bananas, Bananas Foster, Banana Flavored Gum.

    As an improvement, I can apply each rule to only those rows where I haven't assigned a value yet (e.g. if FoodType is blank for that row).  That is if I want the food to be assigned a category by the first rule and not have the FoodType be overwritten by a subsequent rule that would say assign any food that contains the phrase 'Gum' to the FoodType of Candy.

    Regardless of the rule specifics, I see a need to cycle through all the records and then I would check that all food items have a FoodType filled in.  If not, then I'd add a new rule to the stack.  And, I see that being done with a macro, but would not know how I'd apply all those rules without a macro. Even if I was technically able to do it via a query, which I think I know how to do, having to manage 100 rules, while they are displayed horizontally across my query screen in Design View seems very unwieldy to me vs. being able to at least them better in a table-based named macro or a VBA macro.

    If I am missing something, please let me know. 

    Thanks,

    Scotty

    Monday, November 7, 2016 8:42 PM
  • Hi DBGuy,

    In reading your latest response, I now understand what you mean in being able to clear the table, but not delete it.  That will work.  As someone who works extensively in Excel, but is a newbie when it comes to Access, I wasn't thinking of the DELETE query as an option.  I did try that and it certainly works.

    ...

    Thanks,

    Scotty

    Hi Scotty,

    Glad to hear you got it sorted out. Good luck with your project.

    Monday, November 7, 2016 9:09 PM
  • Hi Scotty,

    you had mentioned that,"In reading your latest response, I now understand what you mean in being able to clear the table, but not delete it.  That will work.  As someone who works extensively in Excel, but is a newbie when it comes to Access, I wasn't thinking of the DELETE query as an option.  I did try that and it certainly works."

    it seems that your issue is solved by the suggestion given by .theDBguy.

    if so then mark the suggestion given by .theDBguy as an answer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 8, 2016 1:34 AM
    Moderator