Queries :: Sort By Percentage Match To Multiple Field Search

Mar 19, 2014

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%)

View Replies


ADVERTISEMENT

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 :: Search Box Result - Add Up List Of Dates That Match A Criteria

Jun 13, 2013

I am trying to add up a list of dates that match a criteria... a search box result.

I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)

But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.

Code:
Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant

[Code] .....

I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?

View 5 Replies View Related

Queries :: Match Multiple Rows From Same Column

Apr 2, 2014

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.

View 4 Replies View Related

Queries :: Running Sum Of Percentage Field

Jun 17, 2015

I am trying to make a query that give me an output of an aggregate percentage column, or running sum of percentage. Please see my example:

Month Sales in % Running sum percentage
Jan 5.03 % 5.03 %
Feb 2.17 % 7.20 %
Mars 1.28 % 8.48 %

I have the column Month and Sales in %, What do I have to do to get an output like the example over?

View 2 Replies View Related

Reports :: Sort Records By A Calculated Percentage Using Text Box Values

Jan 27, 2015

So I have a report with the following text box controls:

[Surname] & ", " & [Firstname]
=Sum([Quarter1_A]) - Named "Quarter_Total"
=Sum([Quarter1_T]) - Named "Quarter_Target"
=Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)

The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?

View 14 Replies View Related

Queries :: Adding Percentage Field Into Query

Apr 25, 2014

I have a database which tracks the performance of my team and how long it took them to send something out to the customer. We have a target of 5 minutes.

So I quite a few queries to drill down this information. I have a summary query that takes that information and tells me the total amount of things sent, the amount of things sent in time and the amount of things sent late.

My team have to get at least 95% out on time. So how would I go about adding this bit into the query. The calculation is:

The amount sent on time / the total amount * 100

But is there anyway to add this into a 4th column displayed next to these figures?

Looking around a few people have talked about SQL but I no nothing about this and it seems quite daunting, is there a way to do this as a calculated field?

View 1 Replies View Related

Queries :: Update A Field If Find A Match In Another Table

Jun 29, 2015

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]));

View 1 Replies View Related

Queries :: Calculated Field - Converting Percentage Marks With Grades

Mar 11, 2014

The calculated field converts all percentage marks perfectly fine with grades except 100 which returns to a stupid "E" . I've been trying all sorts and now give up.

Code:
Grade:IIf([Percentage]<20,"U",IIf([Percentage]<30,"G",IIf([Percentage]<40,"F",IIf([Percentage]<"50","E",IIf([Percentage]<60,"D",IIf([Percentage]<70,"C",IIf([Percentage]<80,"B",IIf([Percentage]<90,"A",IIf([Percentage]<100,"A+")))))))))

View 1 Replies View Related

Exact Match In Search

Jun 17, 2005

How can I get an exact match in a search. If I search for say C1 I get results showing everything with C1 i.e C1, C11, C12, C13 etc. I just want to show C1's
This shows my query at present.

Like [Forms]![Search]![BoxNo] & "*"

View 4 Replies View Related

Queries :: Cannot Change Sort Order On Float Field

Apr 22, 2013

I have a query with a float/delta column which is the expression:

Code:
Float: [Date1] - [Date2]

When I try to change the order by in the column filter drop-down i get a data type mismatch in criteria expression error.

View 1 Replies View Related

Forcing Exact Match In File Search

May 31, 2007

Hi,

Does anyone know how to perform an exact match using file search. The code I have below searches for the users AcroRd32.exe file (I have done it this way to allow for different versions of the reader). The problem is that foundfiles returns also AcroRd32info.exe ie FoundFiles(1). i only want to search for the prior!

Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "C:Program FilesAdobe"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"

If .Execute > 0 Then 'check if there is such a file

stAppName = .FoundFiles(1) 'then define this as the applicate string name
stAppName = stAppName & " " 'append a space or file string not correct
'specify path name for location of file.pdf
stlocation = GetPrivateProfileString32("C:WINDOWSSSI_PROGRS_DATABASE.ini", "DIR", "DATABASE_FILELOCATION")
stpathname = stlocation & "file.pdf"
Shell stAppName & stpathname, vbMaximizedFocus
Else
'if no file is found then need to instruct user to install reader
MsgBox "You need to install Adobe Acrobat Reader to open this file", vbExclamation
End If
End With

Cheers to any help..

Tania

View 2 Replies View Related

Queries :: Multiple Items In One Search

Jun 29, 2013

I have an Access database that links one form to one table, so a fairly simple database. The database will average 250k records. How do I query to pull up many (ex. 100) distinct records.

Example - I want to search in a field called 'debtornumber' and want to pull up lots of various account numbers (ex - 0458714 and 0587428 and 0578925 and....). I have hundreds of the individual items to query daily (that I can paste from Notepad or word) and need to pull up all items in the query at one time.

View 14 Replies View Related

Multiple Field Search With Access.

Dec 17, 2004

Hello,


I am trying to make a multiple field search function with VB in access:
private sub search (table1.field1,table1,field2....)
........
end sub
I Would like to know how i can send a field of a certain table as a parameter of a function?

also how i can know that i have arrived to the end of the column in the table?


Thank you all.

View 2 Replies View Related

Can't Add Field With Multiple - Search Key Was Not Found

Aug 24, 2012

I am trying to add a field with a combo box which allows multiple values to the back end of a split database. When I do this and then open the form on the front end I get the following error:

"The search key was not found in any record." The values are chosen from a value list that I created.If I add a combo box field that does not allow multiple values I can open the form fine.The database was created in Access 2007 and is now running on Access 2010.

View 7 Replies View Related

Queries :: Search Across Multiple Tables With One Query

Apr 18, 2013

Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.

I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.

I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.

View 7 Replies View Related

