I am new to microsoft access. I am trying to develop a data base that will store data as well as return means and SEMs. I have multiple parameters entered into an access table. The fields are Group Number, MAP, AP, and KV and are stored in a table called "Source Table". I have a query to return the averages for each group, but I am having trouble making a query to return the SEM. the formula for SEM is
SEM = Standard Deviation / (Count ^ (1/2))
This should be easy because access will return Stdev and Count, but when I try and enter the formula into expression builder, all hell breaks loose. Any advice would be greatly appreciated.
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?
I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.
I have tried using the iif formula without success. Here is what I want to calculate.
I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.
If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....
If both fields are equal then score is 4.0 .05 score is 3.5 1.0 score is 3.0 1.5 score is 2.5 2.0 score is 2.0 2.5 score is 1.5
I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:
Transaction ID Order Number Customer ID Title ID Date Status
I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?
Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.
Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.
I need a formula that will give me the count of the number of files I scored 3.0, 2.5,2.0,1.5, and 1.0. These scores are in one field called Claim Quality Index.
What I am trying to do in a query is to count the number of files I scored a 3.0 and in the next column of the query give me the count of the number of files I scored a 2.5 - same for score 2.0, 1.5, and 1.0.
I have chosen the count in the query but I cannot get the criteria to only give me the above. I believe I need to use the expression and name the field CQI3: ????(formula that I cannot get right.
Can anyone help - I have tried various ver. of the IIf formula but to no avail.
Could anyone give me information on learning how to use the formulas in Access 2003? A good book, site..etc. Example I need to know the formula that takes 20061015 and puts it into 10/15/2006 format. Any help is appeciated!
I am trying to Automate the creation of a quote number. I plan on making a form that contains a SalesID ComboBox and a Command button that opens a new form. On Click I want the new form to open a new form and insert the created quote#
Basically the number needs to consist of:
The letter "Q-", "Date", "SalesID" (selected from the drop down menu) and "-#" (sequenced number)
this should give me something like this:
Q-13107BOB-1 Q-13107BOB-2 Etc.
How can I do this?
Could someone provide a name for what i am trying to do so I can look it up on Google?
Hello, I have a formula on my report in a text box that just shows the total delay for a crew by using the supervisors name. This information isnt stored anywhere its just to show a percent on the report! the formula is:
I have a database that contains a few tables and queries etc. I made a copy of this database so that I could change some of the VB code with out affecting the database information. I have completed all the code and now I want to input those old tables into my finished verion. I am not sure how to do this. Any ideas on how I could do this would greatly be appreciated. Thanks.
Hi I really need to insert a movie in my database. I have tried inserting a Windows Media Player object in a form, but I dont know how to link it to a movie on my computer. Please help me with this. By the way I'm using DAO, if that helps at all.
Please help me as soon as possible, I'm in a hurry Thanks in advance
i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas? thanks briar
Ok, I know this is already part of another thread, but my problem is a bit more complex...
The database I've been designing catalogs traffic signal equipment at every intersection in a 12-county area. There's about 480 intersections in the database. My boss pointed out the need to have the intersections listed in geographic order. For example, if I have three intersections on IL Rte. 5 - John Deere Rd, Bauer Parkway, and Industrial Park Rd, they need to be listed in the order in which they exist on the road (from east to west or north to south). Alphabetically, the intersections would be Bauer, Industrial, John Deere. But, as you drive down the road (from east to west), the intersections occur as Industrial, Bauer, and John Deere.
I can sort the intersections according to their "corridors" without any trouble, but I have to force the geographic order of the individual intersections by assigning each an index value (like "1", "2", and "3"). Better yet, I can assign the indices in multiples of 10, 100 or 1000, (1,2,3 becomes 10,20,30 or 100, 200, 300, etc.) and guarantee there will likely always be a sequential position available in the geographic index for a new intersection. Plus, I can always pick the "middle" of the range for each new insertion. For example, if Bauer has a geographic index of 200 and John Deere's is 300, then I can give Zebulon a value of 250 - leaving equal room for insertions before and after Zebulon in the future.
Pulling this off isn't hard - I know how to do it. The problem is running out of positions in the index. In the case of my database, it is unlikely we'll ever add more than two or three intersections between two existing ones, so using multiples 100 is probably best. In this case, I can add at least 6 intersections before I run into the possibility of having two intersections whose indices have a difference of only 1. Using 1000, the value goes up to 9. Each multiple of ten gives you 3 more entries (minimum) before encountering consecutive indices. I guess the inherent problem is figuring out a more "infinite" way of accomplishing this. I don't readily need it, but on the off chance that I do end up with consecutive intersections (that I have to insert between), it'll require manual re-numbering to fix the problem.
That's my solution, but there may be something simpler. Any thoughts?
Can anyone tell me what i'm doing wrong with this insert statement?
INSERT INTO TBL_PROCESS_NAME VALUES( ' & [Forms]![frmIntroduction]![txtProcess_Name] & ' )
the form is loaded when the query is run, i've tried taking out the & and the ' but to no avail, all i get in the table is [Forms]![frmIntroduction]![txtProcess_Name]
The security table is made up of two primary keys: thing, personorgroup When i run this statement to insert into the security table
INSERT INTO security (thing, personorgroup, accessrights) SELECT '252600649', '4020', '255' FROM PROFILE WHERE not exists(select * from security where security.thing = '252600649' and security.personorgroup = '4020');
I get this error: Server: Msg 2627, Level 14, State 1, Line 25 Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'. The statement has been terminated.
Anybody know how i can perform my insert successfully? :D
i am trying to achieve a simple thing (at least it seems so to me!)... I am trying to add a new record to a table (LP_Product_Name) with only one field (Product_Name) as a part of a 'on Lost Focus' event of a text box (txb_ProductName). Code:Private Sub txb_productname_LostFocus()' Declare Variables Dim db As Database Dim rs As DAO.Recordset Set db = Nothing Set rs = Nothing' Assign Values to Variables Set db = CurrentDb() Set rs = CurrentDb.OpenRecordset("LP_Product_Name")' Enter New Product name to the table With rs .AddNew .Fields("Product_Name") = Me.txb_productname End With ' Close variables Set db = Nothing Set rs = Nothing' Restore Visible formMe.cmb_productname.Visible = TrueMe.cmb_productname.SetFocusMe.txb_productname.Visible = FalseEnd Sub When I type something in the textbox and shift focus to another field, nothing happens... no error message and no added value to the table! I dont know what is going on. It just doesnt work!
On the same form I also have a combo box that uses the same table (LP_Product_Name) as its rowsource. I want the Combo box to immediately show this added value.
HI, I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow. my form made of a combination of two tables attributes ad when I enter say:
name tel number address car car reg booking time
then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking. thanks
Hi All, This should be a simple thing however Im having some problems. Im trying to insert a record into access. I can insert everything except the date. I cant seem to get the right format. Below is what Im trying: Code:strSQL ="INSERT INTO GISrequest (givenName, surName, email, division, title, purpose, audience, date) VALUES ('firstmname', "&strVal1&", 'anemail@somewhere.com', 'thedivision', 'thetitle', 'purposestring', 'audience',#" & AccessDate(NOW()) & "#)"
I think the error is in how I format the date, but Im not sure. Ive looked on the net for examples but I havent found anything that works... can someone suggest what may be wrong or perhaps provide (or point me to ) an example of inserting a date into an access database with ASP?
hEYA all, ive got a problem, im trying to inserting data from an array to my access table- but cant do this due to this error:-Data type mismatch in criteria expression.
Heres my insert code- which its genereating the error at: Code:sub InsertDBCmd( )'================================================= ========================================SET objRs = SERVER.createObject( "ADODB.recordSet" )dim sqlStatmentsqlStatment = "INSERT INTO Members "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "[A/C no], [Address_Code], "sqlStatment = sqlStatment & "[Managing Director], [Title], [Buying Group Member], [M D Sal], [Rebate Contact], " sqlStatment = sqlStatment & "[Rebate Title], [Rebate Sal], [Replied], [Member Name], [S/Holder 2], [S/Holder], " sqlStatment = sqlStatment & "[ALPHA TYPE], [Member Type], sqlStatment = sqlStatment & ") "sqlStatment = sqlStatment & "VALUES "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "'" & C & "', '" & No & "', " For n = 1 to 15 if n <> 15 then sqlStatment = sqlStatment & "'" & FieldsArray(n) & "', " else sqlStatment = sqlStatment & "'" & FieldsArray(n) & "' " end if Next sqlStatment = sqlStatment & ") "'rESPONSE.WRITE(sqlStatment)'RESPONSE.ENDobjRs.OPEN sqlStatment, objConn,1,3'================================================= ========================================end sub
