Query Search Help Required
Dec 10, 2006
Hi,
I have my Query looking at multiple colums on a single table.
The table has approximately 2000 records.
I would like to create a Query that searches for criteria in one colum only. I have created a Query and providing i search for exactly what is written then it shows results, e.g. when i seach for Horse Furniture then i get many results but if i search for Horse i get no results?
I would like the search to be dynamic, so when the end user runs the query they are presented with a dialog box asking what they wish to search for (this i have no problems with) however i need to know how to get my query to be dynamic and provide results based upon single information.
is this possible and if so then how?
Thank you in advance for your help, i have been banging my head for a while on this one and am getting no where.
Bev:mad:
View Replies
ADVERTISEMENT
Apr 11, 2013
I'm new user converting over from Lotus Approach. I'm using Access 2013.
I have a database with two tables being Suppliers and Products. This is a one to many type database with Suppliers being the one table.
Both tables have their own data entry forms.
In the Products table and data entry form is field named Category with a value list of about 20 different values. The query I need to create is to search on the field value from one of the listed values in the associated combo box.
I have set up the basic query using the Query wizard. To run This I have to entry the value I wish to search for in the Query in Design mode.
My problem is now I want to drive the query from a new form. On the new form I have created a combo box where the field list is the same as on the product data entry form.
I need to use this combo box to select the Category field value I need to search for and then click on the button to run the query. I seem to be stuck on the SQL code to achieve this...
View 2 Replies
View Related
Nov 3, 2014
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies
View Related
Sep 13, 2007
I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible?
Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.
View 2 Replies
View Related
Sep 12, 2005
Hi All,
I am relatively new to Access having created my first database a few months ago. I have hit a problem and I am not sure if I can solve this with queries or I need to move to Visual Basic.
I want to create a table containing the stock a product for each day for the next sixty days. I have the current stock in one table, a monthly forecast of sales in another and purchase orders in another.
I want to use the monthly forecast divided by how many days in that month and move forward day by day allowing for purchases coming in.
The tables (simplyfied) are :-
Stock Table.
eg.
Code Stock Qty Date
101 10 11/9/05
102 200 11/9/05
Forecast Table.
eg.
Code Month Fcast Qty
101 P09 30
102 P09 90
Purchase Order Table.
eg.
Code Due_Date PO Qty
101 13/9/05 100
The table that I would like to create would contain Code, Date and Stock.
eg.
Code Date Stock
101 12/09/05 9
101 13/09/05 108
101 14/09/05 107 ...........
102 12/9/05 197 ...........
Any ideas/direction would be welcome.
Carl
View 4 Replies
View Related
Mar 28, 2007
Hi All
Im after some help with a query im building.
I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.
Each site will take a quiz and the problem I am having is with the statistics side of things.
What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.
To summarise I need 10 percentage correct fields, broken down by site.
Please can anyone help?
Many Thanks
Dazstarr
View 5 Replies
View Related
May 17, 2007
Hi
I am struggling with something im sure is quite simple.
I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.
What I need to do is crosscheck the calls answered by employee in both tables. For example:
Table 1 has 3 records for John. (This means 3 calls answered).
Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).
What I need is a report with both these sets of data and a new field with shows the the difference between the two.
Can someone please help me.
Many Thanks
View 4 Replies
View Related
Jan 29, 2008
I have a table set up for training with the following fields. Date, Employee Name, Class Name.
I have entered all the current data. I want the database to tell me who doesnt have a class completed.
I also would like a code to put in my query that will show recurrent date, although there is different recurrent dates dependent on the class.
Any help would be appreciated!
Thanks in advance.
View 4 Replies
View Related
Jan 11, 2008
Hi everyone.
What I currently have is 2 columns in one table 1 is required and 1 isnt.
However what i need to do is work out the sum value of the greater amount from each line.
IE
A B
1 2
1 0
2 1
which would give a total of 5 as its adding B1+A2+A3
I tried creating a 3rd columb and entered this IF Formula but apparently access doesnt support If formulas
=If([claims]![Mileage1 IR]>[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])
my plan was to get the "IF column to show me the higher amounts which could then be totalled up.
Any ideas where I may be going wrong?
Kev
View 12 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
Apr 27, 2007
I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).
There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.
View 2 Replies
View Related
May 11, 2007
My database is built using Access 97.
I have the following table and 2 queries:
Table: “Materials Master Sheet”
field: “Material”
field: “Re-Order Level”
field: “Re-Order Quantity”
Query (crosstab): “Most Recent Count Numbers”
field: “Material Name” (joined to table’s “Material”)
field: “Count Quantity”
Query (crosstab): “Orders Pending Delivery”
field: “Material” (joined to table’s “Material”)
field: “Total Orders”
I am attempting to create a query called "Count vs ReOrder" with the following fields:
1. “Material Name” - comes from “Most Recent Count Numbers” query
2. “Order” - if the inventory count has reached the reorder level point, then 1, else 0; criteria: 1
expression: IIf([Most Recent Count Numbers]![Count Quantity]<=[Materials Master Sheet]![Re-Order Level],1,0)
3. "Suggested Order" - reorder quantity - count of any orders pending delivery; criteria: > 0
expression: [Materials Master Sheet]![Re-Order Quantity]-[Orders Pending Delivery]![Total Order]
This works great if all materials have orders pending delivery. However, if there are no orders pending (material not displayed in crosstab query), the material is not displayed (even though, in reality, the suggested order should be >0)
Currently does this:
material 1 - count = 1, re-order level = 2, re-order quantity = 8, orders pending = 4: shows material and suggests ordering 4 (correct)
material 2 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 4: material not shown (correct, as suggested order would be 0)
material 3 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 0: material not shown (incorrect! should be shows material and suggests ordering 4)
How can I go about addressing the missing “Orders Pending Delivery” numbers?
View 2 Replies
View Related
Nov 7, 2014
I've been trying unsuccessfully for weeks to try and query a usable data set that would later be used for calculated values in a report.
I'm building a Hospital Acquired Infections Database for my facility to record data and process reports automatically. Previously, I built the system in Excel using VBA and userforms, but the size of the file has become too cumbersome, and now takes too long to process my reports.
So here's the issue.
UNIT_DATA TABLE:
UNIT
INF_MONTH
INF_YEAR
PT_DAYS
CVC_DAYS
IUC_DAYS
VENT_DAYS
APV_DAYS
PT_LEVEL TABLE (There are many fields in this table for recording purposes but I will only list the ones I need for reporting purposes):
UNIT
INF_STE_MAJ
SPECIF_SITE
INF_MONTH
INF_YEAR
So the important numbers I can't seem to collect are the total number infections per month from the PT_LEVEL Table for only select SPECIF_SITE infections.
the Tables are joined Left to Right as Follows:
UNIT_DATA.UNIT - PT_LEVEL.UNIT
UNIT_DATA.INF_MONTH - PT_LEVEL.INF_MONTH
UNIT_DATA.INF_YEAR - PT_LEVEL.INF_YEAR
The UNIT_DATA Table is a monthly collection for the 5 metrics (PT_DAYS, CVC_DAYS, IUC_DAYS, VENT_DAYS, APV_DAYS) regardless of whether or not the number for each is zero. Every unit in the database will have a record for each month and year.
The PT_LEVEL Table will only have records if and when a patient develops an infection. This means there is no cumulative monthly data for the PT_LEVEL Table Data based on SPECIF_SITE infection type.
I need to first do a roll up count for every month and year in the database, for which I was initially trying to use the UNIT_DATA table for since it contains every month and year. The problem is when I try to query the SPECIF_SITE from PT_LEVEL, I can get the number of Infections for months where infections where present for each unit and null values, for each month, and each year in UNIT_DATA, but when I include a where condition to narrow the view to only selected SPECIF_SITE's it only shows data for that SPECIF_SITE for months where they occured.
I need a cummulative monthly aggregation of the totals for a selection of SPECIF_SITE infections, for every unit, for every month, and every year, since July 2010.
View 14 Replies
View Related
Dec 10, 2007
Hi
Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds.
1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of?
2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.
My knowledge of Access is Intermediate.
Kind regards
View 14 Replies
View Related
Jul 10, 2013
I am trying to create an update query. I am trying to update a field in a table with the current date as a request.
I have a table named tblTest and a field named Date2 that I am trying to update with the current date, the button that the VBA is applied to is in a form name frmTest. This is my code:
Private Sub Command39_Click()
Dim t1 As Date
t1 = Date
db.Execute("update tblTest set tblTest.Date2") = t1
End Sub
But when I press the button I get:
Run time error '424'
Object Required
It highlights the 4th of code....
View 8 Replies
View Related
Jul 29, 2005
I am completely new to this. I have a tape library and in the database I have a tape description. I want to create a search that will include every word in every tape description cell.
If someone wanted to find a tape with an elephant they would go to this query and type in the word elephant and every tape that has the word elephant in the description would appear.
How can I do this?
View 2 Replies
View Related
Feb 7, 2006
I have form on my database that runs a search query. Basically what should happen is the user types in what they want to search in a text box and the result is displayed in a list box.
This currently works but only if the spelling of the search is entered 100% accurately. e.g. if im searching for Access, no results would be displayed if i just typed in 'acc' but if i typed in 'access' it would display a result.
I would like it so they could possibley type in the 1st 3 letters and the results would appear.
The problem appears to be that it is searching the letters of the word from last to first, rather than first to last.
My query criteria is:- Like "*" & [Forms]![frm_clientsearch]![search]
Thanks for any help.
View 2 Replies
View Related
Sep 5, 2007
hi im new here,
im making a database in access for my dad hes doing a Parts database for his company and ive got 5 fields in my table manufacturer, part number, description, location and stock quantity, my question is i want to search for a part number eg 001 and its finds it and shows me a the des, manu etc etc
View 3 Replies
View Related
Sep 5, 2007
hey everyone,
I have a problem with my query, I can search for available ID's with a start & end data. But what I need is also to search on for how many people the ID is suppossed to be. And after that when you click on "boek bungalow" you go to the booking form. In this form the 'bungalow id, begin datum & eind datum' will fill in automatically. but what I need is the field 'aantal personen' to fill in automatically to. I hope my problem is understandable for you guys.
thanks alot guys,
Jeroen
View 3 Replies
View Related
Sep 14, 2007
Hey,
I've done some review on parts of my search. I think I have the code right for my MO search but every time i click my search button it says i canceled my previous operation. Not really sure what I coded wrong perhaps someone here knows. If someone can help me get just the MO part of the search program I'm making work i can backward engineer it for the whole program. Currently I've been referencing a program created by a "Jason M" which pbaldy refered me to. It's been quite helpful. So if someone could help me complete this search string I would be in your debt. The search is in the query form. I have it attachd here. Basically I just want it to display in the sub report the MO number I type in the search box and display those once I click search. Thanks for all your help guys and being patient with a lowly network admin!
View 4 Replies
View Related
Feb 15, 2006
Alright guys, I have a question. I have this form that serves as a search query. What happens is I have it set up to enter information in the text box in the form header and then have it update the detail section. It works just fine if I put exact information but I want to be able to search for say, "Sam" and have it produce "Same Person" and "Sam Hokins" or whatnot (just an example)
Here's my code for the command button:
Private Sub Find_Click()
On Error GoTo Err_Find_Click
Dim LSQL As String
Dim LSearchQry As String
LSearchQry = SearchQry
LSQL = "select * from tblAddressBook"
LSQL = LSQL & " where ClientName LIKE '" & LSearchQry & "'"
Form_frmFindClient.RecordSource = LSQL
MsgBox "Results have been filtered with Client Names containing " & LSearchQry & "."
SearchQry = ""
Exit_Find_Click:
Exit Sub
Err_Find_Click:
MsgBox Err.Description
Resume Exit_Find_Click
End Sub
Thanks!
View 2 Replies
View Related
May 31, 2006
I would like to make a search query using one table. I would like to have a search button so I can search on 4 of the fields on the table. Anyone knows? Thank you
View 4 Replies
View Related
Jun 3, 2006
On a form there are 5 fields with two tables. There are two tables, first table contains all the five columns. Second table contains three columns which are fixed.
First two fields are entered by user, third field is combo box getting the values from the second table, I select the value from combo box and in the next two text box values corresponding to combo box value from the table. After all values appear on the form in respective fields I want that when I click add button all values should go to first table.
Thanks in advance
View 1 Replies
View Related
Jul 26, 2005
I'm using a query to look up report criteria by type, response etc using a query to search value in a field ex [Reply]=[Forms]![Form1]![Cmbreply] Or [Forms]![Form1]![Cmbreply] Is Null
I'm trying to work a month look up the problem is the date is set July 25,2005 and my field is searching for the month July not the specific date. I'm stuck on how to look for the month only and ignore the day and year - any ideas
Thank you
View 1 Replies
View Related
Apr 21, 2006
Hi
I hope someone can help me. I am using Access 2003 to create a database to keep a track of projects in my craft magazines.
I have a table that gives the magazine name, issue no, project name, page no and keywords. When I enter my data I will use various keywords to describe the project. For example, for a football card for a teenage boy I would use keywords such as football, boy and teenager.
What I need to be able to do is have a query based on this table that if I search for "football" it will pull up all records where I have used football as one of the keywords.
In my current query I can do this providing "football" is the only keyword I have used, but if I have used more than 1 keyword in my table then my query will not show any results.
Is there any way I can do this?
Hope you can understand what I am asking, and if someone can help, I would appreciate simple instructions as I am not very experienced with access
Thanks
Kathie
View 2 Replies
View Related
Jul 3, 2006
Hi guys,
Cant seem to work this one out. I have quite a complex search form. The underlying query displays the results in a list box on the same form.
So far I have used the following expression for all the fields on my form (whether text or integer values):
Like "*" & [Forms]![Frm_FrmSearch]![AssetName] & "*"
This appeared to work correctly. However, now my Asset Management System is storing a number of Equipment Type's. As one of the query criteria is Equipment Type ID it means that selecting PC (1) also displays the details for Printer (11), Scanner (12) etc......
I know why it does this (because these numbers start with a 1 and I am using a like expression). However I cannot seem to get it working.
The equipment type value is present in every record so I dont think I can use =FormValue or FormValue Is Null. I did try:
=[Forms]![Frm_FrmSearch]![EquipmentType] Or
Like "*" & [Forms]![Frm_FrmSearch]![EquipmentType] & "*"
but it seemed to skip the first parameter and still displayed printers etc. as before.
Any ideas?
View 1 Replies
View Related
