Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

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 Replies


ADVERTISEMENT

General :: Getting Values From Table2 Based On Value Selected In Table1

Oct 3, 2014

I have a table named Product and Quotation.

This is what i want to achieve

In Table Quotation when i select product name from dropdown menu i want all the related information for that product to be visible in Quotation like product id, productdescription & productcost

View 2 Replies View Related

Tables :: Limiting Table Input Based On Variable Values

Feb 7, 2014

I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product.
Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales.

Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?

View 3 Replies View Related

Forms :: Allowing Different Values In One Field Depending On Selected Values Of Another

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

Queries :: Sum Table Values Based On Combo Box Selection?

May 20, 2014

I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.

I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.

In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.

What do I need to change to get it to sum correctly?

I have attached the database below so people can take a look.

View 1 Replies View Related

Queries :: ORDER BY - Update Values In One Table With The Values In Another Using Join

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

Tables :: Converting Excel Table For Lookup Of Values Based On Row And Column Headers

Sep 3, 2014

how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?

View 12 Replies View Related

Queries :: Rename Field Values With Values From Another Table

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

Queries :: Calculation Query To Add Values Of Three Fields

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

Queries :: Calculation Only Works If Field Have Values

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

Queries :: Calculation That Counts Where Values Equals 4 In Fields

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

Tables :: Unable To Insert Multiple Values Selected From Listbox To Database

Nov 19, 2012

i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.

i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.

(ps: in my property sheet for my lstCuisine listbox its multi select is simple)

Here is my codes:

Private Sub Add_Record_Click()
If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then
MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
Else
DoCmd.GoToRecord , , acNewRec
End If
Dim conceptValue As String

[Code]...

View 10 Replies View Related

Modules & VBA :: Deleting Values From A Table Selected From A List Box

Mar 19, 2014

Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).

If I use the following code:

Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem

I get an error stating Object doesn't support this property or method.

View 7 Replies View Related

Forms :: Save Button Which Writes Selected Values To A Table

Jan 28, 2015

I have a form full of cascading unbound combo boxes which allows me to assign companies and people to a project.I have a save button which writes the selected values to a table.To view the assignments when the record is later viewed I have placed a bound field behind the combo box.

View 7 Replies View Related

INNER JOIN Based On Equivalent Values, Instead Of Equal Values

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

Constraining Combo Box Values Based On The Value Selected In Another Combo Box

Sep 7, 2006

Thanks in advance for your help.

I would like to set up rules/constraints such that the value selected in combo box A determines the available values in combo box B. For example, "Combo box A" is bound to the [PartnerType] field and "Combo box B" is bound to the [PartnerRole] field. Let's say that the two choices in Combo Box A are "LLC", "LP", and "Corporation". There are 5 possible choices in Combo Box B: "X", "Y", "Z", "Q", and "U". If a user selects "LP" in Combo Box A, I would like Combo Box B to only show choices "X" and "Y". And if a user selects "LLC", only choices "Y", "Z", "Q", and "U".

Similarly, I would like to set this up so that Combo Box B is not initially visible--it becomes visible when a user selects "LLC" or "LP". If a user selected "Corporation", Combo Box B would remain hidden.

View 2 Replies View Related

Update Query Based On Lookup Tables Values

May 30, 2005

Hi, not sure if this is possible or the most economical approach but here goes:

I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.

I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.

Can this be done?!

Thanks in advance

View 2 Replies View Related

Pulling Data From Other Tables Based On Values Entered

Dec 13, 2007

I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex).
So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report.
Probably going about this wrong.....but any help would be appreciated.

View 5 Replies View Related

Forms :: Returning Values From Tables Based On Criteria

Jun 27, 2013

I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.

Table 1
EmpID Name
1 EMP1
2 EMP2

Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B

Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18

View 2 Replies View Related

Tables :: Linking Multiple Field Values To A Field Selected From Combo Box

Feb 16, 2014

