Related Topics

Paye - Calculate At Runtime Or Store The Deduction?

Payroll - Complex If Statement for PAYE
I am trying to build a small payroll and so far everything has been OK until now. The challange I have is calculating the PAYE (Pay As You Earn) personal tax from the employee total earning.

I my country the tax rate for PAYE is applied on a graduated scale as shown below:

- First $30,000 at 5%
- Next $30,000 at 10%
- Next $50,000 at 15%
- Next $50,000 at 20%
- Above $160,000 at 25%

So what I want is for my textbox "txtPAYE to evaluate "txtEarning" based on those garduated scale with an IF statement. I don't know how possible that is. It would have been very strait forward for me if it was for a tax that has a constant rate

Query Help: MS Access and FileMaker Pro
I am writing my first database app (windows form in c#) and I am using an access database. I have 2 tables with the following fields:
payroll: (Store, lastName, PayFreq, SSN (PK), HireDate, EffectiveDate, TermDate, DeductionCode, Deduction, NAD CHAR) AND hrconnection: (Store, lastName, CoTime, Plan, Dependants, HireDate, EffectiveDate, TermDate, SSN (PK), Deduction).
I want to find the records based on SSN that are in the payroll table and are not in the hrconnection tables and then find the record based on SSN that are in the hrconnection but are not in the paypal table.

Store Value or Calculate
I started my project several month back to relearn Access after being away from Access for many years. One of my original question was about storing a calculated value and after some discussion I decided to store the value. Now I need to revisit this decision.

I receive, after the fact, sales information which I import, add some extra information, and produce some meaningful reports. There is onearea that I am doing circles on and hence my question.

This represent the quantity on hand by product group by sales day. The value to calculate is the quantity on hand at the end of the day which will then be used as the opening quantity for tomorrow.

Option 1: Should I store this end of day quantity and then run a query, or a dlookup, to find yesterdays end of day quantity to calculate todays end of day quantity?


Option 2: Should I create some starting amount and then run a query to calculate yesterdays end of day quantity?

The basic structure for the stock table is:

Calculate Average of a field
I have two tables, the first stores data related to observations visits for patients and the second stores data used to create an executive dashboard monthly. What I need to do is take one of the fields in the observations table and average it out and store it in the dashboard table for monthly and YTD reporting.

Does anyone know how to calculate the average of a field in onetable and store it in another?

I have the SQL that accurately calculates the average of the field, I just can't get it to store in the other table's field.

Report with Running Sum by week
I have a report is built off of a query that contains the following fields:


I would like to be able to keep a running total of how much the employee has contributed each payroll based on his deduction. The payroll is bi-weekly so there are 26 periods in the year.

the payroll dedution amount is a calculated amount based on the annual contrbutuion/26

I was hoping that I could have a field on my report that would allow me to key in the week number and it would calculate the Sum of his dedcutions but I can not find a way for this to work.

Will this work or is there a better way to go about getting this information onto my report.

DSum in Query
DSum in Query
I have a financial database that has many Tables, each table has many fields primarily holding currency data.

I understand from research that it is incorrect to calculate and store a ‘result’ in a Table, so, I’ve created a Query in order to calculate and store my result which is the TOTAL of all the currency fields in a given record. From my research, I believe the best way of doing that is to add an extra currency field to my Table so the field name is there for when I build my query (still holding with the idea you shouldn’t store a result in the Table itself).

I’ve entered the following formula into the Query by using the ZOOM feature (it is entered into the filed column [WkCashInUnTOT]. The syntax looks like this:

Using a Query to Set a Field equal to a Sum
I'm using Access 2007, and I want to calculate the sum of a field in one table and store that sum in another field in another table. I don't expect the original field to change much so I don't want to use a query just to calculate the sum and display it, I want to actually store it and display it whenever I want. Something similar to what you would do in Excel, except with sums from different tables. And if the original field does change, I want the sum to also update and the field in the other table to update as well.

So, to simplify, here is the above broken down:

I have Table1 and Table2 and Field1 and Field2.

Table1 --> Field1
Table2 --> Field2

I want the Field2 = Sum of Field1. I've read everywhere it's better to just calculate the number rather than store it in a table, but I don't need the number to be recalculated since it will most likely never change

Store form calculations into table
I have a simple but large database to calculate the trade and selling price as well as store important information about our products.
I have imported an excel file with the cost price and the trade and cash price already calculated. In my form I have made formulas to calculate the trade and cash price if the cost price needs to be changed, but I do not know how to save these values back to the table (update the trade and cash price when the cost price has changed).
I know a bit about Access, but nothing about macros and VBA.

Have function to calculate work days, what do I do with it?
I am trying to set up a billing system for reservations and need to calculate the number of work days between my reservation dates. I got the code for a function off the microsoft website,

Would it make sense to have a textbox that would populate a "Workdays" field in my reservation table, and then use that field in a query to calculate the reservation cost? Or do I even need to store the workdays value? Maybe on the after update event for Dateout and DateIn fields on my form?

Needing more info about using Access runtime vs. ACCDE
I'm trying to understand Access Runtime, as compared to an ACCDE file.

1) If I use Runtime, does it eliminate the need to hide the navigation pane, menus and ribbons?
2) Does it create an executable file that can be used on any computer, regardless of whether Access is installed or not?
3) Does Runtime need to be previously installed on the Users' computers?
4) Is a Runtime file a smaller footprint than an ACCDE?
5) Are there disadvantages to distributing a Runtime file instead of an ACCDE?

