Queries :: Tracking Time Spent On Customers Records Based On Date Of Entry
Apr 17, 2014
I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.
I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment.The query I have consists of four fields which are
1- Order ID 2- Payment 3- Date (Default value set to Date ()) 4- Date for next time Payment (Default value set to date () + 30)
I made another field called "states". In this field I putted the following expression
Code: IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")
Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet. However, this method has the following shortcomings:
1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true. 2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish. 3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.
To overcome the above shortcomings, i modified the expression to
Code: IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")
This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days. However, the customers who have payed will appear also, and that is the problem. The problem here is I can't compare the current payment date with previous one for one customer. method to create suitable expression, or even another query scheme ??
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 query which is displaying a date/time field a record was input. in my query I'd like to only display the records where the date/time based on what today is i.e. Monday it will display <Friday or yesterday for Tue to Fri.
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
In My Db I have TblSupportTickets and a Table which records support ticket activity. When adding a new activity detail to the Db it automatically records when the FrmActivityDetail opens and on closing, it records the time the Form was closed. So in TblSupportTickets which relates by ID to TblSupportTicketActivity on a one-to-many relationship.
I then created a query with SupportTicketID, TimeOpen, Time Closed. SupportTicketID criteria is Forms.FrmSupportTicket.txtSupportTicketID
This lists all the time open's and timeclosed's for that support ticket.
how can I work out the total time spent on that support ticket?
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.
I have a form where someone enters a 'Job' to be done which in turn creates a record in a table with all this information
On the form I have a field called 'Repeat Frequency' where the user can choose how often they would like this 'Job' to repeat. e.g. Monthly
Then, on another form someone else can check what 'Jobs' should be done on that day
I have managed to get this to work fine using the DatePart function and it displays all jobs to be done that fall on the current day (e.g. if they choose to repeat it weekly, it will look for all entries where the datePart "d" [DateToBeDone] = Date()-7, -14, -21 and -28)
The problem is that i have been limited to the options of 'repeatability'
What i would like to do is change the options from 'Weekly', 'monthly' etc and instead have a field makrked "Repeat every so many days"
Is there a criteria i can use in a query where i can show records every so many days? i.e. if there is a job in the system that should repeat every 7 days and the [DateToBeDone] is set to 31/10/13, it would return this record on 07/11/13, 14/11/13, 21/11/13 and so on?
I Have tried DatePart ("d", [DateToBeDone]=DatePart ("d", (Date()-[RepeatDays] but what happens is it only shows it if the day is that many number of days in the past and not multiples as i said above.
I am trying to create an Access database where I can let the employees enter their time-in and time-out, at the end of their shift. I already created this function working, but running into another issue. Everyday, employees need to enter a break. Sometimes they take a break and sometimes they don't, if they work shorter hours. Thus, the break is not a default 30min and has to be enterd by the employee.
Below is the code I have to calculate the total hour:
txtCalcTime has the below code in control source: =Int([CalcTime]/60) & ":" & Int([CalcTime] Mod 60)
Control source for this form "FrmTimes" property, in which Txtcalctime resides:
QryTimes "QryTimes" query has the code below: SELECT TimeID, TimeIn, TimeOut, Brake, DateID, DateDiff("n",[TimeIn],[TimeOut]) AS CalcTime FROM EmpTimes;
This shows the total number of hours worked. But how can I make it to substract the break time entered by the employee in "txtbreak" on FrmTimes
I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?
I am working on a project where I am creating a database that tracks open orders (old and new orders that still need to be billed but haven't been billed for one reason or another).
I have my "Master" table built and now I need to create 2 queries, one query retrieves the Top 20 orders based on value and that are older then 30 days and the second query is to retrieve the Top 100 orders based on the same criteria but the Top 100 query should not include the orders that appear in the Top 20 query.
My Top 20 query is below
SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS FROM MASTER WHERE (((MASTER.AGE)>=30)) ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;
I have tried multiple ways to do the Top 100 query and exclude what is returned in my Top 20 query but so far no success.
Hi, hope someone has an idea of where to start on this.
Our company offers projects to multiple contractors and we have to enter when we spoke to them and their response. I put a textbox with default of Now(), thinking these would be recorded in real time and it would be no effort at all. But the offers are frequently entered days later, and keying in dozens of specific dates/times in the long format is very irritating for users.
Does anyone know of something similar to the DatePicker or calendar popups that will let you use arrows or comboboxes or something to quickly enter a time? We record date, hour & minute (no seconds) because it updates the contractor's order on the rotation list (i.e., next call is made to the contractor who has been waiting the longest for an offer). No ActiveX please, we're in secured network environment.
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName FROM Staff INNER JOIN myTable ON Staff.ID = myTable.StaffID WHERE myTable.myDate >= #3/2/2014# AND myTable.myDate <= #3/3/2014# AND myTable.myTime >= #8:00PM# AND myTable.myTime <= #11:00PM# ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically
I have a database that I will use for invoicing, but I would like it to automatically create an invoice for customers based on parameters set for that customer (e.g., monthly, biweekly, etc.). I have tables containing the customer information, the item they are being billing, the price, etc. I want to be able to have access automatically create the invoices and add them onto the invoice table each month.
Maybe there is a better way, but I thought that if I created a query for all people that are billed biweekly and all people that are billed monthly, that I could run the queries when applicable and then somehow write a macro that would go through the list of customers and add each of them to the Invoice table and add an autonumber. That way I could click run query, run macro, and then do my invoicing. I don't know if that is the way to go or not.
I'm trying to find a solution to a problem. Is there any way using VBA to generate a random number, based off the current system date and time? The theory is that a random number seeded by the system date/time will be truly unique and the possiblity of it ever occuring again will be nearly nil.
Or is there a better way to create unique ID's (such as member ID's, or something similiar) based off the system date/time that can be used as a primary key in a relational db.
I have a time tracking system where users can clock in and clock out. I want to have an error message when they clock in and then accidently clock in again. Therefore, they can only clock out after they have clocked in. The data goes into the table called "Timeclock Table". The fields in that table are SSN, Clock-In, Clock-Out, and TimeStamp. the clock in and clock out are check boxes. can you guys help me??????
I have a form and I want the dates to be displayed based on predefined time. In this case, I want my form to show present date from present day's 7:00 AM to next day 7:00 AM.
In this thread: http://www.access-programmers.co.uk/forums/showthread.php?p=549287#post549287
I had a problem. Now I decided to just make two tables, one with sales for a "passer by" and/or directly to a client without a case/labour costs. And a table for sales in relation to a case with labour costs etc. I guess I still m*ssed up on that part and have to make do with that decision (kind of like I depicted in the photoshopped image).
For invoicenumbers I want to use DDMMYYYYHHMM. When adding that field to the design of a table, how do I go about that? Do I just use a Text field and create an input mask for that? Or do I need to use a number format with a certain input mask? Or would it be wiser just to use the date/time type of field and give it a new inputmask? I guess 000000000000 would be the one for that, right? Although I read the help file I do not know if I should use ;0; behind those first group of zero's or not. Yes it is a mandatory field, but it will fill in itself as soon as a product is typed in in the product/invoice list, with a default value of the current date and time.
I need to brush up my knowledge on the formatting for that so that 28-05-1975 19:55 will be displayed and stored as 280519751955. Or is that also done automatically by using that input mask? Or will that give an error when Date or Time() etc. is used for that field in VBA?
Just a few questions I need some help with, I've been trying some stuff but it really doesn't "stick".
And if anyone has some time, I could really use some general advice/ideas for setting up the invoicing part differently for a new version of the db yet to come in the future.
Hello All... I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid". The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
