Addition of Data from Queries to Tables

[Derrick_0690]

Good Afternoon Everyone,
My question is regarding the automatic calculation and addition of values to a specific table. Not sure if this is possible or if this is super easy to accomplish. Any input would be appreciated.
First some background info:
Every day the database is updated.
First we import data, from a specific date, from an Excel spreadsheet to a table in Access. This data is just simple figures.
However, we also need to calculate percentages from this data that is imported and store it in a separate Percentages table as well.
So what we do is run an Append query, which appends the values from a separate query that calculates the percentages from the imported data, to the Percentages table.
My question for you is:
Is there a way that, upon importing the original figures into Access, for the database to automatically calculate the percentages and append them to the Percentages table?
My goal is to eliminate the physical steps needed to add the percentages from the query to the percentages table. These steps include the execution of the Append query
I do not know any code/programming. I just know how to use wizards and the expression builder.
Hopefully that made some sense,
Regards,
Derrick_0690

 

[bparkinson]

It's a database best practice not to store calculated or derived data. The correct approach is to create a query that calculates the percentages. That query will do what you are trying to accomplish - eliminate the physical steps.

 

[Derrick_0690]

I see. Alright thank you.

Just for my reference, why is it not a good practice to store calculated or derived figures in tables?

I'm pretty new to access and im teaching myself as I go along. I dont know any code, just wizards and expression builders.

Thanks

 

[bparkinson]

I see. Alright thank you.

Just for my reference, why is it not a good practice to store calculated or derived figures in tables?

I'm pretty new to access and im teaching myself as I go along. I dont know any code, just wizards and expression builders.

Thanks

For a couple of reasons. First, if the data used to calculate a total, for example, changes, there must be code in place to detect that, recalculate the data and persist the new total. Second, its wasteful of space.

Compare that to doing the calculation in a query. The calculation is made each time the query is run, so it virtualizes the summary data just as if it were in a table, but without the overhead and required extra code.

That said, sometimes it is correct to violate these principles, but not often in my experience. For example, it's not uncommon in order entry systems to see the customer name, ship-to and bill-to addresses stored stored in the order table.

 

[Derrick_0690]

Awesome!

Thank you for clarifying

Regards,
Derrick0690