Ok got it. In that case, i think you create a calculated field and assign it the beginning date.
What I mean is you can create a self join on the table to calculate the difference in time. If the difference is >=1 then assign next date.
So basically the resulting new column will just have dates in yyyy-mm-dd format without the time and that new column will be a standard day.
After that you can just group by the new column...
rahulapatel
Last Activity: Jan 13 '17, 06:29 PM
Joined: May 11 '16
Location:
-
Question. Does the time in s_date ever after 7:00 am? I see that you have 6:59:59… but up to what extent that time matters?
Also, I'm assuming there is a typo on line number 3... The the s_date probably should read 2015-05-01 6:59:59Leave a comment:
-
Thanks to Tony Hine, Zmbd and jforbes for helping me with your valuable pointers and suggestions. I have accomplished what I wanted. Here is what I did:
APPEND Query to add to the tblWorkerOverdu es:
...Code:INSERT INTO tblWorkerOverdues ( Unit, Overdues, [Month] ) SELECT tblWorkers.Unit, Count(tblOverdue.Case) AS Ovedues, Month(Now()) AS [Month] FROM tblOverdue RIGHT JOIN tblWorkers ON tblOverdue.Unit = tblWorkers.Unit WHERE
Leave a comment:
-
Also, if I use a APPEND query, may be I can use the following SQL statement:
The...Code:INSERT INTO tblWorkerOverdues ( Unit, Overdues, [Month] ) SELECT tblWorkers.Unit, Count(tblOverdue.Case) AS Ovedues, Format(Now(),"mmm") AS [Month] FROM tblOverdue RIGHT JOIN tblWorkers ON tblOverdue.Unit = tblWorkers.Unit WHERE Format(Now(),"mmm") NOT IN (tblWorkerOverdues.month) GROUP BY tblWorkers.Unit;
Leave a comment:
-
Thank you guys. Appreciate all the input and suggestions.
@zmbd : Our organization structure is such that the [Worker Code] is going to be associated with the location. Fro example, [worker code] a1 is always going to be California location. The [Worker Name] associated to that worker code can fluctuate on a regular basis (sometimes monthly). I included the location column in that table so when I am doing the reports, I can do individual...Leave a comment:
-
Also, going by the above flow, I might face another problem with the APPEND query. I do not want the query to execute if it has already been executed for the current month. i.e. if there is [month] field already has Format(now(),"m mm") value then abort query. I think I might need to use VBA to achieve that. Any ideas?Leave a comment:
-
Thanks again Tony. I said exactly what you are suggesting but I guess I didn't word my explanation correctly.
I modified my current structure and split my current table into 2 tables.
tblWorkers
[Worker Code] [Worker name] [location]
a1----------- john doe----- california
a2----------- jane doe----- utah
b1----------- john smith--- texas
tblStats
[worker code] [overdues] [month]
a1------------25--------...Leave a comment:
-
Thank you for your response Tony.
I understand the structure is not the best, however we get data from a remote server for current month only. And the data we get is a list of cases every month and what I'm interested in is only storing the count of cases each month.
So what you are suggesting is I create a separate table with fields, worker code, count of cases and month and i'll identify the count with the month. That...Leave a comment:
-
Adding a field to existing table in access using vba
Ok so I am working on a Access project and I have hit a roadblock and I need your expert help.
I have a table(tblWorker Stats) with fields: [Worker Code], [worker name], [Jan '16], [Feb '16], [MAr '16], [Apr '16]. All the month name fields contain a count of cases that the worker has not processed in time for that month. I have another table (tblOverdue) with fields: [worker code], [case number] which lists all the individual case numbers...
No activity results to display
Leave a comment: