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
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 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.
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.
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.
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 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 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.
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
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
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
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 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.
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
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
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 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 a subform query which includes EventName, Date, and Tags. Tags is a multivalued field.
What I need to do is allow people to filter this subform using the inbuilt filter, and then use the filtered data to populate a graph.
I am doing this by taking the Filter property from the form and then using VBA to build an SQL statement with the right data, and using that on the graph. It works fine when people filter on the EventName and Date fields.
However, when someone filters the Tags field, it fails. The filter property gets set to something along the lines of
([Lookup_Tags].[TagName]="whatevertagtheypick")
This syntax seems not to be supported when I put it into the SQL statement.
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
I have a query that includes 4 different tables. It only shows info for the Sales Orders that have information in all four tables. Is there a way to get it to show any information available? For instance, if it only has info in two of those tables, it would show that info and the fields for the other two tables would be blank?
Hi, I'm currently creating a performance review database for my team and I'm having a few issues.
I was wondering if there is any way other than making a multi table query to have multiple tables linked to a form. The reason for this is because the performance review has 5 sections and I made a table for each section and would like data entered into the form to be entered into those tables.
What I first tried was making a multi table query with all the fields from all 5 tables. Then when I am done updating the values in the form to that 1 table with all the fields I run an append query to populate the data into all the other tables. The issue with this way is it's a manual task and with autonumber on my tables I have to delete all the values from the other tables and then run the append else I have duplicates from when the data was entered last time. Any tips or help would be greatly appreciated.
Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem: I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field - Like [forms]![Form1]![cboOffice] & "*" Like [forms]![Form1]![cboJobTitle] & "*" Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank. If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
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')".
I am creating an absenteeism database which has the following tables:
tblEmp - PK - auto number, EmpId, First Name, Last name tblFunction - PK - FID (autonumber) has departments tblTL - PK TLID (autonumber) has list of team leaders tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism
I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis
Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.
In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
Is it possible to create a query that asks for multi entries in one column .....
For example : If I have parts that I purchase and some of them get rejected I want to inform someone of those parts, these parts could be on order numbers, 1,2,8,10 ....
I cannot just search on rejected because some parts could have been reject say 2 weeks ago but new ones have not arrived yet so the flag "rejected" is still showing ....
Is it possible to set the criteria that when asked for the order number/s you could type in ,1,2,8,10 ....
I have a master table with several pick list columns. One field in particuliar has 3 options(fed from a separate table): a b c But, the user will have a need to select more than just 'a' for example. they will need to select 'a' and 'b', or 'b' and 'c'....etc
