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.
I have never created a single query that is this complicated.
I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.
The current task at hand is to determine if each member has achieved or meet certain requirements each year.
They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?
I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.
So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.
So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F
I keep getting "You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)"
In this case it keeps complaining Callsign.
You did not enter an aggregate function in the TRANSFORM statement.
Here's the statement I created:
SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal
FROM [Work Hours]
WHERE (((Year([Date Worked]))=(Year(Date()))))
[Code] .....
It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don't see what is wrong with it.
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 RelatedI have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.
Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.
I am currently using Access 2010.
Right what im after sounds complicated but its quite simple.
I have a client database, in this database each client has 3 skill fields, i also have a form with a listbox and an unbound textboxes that are linked to the query so when the textbox is typed in the listbox updates.
Now ive sorted out the problem of not all fields showing it some of them are blank now the problem is.
When input text into one of the boxes the listupdates, now if theres text in two boxes. and theres data in one of each that matches it will show neither, because the query is looking for data that matches in both fields, now i need partial matches so it will give results of data in both fields.
not understanding fully what i mean?
i have zipped my db, if you open the db then open the form 'MainListRecTrades2nd' and fill in data in both primary and secondary trades that matches from the list you will see.
http://awilderbeast.com/CWSystem.zip
The solution i was thinking of would to have 1 textbox that searches all the fields and displays data from any of the fields in the list
Thanks for any help
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
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.
I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.
I have attached two example for your reference.
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!?
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.
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedI couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:
table Books:
- BookID
- Author
- Title
- Year
- Cabinet
table Papers:
- PaperId
- Author
- Title
- Year
- Cabinet
table Catalog:
- CatalogID
- Author
- Title
- Year
- Cabinet
And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.
So the resulting query would look like:
Type.....ID....Author.....Title....Year....Cabinet
Book.....001...John.....Good book....2005....C01
Book....002.....Mary....Other book...2006...C01
Paper....001....Albert...PaperABC....1987....C01
Paper...002....John.....PaperXYZ.....2006....C02
Catalog...001...Mark....Cat00A....1989.....C02
Catalog...002...Bill......Cat00B.....2004.....C03
etc.
Is this possible?
Any help is appreciated!
Thanks.
Daniel
i essentially have 2 table:
1. Table BIC
A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.
2. Table Original
A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.
** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.
What i need to do is create a query that will:
1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".
2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.
3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.
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 RelatedI have a search form, and it has 2 combo boxes, and 3 textboxes. I would like one of the textboxes to search 10 different fields for the inputted data. Along with that search all of the others must be able to be used to narrow down the criteria. How can I do this with SQL? Right now I have a select distinct, where, and I use And to include all 5 parameters. What can I do to use the one box to search numerous fields and still return options when coupled with the other search parameters. Thanks
Riley
Hey guys I need some help, I have attached my database.
I need to search multiple fields.
I need to search from field strserialone - strserialtwelve.
Thanks for taking the time..
I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.
I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.
An example of the parameters used for one of the checkbox fields in Query Builder is as follows:
Field: SumAnger: Sum([Anger]*-1)
Table: Default as only one table
Total: Expression
Show: Checked
This works fine.
My latest parameters for the date range are this:
Field: [cDate]
Table: Default as only one table
Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM!
Show: Checked or Unchecked makes no difference
Criteria: Between [From Date:] And [To Date:]
This gives a statement in SQL view of:
SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate]
FROM tblCommsLog
WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));
what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
View 3 Replies View RelatedIm trying to make a find page that looks up 7 fields and after reading about the Dynamically search multiple fields i downloaded the sample and changed a few things. But I have a few problems .
1) If you open the FRM_SearchAll from the navigation pane , it will not show the detail form by dbl_click or view button also will not show the picture .
2) If you open it the navigation form , it has the following error
Forms!FRM_SearchMulti!SrchText before you start it.
3) As above it will not show the detail form by dbl_click (it has Error)
Run time error : 3075
Syntax error (missing operator) in query expression '[Food]Snack a Jacks'.
4) If you try to view detail form with the button (the error comes up)
Syntax error (missing operator) in query expression '[Food]Snack a Jacks'
5) also will not show the picture .
Find the link by tacking on the following to this site's primary [URL] .....
It was, essentially, a type-and-show-as-you-go search functionality for an Access Form. It works great, except that I'm experiencing intermittent slowness when using the form. As I type, the characters appear very slowly and experience a significant lag time.
I only have about 70 records so far, so I'm pretty sure the slowness is not being caused by an overabundance of data. At other times, it goes super fast without any lag.
I want a search box on my db that will locate data from certain fields within my database. Today i came across the posting by John Big Booty with the above title and what he has done is exactly what i require.
Now i have followed all instructions to the letter however i keep getting an error which i have attached ......
The code currently works for searching data on ONE single table.What if I wanted it to search data simultaneously on TWO similar tables? I was messing around trying to build up a query that uses both tables but it failed.
View 3 Replies View RelatedPlease bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :
Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"
End Sub
The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .
I want to create a parametric search for tools on the shop floor based on multiple fields [using VBA]
I've had a good search and turned up lots of useful things; but each example insofar has been fairly specific as opposed to a guide on how to implement it in a wider setting.
Let us say, for example, that my database has three fields each with a bound combo box: location; house-type; number of bedrooms
As I type in location, I want it to filter my results in the other boxes to that location (should be a simple case of applying a filter?) - which leaves me with all the house-types and bedroom-counts in that location.
As I type in the house-type, I want it to then narrow down the search again to filter the "location"+"house-type" to leave me with the available number of bedrooms.
What is the best way to implement this, given that I have about 20 fields*, some of which will inevitably be blank: as the user types in (or selects using combo boxes) the data required, it narrows down the search - and finally there's a "search all" button which returns all results matching the current criteria.
When I say "implement" I mean what underlying structure should I use. Is it best to create a query and update the query as items are entered, or would it be better to filter the form that's having the data entered?
The fields are organised sensibly into sub-forms for various characteristics (i.e manufacturing data, materials data, cutting data, etc, etc), don't worry - it's note a huge mess - and the code is all there to pull the data together as required!
i'm a begginer at access, so please excuse me if this question is very basic.
i have a multiple table select query which shows me which user has which piece of a equipment at that given time.
I also have a form that logs any problems the users have with the equipment, what i want that form to do is show the results of the query based on inputting the users name. ie put the serial number and model number into the relevent fileds on the form.
I can get the form to run the query but that just shows it in the query window, i need it to show on the correct feilds on the form.
Hope that makes sense and that somebody can help me.
Cheers
Burty
Hello,
My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts).
The relevant fields are:
PERS
pers_id (long) ---> primary key
pers_forename (text)
pers_surname (text)
CONT
cont_id (long) ---> primary key
pers_id (long) ---> foreign key
come_id (long) ---> foreign key
cont_number (text)
The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".
Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ...
I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1):
SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone
FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id
WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1));
This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.
But now i also want to see the email address (come_id = 5):
SELECT PERS.pers_forename, PERS.pers_surname,
CONT.cont_number AS Phone, CONT_1.cont_number AS Email
FROM CONT AS CONT_1 RIGHT JOIN
(PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id)
ON CONT_1.pers_id = PERS.pers_id
WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1)
AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5));
It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression.
The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither!
I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ...
I hope that someone of you can explain this.