hi all,
I've got one question regarding dates.
I've already make the form to show the count of records by using query according to the start and end date selected by the user.
What I want is when I open the form, it should show the results of count between the minimum and maximum dates inside the data table. It means I want to show the count result without date specification that is the total count from all records of the whole table .
Can anyone help me how can I make it?
thanks a lot in advance.
I would like to know if it is possible to pull the max and min start date and end date. Basically i have an option where by the user selects the desired date range that they would like.
Now I would like to know how to get the earliest and latest date range should they not select a date range. My date range is being displayed at the top of my report.
Does anyone have a good technique for enforcing a minimum number of records in a table?
I am attempting to ensure that in a table of Roles, there be a minimum of two Roles defined as Technicians. The field which identifies a Role as Technician is a Boolean.
I wrote a Function which does a domain lookup to count the number of Roles defined as Technician and am calling it from the BeforeUpdate event.
However, if there are 2 Technician Roles, and I mark a third Role as Technician, change my mind and try to unmark it...of course the Function can still see just the 2 existing.
I realize I can Undo the edit, but a user may not.
I have six textboxes in my form. First 5 textboxes have dates ( MMDDYYYYY). In the sixth textbox I need to display the minimum date among these five dates.
I'm trying to get my query to display only the minimum value within my query results. I have the following fields, CustomerID, OrderID, ProductID, WarehouseID, Freight Cost.
My current query will return results that show me the locations and freight cost of each product to my customers. My intention is for the query to only return records that has the lowest freight cost as there may be several warehouses with identical products but with varying freight costs due to location to customer.
I've tried to apply the MIN function on Freight Cost but it will only sort the records in ascending order instead of only displaying the record with lowest frieght cost.
I'm extremely new to access and have been given the task to create and send a report for the number of rejects at the end of the day. I'm trying to set up a query to do this but it isn't working quite as I want it to.
I have Q1-C that is set to count the number of rejects at stations. However when I run this query, it counts total rejects by type (all records) and not by date. For example, on 12/3/12 there are 2 rejects (1 - Epoxy on screen and 1 - electrolyte leaks) but the query counts 17 electrolyte leaks and 1 epoxy on screen for 12/3/12.
Next I would like to build a report that would display the type of rejects of the last day for both 'Rejects at Stations' and 'Screen Rejects'. So the report "Productions Rejects"should show rejects on 12/3/12 for Q1-C and 12/4/12 for Q1-O. However, the report now shows all the dates of rejects.
I need to create a query that will return for the latest document, the greatest document number. If I do select max(doc_date), max(doc_nbr), I end up getting the max doc_nbr, but it has an earlier doc date. How would I structure the query to do this?
An example is for these values:
1/30 1 1/30 3 1/30 5 2/28 2 2/28 4
The result should be: 2/28 4
I will then need to see if this returned value is different than the value that was returned the day before, but I am pretty sure I know how to do that.
I have created a db for my warehouse, now what I want is to set a maximum no of items to be issued to one person = 20 (means 20 rows only) in a subform. If the number exceeds it prompt user that you reached to the maximum no of issues.
Code: ' count records in query Dim rs As DAO.Recordset Dim db As Database Dim strSQL As String Dim beginDatum As String Dim eindDatum As String Set db = CurrentDb
I've got a single table with multiple fields, three of which are a date field ('DDate'), a time field ('TimeET') and a unique identifying field ('Unique Call Key'). I'm attempting to write a query in the QBE that will allow me to count the number of instances of 'UniqueCallKey' for each hourly time interval (7:00:00 AM - 7:59:59 AM, 8:00:00 AM - 8:59:59 AM, 9:00:00 - 9:59:59 AM, etc) for any selected date range (BETWEEN 'DDate'(1) AND 'DDate'(2)). When I try to simply use the Count function on 'UniqueCallKey' as an Expression and 'TimeET' with 'Like '7:*AM' as a Where criteria then do the same with another instance of 'UniqueCallKey' and 'Like 8:*AM' as the criteria for a second Where criteria for 'TimeET' the query returns an empty set. What I'm trying to accomplish would be a column of dates, a second column that counts the number of instances of 'UniqueCallKey' at the 7AM interval for each date in column 1, a third column that counts the number of instances of 'UniqueCallKey' at the 8AM interval for each date in column 1, etc to a final column for counts at 7PM.
I need a query that will produce the records that have maximum date for each author (distinct authors), ordered by date descending. For example, output should be in this case:
Can anyone please tell me the maximum number of records a table can accomodate? Also what happens if the number of records exceeds this maximim value? Awaiting reply
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
Hi People, I am working on a database related application in which the user enters the data from the form and it is stored in 3 related tables in DB.
This is what I want to do,
If IsNull(Me.Q1_2005) = True Or Me.Q1_2005 = "" Then rs!January2005 = Me.Jan_05 rs!February2005 = Me.Feb_05 rs!March2005 = Me.Mar_05 If parametername = "time" then rs!Q12005 = DMax([Me.Jan_05], [Me.Feb_05], [Me.Mar-05]) else rs!Q12005 = DMin([Me.Jan_05], [Me.Feb_05], [Me.Mar-05]) End If
If user enters monthly data of Jan'05, Feb'05 and Mar'05, but he is not entering quarterly data of 2005 Q1. So I need to update Q1 data in that record, that has a condition. If the parameter name is time then I want maximum of the three month data assigned to Q1 record in DB table else I want minimum of those three text box values assigned to Q1 record in DB table.
Please help me with this kind of conditional statement.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings FROM tblBookings GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0 The Hilton. Bookings 3 The Carlton. Bookings 0 The Lowry. Bookings 2
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I have a table which has repeated IDs, I want to find the start of the ID then mark it as Y in the next field, the remaining repeated IDs should have blanks in the next field, how do I do this, thanks for any help
I have a counter on my form which will increase or decrease depending on weather a number of tick boxes report true or false. Is it possible to set a minimum aloud value on a box regardless of other criteria. At present I can depending on certail criteria, finish up with a - number which for other reasons, I dont want.
I have a main form and 5 tabs within that mainform that are subforms. They are linked by an EventID.
The subforms are continuous forms, but only one record is "required". What kind of code do I need to make sure that the data entry person enters at least one record in each subform.
Also, is there a way that when I am tabbing through my subform and get to the second record and leave it blank, that when I press tab it will take me to the first record of the next tab subform.
I have a query that uses 2 Fields from a large table. Cashier and Date. The problem I am having is I would like the select a time frame, a Start and Stop date, and count how many times the cashier comes up for that day.
Sounds easy, I would think so but having a problem. The dates are automatically imported into the large table as format 01/01/2005 12:00pm (Can't do anything about this).
When writing the SQL and the Count function, it is using the time as well in its calculation, but I simply want it to count on the date only. So unless a cashier existed exactly in the table as 01/01/2005 12:01pm, 01/01/2005/12:01pm it only counts it as a total of 1, instead of twice for that day.
I simply want the SQL function to read the Date field as 01/01/2005, and drop off the Time. I can format it, but only changes it visually and the SQL statement still sees the time.
Any help would be appreciated.
I found this code on the boards here, almost what I want but does not group each Cashier by day and total, it does the total of all cashiers for that day. I need the Cashiers sorted for each day, and totalled.
SELECT Format([Date1],"dd mmm yyyy") AS Day1, Count([ Cashier1].[Cashier1]) AS CountofCashier FROM CashierQ1 GROUP BY Format([Date1],"dd mmm yyyy"), Format([Date1],"dd mmm yyy") ORDER BY Format([Date1],"dd mmm yyyy");
