I have a sub report that is based on a query. The Where clause of my SQL is giving me a bit of hang up. What I am attempting to do is return the records that are the items used to test products we test. The ID's of the records are gathered in a public function. The function is called GetCal Equipment. This returns all of the requested numbers just fine. For example ID numbers 4, 112, 124, 138, 232, 338 are returned when I call the function. Any number of records can be returned at any point. This is the unique numbers used to identify the records. I thought by having the function return the numbers the records would come up. However when I use that in my where statement, none of the records are returned. The whole SQL is as follows:
Code:
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In (GetCalEquipment());
how to get the query for the report to return the records.
I have a query that I set up to return multiple records. The number of the records is in that text box. I have tried to set the where clause to the text box but for some reason I don't get any of the records I want returned. I am using a Where... IN statement. I have tried to put the numbers in single quotes and no quotes and I have had no luck at all. If I do individual records they will return, just not when I try and do more then one at a time. This query is a part of something more, but since it doesn't return anything, Here is my sql statement
Code: SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue FROM CalibratedEquipmentListTable WHERE CalibratedEquipmentListTable.ID In ([Forms]![ReportForm]![Text41]);
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 running a query from two related tables in the database.the table relationships and the query design. Instead of doing what I want it to do/what I think I'm asking it to - which is show the sum total number of weeks on the program for each student - it shows me the number of weeks for that student just for that claim, not the total for the student overall. I've added some 'dummy data' (2 records in the claim table relating to 1 student), and the query then returns the student twice in the results.
I have a list box on a form which uses the following SQL to pull the list items from a table :
SELECT DISTINCT tblMyTable.MyField FROM tblMyTable ORDER BY tblMyTable.MyField;
For some reason, during testing, the first two items on the list are blanks. I am quite happy for one blank to be returned at the top of the list (as this would easily identify for the user any records for which this field has not yet been completed, which is a good thing) but I can't understand why it would appear twice?
Why doesn't the DISTINCT command ensure any blank entries only appear once?
Incidentally, I have a subform which populates on the back of selections from the list boxes, so I can quickly see the corresponding records which generate these blanks. What's puzzling me is that the same records show for both blanks on the list - suggesting it is the same value repeating itself (and not, say, "" vs " ", for example, which was what I originally suspected...)
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity from( SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center FROM [Open Items] )a left outer JOIN [Open Items] AS [Open Items_1] ON (a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity) Where ([Open Items_1].MonthEndDate=#8/31/2014#)
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
I have 3 tables - Students, which contains all students; Attendance, which contains dates a particular student was absent; and AttendanceCtrl which contains Quarter beginning/ending dates. I need to be able to calculate the number of days a student was present, absent, tardy in a quarter. The query needs to extract all students regardless of whether or not they missed any days. I tried using a Left join Students to Attendance and then joining AttendanceCtrl but Access said it couldn't do it because of "ambiguous outer joins". It said to create the first join then use that query in the second join. I tried that. I created a query with a Left join joining Students to Attendance. That gives me what I wanted, all students from Students and also those students with matching records in Attendance.
The 2nd query is giving me problems. I tried joining the first query (qryStudents) to AttendanceCtrl and calculating my totals. Problem is, the query only gives me students what have records in the Attendance file; it doesn't give me all the students. If a student did not miss any days, I need to show that he was present 90 days out of 90. Because there's no matching record in the Attendance file, I get nothing for those students.
As I said the first query (qryStudents) seems to be working but I need to take the records from that query, group them together by StudentID, and then calculate my totals. How can I do this? Am I approaching this wrong altogether?
I have just joined the forum and spent the last day or so looking around for an answer that I wasn't able to find.
I have been asked by my employer to capture details of who has signed our confidentiality policy. I have made a very simple database which only has three tables, EMPLOYEE, POLICY and VERSION.
EMPLOYEE has three fields, NAME, NUMBER and BRANCH. POLICY has three fields, NUMBER, DATE and VERSION. VERSION has two fields, VERSION NUMBER and DATE. I won't worry about the VERSION table any further in this post as it doesn't figure in the problem query.
Primary key for EMPLOYEE is NUMBER. Compound key for POLICY is NUMBER and DATE (ie. the employee can sign the policy more than once and we want to record when this was, and whether the policy version changed).
I have an Access "type 3" join defined in table relationships from EMPLOYEE.NUMBER to POLICY.NUMBER.
I am trying to write a query that returns the employees that haven't signed the policy. I have no problem returning the employees that have (ie. employees whose numbers match the numbers in the POLICY.NUMBER field), but it is the EMPLOYEE.NUMBER entries that are not present in POLICY.NUMBER that are causing me grief.
I have tried using the expression builder in the query builder window (it gave me <>[tblPolicy]![Number]) which doesn't return any records (I have over 490 in the database and should be getting back at least 50 or so).
I cannot seem to get my query to return all the records i want it to, i want to search by year group, which it is doing fine, but if a record has a table wich does not have data in it, the query will not return that record, i can't find the solution anywhere!
I have a database that contains different departments per office location with the dept. codes such as 100, 101, 102, 103 etc.
I like to have a criteria in a query that will give me all departments that are running from one office location OR if I do not want one dept. to show in my query to be excluded.
Also, the way I currently set the parameters is, it is asking for the office location by state, county, city, address and department code. I set the department criteria as Not [Department] which excludes the dept. that I do not want to see in the query, BUT I also want to have an option that when I run the query to SEE all the departments.
I had a query which was working fine to tell me the number of weekend bookings for holiday parks for a specified year:
SELECT Bookinfx.[Park Name], Month([Start Date]) AS FilterMonth, Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings FROM Bookinfx GROUP BY Bookinfx.[Park Name], Month([Start Date]), Year([Start Date]) HAVING (((Year([Start Date]))=2005));
However it has become necessary to only find bookings of specific accommodation types so I modified it like so:
SELECT Bookinfx.[Park Name], Month([Start Date]) AS FilterMonth, Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings FROM Bookinfx WHERE ((Bookinfx.Day)="FRI" Or (Bookinfx.Day)="SAT") And ((Bookinfx.Nights)<=3) And ((Bookingfx.[Accom Type])="CHALET/LODGE" Or (Bookinfx.[Accom Type])="STATIC VAN") GROUP BY Bookinfx.[Park Name], Month([Start Date]), Year([Start Date]) HAVING (((Year([Start Date]))=2005));
Now when I run this, the first problem is that it asks me for the accommodation type, which is odd as I've already specified this in the query. The second problem is that if I enter say 'chalet/lodge' it returns records telling me that there are bookings for chalets and lodges at parks that only have camping! I have trawled through the database to make sure no erroneous records exist and so I know it is a problem with the query.
If anyone can shed any light on what I've done wrong, I would be extremely grateful! :)
Depending on the answer of a question, i would like to get the records that have no data in that specific field, otherwise i want the query to give me all records. I've tried this in "criteria" but it doesn't work: IIf([Question? J/N]="N";"Is Null";"")
Hello, I am sure this is quite simple, however I cannot figure it out. I have a query that shows tblPartNumber and tblAverage. The average is calculated in the query by taking the total minutes to assemble the parts, divided by the number of parts completed. What I am trying to do is run a query that will show me the most efficient (lowest average), time for each Partnumber. Ideally the query would show me something like this;
Part # Average 1234 .72 1234 .86 1234 .94 etc...
I searched on here and it seemed like the Top 10 is used, but in my application that ommits all of the other partnumbers because it only shows ten of the records for the first partnumber. I only want to see the five most efficient averages. Any help is greatly appreciated.
when running the attached report I am getting duplicate records. If using the protocol specified tc02026 there should be 8 records total. When running the report it pulls from both queries which have select criteria. How do I limit the report to only give me the 8 records and eliminate the duplicates? When I run each query it only gives me the 8 records I am looking for. Each Sample# in the report is a unique number(to help see the replicates).
i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
I'm using Access 2007.I have 2 tables that are identical in structure.The tables are called [Workorder Parts] and [Workorder Parts Temp]. They both have the same structure:
[Workorder Parts] has 128 records in in and [Workorder Parts Temp] has 28 records in it that are a small subset of [Workorder Parts] therefore, the contents of the key field column in [Workorder Parts Temp] is the same as in [Workorder Parts].I tried the following SQL but get an error message saying "Cannot Update "WorkOrderPartID" Field not updateable":
DoCmd.RunSQL "INSERT INTO [WorkOrder Parts] SELECT * FROM [Workorder Parts Temp]"
My aim is that I want all of the records from [Workorder Parts Temp]to be copied to [Workorder Parts] and have their "WorkOrderPartID" fields correctly updated with a new value...
Code: Item Revision Start End 332 B 9/2/11 9/15/11 332 C 9/21/11 9/22/11 332 E 11/2/12 11/29/12 2A9 A 1/13/13 1/14/13 2A9 C 1/16/13 1/18/13
I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):
Code: Item Revision Span 332 C 6 332 E 407 2A9 C 2
I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?
In a listbox I have a one or more modules selected and will construct the SQL in the AfterUpdate, presumably.
How to get a query to list only those procedures that belong to all the selected modules? To get into the result list a procedure must belong to all the modules that were selected. The code below gives me all the procedures that belong to at least one of the modules selected. I need ALL.
Code:
strIN = Left(strIN, Len(strIN) - 1) ' list of ModID's of selected in listbox strSQL = SELECT tblModuleProcs.ProcID FROM (tblModules INNER JOIN tblModuleProcs ON tblModules.ModID = tblModuleProcs.ModID) INNER JOIN tblProcedures ON tblModuleProcs.ProcID = tblProcedures.ProcID WHERE (((tblModules.ModID) IN(" & strIN & "))) " ORDER BY tblProcedures.ProcedureTag;
I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error.
Code: UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A'; UPDATE [tblMonthly] SET [Date] = #20130801# where [File] = 'B';
