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 Replies
ADVERTISEMENT
Mar 24, 2015
I have made a query to search for some items on a table. I want there to be a button on the search form that says 'search' and takes you to the corresponding page on the form. (The form has multiple pages in, all with different products on) Is this possible?
View 4 Replies
View Related
Jan 22, 2015
I have made a database to show me dates that I need to check various documentation from my contractors.
Once a month I want to print out a query/report to tell me what checks need to be made in the following month.
The dates are
Licence Check
Licence Expiry
Van Ins Exp
GIT Expiry
MOT Expiry
Passport Check
Vis Expiry
I need a query table that shows a list of names that have anything to be checked in the month.
So the Column headers would be Name, Surname, Licence Check, Licence Expiry, Van Ins Exp, GIT Expiry, MOT Expiry, Passport Check, Vis Expiry.
Its easy to do a list with one date but when I add multiple dates into the query it looks for names and surnames with the date within the next 30 days for every date and therefor brings back no records.
I have attached the picture. Obviously not all the records will show dates. Some will be blank.
View 14 Replies
View Related
Jul 7, 2014
I am importing data from a supplier. The table has many invoices included and varying items on each invoice. I need to find a way to separate each invoice with all the included items. For example:
inv# pieces linedesc amount
11 500 nuts $20
11 100 bolts $15
11 75 washers $10
12 150 cameras $75
13 10 desks $150
13 10 lamps $50
View 4 Replies
View Related
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
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
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
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
Jan 6, 2005
How to use like to search for items containing filed
I have a table as such and want to have a select statement look for instances where the RequirementName has Evaluation>Child this is used as the row source for a combo box.
Table Name: Requirements
Column Name: RequirementName
Observation:
Evaluation:
Evaluation>Child
Evaluation>Child>Site
Evaluation>Child>Semester
Evaluation>Child>Supervisor
Evaluation>Child>Certification Number
Evaluation>Child>Language
Evaluation>Child>Speech Disorders
Evaluation>Child>Speech Disorders>Articulation
Evaluation>Child>Speech Disorders>Voice
Evaluation>Child>Speech Disorders>Fluency
Evaluation>Child>Speech Disorders>Dysphagia
Evaluation>Child>Related Disorders
Evaluation>Child>Audiology
Evaluation>Child>Total
Treatment:
Treatment>Child
Treatment>Child>Site
Treatment>Child>Semester
Treatment>Child>Supervisor
Treatment>Child>Certification Number
Treatment>Child>Language
Treatment>Child>Speech Disorders
Treatment>Child>Speech Disorders>Articulation
Treatment>Child>Speech Disorders>Voice
Treatment>Child>Speech Disorders>Fluency
Treatment>Child>Speech Disorders>Dysphagia
Treatment>Child>Related Disorders
Treatment>Child>Audiology
Treatment>Child>Total
Evaluation>Adult
Evaluation>Adult>Site
Evaluation>Adult>Semester
Evaluation>Adult>Supervisor
Evaluation>Adult>Certification Number
Evaluation>Adult>Language
Evaluation>Adult>Speech Disorders
Evaluation>Adult>Speech Disorders>Articulation
Evaluation>Adult>Speech Disorders>Voice
Evaluation>Adult>Speech Disorders>Fluency
Evaluation>Adult>Speech Disorders>Dysphagia
Evaluation>Adult>Related Disorders
Evaluation>Adult>Audiology
Evaluation>Adult>Total
Treatment>Adult
Treatment>Adult>Site
Treatment>Adult>Semester
Treatment>Adult>Supervisor
Treatment>Adult>Certification Number
Treatment>Adult>Language
Treatment>Adult>Speech Disorders
Treatment>Adult>Speech Disorders>Articulation
Treatment>Adult>Speech Disorders>Voice
Treatment>Adult>Speech Disorders>Fluency
Treatment>Adult>Speech Disorders>Dysphagia
Treatment>Adult>Related Disorders
Treatment>Adult>Audiology
Treatment>Adult>Total
Observation:
Evaluation:
Evaluation>Child
Evaluation>Child>Site
Evaluation>Child>Semester
Evaluation>Child>Supervisor
Evaluation>Child>Certification Number
Evaluation>Child>Language
Evaluation>Child>Speech Disorders
Evaluation>Child>Speech Disorders>Articulation
Evaluation>Child>Speech Disorders>Voice
Evaluation>Child>Speech Disorders>Fluency
Evaluation>Child>Speech Disorders>Dysphagia
Evaluation>Child>Related Disorders
Evaluation>Child>Audiology
Evaluation>Child>Total
Here is what I have so far and it does not seem to return anything.... this line is where i haveing trouble "AND RequirementName LIKE '%" & subcategory1.value & "%' "
Private Sub Subcategory1_AfterUpdate()
Dim sRequirementName As String
sRequirementName = "SELECT Requirements.RequirementName " & _
"FROM Requirements " & _
"WHERE Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' " & _
"AND RequirementName LIKE '%" & subcategory1.value & "%' "
Me.RequirementName1.RowSource = sRequirementName
Me.RequirementName1.Requery
End Sub
View 2 Replies
View Related
Mar 6, 2008
I am trying to learn to use MS access 2007 and have a query about copying and pasting.
Is it possible to paste the same line of text into multiple lines or do I have to arrow down and paste into each individual box?? I can highlight any number of boxes in the same column but when I click paste it only pastes into the first highlighted box.
Any help would be really appreciated.
Thanks
View 2 Replies
View Related
Dec 13, 2004
I have to redo the company I work for’s insurance program and they want it done in access. I’ve hit a big snag though. Not only are my Access skills very rusty. But also I have a very complicated task here! :)
I would need a customer table an insurer table and an adjuster table. These tables would link to a Claim table. In the claim table I need to make it show the items that the person is going to get with there insurance money by getting details from a table with all of the information for the items.
But how do I make it so you can add more then one item to the claim?
Some people may have 1 item to replace some people may have 20 items.
Hopefully someone understands what I mean here
Thanks Heaps
Ashley Colman
View 6 Replies
View Related
May 4, 2005
I am trying to enter multiple parts into the same field and using commas to separate them. Is there a way to create multiple records with the same information, but change the part numbers in them according to comma's?
Would look like this.
Primary Key Location Part number
789 bin89756 89756,87564,78965,12565,45896
I want it to do this in the end.
Primary
Key Location Part number
789 bin89756 89756
790 bin89756 87564
791 bin89756 78965
792 bin89756 12565
793 bin89756 45896
View 2 Replies
View Related
Mar 24, 2014
I'm trying to build an database for aircraft operators. I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.I've got following controls on my form (unbound):
1. AircraftType (combo box) from tblAircrafts
2. CompanyName (combo box) from tblListOfAircraftsOperators
3. TeailNumber (text box) from tblAircraftOperators
4. AirportNameSearch (combo box) from tblAirports
5. PassengersNumber (text box) from tblAircraftOperators
6. ManufactureYear (text box) from tblAircraftOperators
7. SourceSearch (combo box) from tblInfoSource
8. CountrySearch (combo box) from tblCountry
9. CategorySearch (combo box) from tblAircraftCategory
10. EamilToOperator (text box) from tblAircraftOperators
11. InteriorPhoto (Bound object frame) from tblAircraftOperators
12. ExteriorPhot (bound object frame) from AircraftOperators
I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:
SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber, AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto, AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType
FROM tblAircrafts INNER JOIN (tblAirports INNER JOIN (AircraftOperators INNER JOIN tblListOfAircraftOperators ON AircraftOperators.CompanyName =
[code]....
View 2 Replies
View Related
Nov 23, 2006
Hello to all .
Have you got any idea ?
I have a List Box in a table and it is set to permit multiple items selection
from it .
The fact is I can select, as I wanted, multiple items from it through
the Form but when I go to the correspondent table to see the results
the selected data from the List Box isn't there .
Note all data selected from the List Box is visible trough the Form but
not the table .
Has this problem anything to do with mismatch in data type configuration on the table ?
Lots of thanks in advance
Miguel
View 1 Replies
View Related
Feb 1, 2005
Is it possible to select more than one item in a combo box? I have attached a sample of a database I am working on. I have a table called Interests and on the Bookings form, I would like to be able to select more than one interest in the Interests combo box, but I can't figure out how to do it. In databases I have seen, you simply have to hold down ctrl or shift to make multiple selections - that's what I want to do.
Can anyone help, please?
Thank you!!
View 13 Replies
View Related
Jan 30, 2007
hello once again
Scenario:
I currently have a form that the users can browse through listings of items for sale. While going through the records, they can mark with a checkbox of which items to include in a report to send in an email. The check box is currently part of the listings table, and i then run a query to pull items with the box checked, then put those in to a report, then they can email.
Problem:
about 5 users use this database, so if one user is selecting items that they want it's going to cause a problem for another user to select only the items he wants since it is all updating to the same table. Ex, if I select items 2 and 3 and another person selects items 4 and 5, when I click to show the report, its going to show items 2,3,4,5. Make sense?
Solution:
Since there are only 5 users, I made them all aware of this, and also created a reset selected email checkbox button, that sets the checkbox back to unchecked so the next person can select their own items and email. There has to be a better way to fault proof this, I just can't think of one. Any ideas would be greatly appreciated!
View 2 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
Mar 24, 2006
Hi,
I would like to be able to select multiple items from a dropdown list like we often see on web forms. The kind where you hold down the control key to select up to 5 items. Is this possible on an access form? If so, how would do we implement, and how is such data be stored?
Thanks!
~Bruce
View 12 Replies
View Related
Aug 6, 2013
I'm trying to setup a listbox so that multiple items may be selected and removed at once. The Listbox Multi Select property is currently set to "Extended" and I have the following code on the onclick event of a command button:
Code:
Private Sub Command12_Click()
Dim i As Integer
For i = List10.ListCount - 1 To 0 Step -1
[Code]....
I have tested selecting 4 or so records on the list box and then pushing the remove button but it seems that the only record that is removed is the selected record that is furthest down on the listbox. The other selected records become unselected and must be reselected in order to continue removing records.
View 1 Replies
View Related
Jan 9, 2014
In my form I have the listFunctions list box set to Multi Select "Extended" in the following code contains a line to execute a query based on the selected items in a listbox. but for some reason instead of only changing the selected items it is changing all items in the listbox. I stepped through the code and it is looping the correct number of times based on the amount selected but is still changing all.
eg.if I select 3 items from the list, it loops through the execute 3 times.but the total 6 items will change.
Code:
Private Sub cmdEdit_Click()
Dim varItm As Variant
Dim sSQL As String
Dim ssSQL As String
[code]...
View 1 Replies
View Related
Jul 13, 2013
I have a form with a listbox in which users should be able to move the items up and down. The listbox has 4 columns and multiple selection is enabled.
For a NON multiselect box I have it working, and also for a multiple selection box but in that case it works only for one row at a time.
For a NON multiselect listbox:
Code:
cmdDown_Click()
Dim i As Integer
Dim t1 As String, t2 As String, t3 As String, t4 As String
i = selectie.ListIndex
t1 = Nz(selectie.Column(0, i))
t2 = Nz(selectie.Column(1, i))
[Code] ....
You can select an item and then press "Down" as many times as you want to put the items as "down" in the list as you want it to be.
Multiselect listbox
This code also works for a multipleselection listbox when one item is selected, however, after you press "down" the item is still selected (highlighted) as the code reselects is (last line) BUT the value of selectie.listindex apparently is set to "-1".
Pressing "Down" again generates an error (i = -1). I can evade the error by adding "if selectie.listindex <=0 then exit sub", but that doens't fix the problem that the only way I can manage to reset the listindex to the new "position" of the item is to click on it again and THEN press "Down".
To fix this and be able to press "down" multiple times listindex should be set to the new value.
Adding a "me.selectie.listindex = i -1" doens't work (error), it seems like this value is readonly.
I can't find a way to "simulate" a mousepressed selection and really set the listindex.
The other problem is of course that this code doens't support moving multiple items at once: listindex points to the last selected item, but only one.
So, I tried another piece of code to move multiple items, not using listindex (since that resets to -1), but I run into another problem
Code:
Private Sub cmdDown_Click()
Dim var As Variant
Dim i As Integer
Dim n As Integer
Dim t1 As String, t2 As String, t3 As String, t4 As String
n = Me.selectie.ItemsSelected.Count
[Code] ....
The problem with this is: I can select multiple items and press "Down", but the problem now is that the selection is lost as soon as the code removes the first item, and the 2nd loop skips the if selected(i) = true (nothing is selected anymore).
The me.selectie.selected(i+1) = true doens't work since that would only reselect the first item after moving it.
The code "forgets" which items were selected and moves only one item...So I guess I need to put the indexnumbers in memory while moving the items.
I have been searching a lot, but can only find VB-solutions. In VB it's a lot simpeler using f.e. the .list property of a listbox, which is not available in MSAccess
The solution I'm thinking about is:
- set an array with the numbers of selected items
- put indexnumbers of the selected items in the array (f.e. 3 and 4)
- move items based on the indexnumers in the array
- when moving an item update the indexnumber in the array (3>4, 4>5)
- after moving all items reset the selection based on the array
It occurred to me then if I'm going to use an array anyway, I might as well load all items in an array, do the "resorting" and the reload the items in the list from the array. Might be more straightforward?
Btw...It seems VB has a simple solution to moving items: listbox.list(i) = listbox.list(i+1) or something like that moves an item. Even Excel seems to have this property but not MS Access!
View 1 Replies
View Related
Jan 14, 2014
Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
View 7 Replies
View Related
Nov 23, 2006
Can some one tell me why, on the attached database example, I can select multiple items from list boxes
"TipoCliente" and "FaixaEt" through "frmClientes" form but they are not passed forward to table "tblClientes" ?
Try to onpen attached database example and use form "fmrClientes" and select multiple items at
"TipoCliente" and "FaixaEt" list boxes by entering them with mouse click and Ctrl key pressed .
Select also single item from
"NivEns" Combobox and write anything on "Nome" and "Apelido" fields .
Go to the table "tblClientes" and you can see text fields "Nome" and "Apelido" and also the item "NivEns" from Combobox "NivEns" are all there but not the items you selected from "TipoCliente" and "FaixaEt" list boxes .
What is happenig ?
Lots of thanlks
Miguel
View 2 Replies
View Related
May 28, 2014
In appendix is .mdb file with this thema.
Inside you can find one form with listbox (with multi selecting ability).
I use this code :
Dim strSQL As String
Dim i As Variant
With Me.se1
For Each i In .ItemsSelected
SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery
Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.
BUT
2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.
View 10 Replies
View Related
