(This means: Between the amount of 0 and 100, the eventual amount equals to 5. Between 100 and 500, the evebtual amount is 10. etc. etc.)
Over the amount of 50,000 for every 5,000 the amount goes up, the eventual amount (column c) goes up 10, with the condition the eventual amount (column c) should not be over 500.
My current If-statement: Eventual Amount:
IIf([Amount]<100,5,IIf([Amount],500,10,IIf([Amount]<100,15 etc. etc.)))
Is there an easier way to do this... It's going to take mighty long to do it like I am currently doing it.
Thanks a lot
Regards
kruger101
One file is numbers I averaged over the past 3 months. The other file is actual numbers. Not all zip codes are in each file. I need to figure out the difference between the 2 and add the difference to this months numbers. So I created a query and found there were 94 records that matched based on zip cod and customer type. Then I found the difference in sales amounts. Now I'm stuck at figuring out which zip code and customer type don't match in the 2 files. So if zip code 10001 with customer type S isn't in the actual data for this month I need to add the zip code and customer type and have it be -303.23.
I’ve posted on here a few times lately, as I’m creating a database at work. It is going to be used to keep various information about new persons applying for jobs, and I want it be viewed/sorted by either 'Job Grade' or 'Job Title' I keep doing a lot, and then getting stuck on certain things. I’m stuck again, but I think it’s a little more difficult this time…
What I would like to happen is have the database open up, and a screen appears with two choices – ‘View by Job Title’ and ‘View by Job Grade’. When you click one, a new screen appears and a list of some sort appears with all of the job grades / job titles (depending on which button is pressed) on that are currently in the database.
From here, you can choose a job grade / title and it will bring up the form I have created – but only with the records that have the chosen job grade / title.
So for example, there could be 20 records in the table. 5 with job title ‘Manager’, 5 with job title ‘Assistant’. Then there could be 5 jobs with grade ’1’ and 5 jobs with grade ‘2’. When you clicked on ‘View by Job Title’ and then choose ‘Manager’, the 5 records with the job title ‘Manager’ would come up.
I hope I’m making sense so far.
The data would all be stored in one main table, and I already have the form created, so it would be good if I could use this.
Is there any way to do all of the above?
If anyone could help at all, it would be very much appreciated.
I have a database for my company which I am making.
The products are hydraulic cranes and come in numerous models (or sizes), in turn these models come in numerous versions, 5 different ones in all, lets call them for simpleness 1-5. However, some cranes come in versions 1-3 only, others 4-5 only, some all versions and no-doubt some a random mix!
At present I have a table with the fields "Model" (Text), and seperate fields for each "Version" (ie. 1, 2, 3 etc), these are Yes/No tick boxes. Ticking a given box for any given model, means that only these options are available on a form. Ie When you select a "400" crane, you can for example only select Version numbers 1, 2 and 3 - those that are checked in this table.
What I need to do is when selecting each individual model on a particular order is to display 2 figures hydraulic oil flow-rate and also hydraulic pressure required from a reference table I assume. These figures are different for every given model/version combination.
The difficult part as I see is relating the yes/no check box from the table above to version number on the reference table.
How difficult is it to make a sychronisationfunction? I have a meetings database. Sometimes the meetings are enterd localy and need to be sychronized with the main file. Is this hard to do? where do i start.
Hi Everybody. I've been nosing around here because I have this difficult problem with a database design. I modelled my program in UML so I have a class diagram. Now I want to create an access database out of it, but this is too hard for me.
It's about a school project for flowers. Every year, they make a cross plan. This cross plan contains crossings. Many crossings. And every crossing exist from 2 genotypes. A mother and a father. With this crossing several new genotypes are created. How on earth do I realize that in a database? A plant is male as well as female so you don't need to indicate which sex it is.
Further more I want a genotype to be judged on his characteristics by a user as much as he wants to.
Well...I hope someone can help and if you have questions about it don't hesitate to ask.
Where [WeekUren] contains multiple references to unique records in [Projects].
[WeekUren] has the following collums which are important: -ProjID -Week -Persoon
Where these three always form an unique combination. For instance: 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 1 | 1 | 2 1 | 2 | 2 2 | 1 | 2
Saying: Person1 will work week 1 till 3 on Project1, Person2 will work week 1 and 2 on Project1 and week1 on Project2.
Weeks are here relative to the project start date (a value of [Projects]) and always start with and increment with 1. So week1 of Project 1 and 2 don't neccesarily fall on the same dates, hence Person2 is still able to work both Projects.
Bear with me here.....
Now I need to select all duplicate records (where people are assigned to two (or more) projects in the same real week.
Real week is defined by: Code:DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId)))
And the Query to convert [WeekUren] in to the relative form is: Code:SELECT WeekUren.Id, DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId))), WeekUren.Uren, WeekUren.Persoon, WeekUren.ProjIdFROM WeekUren;
The query to print out duplicates is ofcourse: Code:SELECT *FROM WeekUrenWHERE (((WeekUren.Week) In (SELECT [Week] FROM [WeekUren] As Tmp GROUP BY [Week],[Persoon] HAVING Count(*)>1 And [Persoon] = [WeekUren].[Persoon])))ORDER BY WeekUren.Week, WeekUren.Persoon;
Sofar so good, combination of the two however give me a syntax error. Hence my question here how to construct such a diabolical Query. Thanks!
I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example: I want this: AUTO ONE CLARKSON CLARKSON WA AU006495 to be recognised as: car parts
or with this tranaction: CLARKSON MINI MART CLARKSON553908 look for "mini mart" and return supermarket
This step will make classifying transactions a lot simpler and user friendly. thanks
I have Two fields with DateWorked and EmployeeNumber. All I want to do is count the number of employees that worked on a specific date. In the employee numbers I have 4 dummy numbers that I don't want to count in my query (00001-00004), but will have entries almost each day. The rest are 5 digit numbers.
Some employee will work some days, but not others.
So I set up my query with:
EmployeeNumber Table Total: Count Criteria: >4
I did that think that it would count all employee numbers great than four, but all it seems to do is give me the count of days that more than 4 employees worked.
What am I doing wrong? I feel like this is simple, but after some of the more complex things I've done over the past week, this seemingly "easy" thing has me stumped.
Alright, so my pride and joy and departmental savior database has been fully implemented and people are so pleased with it that they *all* want to use it at the same time. Initially, I made it custom for three peoples' UI preferences, but since it's such an intuitive design, other staff now feel comfortable using it instead of passing the work down the line to the one person who knows how to use that "newfangled thing".
A couple years ago, I used FMPro and allowing for multiple users at once was an annoyingly complicated process dealing with permissions and rights and accounts and passwords and yadda yadda. Is it any easier to set up in Access? Does anything special even need to be done to allow for multiple simultaneous users?
This is a toughie (i think so anyway!). I'll attempt to explain!!!
I start with a CONTINUOUS form in my DB that shows all readings for a single given customer's connections. E.g. Customer A has 5 connections and each connection has say 3 readings. Thus this query which uses joins between the Customers, Connections, and Readings tables, would return a list of all readings for each connection, so in the above example: 1*5*3 = 15 records.
Not too tricky so far, but then what I want is for each connection to have just one line showing the latest reading (easily achieved by use of SQL Aggregate Max function on the date field coming from the readings table) TOGETHER WITH the last-but-one (next most recent) reading. So back to the example taking Customer A's connection 1, the row would be as follows:
Cust Conn CurReading PrevReading A 1 750 500
Where the CurReading value (750) comes from a different record than PrevReading (500).
I've tried all sorts of ways (subqueries etc.) to achieve this without success. The main problem being that any sub query would require parameters from the current record's fields, which seems not to be possible. Can anyone help or is this simply not possible in MSAccess Forms. If it isn't possible anyone have any suggestions as to an alternative way?
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
This part works but I need to add a second one
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
So I want the text box to be RED if the status of both Ch A and Ch B is 0 but if either one has a status greater than 0 then the text box would be green.
I'm using this code to do some calculations on a form. This code works fine as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?
If Me.[qual] < 4 And Me.[completed] < 61 Then Me.[GtoG] = [GtoGtotal] Else End If If Me.[qual] < 4 And Me.[completed] > 61 Then Me.[GtoG] = Me.[qual] + 61 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Else End If If Me.[qual] > 4 And Me.[completed] < 61 Then Me.[GtoG] = Me.[completed] + 4 Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If If Me.[qual] > 4 And Me.[completed] > 61 Then Me.[GtoG] = 65 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If
I am trying to do something really simple, but my lack of experience in Access has bitten me. I have a report... there is a field called payor_code and one called bill_time. These are both based on tables.
The variable is payor code... it could be 2 different things.
All I want to do is make a calculation for TOTAL that multiplies the bill time, times the correct rate. (which is determined by the payor code)
I want to basically do this:
If payor_code = "02" Then total = Bill_Time * 1 Else total = Bill_Time * 2
Question..... i have a report in access on which grades of student are shown, like the following example:
Lifescience 6 Brainstorming 7 learningdev. 9 communication 5
My goal is to, next to the grades, also to put the grades as words...
Lifescience 6 six Brainstorming 7 seven Learningve. 9 nine Communication 5 vife
I have no clue actually how to do that, i of course thought of an IIF statement, the only thing i know is the crystal reports syntax, but in this case i am not sure how to 'translate' it to the expression on the field. (that is: i think i have to use the field where the grade comes from?)
Thanks for this, i think, relatively simple question........
Ok guys, I am stuck. If I have the following codes that works fine when I separate each "IF" statement, but I was trying to combine it all into one code. When I combine the code, I continue to get the msgbox even when the criteria has been met. Please tell me what I am doing wrong. Just trying to simplify the codes.
If Me.cboFat = 1 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 2 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 3 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 4 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If (Me.cboFat = "1") Or Me.cboFat = "2" Or Me.cboFat = "3" Or Me.cboFat = "3" _ Or Me.cboFat = "4" And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken,Closing Date is a required field!" Cancel = True End If
I'm trying to calculate the value of a field based on the other columns in the table. It's working in a form but I need to save the value in the table. I'm selecting the default value of the field and entering the following:
IIf(DateDiff('m',[Vesting Start Date],Date())>12,0.25*[Number of Options]+(DateDiff('m',[Vesting Start Date],Date())-12)*0.03*([Number of Options]-(0.25*[Number of Options])),0)
It doesn't recognize any of the column names like Vesting Start Date. Is it possible to calculate the value of a field based upon the other columns?
I have a nested IIF statement in one of the parameters of my query that checks for the value in an option box and returns the results for a specific record based on the value. This works great for what we need and we've been using it for over a month without a problem.
I just found out that there may be instances where management might want to see a group of records with combined data. I've racked my brain and searched this forum and have not come up with a solution yet.
In the first line is where I tried expanding the criteria to a group but I either get blank results or an error depending on how I've tweaked it.
This is an example of what I'm after:
IIf([Forms]![frmRejectopt]![optgrpWC]=2,([tblProcess]![ProcessID]=1 Or [tblProcess]![ProcessID]=9 Or [tblProcess]![ProcessID]=10 Or [tblProcess]![ProcessID]=11),IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))
I hope I was clear in my explanations. Thanks in advance for your help.