I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)

Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)

Forms:
* Player form
* Match form

Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).

View 1 Replies View Related

Reports :: Calculation Based On Row / Value Selected From List

Dec 8, 2014

I have a table with a primary key as date, and various other fields with numerical values. I also have a query that calculates running totals for each of these fields, as well as including the fields from the table.

I need to create a report or form in which I can select a date(primary key) from a drop down list.

I need the selected date to determine a row/value from a field (a running total from the query)

I then need to use that value in a formula;

result = sum([field]) - value

What I am trying to do is calculate the difference between the sum of a field, and a specific row/value in a calculated field containing a running total of the original field.

View 10 Replies View Related

Queries :: Sum Values Based On Multiple Criteria

Aug 12, 2013

If I want to sum the percentages from April, May and June only if a column is Not Null, how would I do that?

example

Tbl 1
PK, Month
Percent

Tbl 2
FK, Month Enrolled
Qty of Rx in the 1st month enrolled
Qty of Rx in 2nd qtr
Base (if Qty of Rx in 2nd Qtr is null then Qty of Rx in 1st month enrolled)

If Qty of Rx in 2nd QTR is NOT NULL then QTY of Rx in 2nd Qtr * Sum of April Percent+May Percent+June Percent, otherwise Qty of Rx in 1st month enrolled * Month Percent

Im getting stuck on how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.

I only know how to create Query's using the design mode. I dont know how to write SQL statements.

View 1 Replies View Related

Queries :: Averages Of Values Based On Dates

Aug 20, 2013

I'm not sure if this is the right forum. If not let me know and I'll move the thread.

Table 1
Date
Measurement A
Measurement B
Measurement C

Table 2
Table 1 ID Link
Data 1

Tables are linked 1-to-many from table 1 to table 2 by ID.

I would like to average the Data1 data per Table1 ID and report it with the Table 1 Measurements.

View 1 Replies View Related

Queries :: Match Based On Multiple Values Within One Field?

Sep 22, 2014

For my study on academic research I need to match patents that refer to academic research as prior work with the actual prior work.

I have two tables (see attached images below).

One regarding AcademicPublications (AP), which is neatly organized with title, year, journal, volume, pages, first author, etc... 480,000 rows

One regarding Patentswhere all this information is hidden within one field, in the most messy way possible... for instance, a field could have:

Quote:

Sugita et al, "Nonsurgical Implantation of a Vascular Ring Prosthesis Using Thermal Shape Memory Ti/Ni Alloy (Nitionl Wire)," Trans. Amer. Soc. Artif. Intern. Organs, vol. 23, pp. 30-34.

or

Quote:

Willingham et al., Cell 13, 501-507 (1978).

Or many other ways.

I want to create a new table that is set up like this:

Patents.PatentNumber | AP.ID | Patents.Reference | AP.Title | AP.Year | AP.Volume | AP.PageStart

The question is: How do I match different fields from one table on one field of another and make it return another field (the ID)? Some references are too horrible to match, but I need as many as I can get.

I can imagine two queries would give me the bulk:

A match in [Title] AND [Year]

A match on ([SourceTitle] OR [AbbreviatedSourceTitle] ) AND [Volume] AND [Year] AND [PageStart]

I understand that I have to make use of the Like "*"&[value]&"*", but how do I make it return the matching ID?

View 10 Replies View Related

Queries :: Adding Values To Cols Based On Criteria

Sep 24, 2014

I am trying to map certifications done by colleagues in my department.There are 4 certifications and I have which I have pulled out from the Certifications tables using individual query for each certification.Now, I want to add "Certification-Name_Certified" col which will have "yes" or "no" values for each certification to the master data of the department personnel as it only have unique records using a query.

View 4 Replies View Related

Queries :: Counting Text Values Based On Their Status

Jan 25, 2015

I have a question regarding counting of text values base on their status and using that result to a calculation.

Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.

What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.

The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.

I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved