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:
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?
We're trying to create a database to read quotes from a system based on changes made to components.
We have the database set up to store the quotes happily. We're pleased with the input forms and data capture however we are struggling with a query to get useful data from the database.
I have a main quote data table listing all the required fields such as costs and supplier data for the quotes, a table storing components that may be changed as part of a quote and a table listing alterations that could be made to these components. Each quote could have a number of changes made to a number of components. All these changes are stored in a changes made table which lists the quoteID, ComponentID being changed and The AlterationID of the alteration being made.
I want to be able to input a varied amount of changes via a form and be shown a list of all quotes where at least one change matches. I've managed to get this far using a lot of OR statements however the complexity is introduced as we need to sort these by an extra column produced by the query displaying the percentage the changes made in the quote match the search input.
If a quote appears matches my changes and there are no other changes on the quote - (100%)
If a quote matches all changes I have input but I input 5 changes and the quote has 6 - (5/6 - 83%)
If I input 1 change and a quote matches but has 8 changes on the quote - (1/8 12.5%)
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.
i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
I have 2 tables in access. One is a table with the us state abbreviations.
I have another table, one of the fields is an address field, e.g.
SOQUEL CA 95073 SOUTHAVEN, WA 98671 SOUTHBURY, CT 06488 SPENCER IA 51301 SPOKANE, WA 99201 SPRINGFE VA 22150
I would like to create a query, joining these two tables together so that the query can give me the 2 state abbreviation e.g.:
Address field/ Abbreviation field SOQUEL, CA 95073/ CA SOUTHAVEN, WA 98671/ WA SOUTHBURY, CT 06488/ CT SPENCER, IA 51301/ IA SPOKANE, WA 99201/ WA SPRINGFE, VA 22150/ VA
I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.
I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.
E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.
I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).
The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?
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 parent-child one to many data in one pair of relationships, and now I've been asked to see be able to find out what matches a defined regimen; each is also defined in a parent-child relationship.
Best is to show sample data. I'm going to show them as two tables, but the "Components" are actually in a parent-child relationship, e.g.,
PersonList -= Meds Regimen -= Meds as well
Note that PersonList and Regimen do not really have any relation; we just want to see if things are being done one of the ways they are "supposed" to be done, without a slow manual check. It's worked as set up for reports, and I really don't want to change everything to a big long list of fields, one field per med for a lot of reasons (not least of which is that is denormalizing)
Quy 1 Result: PersList T1Component Andrew Med 1 Andrew Med 2 Brett Med 1 Brett Med 3 Brett Med 4 Charles Med 2 Duane Med 1 Duane Med 4
Quy 2 Result Regimen T2Component Goody1 Med 1 Goody1 Med 3 Goody1 Med 4 Goody2 Med 1 Goody2 Med 2
I'd like to be able to do two queries - one that are "OK" one that are not. Don't need to replicate the med list, just the regimen if matching..
"Good" would return Person Regimen Andrew Goody2 (he has med 1, 3, and 4) Brett Goody1 (he has med 1 and 2)
"Bad" would return Person Charles Duane
What they "almost match" does not matter; it tells people which ones we need to check into a bit more.
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 have a table Billing_Temp that I need one field updated if I find a match in another table Random_Temp. I runt the query and it prompts for "Enter parameter value: Random_Temp.peopleID... what could be going on? Both tables have a field called peopleID and always Billing_temp has many more records than Random_temp:
UPDATE Billing_Temp SET Billing_Temp.audited = -1 WHERE (([Billing_Temp].[peopleID]=[Random_Temp].[peopleID]));
I have a form where the user will choose a contact name or names from a multivalue combobox and I want the email address field to update with the corresponding emails separated by "; ".
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
Currently we track areas of non-conformance for a fleet of flight simulators. Each flight simulator has a particular ID number. In some instance an area of non-conformance is associated with a single simulator, at other times it is a fleet wide issue and applies to all or some simulators. In order to track as well as advise leadership and the contractor responsible for maintenance of the simulators of the situation we generate individual response letters.
In order to track each instance of non-conformance my idea was to create a new record for each deficiency. In the event that it is applicable to multiple simulators I would like to fill out the form with all pertinent data and then place a checkbox associated with each simulator and when the record is saved, it creates one record for each simulator with a checkbox ticked.
Once the deficiency on each simulator is fixed, I would check a box for a field called rescinded, which would remove that particular deficiency on that particular simulator from the active list of deficiencies but the others would still remain because they are associated with unique records.
I need to enter workload counts for 10 people, and it is done on a monthly basis. So I have a table of Months (Jan-Dec), a table of names, and a joined table with the months, names and a field for the workload counts.
I would like to make a form where I could select the month and all the names show up so I could go and enter the counts for everyone at the same time. I've attached a diagram to show what it would look like
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).
I am using access 2010. I have "classlevel" table with 2 columns-Class and Value1 .Value1 column has numeric values that i ll input from webpage (webpage to ms access connectivity).I want to sum the values of column "Value1" and i have another table-"Volume" which has 2 columns "VolumeLevel" and "Value2". So i want to match the sum that i calculated from first table-"ClassLevel" with the "value2" column in "Volume" table and get the corresponding "volumelevel" column value from that table and there is a third table that will get this volumelevel value.
There is no common column to join these tables.
Classlevel-
Class Value1 Class 0 3000 Class 1 2000 Class 2 300 Class 3 400 Class 4 500
I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
I am using access 2010. I have "classlevel" table with 2 columns-Class and Value1 .Value1 column has numeric values that i ll input from webpage (webpage to ms access connectivity).
I want to sum the values of column "Value1" and i have another table-"Volume" which has 2 columns "VolumeLevel" and "Value2". So i want to match the sum that i calculated from first table-"ClassLevel" with the "value2" column in "Volume" table and get the corresponding "volumelevel" column value from that table and there is a third table that will get this volumelevel value.
There is no common column to join these tables.
Classlevel-
Class Value1 Class 0 3000 Class 1 2000 Class 2 300 Class 3 400 Class 4 500
What I would like to do is for the BoatReg field in the Quotes Table to only display the boats that have been registered to the Client that has been selected, currently it displays all the BoatRegs.
I have two tables. TableA and TableB They both have the same columns.
I need a query that will look at the diferences between TableA.Column1 and TableB.Column1 and copy whatever TableB.Column1 is missing from TableA.Column1
So I want it to copy over the entire record based on what TableA.Column1 has the TableB.Column1 does not have.
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
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.
