is there any way to put into the calculated field (in expression builder) conditions? What I need is something like
Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)
I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).
I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:
1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill
Here are my excel formulas that for the life of me I can't get to work.
Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))
Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))
Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO") ,"NO"))
Can I have calculated fields that refer back to a calculated field?
Is it possible to set multiple conditions across multiple tables using OR in a where clause? For instance, can you run where......
(table1 = 'test') or (table2 = pass) or (table2 = "fail) AND (class = 'SQL');
Been trying to crack this one for a while hoping someone on here might be able to help me. ;)
I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.
This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...
Any help would be greatly appreciated.
Graeme
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I have three columns where A is a number, B is a number and I want to make C = to the greater of the two. i.e.
Col A = 10, Col B = 12 therefore Column C = Col B or Col A = 12, Col B = 10 therefore Col C = Col A
I want to use an expression to compare 2 dates and calculate the date of the last set of accounts for a company. The user will enter(DD/MM) of the company year end e.g. 31/12. I then want to compare this with todays date (in another field) to ascertain if the month has already passed in the current year and hence calculate the last year end. i.e. If (current month>company month end), year end date is DD/MM/YYYY (where YYYY is taken from today's date), year end date is DD/MM/(YYYY-1). I am not a programmer and although I understand database theory (from the teaching perspective) I am not expert in developing databases.
View 1 Replies View RelatedIs there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.
Then the result is used to calculate how much loose coin will remain.
But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.
Access 2010 database.
I am trying to get an average from several numbered fields, which some may occasionally be blank.
=Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0)
I get an error message that says "The expression =Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0) cannot be used in a calculated column. This is a hypothetical expression being used.
The real expression is ...
([GC A1 - 2]+[GC B1 - 2]+[GC C1 - 2]+[GC D1 - 2]+[GC E1 - 2]+[GC A2 - 2]+[GC B2 - 2]+[GC C2 - 2]+[GC D2 - 2]+[GC E2 - 2])/10
This expression works great until a field is left blank, and then the average box is left blank.
The fields I am trying to add are Long Integer. Would that affect it?
We have our access database with a bit of a messy structure?
We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.
Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.
Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.
Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.
I am using Access 2010
I have calculated fields on my tables and used zero values as default to show totals. But when I view my report, all the zeros appear. Don't want those zeros to appear on the report.
View 1 Replies View RelatedThe interface being used is a main form with various tabs and a subform on each of these tabs.
There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies View RelatedG'Day:
I have a form that I created that allows the user to input an "ExhibitNo" and then a "PlacingNo" to allow each entry to receive a set monetary amount (based on values in a table).
The "ExhibitNo" has two references linked to it. They are "ClassNo" and "ExhibitorID".
My supervisors want me to make it so that when the "ExhibitNo" is entered on the form that it will not allow a monetary amount.
This is where I started, but it does not work. Does anyone have a better idea?
Private Sub Combo14_AfterUpdate()
Me.Refresh
If [ClassNo] >= 0 >= 5000 Then
If [ExhibitorNo] <= 0 >= 499 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If
If [ClassNo] >= 5000 Then
If [ExhibitorNo] <= 500 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If
If [ClassNo] >= 5000 Then
If [ExhibitorNo] >= 500 Then
[Premium] = 0
End If
End If
End Sub
Thanks
how to do multiple conditions, Nested....I have 6 conditions that i like to check,
Code:
if (condition-1) And
(condition-2) And
(condition-3) And
'[code]....
I'm trying to create an If/Then statement that needs to match to multiple values. My initial thoughts were to create a list but that doesn't seem to work the way I thought it would.
For example:
If Me.Field = 1,2,5, or 8 Then
Do 'X'
Else IF Me.Field = 3,4,6 or 7 THen
Do 'Y'
Else Do 'Z'
I haven't been able to get the syntax to work right. I'd rather not have to do 'Me.Field = 1 or Me.Field = 2, etc.' but if that's the only way to have it work right, then I guess it'll have to do.
I am trying to calculate [current status] based on multiple conditions. for example
Current status = A , if (w>0 and x=0 and y=0 and z=0)
Current Status =B , if(w>0 and x>0 , y=0 and z=0)
Current Status =C , if(w>0 and x>0 and y>0 , z=0)
Current status =D , if(w>0 and x>0 and y>0 and z>0)
Where A,B,C,D are text values and w,x,y,z are dates
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
For some reason, this one is driving me nuts so i would really appreciate some help.
This is kind of complicated to explain but here goes.
I am trying to create a query so that, based on certain conditions certain rows from the Form/Table are either visible or not visible on the report.
I have four fields involved:
Antic Dep$ (Anticipated Deposit Amount)
Antic Loan$ (Anticipated Loan Amount)
Date Dep Booked
Date Loan Booked
Basically i am trying to hide any records that in which the Date Dep Booked and/or Date Loan Booked are older than the current month...
I can think it through logically but cannot get my head around the query.
If Date Dep Booked or Date Loan Booked is not older than current month then show the row;
If Date Dep Booked is older than current month and Date Loan Booked is older than current month then hide the row; unless
If Date Dep Booked is older than current month but Date Loan Booked is not older than current month then show the row; except
If Date Dep Booked is older than current month and Antic Loan$ is zero then hide the row; but
If Date Dep Booked is older than current month and Antic Loan$ is not zero then show the row;
Then i also need to do the reverse for the Date Loan Booked, i.e.
If Date Loan Booked is older than current month but Date Dep Booked is not older than current month then show the row; except
If Date Loan Booked is older than current month and Antic Dep$ is zero then hide the row; but
If Date Loan Booked is older than current month and Antic Dep$ is not zero then show the row;
Phew! That probably makes no sense but i had to try.
Once again, i appreciate any help so that i can keep the remainder of my hair.
Switters
I have a Customer Issue form that writes the following into one table named Table1: Date, Customer Name, Ticket number, Agent, Issue and Comments. This is very simple.
All of this is filled in from a form that has links to 3 other tables for drop downs; Customer Name, Agents, Issues. This is working perfectly.
What I want to be able to do is generate a report based on a date range for a particular agent. Say 9/1/2013 - 9/30/2013 for John Doe.
For the report portion, on the form I have to combo boxes, one for start date, one for end date. I also have a drop down for selecting the agent. When I click a button on the form named Report, It will generate a preview of the report.
Here is the problem. I can get this to generate a report based either on the date range, which gives me all of the agents, or by agent, which gives me all of the dates. I can't get it to do both.
Here is some code that I have on the Report button:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent
This is the code that will let me choose the agent, but gives me all dates.
If I change this code to this:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
It will display all issues in the date range, but gives me all agents.
I was thinking I should be able to combine them with an AND or an & to get it to use both the agent and date fields, but I can't get this to work.
Something like: DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent And "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
or
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent & "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
I'm trying to get this query to return all records except those with category: 4 AND issue: 12
Instead I'm always getting ZERO category: 4 and ZERO issue: 12
Should not the parenthesis force this to be taken as a single "WHERE"?
SELECT ASSIGNED_TO, COMPLAINT_NUM, DATE_RESOLVED, CATEGORY, ISSUE
FROM tmptbl_tm_YTD
WHERE (CATEGORY<>4 AND ISSUE<>12);
Hi All,
My database has two tables:
1. Complete Address Data
2. Address conditions.
Table 1. has verbose customer [address] data like 11 smith st, 14 Kent Rd etc.
Table 2. has [std] address conditions, ST, RD, Street, Road etc.
How can I query ALL records by field [address] in table 1 by using ALL [std] address conditions in table 2?
The address conditions can occur anywhere in the [address] string, so I was thinking about maybe a CONTAINS condition, but I'm not sure
I want to open a filter form with mulitple conditions.one of them is a date condition.
I'm using access 2013 and the code below:
Code:
DoCmd.OpenForm "Edit_Mission", acNormal, , "[Report_Date]= " & Me.Date & " And [Supporter_Name]='" & Me.Supporter & "'"
it's opens the form but with no data. I also tried the # and it's still didn't work.
I have a main form which has couple of subforms bound to one main table. One of the subfomrs is about employment info. It has 3 TextBoxes: (Job Start Date), (Employer Name), and (Wage).
It has also 3 ComboBoxes: (Job Type [Full Time or Part Time]), (Hire Status [Permanent or Temporary]), (Quarter [1st, 2nd, or 3rd]).
I want to force users to fill all these 6 fields if they put any value in any one of them. I tried to set a Validation Rule in the TextBox/ComboBox’s property including IIF statement condition, but I did not succeed, and these rules did not work.I put the following code in the subform’s (After Update), (Before Update), and (On Current) events which works very well when I keep the (Wage) value 0 and move to a new record or to another subform:
Private Sub Form_AfterUpdate()
If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
MsgBox "You did not put how much is the wage."
Cancel = True
End If
End Sub
[code]....
how to make a similar validation rule for the other TextBoxes and ComboBoxes.