We have vehicles which are lent out to employees on a daily basis,
I currently have a query shown as a report which vehicles are out between the given dates using this criteria in a query
>=[ENTER Start Date] And <=[Enter End Date]
what I want to be able to do is show which vehicles are NOT being used between these dates tried a few different ways but no success
I have a Table with Date Booked From and Date Booked Till
I want a query to show all booked vehicles between the dates in a parameter query, I thought I solved the issue but this is not the case
I am using the following codes
DateFrom - >=[ENTER Start Date]
DateTill - <=[Enter End Date]
All works fine but I have noticed if I put in the
start date 01/01/2013 and End Date 07/01/2013
if there is any vehicles booked between these dates E.g. 03/01/2013 - 05/01/2013, they wont show up.
how can i get round this problem?
I have a query with the following fields
*Date From
*Date Till
I have got the following formulas for each
Date From - >=[ENTER Start Date]
Date Till - <=[Enter End]
The results work fine to show which vehicles are booked between these dates, the only thing is I want to be able to show the vehicles that are not booked for the given dates in the parameter query is this possible?
I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.
I started running this query in may, that's why it only shows may and june's data.
I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know
Is it possib;e to create a query that would all the dates withn a range of Date1 and Date ?
Ex if I have
Filed Date1 Date2
x 1/1/07 1/3/07
c 1/6/7 1/8/7
for query to show
Field Date
x 1/1/7
x 1/2/7
x 1/3/7
c 1/6/7
c 1/7/7
c 1/8/7
I have a table that has the following:
ID
Program Year
Program
Start Date
Midpoint Date
Internship Date
End Date
I have successfully created queries to create reports showing all Start Dates by Month no matter what program and similar reports for all the other date queries. What I need it to do is list everything happening within a month and sort them by category. How do I do that? I've attached the database so you can see my queries as well as the main switchboard reports associated.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedI have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.
View 1 Replies View RelatedI have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?
View 9 Replies View RelatedI need an iif statement in my query criteria that works as follows:
Code:
iif([Field] = "FirstPossible","str1",iif [Field] = "SecondPossible","str2",*)
The problem is, I'm not sure how to display all of the records if neither the first iif or second iif returns true.
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
I have 3 Tables with relationships between Pupils to Read and Books to Read:
Pupils ID, Forename, Surname
Books - BookID, Book, Author, Level, Genre
Read - ID, BookID, DateOut
On another thread in the forum I got to the position of the code below. However this code does not select only the read book with the biggest(latest) date, the Max, it displays all Read books.
SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut Books.Book
FROM (Books) RIGHT JOIN (Read) ON Books.BookID = Read.BookID) RIGHT JOIN Pupils ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Books.Book
HAVING (((Books.Book)<>"Blank"));
I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.
The incoming revenue is a field of running sum of revenue for each product.
Desired output:
I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.
I am attaching the database, with sample data.
I am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with
Description
CarNum
SerialNum
Category
Condition
In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)
The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.
Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)
I am trying to run a query and for some reason its not showing the data from the table (its blank), this is a monthly reporting I do - last months query works perfectly.
Table: log and list
Here is the SQL
SELECT log.*, UL.langue, UL.version
FROM log, (SELECT list.id, list.[langue], list.version, list.no_joueur FROM list GROUP BY list.id, list.[langue], list.version, list.no_joueur) AS UL
WHERE (((log.id)=[UL].[id]) AND ((UL.no_joueur)<90000000));
Table has all the columns.
I am working with a database and existing query from my predecessor. The field in the query appears in both the database table and the query.when I run the query it doesn't appear. Is there a limit to the number of columns in a query I easily added a new column and moved a column,
View 9 Replies View RelatedTo cut a long story short, i've got a union query that is a full outer join of 2 tables.
Anyway, the results i have got back needed formatting to no decimal places.
How do i do that?
I've tried leaving it as it is and formatting it in the report, but it still doesn't format and i can't total it up.
It's like it is showing as a text and not number.
Is there a way of converting the result to a number, particularly in a sql query.
Good afternoon!
I have a database that tracks the withdraws and returns of shared tools.
The withdraw form has a check box on it that is checked to show removed when the item is ordered (this keeps that item from showing up in inventory searches). When the item is returned, that check box is unchecked, showing that the item can be withdrawn again.
When a person withdraws an item their supervisors name is also logged so that we know who to chase down when the item doesn't come back.
I have a query that is supposed to show what items are still outstanding based on a date range (I got the prompt for date range to work..yea me). However, the query shows every time that the item was borrowed.
Example...
I borrow a hammer and my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The next I borrow the same hammer and again my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The day after I borrow the same hammer and my supervisor is Joe. I return the hammer. It does not show as still withdrawn in my query.
Some other day I borrow the same hammer and my supervisor is Joe. I do not return the hammer. The two times I borrowed the hammer with my supervisor as Bob show up as well as the other time that I borrwed it with my supervisor as Joe shows up as well as the one that shows I still have it.
. One of the criteria in the query is essentially only show the items that have the check box checked (criteria=true). But that apparently does nothing, or (more than likely), I am doing something wrong.
This is the SQL:
SELECT tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number
GROUP BY tblItemSpecifics.Removed, tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
HAVING (((tblItemSpecifics.Removed)=True));
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
How to query Select Distinct field + showing other fields? E.g.:
Code:
SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable
[Code] ......
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code:
SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType
FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID
WHERE (((tblMaterialSpecifications.ActiveInactive)=-1))
ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.
View 11 Replies View RelatedAccess 2010. This has worked before but I don't understand why this is not working for me now. When i select an item in a combo box in a form and click on a button to run a query with the results, the query is blank. If I run the query alone, it prompts for an item, I can type it in and it works. I have even tried the DoCmd on the combo box but still same results. Attached is a dummy down DB. If you run the query, it will prompt, select Paper or Rock, see results. But run the form, the drop down will not show the results. What am I missing?
View 3 Replies View Related