Sum Field Values Only If Another Field Values Meet Criteria
Mar 20, 2015
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center
Job Type
Hours Paid
Pay Code
Earnings Amount
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate() If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then Me.Test1Result2.RowSourceType = "Value List" Me.Test1Result3.RowSourceType = "Value List"
I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)
Tables: * Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player) * Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box) * Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box * Venue - similar to Competition table * Opponent - Similar to above two tables * Match - MatchID, Competition, Venue, etc (form corresponding to table attached)
Forms: * Player form * Match form
Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).
i want to add a control in that form that sums up and aggregate all the values of field called [amount] based on the value of [Name_Patient] as criteria
I have several fields that I fill in on a form and they all need to add up in the last field. The first way that I tried this worked good for the initial data entry on the form. However if I need to edit one of the fields in the form then it resets the TotalCost field and I have to enter all of the numbers again. Is there some way that I can make this work? Or is this an Access quirk I will have to live with?
I tried to do this two different ways.
Here is the code for the first method:
Option Compare Database Dim C1 As Long 'For Total Cost Dim C2 As Long 'For Total Cost Dim C3 As Long 'For Total Cost Dim C4 As Long 'For Total Cost
[Code] .....
This way works great the first time that I tried it, I had to re-enter info in all the fields if I wanted to change one.
Here is my second method:
Which didn't work, i received an error message:
"The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure."
And then nothing changes.
Code: Option Compare Database Dim C1 As Long 'For Total Cost Dim C2 As Long 'For Total Cost Dim C3 As Long 'For Total Cost Dim C4 As Long 'For Total Cost
I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
I really need some help with this! I have a table with a field called [days.out]. It contains values from 1 to about 350, non sequencial with duplicates. I would like to run a query that would create a new field called [days.grouped]. This field would contain the word "1-15" if the value was between 1 and 15 in field [days.out] and "15-30" if value was between 15 and 30 in field [days.out], etc....
I am Access novice but am creating a databasr of over 250 names, addresses etc for a later mail merge. The problem i have is that the fields for phone/fax numbers require me to input unwanted data, thus creating a number that is longer than the actual number. I can i tell Access to disregard the unwanted data and set the fields to the value i require not want Access requires ?
I have a DATE/TIME field in a table. I want this field to be optional. But when I try to insert a record without a date value for this field, the SQL fails. How do I fix that ?
Also I want to display this field only if there is a valid date...How can I do that?
I have a numeric field called FileNo and an autonumber field called FileID in a table called tblFile. I also have a lookup numeric field called FileTypeID (with values 1 or 2 or 3) on another table called tblFileType.
I need the value of FileNo which I am showing on a Form frmFile to change dependant on the value of the FileTypeID i.e. If FileTypeID = 1 FlieNo should start at 100 If FileTypeID = 2 FileNo should start at 200 If FileTypeID = 3 FileNo should start at 300 Then when I create a new record I need to increment by 1 the value of FileNo according to the FileTypeID
I've setup a button and attached this code to its onclick event but it only works as long as I don't change the FileTypeID
Dim B As Integer Dim H As Integer Dim N As Integer
Do While FileID > 0 DoCmd.GoToRecord,,acPrevious If FileTypeID = 1 Then B = FileNo Else If FileTypeID = 2 Then H = FileNo Else If FileTypeID = 3 Then N = FileNo End If End If End If Loop
DoCmd.GoToRecord,,acLast If FileType = 1 Then FileNumber = B + 1 Else If FileType = 2 Then FileNumber = H + 1 Else If FileType = 3 Then FileNumber = N + 1 End If End If End If
I have a list of Vehicles in which each have their own maintanance schedules. Most of the maintanance for the Vehicles overlap. Instead of entering the same information over and over I'm assigning each type of vehicle a code number (example '05 Cargo is a 1, '04 Cargo is a 2). Now what I want to do is assign each maintanance the vehicle code, for instance I have 13 different types of vehicles that all need oil changes, so I want to assign the Oil change maintanance 13 codes. Is there a way to assign that maintanance 13 codes without having to enter it 13 times??
Hey, I'm new to microsoft access, and I could do with a little help please :) . On a database I have created, I have a table with the field "Results" in it. This field has been set up in the Lookup properties to be a choice from "Win" "Loss" or "Draw". My question is, what is the expression I would use to count the total number of records in my form with "Win" selected?
I tried to set up the DCount expression, but this gave me some odd results.
Currently my database is set up like this,
TBL_Match_Results: Fields:
GameNumber (Autonumber + Primary key) Date Result (With the choices above)
Then I made a continuous form from this table, and on the Form Footer I had a text box with the value,
I had hoped this would simply give me the total number of records with a win in them. However, insted the value changes depending on which record I have selected on the form. It is "10" if the selected record is a win, or "0" if it is not. I'm not sure if the problem is the poorly written expression (I'm not 100% sure how to work them), or if I'm even using the right expression (I was only told to use DCount). If anyone could shed some light on this, I would greatly appreciate it!
EDIT: I've just realised where the 10 is coming from, it's the number of records in my table, when i added a new one it changed to 11.
I have a db with a field that I need to split and put values into two other fields. The format of field1 is either a-b, aa-b, aa-bb, or a-bb. I want to take the value before the "-" and put it into field2 and then take the value after the "-" and put it into field3 and then delete field1. Is there a straightforward way to do this? I want to do it in the query design grid so let me know what should be in the "criteria" line versus the "update to" line.
I'm using Access 2000 and have approx 5500 records to convert.
I am using a form where I select values from combo boxes to enter parameters for a select query. It works fine where the table queried only has one value in the field concerned. However, it does not work on the fields that have several values (entered through a multi-selection box). Are there any ways to query fields with a multi-selection? Thanks, Niels
I am trying to create a DB that would calculate the totals of different projects and also calculate the global total (i.e. the total of all the projects combined together)
So far I have created a query that can calculate the total of the projects in a field which I have named Total1. But now I want to calculate the sum of the field Total1, i.e. add all the totals of the projects up.
Sorry my explanation does seem quite lengthy, any help would be much appreciated.
Hi I'm trying to produce a database that will convert Ebay download files to a format more suitable for import into Sage Line 50. I have a table "Customers" that has all the customer information in it.
One of the fields is "Country", this lists the full name of the country the customer lives in. However, Sage requires countries to be listed as a two letter abbreviated identifier, GB, US etc.. I have another table "CountryCodes", with two fields, the countries full name and its abbreviated identifier. I need to be able to compare every "Country" field in the "Customers" table with with the "Country" field in the in the "CountryCode" table and change it's value accordingly. I also need the "Country" field to change to fixed value (ZZ) if the country is not found in the "CountryCodes" table.
I'm really hoping this is just a little bit of code here, but here's what I'm trying to do, without success so far...
I have a cascading combo box field called [Name], which is linked to a table that only has 3 columns, ID, Name, and Number. The combo box displays the Name column, with the other 2 hidden. When a name is selected from the list, the neighbouring field [Number] is auto populated with the Number column in the combo box. This works beautifully in saving some data entry time. My issue, however, comes when I try to export data into and Excel format. The two fields, [Name] and [Number] display as numbers instead of their values. What do I need to do so that the names "stick" as words, not as numbers?
I know this must be related to the hidden ID column, and that forms don't store any actual data. I tried to modify the table design, but then it throws off the auto-pop function on the form. I thought it may have something to do with joining the table fields to each other, but I couldn't get that working either. Any suggestions would be welcome. Thanks.
