Ive had a quick look at other threads to see if this question has been asked already, so hopefully Im not repeating a previous question!
I am completely new to Access but have been asked to perform multi-criteria queries across multiple tables. I realise it would be easier to just put all the information into one table, but if anyone could explain how to do the above I would be very grateful.
Thanks
I have an events database with a description field which I would like to be able to search for multi criteria which would be determined by the user. For example, the user might want to search for all events which are Merchant Navy, Dieppe and Vimy related or they may want to search for only Veterans Week related events.
I've tried creating a form with text boxes that the user would put the criteria in and then basing the query on those text boxes using a like statement.My problem is that I don't know how many criteria the user will be using so I can have too many or too few text boxes. If too few, the user is not getting all of the records that they are looking for. If too many, the user ends up getting all the records in the database as the system uses the bank boxes to bring back all values, so basically all records.
is there anyway that I can have only one text box where users would enter in as many or as few crietia words seperated either by a , ; space etc., and the system would return all records which contain any of those words.
I have been working with Access for some time managing some databases for my company. But now I have some problems making the database easier to use by automatically creating a Query based on different criterias.The code looks like this:
Sub Multi_value()
'Definitions
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = Application.CurrentDb
[code]....
The problem is that the "Business Field.Value" is inserted into the Query-criteria with brackets which for some reason are not supposed to be there when I use the .Value-parameter instead of the "Business Field"-parameter.
I am trying to make a form where the user can check/uncheck query criteria via several check boxes. The idea is that the user can start with many criteria and then deselect criteria if the search does not return enough results.
I have been setting up several queries and thought I would combine them in a "Master Query", since I thought it may be easier to deal with each criterion and the respective switch this way.
Lets say we run a hairdresser.
I have a field in the form that allows me to select clients. This is also used in the query. Works fine. Now to the hard part.
Example 1:
Each customer has a budget to spend on haircuts.
Each hairdresser offers haircuts from $x to $y.
The query should return all hairdressers that are appropriate for the budget of the selected customer.
There should be a yes/no button on the form to ignore or use this criterion.
Example 2 (this completely threw me off):
Each customer has a set of preferred services from a table (e.g. cutting,washing, coloring).
Each hairdresser offers certain services also based on this table (e.g. cutting,washing, coloring, drying).
The customer and hairdresser table use the services table and a multi combo box to select the services.
The query should return only hairdressers that offer some or none of the services wanted by the client.
Again, there should be a services yes/no button on the form to either ignore or include this criterion.
To clarify, the hard part for me is the query. I am fine with setting up the tables and the form. Just not sure how to implement something like "IF (ServicesCheckBox = -1, 'then use service criterion', 'ignore service criterion')".
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
Good morning everyone,
I use this code to filter a subform with multiple criteria.
Private Sub Filter_Click()
Dim strWhere As String
If Not IsNull(Me.Coordinator) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[EmployeeID] = " & Me.Coordinator & ""
End If
If Not IsNull(Me.Customer) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[CustomerID] = " & Me.Customer & ""
End If
If Not IsNull(Me.Supplier) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[SupplierID] = " & Me.Supplier & ""
End If
Me.Track_All_Orders.Form.Filter = strWhere
Me.Track_All_Orders.Form.FilterOn = True
End Sub
This Code works great, however i want to use it to filter my report as well, so I put strWhere as a global var and i wrote this code to generate the report with the same filters criteria as the subform:
Private Sub cmdGenerateReport_Click()
Dim stDocName As String
stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub
Nothing happen when i click on cmdGenerateReport!! any clue?
Million thanks in Advance,
Best Regards,
Hello,
I have been trying to produce a front end for a multi criteria search. I have used one of the sample databases from the site and amended the code as necessary, but obviously not correctly. I can't get it to show me the records based on my search criteria.
I would be grateful if somebody could have a look and let me know what I've done wrong (cut down DB attached). If I can crack this I want to do another multicriteria search for other parameters.
One other question - is it possible to take those filtered records and dump them into a report? For example, say I select one parameter and want tpo print all records associated with that parameter?
Thanks
On a report I want to Dlook up using if a name and a date match on a query line it will return the total that is associated in that same line. What do i need to add to the below is if
=DLookUp("[Total]","qryaQFFSumOfTotal10day","[AssociateName]=Reports!rprtQFF10day![AssociateName]") & qryaQFFSumOfTotal10day","[ErrorDate]=Reports!rprtQFF10day![Date1]") if null = 0?
Hi all:)
Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form.
I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this
Thanks Jackie
Is there any way it would be possible to have a number automatically created using NZ() or DMax function; based on certain criteria and also when creating the number preventing a duplicate number creation in the even of a simultaneous record creation?
Here's my example:
I have a CCN Number (Currently the employee manually types it in; and it is the primary key. What I would like is to take out the human element (error possibilities) and let access create it for the employee.
Here is how the CCN looks and the breakdown of each section (the important part's will be the first 3 sets of numbers):
96-06-184-100-000
96 = Region Code
06 = Current Year
184 = Julian Calendar Date (based on the receipt date field)
100 = Filler number based on day of the week (Eg. Monday = 100, Tuesday =
200, etc.)
000 = Filler numbers (Should always be Zero's (000)
I need to find away to pull this information from a table of some sort and combine it to create the CCN and to then add 1 (incriment) to the 4th set of numbers. Example 100 (1st record of the day) 101, 102, 103, etc.
Any help or ideas would be greatly appreciated.
Me.Text11 = Nz(DAvg("[final whse-in diff]", "dbo_inventory", "[CAFETYPE]=" & Me.Text7 And "isnull(me.[DATE FIXED])=" & True And "isnull(me.DATE_IN)=" & True), 0)
I am getting a type mismatch error with this.
My question is:
1. is the syntax correct....
2. Is my way of checking for a value to be null correct....
I have a form that contains three multi-select combo boxes, called Market, Readiness, and Role. I am trying to use the user-selected criteria from these boxes as parameters for my query, which is linked to a personnel report.
I have been attempting (without success) to modify code to gather all the selections in each box and then open the updated report. The code below uses the combo box called "Market" and a report called "Succession".
Private Sub Run_Report_Click()
Dim StrWhere As String
Dim varItem As Variant
If Me.Market.ItemsSelected.Count > 0 Then
[Code] .....
Questions:
1. This code is missing something, since when I click my event button to run it, a box pops up to ask for the parameter I already gave in the combo box.
2. What is the syntax to add in the other combo box selections?
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
I have a search form with blank fields tied to a table, four criteria search boxes, and a button to take the input from the search boxes, search the table, and populate the results on the form's blank fields. As of now, it works as long as all four criteria boxes aren't null.I used filters to achieve this, and here's the code that works as long as all four boxes are not empty. (My criteria boxes are as follows: a textbox called "Keyword" and three combo boxes called HRCombo, BuildingCombo, and RoomCombo, and the fields they're tied to are as follows: "Item Description" "HR Holder" "Building" "Room") My first line "Me.Filter = ..." was broken up to make it easier to view.
Code:
Me.Filter = "[Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34) & "
AND [HR Holder] = '" & Me.HRCombo & "'" & " AND [Building] = '" & Me.BuildingCombo
& "'" & " AND [Room] = '" & Me.RoomCombo & "'"
Me.FilterOn = True
Me.Requery
I need it to be able to do the search no matter which combination of criteria boxes have input. Someone recommended using if statements to do the following: Create four strings, one for each criteria box. Use 4 if statements to check if the box is null - if it is null, assign an asterisk to its string, and if its not null, assign the value I used for the above Me.Filter statement to each box's string. Then, use Me.Filter and concatenate the four strings at the end. Here's the code I used for this, and, with my limited knowledge, I can't get it to work.
Code:
Dim StrA as String, StrB as String, StrC as String, StrD as String
If Me.Keyword is null then
StrA = "*"
else
StrA = [Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34)
End If
[code]....
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
E.g.
=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')
and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
Hi there.
I am in the process of creating a database without using the wizard and am confused regarding multi table queries. Can someone explain in simple terms why when you create a form from a query, some forms wont let you add data. I think it is to do with the join type but I just dont understand it at all. Say you are creating the order details query, you have to select the product ID from the order details table but the product name from the products table. Why is this? (lol).
Please help a confused newbie!!
Thanks
have a query that works fine when I have to select one parameter, however I don't know how to select multi parameters...In this query I would like to able to select as well as the specific 'POSTITION' value Also 'BASE' and all those parameters that have a check box empty or full.
Code:
SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.Name, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMember.StartingDate, tblCrewMember.Resined, tblCrewMember.ResinedDate, tblCrewMember.Birthday, tblCrewMember.IDCrewMember, [GroupBy] AS Expr1
FROM tblCrewMember
WHERE ((([GroupBy])=[Position]))
ORDER BY tblCrewMember.StaffNumber;
I have got a db and that has 10+ ref tables, and i need to show everything from those tables in one, and these tables includes Attachment field. I have tried union but did not work.
How can i achieve this?
I have a simple question that I know will have a very complex answer
You have two sets of tables with identical structures. The first set contains data that is a model for the second set. The users run queries that append data to the second set of tables
Here is an example of the structure
TblSourceOne
SourceOneID
SourceOneData
TblSouceTwo
SourceTwoID
SourceOneID
SourceTwoData
TblSouceDetails
SourceDetailsID
SourceTwoID
SourceDetailsData
As you can see TblSourceTwo contains a foreign key from TblSorceOne and TblSorceDetails contains one from TblSourceTwo
The main table for the second set
TblTargetOne
TargetOneID
SourceOneID
TargetOneData
The user adds a record to the main table (TblTargetOne) then uses an append query to add all of the applicable records from TblSourceTwo to TbleTargetTwo
TblTargetTwo
TargetTwoID
TargetOneID
TargetTwoData
So now the user has a data model in TblSourceTwo which has been “copied” to TblTargetTwo
Next for each record in TblSourceTwo that has been copied to TblTargetTwo that has daughter records in TblSourceDetails another query is executed to copy all of the pertinate records to the TblTargetDetails
TblTargetDetails
TargetDetailsID
TargetTwoID
TargetDetailsData
Now the user has completely copied all of the necessary record for both table.
Finely the question
How can I use a multiselect list box to achieve this in one operation? I don’t care if it includes running multiple queries but it needs to appear as one single operation to the user.
i have a form with a control for "repair type" and a subform that has a query that searches a table holding vehicle repair records. on the form the user can enter up to 5 repair types, [repair1], [repair2], etc.. Then the control on the form holds each type of repair to search by. Since the user could use one or all five repair fields and put repair types likes brakes in field one on one record and field 5 on another record, how do i search to just pull out those records that have the specified search criteria?
If i put in [form]![control] in each of the 5 criteria in the query it comes back with no records, because it it looking for that type in every repair field rather than once between the five fields?
I have imported a lot of data from excel into a table. There is in one of the columns some multi concatenate info I want to separate in it's own individual row.I am not sure if there is a way to do this in a query or if there was an MsAccess2007 example somewhere. I looked on the internet but couldn't find anything.
the columns are like this now
123456 7RFE01, 7RFE03, 7MFE06, 7MFH04
237890 8RFE80, 8MFE03, 9GFE06, 5QFH06, 3QFY06
I want the columns to be like this in a query or new table.
123456 7RFE01
123456 7RFE03
123456 7MFE06
123456 7MFH04
237890 8RFE80
237890 8MFE03
237890 9GFE06
237890 5QFH06
237890 3QFY06
I currently have a database of customer information and form set up with a MultiList box based on the regions those companies are based. You select the region(s) you require from the list box, click a button and it runs a query.
However I now want to be able to filter the results further using a combo box with business sectors in also.
The code used for the multilist box is as follows (I don't totally understand all the code if I'm honest, but it works )
Private Sub Command2_Click()
Const cstrQuery As String = "CAMPQRY"
Dim strNames As String
Dim strSelect As String
Dim StrWhere As String
Dim varItm As Variant
[Code] ....
I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now
View 10 Replies View RelatedI created a form that has 3 multiple drop-down selections that is supposed to select records from [TableName_1] based on those selections. I initially created the query using the query designer with actual "hard coded" selections to make sure it worked. When it gave me the expected records, I changed the criteria from the "hard coded" option to the input form's control reference [Forms]![FormName]![ComboName]. I did this one at a time and tested after each change.
After the first change, I got exactly what I expected.
After the second change, it seemed okay. (note: I put the criteria on the first criteria line in the query designer, not on the second line)
After the third change, I get the dreaded: The expression is typed incorrectly or is too complex to be evaluated....
Here is the SQL:
SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA
FROM (SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA FROM M_Lending_Institution) AS LenderSearchQuery
WHERE (((LenderSearchQuery.GeoRegionID.Value)=[Forms]![LoanSearch]![CmbPrefGeo]) AND ((LenderSearchQuery.SpecialtyID)=[Forms]![LoanSearch]![CmbSpecialtyArea]) AND ((LenderSearchQuery.SBA)=[Forms]![LoanSearch]![CmbSBA]));
And once this is sorted out, yes I want to be able to allow for all records to be returned if any of those fields is NULL. I've read a LOT of posts around this subject, but I can't seem to find anything that answers my question without jumping into advanced VBA code.
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.