Queries :: Search Multiple Tables In Access?

Jul 19, 2013

I have a database built in Access 2000. It consists of the following tables:

45 tables each containing the same fields - ID, Vehicle, Fit, Part No, Qty, Level, Higher Assembly.

1 table containing the fields - ID, Part Number, Description, Issue No.

The 45 tables are each for a different vehicle build of materials, whilst the single table contains the additional common data that each vehicle form pulls in when the build of materials part number is added.

I want to be able to undertake a search across the 45 vehicle tables to look for a specific part number that I enter in the search box, and then if any is found, for all those vehicle to be displayed, along with the part number, description and issue number from the single table.

View 1 Replies View Related

Help Creating A Multiple Field Search Form

Nov 2, 2007

Hello, i have a database that simply holds all the information on one table at the moment and what im trying to figure out is how to create a form that i can enter data onto that searches all the different fields at once, instead of one search for one thing and another for another. Some of the fileds are Headset number, gasket number, make, model, engine cc, etc.. so what i want to be able to do is have a form with a text box for each of these fields that then searches the table to narrow down the search results. at the moment i have a search for each, say for one i enter the headset number, it searches and pulls up all records with that headset number, then i have a search for engine cc, but what happens is it searches all records again for the engine cc not following on from the search for headset number so i end up back with a load of records with diferent headset numbers but with the searched engine cc. Basically the search form needs to take data entered into each text bow for each field then the combined results from each, narrowing down the results. can anyone help? in laymans terms!!
Thanks
Paul:

View 5 Replies View Related

Search Multiple Tables From One Form Field

Feb 18, 2006

Hello,

Stuck on a problem and was wondering if anybody has a possible solution. I currently have the beginnings of a database setup that uses a search field on a form to find a reference listing using a part number input by the user. It finds the part number, and then displays all the information about it on another part of the form. My question/problem is, I now want to expand this database to multiple specialties, all with different parts in their respective listings, so they would have different tables. The tables would be the same format, just different information. No one table would ever contain the same info.

Q: How would I set up a form to search for a unique part number over multiple tables, and return that parts info?

It works great for one table, but I am stuck on the multiple tables.

Thanks in advance!

View 5 Replies View Related

Queries :: Search With Multiple Fields Result From Query

Dec 26, 2013

I want to make a form which will allow me to get data from a query and search using 3 fields parallel.

Attached the database in 2010 format.

Password of the db is "nolimit".

The query PTM & Equipment should be bound with the form and

If I enter month, PTM and equipment need rest 2 fields displayed.

Like choosing the above 3 fields from the drop down , the next 2 text box should display the rest two fields.

View 3 Replies View Related

Queries :: Date Range Search Of Multiple Fields

Nov 24, 2013

I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.

View 3 Replies View Related

Forms :: Multiple Field Search In Combo Boxes?

May 1, 2013

I would like to have 5 combo boxes from which users can choose fields to search, e.g.

cbo1 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo2 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo3 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo4 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo5 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE

criteria are stored in txtbox1, txtbox 2 etc. so the search string could be

WHERE cbo1 = txtbox1 AND cbo2 = txtbox2 etc.

If the user doesn't choose anything for a combo, the search should ignore that field.

View 6 Replies View Related

Forms :: Search For Multiple Values In Single Field

Apr 21, 2013

I have a table in which there are approximately 15k records. The name field has approximately 2500 unique values. I need to search the table for multiple names and return the results from the table. This would then be used as a report. Due to the users being very novice, a form is needed for the search.

Example:

Search for Smith, John; Wilson, Bill; Jones, Jeff

Return
Name Year Brand Make
Smith, John 1999 Oldsmobile 88
Smith, John 2001 Chrysler 300s
Wilson, Bill 1994 Porsche Carrera
Wilson, Bill 2007 Kia Sporty
Jones, Jeff 2004 Chevrolet Camaro
Jones, Jeff 2011 Chevrolet Impala

My knowledge of forms is basic, however, I can be a quick learner if on the right track.

View 1 Replies View Related

Queries :: Search Query Using Multiple Combo And Text Boxes

Aug 13, 2013

I have a table with all information on it, that is input via various forms, I then have different queries pulling information from all information to run reports off. These all work fine, my problem is my 'Search Form' - below

I have created a query that finds information from 'all information' using

Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*"

This is working on all text boxes, It only half works on the combo box's when I use

Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*"

If a selection is made in the combo box the query brings the correct results, however, if all the fields are left blank it should bring up every record, but it doesn't do this. I am certain it is the combo box's that are causing this anomoly as when I remove the combo box criteria it works perfectly again.

View 11 Replies View Related

Modules & VBA :: Search 1 Field Based On Multiple User Input

Jul 4, 2013

I have a search form that looks up a value in a field and lets the user know if it is there or not, they have now asked to put in multiple values to search... eg. searching containers number, I will put user input box where they can put these numbers

NYKU023561
TRLU102356
TCNU123023

This will search my container number field and show a pop up message box saying..
Please note below
NYKU023561 - has been found you cannot use this container
TRLU102356 - has not been found please check internal system
TCNU123023 - has beeen found you cannot use this container

Below is the basic search I had before which was all I needed.. how can I adapt this?

Code:
Private Sub Command256_Click()
If (CntSearch & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Dim CNT As String

[Code]....

View 4 Replies View Related

Combo Box Search Multiple Fields - Show Heading Of Each Field

May 3, 2012

I have created a combo box search for my form based on three categories, 'Student Name', 'Nationality', 'Age' using the wizard. When I click on my combo box in form view, I see 'Alex', 'UK', '19' and 'Stephen', 'Sweden', '22' in the dropdown list, but I do not see the headings 'Student Name', 'Nationality', 'Age' as the first item on the list.

View 1 Replies View Related







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