Queries :: How To Select Individuals With More Than One Skill
Jan 2, 2014
How do I select individuals who have, for example, both access and excel skills? I have a table of candidates and child table with the list of skills.
I have a form (called "search" at the moment) with a subform (called "skillslist subform"). The subform's record source is an empty table called "searchlist" with a single combo box using the master list of skills as the source. This is so I can select the skills I am looking for.
Then there is a button that (1) saves the records and then (2) opens the query, as follows:
Code:
SELECT SkillList.CandidateID, searchlist.searchlist
FROM SkillList INNER JOIN searchlist ON SkillList.Skill = searchlist.searchlist
ORDER BY SkillList.CandidateID;
Is this problem need to be solved with a subquery?
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
I have a database that uses a table to capture work by reference number. I would like to be able to allow users to only be able to see work they have been allocated.
I want to be able to email a report to several individuals and have only the data that pertains to that person attached in the email? Is this possible?
In my database for our charity there is a field for email addresses. Most of the contacts have a single email address and we can send them their individual reports without any problem. However a number of the contacts have 2 email addresses and these are stored in the email address field with a semicolon between the 2 addresses. However the system can't send emails to these contacts! Are we storing the addresses in the wrong way? Or is there some reason why Outlook won't regard the 2 addresses as separate email addresses - does it think that it is one strange email address that it doesn't recognise?
I have created my tables and form and am now trying to run reports to organize the data. I have figured out how to group the individuals by group and treatment, but can't figure out how exclude individuals from the final list if they are already in another. As background, some of the individuals are eventually excluded from the experiment, though I keep the initial data. When the exclude individual checkbox is checked ("True" on the report) I do not want the individual to be listed in the "False" list, even though there are entries for that individual when that checkbox wasn't checked (when it was "True"). This is so I can get an idea of the current totals in each group.
Implant Period >Treatment >>Exclude individual? >>>Individual ID
I feel like this should be a fairly simple task, but I cannot figure it out... Maybe there is coding to exclude individuals from the "False" list if they are already listed in the "True" list?
I've taken over a database written in Access '97 so it's quite old and I don't want to rewrite the whole thing. I have a query as the record source for a report like this:
Code: SELECT TOP 1 ID1Child5.ID, ID1Child5.CODE1, ID1Child5.CODE2, ID1Child5.CHARGE, ID1Child5.DEFAULTS FROM ID1Child5 WHERE (((ID1Child5.ID)=[Forms]![ID1MainForm_0]![ID])) ORDER BY ID1Child5.DEFAULTS;
Which gives me the first value.
Now I need to reverse that and create a query that gives me all the other results. Basically everything except the top row.
I've been wracking my brain and scouring the net for information on how to do SELECT queries in Access and I can't figure it out or find correct information. Some idiot had a website that said to use DoCmd.RunSQL to do SELECT queries, but failed to mention that only ACTION queries can be used in that method, so I'm back at ground zero. What I want to do is this:
I want to be able to run a select query from a table, and store all the information in some sort of object so that I can access all the individual fields returned in the query and do things with them. Is there anyway to do what I'm asking for?
I am trying to select a different query based on the selection in the [Product] Field. I have 4 different products and 3 different queries to run. Each product is specific to a particular query.
What I would like is: When Product 3 is chosen "Query 1" is ran. When Product 1 is chosen "Query 1" is ran. When Product 2 is chosen "Query 2" is ran. When Product 4 is chosen "Query 3" is ran.
As it is set up now it appears that when choosing Product 1 I open Query 2 and when choosing Product 2 I open Query 1, but it is the opposite of that. I get the correct query with this code by choosing Product 1 and the Query that opens is actually Query 1, I don't know what is going on here.
When I open Products 3 & 4 it always opens Query 2, no matter what Query I specify.
Please help me sort this out. I am not very familiar with case select. Thanks
Private Sub run_query_Click() On Error GoTo Err_run_query_Click
Dim stDocName As String stDocName = "Query 1" (Should be for Product 1 & 3) stDoc1Name = "Query 2"(Should be for Product 2) stDoc2Name = "Query 3" (Should be for Product 4)Select Case stProtocolName Case [Product] = "Product 1": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 2": DoCmd.OpenQuery stDocName, acNormal, acEdit
Case [Product] = "Product 3": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 4": DoCmd.OpenQuery stDoc2Name, acNormal, acEdit
How I might select all the records within a query except for the most recent one? I have an AutoNumber ID field and a date stamp to use , but I can't figure out how to exclude just the biggest record.I am trying to run an update query on the After Insert Event, so that when the user inserts a new record it will mark down similar older records as 'superceded'.
At the moment I can select all the records, and I can select just the largest one, but I can't seem to take one away form the other. Here is the SQL in it's current state:
Code:
UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]));
I'm trying to do a select statement and put it in a variable which i can then output to a text box. How do I get the value into a variable? I can't seem to get my syntax right
Is it possible to change this query to search for either a Name or by an employee ID number? The query runs a report on after update. Currently I can only enter the name not the employee ID
Code: SELECT DISTINCTROW First(Inventory.Employee) AS FirstOfEmployee, Inventory.[Employee ID] FROM Inventory GROUP BY Inventory.[Employee ID] ORDER BY First(Inventory.Employee);
I've done some searching, and haven't found any threads that match what I am attempting...
I have a database that tracks expenses that are incurred under different ongoing projects. I currently have a report based on a query that asks the user for the project number, and shows all the expense invoices that have not been paid for that project. This report is basically an internal invoice that gets assigned to a department, and so it is assigned an internal "invoice" number. Currently, the user types in this internal number before the report is generated and it is displayed on the report for printing purposes.
Here's the tricky part (for me anyways): I would like to be able to filter the invoice records by project number, and then append the user-entered internal invoice number to the applicable records. There is already a field in the table for this data, and it is currently being entered on individual records after the report is printed. My goal is to be able to do this all in one step.
Any help will be greatly appreciated... Thanks guys!
I have a form that has a combo box that contains every query in my database. This worked fine until i started having to create cascading queries to return the data I want. How can i populate my combo box with only certain queries in my database.
This is the code I was using to put all queries into my combobox.
FROM MSysObjects WHERE MSysObjects.Name NOT LIKE "~*" AND MSysObjects.Type = 5 ORDER BY MSysObjects.Name;
Upon clicking a button or clickin the query in the list, I want the query to run......So the main question is how to I include certain queries in my combo box but not all of them? I do not know SQL or any programming, so as specific as possible will help immensely. Thank you everybody.
Im trying to pass a list box variable in a select query. I understand you cannot pass a variable directly but have to pass it through a function. I may be wrong in this, but whatever I do I cannot get it to work. Here's my code:-
Public Sub GetEquipment() List387.RowSourceType = "Table/Query" List387.RowSource = "SELECT findequipstr() FROM Equipment" End Sub
Public Function findequipstr() As String If IsNull(List371.Value) Then GoTo function_end findequipstr = List371.Value function_end: End Function
If I MsgBox(findequipstr()) within my Getequipment function, the variable is messaged,
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;
My statement below is current returning 4 records. Two of the records have the same GBPAmount value.
RequestID is the Primary Key
Code: SELECT TOP 3 RequestID, GBPAmount, Currency, RequestDate FROM PayRequest WHERE (((Currency)="CAD Canadian Dollar") AND ((RequestDate)>#11/16/2014#)) ORDER BY GBPAmount;
Code: RequestID GBPAmount Currency RequestDate 10207 8.17 CAD Canadian Dollar 03/02/2015 9874 33.82 CAD Canadian Dollar 20/01/2015 11327 109.58 CAD Canadian Dollar 23/02/2015 10495 109.58 CAD Canadian Dollar 05/02/2015
I'm having a problem get a query to select all of the records it should be.
When I filter the source table (200_STANDARDIZED NRGL) to show the data I want to see (PC2 = 6000; GAAP = 02; CGL = 0950, 2735, 2736, 3500 and 3501; STD VENDOR NAME = blanks), I get 33 records.
NOTE: Had to take the PC2 records that were not '6000' out of the dB I've attached in order to be able to send dB bu the PC2 filter is needed in the complete dB.
When I create a select query to the do the same thing, I get either:3 records (when I set STD VEND NAME to Like '') or 30 records (when I set STD VEND NAME to NOT Like '*' ) I've attached the dB - Query 1 is the subject of bullet 1 above and Query 2 is the subject of bullet 2 above.
BTW, the three records that show up in Query 1 are the same records missing from Query 2.
I Want to Create an Query to Find Different Values in A Table/ Query
Like Month Year Name School Post Jan 2012 Ankur School 1 Post 1 Feb 2012 Ankur School 1 Post 1 Mar 2012 Ankur School 2 Post 1 Apr 2012 Ankur School 2 Post 2 May 2012 Ankur School 2 Post 2 June 2012 Ankur School 3 Post 2
Now i want in result of Query is only like
Month Year Name School Post Jan 2012 Ankur School 1 Post 1 (Starting Ledger) Mar 2012 Ankur School 2 Post 1 (Change in School) Apr 2012 Ankur School 2 Post 2 ( Change in Post) June 2012 Ankur School 3 Post 2 (Change in School)
