I have a calculated field in a query. The calculation only works if the fields have values. This is what I used a the calculation:
PrimaryShareTotal: [Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share]
I tried using the Nz command
PrimaryShareTotal: Nz([Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share])
but this doesn't work.
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I created a simple calculation query to add the values of three fields:
Program_Cost, Auditorium_Cost and Millage_Fee.
I followed the steps found here: [URL] ....
But it doesn't work. The query pulls the values for the relevant fields but doesn't actually calculate the total. What am I doing wrong? Here's the query's SQL:
SELECT [Event Information].Event_ID, Sum([Program_Cost]+[Millage_Fee]+[Auditorium_Cost]) AS Total_Cost, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee
FROM [Event Information]
GROUP BY [Event Information].Event_ID, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee;
I have six fields in a record that look like Median.University1, Median.University2, Median.University3, Median. University4, Median.University5,Median.Unive rsity6. How can I create a calculated field that counts where values equals 4 in the fields . I have tried Dcount and Count with no success.
View 7 Replies View RelatedI am working on updating another person access database, how to add a calculated field. I need to add a field to an existing table to subtract the date listed in one field from todays' date. It would need to return the answers in days
ie
10/10/13 - 10/1/13 = Return answer of 9 days
I have been told about DateAdd but I cant find these date function in the option of expression builder.
I have a query with a DateSerial Calculation field that I would like to filter the query by. The DateSerial calculates the same day of every year (5/31/"YYYY"). When I try to add a criteria sort to this field, I get a data mismatch error. Here is the code: ThirdMay: DateSerial(Year(DateAdd("yyyy",3,[LastDayYear])),5,31).
How do I get only dates due in 2015 to show? I have tried all the standard date criteria to no avail.
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
1. I created a table that contains information about people and their details (mainly numerical info).
2. I created a form containing a command button and a label.
3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.
My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.
This code:
Code:
Private Sub txtPart_LostFocus()
Dim db As Database
Dim strSQL As String
[Code]....
But no entries are added to the table.
I open a query in design mode, paste the first debug above in SQL VIEW and run it:
Code:
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 1);
And the entry is added in the table.
PartID and SpecID are long integer, Sequence is integer.
I originally had Me.txtKey in the SQL, but I saw a comment in a post about that being a possible culprit. So I copied it into a long integer, still didn't work (latest trial above).
I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)
here's the problem:
when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... ... for some uknown reason.
to clarify:it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100
when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100
some details:
the average field has this code: Average: CInt(Nz([Grade]))
the query looks like this:
SELECT [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])) AS Average, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
FROM [All Classes P1 Query]
GROUP BY [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])), [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
HAVING (((CInt(Nz([Grade])))<[value]));
I'm on access 2007
hi,
I just want to understand why this works. I have some fields on a form that I'd like to let the user change. If I put something like "rs.update me.first, trim(me.first)" in the form's event procedure , "on update" why doesnt it like it?
I moved the same code to the field's on dirty event and it is ok. I don't understand why it doesn't let me update one field on the form's event. (Oh, my records can be selected by a drop-down or by navigating with the record selector.)
thanks a lot!
I made a query that requires to enter two parameters (both are Number data type). I need the 2 parameters to be a criteria in the same row (AND). When I run the query it gives me zero result (and the data is already exists in the table). What is the mistake?
View 10 Replies View RelatedHi,
Best to have a look at attached relations picture.
Basicly this is what I'm wondering about:
I add a new customer who bought a product from a specific supplier.
That unique combination gives me the right on commission.
From company A I get, let's say 5% of the capital.
Company B gives me 4,25%
Products are insurance policies, so at certain products you are insured for a capital i.e €100.000,-
Percentages given are to be calculated of that capital.
So I have a lot of possibilities here which determines my provision.
What is the best way to handle this ????
I have no clue where to start.
The field [Polissen.Provisie] is now manually filed in on the Form where I add all details, which should remain possible (sometimes there are different agreements concerning the fee)
Hope it's a bit clear..
Please ask if more info is wanted.
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
View 3 Replies View RelatedI have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.
This query works, on anything that starts with the letter D or later... A-C will not work.
Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.
This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.
Code:
SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;
The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.
Running Access 2013.
I am trying to perform a calculation within an IF then Statement. The difference is I need Access to remember a values to complete the calculations prior to setting the final answer. I think this is basic however I am a novice and can't seem to get it to work.
Code:
If [Forms]![Jobentryfrm]![StyleJobCurrentSub]![Assembly] = 39 Then
BL = ([Forms]![Jobentryfrm]![Height] * 2) + ([Forms]![Jobentryfrm]![Length] * 2) + 8
bw = [Forms]![Jobentryfrm]![Length] + [Forms]![Jobentryfrm]![Width] + 12
[code]....
This DLookUP works correctly...
UNDER CONTROL SOURCE:
=DLookUp("[First Name]","Contacts","[Combo378]='" & [Forms]![PROFILE]![Combo378] & "'")
but when a I want to close the form, display the error:
You must enter a value in the Profile.First Name
I junt need the way to update the field in the new table...
Trying to create a query from a table. This table has some fields with zero all the way down for all the records and i wish for the query to omit the whole field if that is the case.
View 9 Replies View RelatedI have a material usage table that tracks material going into products...Oftentimes the same material will be used and multiple records will contain that material.
I want my query to return only records with unique material used. I'm clicking the "Unique Values" and "Unique Records" property setting but its not working.
I have a query that takes a value, Proposalvalue, and depending on the currency, loc curr, it calculates the currency. It gets the currency value from the currencies table and appends to TableB
eg.
proposalvalue currency
50000 1
Currencies
id Value
1 0.6587
This creates the conversionvalue = 32935 in the TableB.
I have a form that can viewedit the data in TableB.
Using this form, I want to be able to change the proposalvalue and for it to automatically update the conversionvalue.
I have a query which generates a full list of times and appointments for a given day from two separate dates using a right join and enters a "-" in the field for unallocated time slots using an ISNULL function.
I want to refine the query further so that if a single task/appointment is block-booked in multiple adjacent time slots, I only want to return the start time of the first slot, the end time of the last slot and display the task name once.
The intention is that this will be used to create a daily timetable list in an Access (2000) report with the date being specified in a Combo box in a form in which it will be embedded - though I'm not concerned about the form/report design at the moment, only how to do the query.
I've tried several variations of SELECT DISTINCT and using nested queries to no avail.
I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].
The idea of the table is that you can input and update the value of each product on a given day.
I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..
A picture of what the table looks like is attached for reference.
modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query.
SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema
FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];
I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.
Below is a code that works, but only when i have each vehicle with the same vehiclenumber.
SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;
I have a table with a string field included for an address.
Some rows have postcodes and some dont
How can I extract the PostCode values from the field?
I am trying to get sum "+" and "-" Values in same Field. I created new Expression Builder in Query and i wrote there :
A: Sum([tablename]<0)
of course it did not work.
How shall i update my expression to work this thing?