Value In A Table Equal To The Sum Of Other Values
Dec 11, 2007
Hey.
Is there any way to make a field in a record automatically set it self so it's the sum of other values in the record
http://img412.imageshack.us/img412/1677/helphg9.jpg
That's an example what i want to do except i had to manually type in 25. Is there any way to get that so it does that automatically. I am happy to use forms or macros or anything as long as it occurs straight away after the data is inputted and not have to run something or open something up
Thanks in advance
View Replies
ADVERTISEMENT
Nov 3, 2005
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2]
FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following:
[table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as:
[table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
I appreciate your help in advance.
View 1 Replies
View Related
May 25, 2006
I have a query that uses a linked table from an external database and joins it with a table from my database to return records that contain the same values.
Is there a way to return only the records that are present in the external database and not in my db?
Thanks
View 3 Replies
View Related
Nov 24, 2013
same as the title goes, why i can't query those figures greater than 15% only? How am i suppose to query them right?.Here is my database you cant check query1 and query 2. I am using Ms Access 2010.
Link:
www dot dropbox dot com/s/n3iuev1c5e0dfby/StockMonitoring.accdb
xD: StockMonitoring.zip
View 5 Replies
View Related
Feb 28, 2013
When I enter a number equal to or over ten million, the last two values get rounded and I don't want them to be. My field is setup as a single, standard, two decimals. When I tested it I typed in 123456789, what I got was 123,456,800.00
enter 9999999 get 9,999,999.00
enter 10000199 get 10,000,200.00
enter 10000001.75 get 10,000,000.00
Single is supposed to handle up to 10^38
I have checked my "Region and Language" settings for the OS (W7) and there is nothing in there about rounding or maximum number size. I would like to leave the data type at single for the space considerations, and because it should work as single.
View 8 Replies
View Related
Feb 3, 2008
Hi
How can I make the primary key (participant #) in my table equal the record number on the Form? In other words, when I open the form, I need two things to always happen:
1. the primary key must equal the record number (which is displayed on the form)&
2. when I click "Add New Record" button, I want a new customer # automatically generated and in the correct order.
Is this possible? I've been struggling...
View 4 Replies
View Related
Jun 24, 2014
Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are
Table 1) tblTasks (TaskID (PK), Description), and
Table 2) tblMeasures (MeasureID (PK), Description, Tasks)
If JCTN table is JCTN_Tasks_Measures (TaskID, MeasureID), is there a way to populate when tblTasks(TaskID) == tblMeasures (Tasks)?
View 1 Replies
View Related
Apr 14, 2006
I am trying to run a query where one field has a Not Equal parameter AND another field has Not Equal parameter (for example, Product Code Not Equal "A" AND Client State Not Equal "NY)
The result of the above query are all records that have Product Code = "A" are missing from the result. I want some of the "A" Product Codes; I just want to exclude the ones where the Client state is NY.
The list of values for each field is too big to state it in a positive way (I would have to list all the other 28 product codes and all the other 49 states).
What am I doing wrong? Thank you in advance for any suggestions.
View 1 Replies
View Related
Aug 29, 2014
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).
View 5 Replies
View Related
Sep 18, 2013
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
View 2 Replies
View Related
Mar 2, 2015
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
View 4 Replies
View Related
Dec 15, 2011
How would I set the field value in one table to equal the field value in another table? Neither fields are primary keys, but the two tables do have a one-to-many relationship. Basically, I just want the two values to be the same.
View 3 Replies
View Related
Sep 15, 2005
There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.
How can I do that ? :confused:
Thanks
View 1 Replies
View Related
Jun 18, 2014
I have a table (Table 1 let's say) which contains a list of numbers that all have unique ID numbers in another table (Table 2). Is there a way to create a column in Table 1 that will link those unique ID numbers from Table 2 to their corresponding values in Table 1?
What happened was I imported a list from Excel and there are frame numbers which are unique (but I still gave them an ID) and I now realize that I have another table which lists all these products, which all contain this frame number. I would like to replace the frame number with the correct ID number.
View 5 Replies
View Related
May 26, 2005
Hey,
I have 2 tables that i compere together with a query and if my criteria is OK than selected data is recordet in new table with some calculation....code:
INSERT INTO tblZaloga ( Proizvajalec, Tip, DN, Predvideno, Naroceno, Zaloga )
SELECT tblACAD_Urejeno.Proizvajalec, tblACAD_Urejeno.Tip, tblACAD_Urejeno.DN, tblACAD_Urejeno.KosovSkupaj, tblIN_Urejeno.KosovSkupaj, Sum(([tblIN_Urejeno].[KosovSkupaj]-[tblACAD_Urejeno].[KosovSkupaj])) AS Zaloga
FROM tblACAD_Urejeno, tblIN_Urejeno
WHERE (((tblACAD_Urejeno.Tip)=[tblIN_Urejeno].[tip]) AND ((tblACAD_Urejeno.DN)=[tblIN_Urejeno].[DN]))
GROUP BY tblACAD_Urejeno.Proizvajalec, tblACAD_Urejeno.Tip, tblACAD_Urejeno.DN, tblACAD_Urejeno.KosovSkupaj, tblIN_Urejeno.KosovSkupaj;
Now i have a problem with racords that does not reach my criteria. The problem is that i dont know witch records are the one that does not reach my criterya? Can someone pls tell me how can i create a table like the code up there but with records that are not procesed?
THX
View 2 Replies
View Related
Jun 8, 2005
Hey,
Can someone pls tell me how can get not equal resoults from my two tables? I have table1 and table2 both with field "Layer" and what I want is to get only that data from table1 that does not have the same Layer as table2.Layer?
I try to JOIN table1 and table2 and i get the same resoult as with this code:
WHERE ((tblACAD_MRK.Layer)=[tblLayerji].[NeteniLayer])
but thats exactly the oposite from what i want, so i try with this code:
WHERE ((Not (tblACAD_MRK.Layer)=[tblLayerji].[NeteniLayer]))
and the code does not give me the resoult that i want, becouse i get all the layers for some reason :confused:
So can someone pls tell me what can i do?
THX
View 2 Replies
View Related
Sep 27, 2005
I'm wanting to write this query that looks at both tables and only returns records that don't match...not for sure how to do this..
SELECT tblCombined.DLID, tblCombined.DUP, tblCombined.QRYRAN
FROM Susp_Master INNER JOIN tblCombined ON (Susp_Master.DUP = tblCombined.DUP) AND (Susp_Master.DLID = tblCombined.DLID);
View 4 Replies
View Related
Sep 21, 2007
Hi there,
I have one question with regards to the WHERE part of an SQL query. I want to limit my query on datasets, where the first three letters of a certain field (which contains strings) equal "Exp". Is there a more efficient way than the following?
SELECT A.Date, A.Name
From tblA as A
WHERE A.Name="Exp_A" OR A.Name="Exp_B" OR A.Name="Exp_C";
Best regards,
JapanFreak
View 3 Replies
View Related
Apr 26, 2008
Hello,
I would like to show records in my query only if records in raw a and b are equal and they have same question id.
Please let me know if you can help.
Many thanks,
Debbie
View 3 Replies
View Related
Apr 28, 2008
I have Table as PL2008 and RPT2. Both tables have one column named PTUNID. My goal is to first Group, sum certain Columns in the PL2008 then match PTUNID from this Grouping to RPT2 Table's column PTUNID and then show the results which do not match.
I have written following Qry but what really happenning is its giving me all the results ( match and no match in both tables ) and Sum values are incorrect.
SELECT [PL2008].PTUNID, Sum([PL2008].[Total Hours]) AS [SumOfTotal Hours], Sum([PL2008].[Actual Cost]) AS [SumOfActual Cost], [PL2008].[PRFIELD]
FROM RPT2, [PL2008]
WHERE RPT2.PTUNID <> [PL2008].PTUNID
GROUP BY [PL2008].PTUNID, [PL2008].[PRFIELD];
Any suggesstion
Thanks
Niri
View 3 Replies
View Related
Feb 7, 2006
It need to compare two tables where the ID Number is not equal. Specifically I need to update new poducts in a table, but only those ID#s that are not already listed.
I know how to compare two feilds where they are not equal...
i.e. [New_Table].[ITEM_NUM]<>[Old_Table].[ITEM_NUM]
...but I'm currently limited by the join properties in the tables, since I'm comparing IDs and not the feilds. ANY IDEAS?
View 2 Replies
View Related
Oct 21, 2006
I have a membership database. A number of members go by their middle names or a nickname rather than their first name. I need to keep track of their real full names, but I want the new-record default of the preferred name field to equal the contents of the first name field. How do I set this in the table design? The table already has 1400 records, and it would be quite a job to manually copy each first name to the preferred name field, a job I would rather avoid.
View 5 Replies
View Related
Jul 12, 2007
I was wondering if someone could help me.
I have a simple table with data that gets updated daily. I need to split the data into 5 equal parts for work distribution. Because the amount of records always changes I can not go by numbering so I need to go by percentage, to split it into 5 ( 20% ) parts. Please help.
View 5 Replies
View Related
Feb 19, 2005
Any idea how i can count the number of records that have the same order number of the one on the report list? For example, there are 3 records in my query that have Order# 1001, I would like it to show a "3" next to order 1001 in the report.
Any idea where and how I would make this happen (in the query or the report)?
Let me know!
Thanks!
ovadoggvo
View 1 Replies
View Related
Jan 22, 2015
How do I do: on event enter last saturday (in a control)?
The best I've been able to come up with is to check if it's A saturday.
Code:
Private Sub ctrl_Weekending_Exit(Cancel As Integer)
Dim strDay As Integer
strDay = Nz(Weekday(Me.ctrl_Weekending.Value, vbSaturday))
[Code].....
View 4 Replies
View Related
Jun 27, 2014
I have a database with with 100s of values for a field. What I would like to be do is specify a value via a form and the query will return all options that equal the specified value.
so for example: Specified value = 150
Field 1 Field 2
------- -------
Name1 10
Name2 50
Name3 25
Name4 25
Name5 100
Name6 40
Name7 125
Name8 50
I'd like the query to return all possibilities that equal 150 so it should look like:
result 1 - name5 + name2
result 2 - Name5 + Name 8
View 3 Replies
View Related
