I have a table linked to SQL Server 2014. As SQL Server 2014 does not support calculated fields I created a query to use formulas. Now I want to write formulas on the existing fields ( TotalMarks ) of table Not to create new fields.
View RepliesI have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
Access 2007-10
Listbox created: List62 (I know I need to rename it, but for now)
Multi-select: Extended
Row Source Type: Field List
Row Source: qryFieldList
Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).
Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:
Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If
[code]....
It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.
Hi
I would like to count number of items witin a text field,
but breaking it down identifying the different items within the
text field.
e.g.
Got a field with fruits listed. Now I am identifying the fruit but want
to know how many rotten fruit there was for each specified fruit.
At the moment my query is by date and the fruit including the column
specifying if the fruit is rotten or not. But there is 3 options in the last
column. How can I count this last column to count how many of these
3 options there are for each fruit in my report?
Please let me know if this makes any sense or not otherwise I will try to explain
it a little bit better.
I wrote a basic query that allows 1 field to search another, and if there is a match, it spits it out, however, I am getting 20,000 matches. I want to add another level of query to reduce the 20,000 matches down to 1 or 2 or none...
So I consolidated a government list, publicly available, into 1 field, and created a table that I can use as my query against the large government list:
SELECT [Consolidated Denied Party Report].*, [Consolidated Denied Party Report].[31]
FROM [Consolidated Denied Party Report]
WHERE ((([Consolidated Denied Party Report].[31]) Like "*" & [Please Enter Your Search Term] & "*"));
this allows me to search for a word, like, create, and it gives me every single result however it also gives me hits to words like PROcreate, which is fine on one hand because it shows possible false flags, but it would be nice to also be able to query down a level.
I now want to have the query look at the word "create" but also look at another column that is say the country... Germany.. and if I have text in the second column, only give me a result if the word create and germany are in the same field.. if create and Italy were in the same field, it would not be a hit.
How to apply the formula, IF (COUNTIF (O:O;O3)> 1; K3/SUMIF (O: O;O3; K: K), 1)from an Excel spreadsheet into an Access query.
View 2 Replies View RelatedNeed a formula which can capture filename for me. For example, I have a db saved at "C:DlocationofficeChina.accdb". Is there a way to capture "China" in a query?
View 2 Replies View RelatedI 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!
View 2 Replies View RelatedAccess 2003
Excel 2003
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
Code:
MyCalc::"IF(T2="","0",TODAY()-T2)"
=TEXT(TRIM(MID($A2,5,2) &" "&LEFT($A2,3)&" "&MID($A2,8,10)&" "&RIGHT($A2,2))+0,"dd/mm/yyyy hh:mm:ss ")
Is it possible to replicate the above formula from Excel into criteria in an Access Query.
Assume the cell reference $A2 is a text field which has customized date info in it but is not a recognized date format.
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check boxes)
Y ACD 2 x x
Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query performs
Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Thanks,
CB
I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".
A visual of my tables:
tblLabels (Main table)
- LabelID
- BaseProduct (old field with text data)
- LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)
tblBaseProduct (new table)
- BaseProductID (PK and FK to tblLabels)
- BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)
I tried to design a query using design view of the query design and this is what I have:
Code:
UPDATE tblLabels, tblBaseProduct
SET tblLabels.LabelBaseProduct = [baseProductID]
WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));
When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.
Hi all,
I need to figure out how to have two calculated fields written to the database table instead of just being displayed in the form.
The user selects a number from 1 to 5 in the first field, and a number from 1 to 7 in the second field. The first calculating field multiplies these two numbers and displays the result. The second calculating field determines where the resulting number fits in three ranges of numbers and displays the result as Low, Medium, Or High.
My problem is the calculated results are be displayed in the form but not being written to the table. I cannot create a report to display all the High results, etc.
How do I update the calculation field formula to write the results into the table so I can create queries/reports on the calculated data?
Thanks,
bugleboy62
I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.
I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurrence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.
I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do....
I am trying to write some fields to an access table definition. It is working for the most part except that I want to write some of the fields as dbText and others as dbDate. For some reason it is writing all of them as dbDate and I don't know why.
Code:
additionalColumns = Array("a","b","c","d","e","f")
'add additonal columns to report
For i = LBound(additionalColumns) To UBound(additionalColumns)
MsgBox additionalColumns(i)
If additionalColumns(i).Value = "a" OR "b" Then
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbDate, 10)
tb1.Fields.Append FieldName
Else
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbText, 150)
tb1.Fields.Append FieldName
End If
Next i
I concatenated 2 fields CR_Numbers: = CR_Number & [Sub Number] to display 12.01: from 12 and 1. I am now wanting to lookup the CR_Numbers to edit or add information into other fields.
I want to use a pop up form to edit/add data without having the form add an entire row to the table. How do I reference the Table ID so that it will go to the equivalent row to add the data in?
Main Table = Change Request
Email Table = AORB_Email
Fields in Form!AORB_EMail: Change Request (CR_Numbers), Priority (Pri_Name), Hours (Hour)
I plan on using the Priority and Hours in the email to set some dates and expiration times as well as in the subject of the email. I have figured out the email setup, and if I manually fill in the Priority and Hours fields everything works as it should.
I have two fields on a table that has numeric values.
I'd like to create a third field where i would just like to divide the two numbers, multiply by 100 and display the results on field three.
I can't use forms or reports as i need to be able to export this information to excel.
Can this be done?
Hi
I updated the tables but I am still getting the old tables and their fields in the query builder. Please help me as soon as possible.
Secondly if you can tell me a cool link that can help me in creating different form fields like buttons, combo boxes, radio buttons along with database connectivity.
I will really appreciate your help in this regard,
Thanks
Jon
Hi
I updated the tables but I am still getting the old tables and their fields in the query builder.
Secondly if you can tell me a cool link that can help me in creating different form fields like buttons, combo boxes, radio buttons along with database connectivity.
I will really appreciate your help in this regard,
Thanks
Jon
Hello,
Hoping someone can help.
Im using Access 2002 to revise my SQL. But it seems I have an issue with the way dates are being interpreted by Access whilst using SQL mode for Queries.
I typed the following code to add a record to my database:
INSERT INTO pt_mstr (pt_part, pt_added)
VALUES ('TimsPart', 01/01/2005)
Sure enough it creates the record. But for the field 'pt_added', I get the value '0/01/1900 12:00:43 AM'. (Nb I exported the data to Excel and this is how it interpreted it.)
Changing the date I am inserting into the database only changes the time value of this enlongated piece of data.
I imagine I have missed something really simple here.
Hoping someone can help.
Tim
I have a series of dates over several months, and I want to write a parameter query which will list only those from the month of August. I know how to write a general parameter query, but I can't figure out how to write one that specific.
View 10 Replies View RelatedI am using Access 2010. How do I prevent the object typed into the textbox on a queryform being written to the table. The result from the name typed into the textbox on my query form correctly produces the result from the query, and my macros then produce the correct report, which I can either print or close due to the controls in the heading of the report. However, when I view the table, the name typed (only) has been inserted into the correct field as a new record in the table. Is there a macro I can add (I assume to an event in the query form) to prevent this happening?
View 8 Replies View RelatedI am periodically importing Excel files into access.Making the data usable requires removing spaces, parsing certain fields, adding datasource field, etc. Currently, I am importing the un-formatted data into a staging table, cleaning it up with a query and then copying the updated staging table to the final table.
View 2 Replies View RelatedI am not sure that I am in the Correct Section for this question but I am sure I can be forgiven for that :
I am attempting to build a db for a friend who has all his data in one table. During normalisation I have seperated the various threads of data into various tables of usable information. My problem is that I need to seperate his clients fullname into First & Surname i.e. Fullname: Mick Burke to FirstName: Mick and Surname: Burke - in other words make two fields from one.
Any help in doing this would be well appreciated.
I have a database and I wish to be able to copy 10 out of about 20 fields into a new record automatically using a command button on a form. Similar to the way the Duplicate button or the Create New button would work but only with these selected fields. None of the fields I wish to duplicate are the primary key. The primary key is an AutoNumber field. After clicking the Save button, the primary key would increment one to confirm the save record.
Any help or suggestion would be appreciated!
Thanks!