Query Returning To To Much Information
Jun 21, 2006
Hi all
I have a query that is getting data fron 2 linked tables. The relationship between these 2 tables is 1 to many. The first table(One side) contains Due Dates for services. I have asked the query to return services for the current date which is working fine. The second table(Many side) has additional work required for each vehicle. The work can be either New, On Hold, In Progress or Completed. I only want work that has not been completed to be returned if the vehicle is due for a service today. I tried having the Work Status field in the query and criteria set to <> Completed. This returned all open work including vehicles not due for service. Is there anyway this can be achieved. If I take the criteria out of the Work Status field I get the information I am looking for but it includes Completed work and I do not need to know this.
Any and all advice appreciated.
Regards
Craig
View Replies
ADVERTISEMENT
Jun 23, 2005
Hi,
I have a query with two table's in them. One is filled, the other is still empty.
Both table's are related to each other with the field "document number".
This was working fine in access 2000, now in 2003 it returns zero results.
I am starting to think it is because one table is empty it returns nothing.
How can i adapt the query criteria or something else that it shows the results from table one, while table two can still be empty.
One table holds the documents and all information about it, the other holds the information wich document is referenced on wich document. Now since i can have documens which don't reference to a document, i still want those in the list.
Anybody any idea?
Thanks
View 1 Replies
View Related
Sep 28, 2005
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?
Thanks for any help.
View 1 Replies
View Related
Jan 21, 2006
Hi.
Can someone help me?
I am trying to make a query return all values (please see attached jpg)
It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?)
In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.
Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.
Many Thanks.
Frank.
View 2 Replies
View Related
May 27, 2006
Hi,
Can anyone see anything wrong with this query?
I have 10 combo boxes where years are selected. If the last year nothing is selected (meaning is empty) then my query should return the last selected value. It seems the IIF query does not seem to work (Still showing empty). Can you please advise and help me on this?
The query I am using is shown below.
Thank you
dfuas
IIF([Trade].[Vintage_ to] = ' ',([Trade].[Vintage_from9]) OR ([Trade].[Vintage_from8]) OR ([Trade].[Vintage_from7]) OR ([Trade].[Vintage_from6]) OR ([Trade].[Vintage_from6]),[Trade].[Vintage_ to]) AS [Vintage to]
View 4 Replies
View Related
Jul 6, 2007
I have a table listing a number of cell phone products and their carrier along with their status' in the production process. I want the query to return the most recent status for each product for each carrier. However, out of the 20+ products that I'd like the most recent status for for each carrier, it's only returning about 12 products of the most recent status for one carrier.
Any help would be much appreciated! Leave a message if you need more clarification.
Thanks!
View 3 Replies
View Related
Oct 5, 2004
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!
View 3 Replies
View Related
Jun 21, 2005
Hello all
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! :)
View 2 Replies
View Related
Jun 24, 2005
I have a left-join query where I know that some of the values in the left-hand table have no corresponding values in the right-hand table...that's why I used a left-join: I want all the values in the left-hand table to show up. I would expect that records without corresponding values in the right-hand table would show up as nulls (I swear I have dozens of other queries that work this way) but in this one, they show up as "#error" instead.
This is a problem because I want to do some calculations on this field. Normally I would use the nz function to change the nulls to zeros.
So,
1. Any idea why this is happening?
2. Is there a way to change the #error values to zeroes, similar to using the nz function?
HEre's the SQL:
SELECT FeederInventorySummary.machine, FeederInventorySummary.Size, FeederInventorySummary.Leaf, FeederInventorySummary.Type, FeederInventorySummary.CountOfFeederID,FeederNeeds Summary_7.FeederQty FROM FeederInventorySummary LEFT JOIN FeederNeedsSummary_7 ON (FeederInventorySummary.Type = FeederNeedsSummary_7.Type) AND (FeederInventorySummary.Leaf = FeederNeedsSummary_7.Leaf) AND
(FeederInventorySummary.Size = FeederNeedsSummary_7.FeederSize) AND (FeederInventorySummary.machine = FeederNeedsSummary_7.ActualMachine);
For every record without a corresponding record in FeederInventorySummary_7, the FeederQty field shows up
as #error.
View 2 Replies
View Related
Nov 21, 2005
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";"")
Who can help me?
Thanks
Fred
View 5 Replies
View Related
Jan 13, 2006
I am trying to select all projects with the keyword "sett" in them. About a thousand rows with this keyword exist but why isn't my query returning any data? :confused:
Here's an example of the data "STPT-SETT-NY-EQ Legacy"
Now i want to return all rows with "SETT".
What am i doing wrong here please :)
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName) Like "%SETT%"));
this doesn't work either. No records are returned when query is run:
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName)="%SETT%"));
View 2 Replies
View Related
Mar 15, 2006
I am trying to build a query which will find the costs from a set of tables where the client has costs.
The client has a cost on booking a fee, he has a hotel room cost and a room facility cost.
My query brings up what I want but each result is repeated 3 times.
I don't know why, can someone correct me please.
SELECT DISTINCT Addresses.ID, Holiday_Bookings.Initial_Cost, Room_Facilities.Cost, Rooms.BasicCostPerNight
FROM ((Room_Facilities INNER JOIN ((Hotels INNER JOIN Rooms ON Hotels.HotelID = Rooms.HotelID) INNER JOIN Stops ON Hotels.HotelID = Stops.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID) INNER JOIN (Routes INNER JOIN WalkTypes ON Routes.RouteID = WalkTypes.RouteID) ON Stops.StopID = Routes.Arrive_In) INNER JOIN (Holiday_Bookings INNER JOIN (Groups INNER JOIN (Clients INNER JOIN Addresses ON Clients.ID = Addresses.ID) ON Groups.ID = Clients.GroupID) ON Holiday_Bookings.ID = Clients.ClientID) ON WalkTypes.Walk_TypeID = Groups.GroupID;
View 3 Replies
View Related
Mar 30, 2006
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.
Thanks, Tim
View 3 Replies
View Related
Jul 3, 2006
Hi everyone,
I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.
I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.
Any ideas?
View 3 Replies
View Related
Mar 2, 2007
Hi all:
running access 2000. I have a query that I selected to return the Top 1 record sorted descending on the date so it would pick the most recent entry. I did it by selecting Top 5 from the drop down box in query design mode and then changed the 5 to a 1 using SQL View from qry design.
SELECT TOP 1 Class.CourseID, User.UserName, Class.TrainerID, Class.ClassLocationID, Class.ElapsedTime, Class.ClassDate, Class.Comments
FROM [User] INNER JOIN Class ON User.UserID = Class.UserID
ORDER BY Class.ClassDate DESC;
It still gives me as many records as are entered on the system. How can I get JUST the most recent record entry?
thanks:confused:
View 3 Replies
View Related
Sep 7, 2007
O.k. I have a query working, and it's calculating perfectly and I'm reporting on it fine. However I noticed that when I run the query it doesn't populate the equation for all the results. Let me explain further.
I have a main form for tracking company information, and a sub form that tracks departments for that company and waste breakdown information. We take measurements for the company in two ways. 1 - a total weight for the day, and 2 - we weigh out categories of waste (within the sub form that tracks the department stuff)..we then calculate what percentage of the daily waste a specific category is. This calculation is done in a query, and works fine for the first department of every company, however when it moves to the next department for the same company, all I get is #Error in the field. eg./
Company "X"
Total Daily Weight = 750 Kilograms
Department "Shipping"
Category - Plastic Bottles - 20 Kilograms
I then have a query that calculates what % 20 Kilograms is of 750, then a report based on the query. This works fine; However in my report I look at the next department, with the exact same informaton as above...I see #Error returned in the field.:confused:
Does all of that make sense?? Is there a way to make sure my query calculation flows through all of the departments??
Thanks.
View 9 Replies
View Related
Jun 16, 2015
I have two tables:
tblSITE and tblUSID (joined by SITEID)
and I am trying to create a query which lists each site and the USID with the latest date in its ConstructionDate field (USID and ConstructionDate are both in tblUSID). If I omit the USID from the query and run a totals with MAX on the ConstructionDate field, I return what I would like to, but I want to add the PK (USID) of the record with the latest ConstructionDate for another query, and every way I attempt it the query returns multiple records per SiteID.
View 3 Replies
View Related
Oct 17, 2014
I have 2 tables, one called Location and the other called PC Inventory. They both have columns called sites. The query is set to search both tables by site name and return the PC's at that site that match the search criteria.The search form has a drop down menu with all the site names. When you choose a site name from the last, the after update event does a re-query to a subform on that same form. The source object of the subform is set the query.[Forms]![PC Search]![Combo6] - this is the criteria on the query that point to the drop down menu.
Private Sub Combo6_After Update()
Me.Child19.Requery
End Sub
This is the requery that point to the subform (child19)..The problem is that some selections from the list don't return any results to the subform. But there is clearly records that meet the criteria in the table. Example: ABC @ Maryland returns all the PC's at the site. DEF @ Maryland doesn't return any PC's at the site, but there are records with that site name.Even if go into the table copy the site name that isn't working and enter into the search field it doesn't return the results.
View 3 Replies
View Related
Jun 30, 2015
Any new data I add to my database isn't showing up when I run a query.I've noticed it on 2 queries. One which is my ChargerSearch query with the field name:
Code:
ChargerSearch: "Charger ID: "+[Charger_ID]+" "+"Scheme Contract: "+[Scheme_Contract]+" "+"Nissan Serial Number: "+[Post_Serial_Number]+" "+"Client: "+[Client]+" "+"Site: "+[Site]
and the Criteria:
Code:
Like "*" & [Enter Search Term] & "*"
And my JobHistory Query which has multiple fields from many tables. With criteria only on the Charger_ID field "[Enter Charger ID]". Both work fine for data that was already there but any new data I've added isn't returning any results when I enter the correct values in the parameters. The data is definitely there in the appropriate tables, it just won't find it on the queries.
View 11 Replies
View Related
Oct 10, 2011
2007 version - my child query is not returning null values. I have 2 fields, both pulled from a parent query. One is "DaysSinceRecd", the other is "AmountPaying". The parent query returns multiple results, which is fine. For example, the parent query might have 10 different records for "0" "DaysSinceRecd", with different "AmountPaying" for each of those records.
In the child query, I am trying to condense that down, so that I only have one "AmountPaying" for each "DaysSinceRecd". It's working fabulously, except that there are records in the parent query that have a value for "AmountPaying" but a null value for "DaysSinceRecd". The null value for "DaysSinceRecd" cannot be changed to another value, it needs to stay null. My child query is not returning the null records. I tried adding to the criteria: IsNull Or IsNotNull, >=0 or <=0. That's not working.
View 5 Replies
View Related
Jul 31, 2014
I have a database for storing part numbers of power supplies, very basic database. I also have a multifield search form and search query. All very basic stuff, however I am new to access and cannot seem to find out what the is causing this issue.
There are 174 entries in the database table, they all start with "0" for the part number, I have wild cards in my Query so that for instance if I searched 0 for the part number field, it should return all 174 entries, but for some reason it only returns 49 entries.
View 1 Replies
View Related
Sep 12, 2012
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
View 2 Replies
View Related
May 22, 2006
I am using the following:
SELECT CPL_CLIENTS.CODE, CPL_CLIENTS.FULLNAME FROM CPL_CLIENTS
WHERE (((CPL_CLIENTS.FULLNAME) Like "*" & Forms!frmSafeKeep_AddClient_SearchDesig!txtSearchT ext & "*"));
As the control source of a listbox, so that the user can enter something in "txtSearchText", and the listbox will be narrowed to only those those clients that have a matching part of their name.
The problem is that the user enters, for example, "duncan", and the list returns nothing, when in actual fact there are 5 matching records. If the user enters "Duncan" (note the capital letter), they get all 5 records as they should.
If memory serves, access should only be case sensitive if you tell it to be - and this is working fine on other forms.
The only difference between them is the data source. Other forms are getting data from linked tables in the backend. This form is getting it from a linked Oracle table.
View 4 Replies
View Related
Mar 9, 2007
I'm really not sure how to go about this. I'm creating a course booking system and when creating a booking I need to check for current bookings with the same employee and course id's (i.e. the employee is already booked on the course). The query takes the employee and course id's from a form, and is initialised when the 'book' button is pressed. It correctly selects if the person is already booked on the course but I want the query to return a value to the form i.e. if it returns null/false then the booking can be created but if it returns a record/true then the booking already exists and a message box can be displayed.
I'm not sure if I'm going about this the right way, can anyone suggest how this can be done as described above or suggest a better way of doing the task?
View 5 Replies
View Related
Jan 11, 2006
I have a table called Prem. It contains three fields (Offer, MailCode, MktgProgam). I want to have a form where a user can select. the Offer (from the offer field) Once selected, it takes the MailCode and MktgProgam information from the Prem table and uses that info (MailCode, MktgProgram) to pull from the main table called "Cust" Is this doable?
Thanks in advance
View 4 Replies
View Related
Jan 27, 2014
I am using A2007 and this one combo box is returning the first record of a query no matter what record I pick. My other combo boxes are working fine.
When I pick a record the "after event" works fine by placing the data in the proper text boxes and then returns the first record in the query.
View 3 Replies
View Related
