Calculation Depends On Different Values..
Mar 13, 2007
Hi,
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.
View Replies
ADVERTISEMENT
Nov 24, 2005
I have a serial no combo box which displays serial numbers available for shipment. What i need is some way to make the combo box show the values 'serial numbers in stock' (simple sql) when the [tocompany] is not 'CVP' and to show 'serial numbers out' (another simple query) when the [tocompany] is 'CVP'
Basically i need to change the row source depending on the value in the [tocompany] field.
Thanks in advance
Ed
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 23, 2014
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]....
View 4 Replies
View Related
May 8, 2013
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;
View 2 Replies
View Related
Jun 23, 2015
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.
View 1 Replies
View Related
Nov 15, 2014
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 Related
Aug 4, 2005
Hi,
In a form, I am using two comboboxes per record (i.e. when you add a new record, two more comboboxes for that record appear).
My goal is to have the second combobox show entries specific to what the first combobox was, with certain caveats (see below).
My current approach is using three tables - tblTypes, tblOptions, and tblEntries. tblTypes would store the different types of entries. Each of the types in tblTypes would contain options depending on its ID.
Basically, the first combobox will list the data in tblEntries and the second combobox will list the data in tblOptions, depending on what "Entry" was selected. New "Entry's" for the first combobox would be created in tblEntries, where the ID in this table would indicate what "Options" are available for that entry. As shown in the sample table below, I want to allow more than one entry with the same "ID" (i.e. there could be more than one entry with the same options, but the entry name would be different.)
A sample of what the tables would look like is below:
tblTypes
---------
ID |Type
1 TypeA
2 TypeB
3 TypeC
tblOptions
----------
ID | Option
1 Data1
1 Data2
1 Data3
2 Data1
3 Data4
3 Data5
tblEntries
----------
ID | Entry
1 Entry1
2 Entry2
2 Entry3
3 Entry4
Is this approach sound? If so, how would I create the comboboxes to use with this set of data? I tried doing so but ran into a certain problem - that is, the first combobox doesn't allow you to "select" entries with the same ID - it lets you select the first one, but no other... (using a query that simply selects all of the entries and its associated ID - I used the ID to populate the second textbox appropriately.
If this approach sounds unreasonable, how would you approach it? And after creating the tables, how would you go about setting up the combo boxes?
Any help would be much appreciated.
Thanks in advance.
View 3 Replies
View Related
Nov 6, 2014
I have a table machine with fields:
ID(PK)model ( lookup column to machine model)serialnotype
Then a table meter reading:
IDmachineserialno (lookup column to tblmachine/serialno)readingdatetotalcounter
I want it to be like when i press lookup column in tblmeterreading/ machine serialno....it shall show the machine serial no|model in the popup combobox.
Instead it shows my machine serialno|id,
for example: when I select the machineserialno. a combobox shall show:
s/n:221233 | sony
instead it show:
s/n:221233 | 1
??
View 4 Replies
View Related
Oct 8, 2014
I have a cross-tab query that is filtered on user-defined date range using a where condition of:>[Forms]![Reports_Menu]![txtFilterDateFrom] AND <[Forms]![Reports_Menu]![txtFilterDateTo]
the Query functions perfectly. The report based on that query, however, only functions with a DateFrom that is before 1/28/14 and a DateTo that is after 2/27/14. If I enter a date in either field that is outside of those ranges, I get the following error:The Microsoft Access database engine does not recognize " as a valid field name or expression.I have defined both of my Query Parameters as Date/Time in the Xtab query design.
View 7 Replies
View Related
Jan 17, 2014
I am trying to update a table with the value of a text box on the form where the table to update is as selected from a combo box on the form.I keep getting the following
Error message:
Run-time error 2465
Microsoft Access cant find the field & table_to_update & referred to in your expression..
But really can't see what I've done wrong. Have checked that the table_to_update string does contain the name of the table so guess it must be sql..
Code:
Private Sub Command91_Click()
Dim table_to_update, sql_string As String
table_to_update = Me.Combo49
Debug.Print table_to_update
sql_string = "UPDATE [" & table_to_update & "] SET [" & table_to_update & "].[Project] = """ & Text89.Value & """ WHERE [" & table_to_update & "].[ID] = " & Forms![T_entity]![" & table_to_update & "]![ID] & ""
db.Execute sql_string
End Sub
View 1 Replies
View Related
Jun 12, 2007
Hi,
I want to update a subform that is dependable on its main form. The subform needs the PK of the main form to link to it. But when I enter a record in both of them at the same time, the subform saves the record but without PK of the main form. Hence, this does not create a link between the two. I guess it does that because the PK is not created yet in the main form during update.
My question:
How can I make the subform update after the main form does?
Any help will be very much appreciated.
B
View 4 Replies
View Related
Feb 25, 2008
Hey all,
I've read some tutorials on this sort of thing, but I can't seem to piece it all together to achieve what I want.
I have a table Products that contains the fields Product, Size and Brand. I also have a form, frmProducts, that has a combobox linked to each of the fields in the Products table. I want to be able to select a product from the first combobox, tab to the Brand combobox and have only those brands associated with the product already selected.
This is how I think it should work:
1.ComboBox1 selects productA
2.ComboBox2 takes its options from a query that searches Products table for all instances of productA and displays all available brands. brandA is selected.
3.ComboBox3 get its options from another query that searches for all instances of productA that also have brandA and displays all available sizes.
What I'm having trouble with in particular is passing the data between queries. For example, I can't figure out how to tell the query to search for all instances of productA when its defined by the first combobox. Do I have to store it as a variable somewhere?
I hope I've been clear.
View 6 Replies
View Related
Dec 8, 2004
I am currently developing an app with MS-access. I need two drop boxs on the same form. First is states and second is cities. When user selects one state from the first dropdown box, the second dropdown box will only display the cities that in the selected state. Is that possible by using ms-access??
Tanks
View 7 Replies
View Related
Aug 6, 2014
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
View 9 Replies
View Related
Dec 5, 2014
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134
[code]....
A few sample outputs
one solution is taking rows 1-6 and 26.
OtherID
11098554
56200936
25138850
56148974
56530104
56148975
Another solution is taking rows 1-4 and 10-14.
OtherID
11098554
56200936
25138850
56024315
56115247
56148974
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
View 12 Replies
View Related
Sep 17, 2013
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
View 4 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
Nov 22, 2013
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
View 14 Replies
View Related
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
Jun 18, 2013
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
View 5 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
Jan 15, 2008
I have a travel time database that should tally the number of minutes traveled and convert them into a time (hours and minutes). I, however, am having difficulty converting the numeric values cleanly. Is there anyway to convert 102 minutes + 100 minutes + 110 minutes = 312 minutes to 5 hours and 12 minutes cleanly? I need to take averages of time traveled and hours worked but cannot do this correctly. Thank you for any help in advance.:eek:
View 2 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
Feb 23, 2015
I have a lookup value in a table, and I would like to remove all values of the lookup value from vba. How can i do it via vba? i've been trying to do it in a query but i have always some errors and i can not remove them.
View 2 Replies
View Related
Mar 8, 2015
I have a table with following two columns
TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010
How can i show this data as components eqch version for a techname to be one component order need not follow
TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL
View 4 Replies
View Related
