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 Replies
ADVERTISEMENT
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
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
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
Nov 28, 2013
Can we have two tables linked to one search query? I've some data that is confidential and woul like to have them in two different table. When I search for it using query form, only the relevant table results will be shown?
View 4 Replies
View Related
May 13, 2014
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
View 7 Replies
View Related
May 7, 2013
I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
View 9 Replies
View Related
Feb 5, 2015
I have a question where I need to effectively invert the results of a select query.
I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary
A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".
I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplication due to the various other "diary" entries in the table".
I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
View 5 Replies
View Related
Jul 30, 2014
Im working on a cross comparison database for the three food vendors for my boss. What is the best way to search across the three tables? They all have similar record titles but arent all uniform, and I cant change the records because I have to have the record names the same as the files that are being exported from the vendors websites. What would be the easiest way to search across three similar but not uniform tables in one database? Ive also tried to make three buttons to update the lists after I download the items to have up-to-date prices on the products but am unsure if they are actually working correctly.
View 3 Replies
View Related
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
Jun 30, 2015
I am fairly new to Access and am having some trouble creating a search form. I have numerous tables, which contain information regarding clients and contracts, that are all connected by relationships. I would like to create a form in which I can type in the name of the client and pull up all the information regarding it from the various tables (i.e. when it was signed, the amount, and etc).
View 2 Replies
View Related
Sep 21, 2004
I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.
I would like them to be able to query Retailers, Distributors and Products.
The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.
Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
Code:
View 5 Replies
View Related
Jan 9, 2014
I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records
Nestling Banding Records
Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
View 10 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
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
Jan 4, 2014
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".
View 4 Replies
View Related
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
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 2 Replies
View Related
Nov 29, 2007
Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?
Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...
View 4 Replies
View Related
Apr 10, 2013
Is it possible to set multiple conditions across multiple tables using OR in a where clause? For instance, can you run where......
(table1 = 'test') or (table2 = pass) or (table2 = "fail) AND (class = 'SQL');
View 1 Replies
View Related
Jun 29, 2006
Hi all
I have a query linked to a report that prints a worksheet specific to a individual work item. This report/query picks up the Work_ID value on a form. I have 2 other forms displaying the same work with different amounts of detail. Rather than create a new report/query to run from each form, I am trying to use the one query/report from each form.
The problem is that I cannot get Access to recognise the Work_ID value from the other forms. I have tried the following:
In the Work_ID criteria field building an SQL statement as below
[Forms]![frmVCRUpdate]![Work_ID] Or [Forms]![frmVCRShort]![Work_ID] Or [Forms]![frmVCRLong]![Work_ID] - This does not work, it keeps asking for the frmVCRUpdate Work_ID value when I try to run the query from the other forms
Adding 2 extra Work_ID Values to the query and on the 2nd and 3rd criteria lines specifying that it look for the Work_ID value from the other forms but I get the error above.
Any suggestions on how I can make this work would be appreciated, I'm not sure what else to do.
Craig
View 2 Replies
View Related
May 25, 2014
I have three tables, tblPeople, tblAddresses and tblVehicles. I have a form called frmPeople that shows a single person record, and has subforms with the list of addresses and vehicles for that person (each record has several). I want to create a single search form with each of the fields in my tblPeople, tblAddresses and tblVehicle tables, and allow the user to type anything into any of the fields on the search form, and have the search form fetch records that match.
So far, I have the query built, and the tables all linked the right way (I think), and I have all the search fields coded. And it works GREAT... except for this one little issue: If I do a search, and I put "Smith" in the last name field, I will get several Smiths in my search result, one for each address and vehicle combination. In other words, if I have ONE Smith in the table, and two vehicles and two addresses for that Smith, the query results give me FOUR Smiths! One with Vehicle1 and Address1, one with Vehicle2 and Address1, one with Vehicle1 and Address2, and one with Vehicle2 and Address2.
What I would LIKE is to have ONE record in the query results, and that record shows me that one Smith. And if I then search for "Plymouth" in the VehicleType field, and I have Smith in the name field, I'll get ONE record, and it will be the one of Smith with his Plymouth (Vehicle2).
Here is how my query looks right now (this one ONLY has the last name and vehicle search function to save space), and this is the one that gives me multiple results (which I do NOT want, unless multiple PEOPLE match the search criteria):
Code:
SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName, tblPeople.PersonID, tblAddresses.Address, tblVehicles.Vehicle, tblVehicles.Plate, tblVehicles.VehicleYear
FROM tblPeople LEFT JOIN tblAddresses ON tblPeople.PersonID = tblAddresses.PersonID
WHERE (((tblPeople.LastName) Like "*" & [NameField] & "*")
AND ((tblVehicles.Vehicle) Like "*" & [VehicleField]));
Did I write the query wrong?
View 4 Replies
View Related
Mar 30, 2006
Hello
I have created a database for my department to log all of our jobs to keep track of them and I want to create a simple search for them but I am having difficulties.
I am familiar with creating queries to search tables for matching records, but is it possible to get search criteria from the user (ideally from a search form they fill in) to form the query?
For example, I would like the user to be able to query the jobs by month and/or business area and/or supplier...is this possible?:confused:
Any help gratefully received!!!
:)
View 2 Replies
View Related
Mar 4, 2013
I am trying to get a multiple keyword search query working.... the single search is working however when i use the Multiple Keyword search the data sheet does pop up but no matter what i put in i just get a blank table and under ID it says "NEW"
The Keywords Column has a bunch of keywords separated by commas on each row. Sometimes there will be the same keywords on different rows under the keyword column... and i want it to return any rows that have 1 or more of the keywords in it or if it is null just to return them all....... this is the statement I am trying...
SELECT Sea.ID, Sea.Category, Sea.Title, Sea.Author, Sea.Organisation, Sea.Date, Sea.Keywords, Sea.Reference
FROM Sea
WHERE (((InStr([Keywords separated by commas,Blank=All],[Keywords]))>0 Or (InStr([Keywords separated by commas,Blank=All],[Keywords])) Is Null));
View 3 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Dec 16, 2013
I am having some difficulty putting togather a small database (attached).
Basically I want to be able to search for mutiple words and get the results
There are 3 main columns in the main table
1) Shop
2) Part Number
3) Part Type
Problem 1:I have created a lookup table (with check boxes) for ComboBoxes in a form but I do not know how to connect it so that when I click on the comobo box it gives me the all different (Shop numbers/part number/Part types) to select from.
Problem 2:Once I have made my selection I want to search and run query to show me the results. I know that once multiple items are selected in a combo box they are separated by commas/space, so the search must take that in account.
Other nice to have
1) If Shop 1 is selected then only the parts which were sold from shop 1 to appear in the Part number Box & visavera & same goes for Part type.
2) Ability to do a wildcard search so if I Enter "A" in part number I can see all the resealts
View 12 Replies
View Related
