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]
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
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.
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?
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.
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.
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.
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 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! :)
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
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";"")
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%"));
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.
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.
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?
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??
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.
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.
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:
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.
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.
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.
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?