The sources I've found so far on Runtime are a bit confusing.

Access Runtime
Access 2007 & 2010 runtime. Has anyone used the runtime versions? I have an access report that uses a csv file for data. It works fine under the full version but the runtime does not open it

Calculating percentage (%)
I am wondering if it is realistic to create a query that will calculate the total % of a field based on four other field.


First field which will calculate the % of Q1
Second field which will calculate the % of Q2
the third field which will calculate the % of Q3
and the fourth field will calculate the % of Q4

AND the last field (the fifth one) should be the sum of Q1, Q2, Q3 and Q4 percentages and which should be equal to 100%

Acccess 2007 Runtime and Queries
While I've created MDE files before, I've never used the Runtime for Access. I was wondering if the runtime limits you to only running existing queries, or can you modify them using the runtime too?

Database Runtime Question
I am designing a database in Access 2003. It is almost complete other then the fact I need to create a runtime for it to enable PCs without Access to
view the database. when the runtime is created and installed on the PCs that need it, if I need to add or make changes to the database will the users with the runtime program be able to see these changes or would
I need to create a new runtime for them to see the changes?

Once they have the runtime installed, updates are simply a matter of giving them updated Access files (MDB/MDE). In that sense it is no different than using regular Access.

design problem: meter overflow
I essence my data fields are a date and a corresponding meter value. Unfortunately the meter only allows for 4 digits, so sooner or later it will overflow. The aim is to calculate
average use over an extended period of time. Is there any best practice to store and handle this kind of data?

If I just store the meter's face value, then the application has to scan sequentially all data to know how many overflows occurred during the interval in order to perform the desired calculation.

If on the other hand, through a data entry interface, I store the adjusted difference between to readings, then I have to sum all values for that interval.

How do I open access in runtime without security warnings?
I am in the process of upgrading from Access 2003 to 2010 the current databases are split and users run the application in runtime.
I have been able to upgrade successfully except for the Security Warning I get when opening the database in runtime.
Have tried changing settings in the Trust Centre, this has fixed the Full Access security but seems to have no effect on Runtime.

2010 Runtime ADODB Connection Failure
I have a routine which connects to MySQL database with ADODB connection. This worked in 2003 and 2007 runtime but throws a 430 error on 2010 runtime. It works in full access 2010. Has there been a change of policy to restrict this type of connection in the 2010 runtime?

If I use a linked ODBC table instead of ADODB the runtime fails with "Runtime Error". It still works in full access

Runtime access 2010
I need a beginners tutorial on the use of Runtime - I have access 2010 installed - I think I downloaded and installed access runtime for 2010 - for the life of me I cannot find any references to runtime in access or in windows list of programs.

I tried to publish the DB but all I got was a accde extension not the accdr.

Access 2007 runtime not visible in Access 2007
I installed Office Enterprise 2007. Then I installed DEveloper Exrtensions, then Access Runtime 2007. All appear to have installed OK - they appear in Program and Features.
Instructions on runtime packaging tell me that a Developer option should appear in the resulting drop-down when I click the Office button (top-left round thing) when I have a d/b open in Access. It's not there! I have un-installed and re-installed the extensions and the runtime - still nothing!
I do have Runtime 2002 (XP) on the same PC, is that relevant?

How to Formulate this Access Query
I have a access database table with sales data in it, detailing sales rep, customer, date of sale, sale item etc.

I need to generate a query which will list which customers haven't brought off us in the last week, month, 6 months (input variable) and the sales rep responsible for the store.
customer name, rep, date of last sale/ most recent sale
store a, tom, 17/3/2011
store b, joe, 1/1/2011

Then I know joe needs to go to store b and make a sale because they haven't purchased anything from us in the last 3 months. My problem has been calculating the lastest sales date. In exceli would calculate the number of days since sale for each company then use the small function to the most recent sale.