I would like to get pro month and marketing campaign the values of about 12 KPIs. This dashboard will be used by different countries/marks. My KPIS do not have the same entries. For example : - the customer satisfaction is calculated on the number of customers "satisfied", "unsatisfied", "acceptable", - the percentage of corrected and on-time reports is based on the number of reports "total", "late", "with failures", "late and with failures", - the availability of the is based on the number of hours... etc.
Do you have an idea of the way to proceed?
I created the following tables : -Tbcampaign (ID_Campaign, Campaign name) -TbKPI (ID_KPI, KPI Name) -TbRegion (ID_Region, Region Name) -TbTime (ID_Time, Month, Year) -TbFact (ID_KPI, ID_Campaign, ID_Region, ID_Time, Value)
... but how can I proceed with the KPIs? Thanks a lot in advance! Co.
I want a progress bar in a form that will increase from bottom to top . The data will from Access table. The bar will start from 0 to Maxvalue. The Maxvalue will be calculated as below:
Total number of batches in the table/ No. of working days between 14072014 and 31112014 / 2
So the above calculation will define the range of a progress indicator.
And now we have to show the % percentage that means how many batches have been scanned for today. For this the following calculations will be used:
Count total batches where scandate=today and scannedby<>null
Now this will show the percentage in the bar that means hopw much work has been completed.
The progress bar should move from bottom to top when % increases.
I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:
tblRegion = RegionID tblStatus = StatusDate
I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.
I am using below code to import various Excel spreadsheets into Access. However every now and again below code goes into error, usually because there is no "toimport" range in the Excel file.
So I would like to use On Resume Next, so it can import all other files. But in order to do it, I need also to be able to somehow capture information about all files that went into error, so I can fix them.
I would like to create like an "error log" and I would like Access to update it with information about all the files that were not uploaded + error message + date/time.
Ideally I would like it to be in Excel file. I tried to use DoCmd.TransferSpreadsheet but it does not do what I need
Code: Function import() Dim strFile As String DoCmd.SetWarnings False ' Set file directory for files to be imported strPath = "C: est"
ID Date Person Title Yes/No Action 001 20/4 Mr A Consultant 001 20/4 Mr B Supervisor 001 20/4 Mr C Consultant 004 20/4 Mr D Consultant 010 20/4 Mr E Consultant 010 20/4 Mr F Supervisor
What I'm looking for is coding that would start with the first ID number in CONTACTS and see if there is the corresponding ID in DATA. If there is, then I want it to create an Outlook email and have:
1.To field populated with the email address in the CONTACTS table
2.From and CC fields to be my email address
3.The subject line needs to read [ID] "Request" and then today's date backward (e.g. 20150420).
4.The body I need to put some generic wording and then a table with the records from the DATA table (first 5 fields plus the headings).
5. The format of the table etc isn't important as long as it is a table format
If it's created the email I want it to go back to the DATA table and put "Email Created" in the Action column for those IDs.
I don't want the email to automatically send; I only want it to create a draft (as this data needs to be manually confirmed before sending).
If for example it gets to checking in DATA table for ID 002 (and as seen there's none there), I want it to skip creating the email and move on to the next ID.
In the end I would expect to see 3 draft emails open in Outlook ready to be sent, looking something like the below (just 001 below)?
From... my email To... Email1 CC... my email Subject: 001 Request 20150420
action the below:
ID Date Person Title Yes/No 001 20/4 Mr A Consultant 001 20/4 Mr B Supervisor 001 20/4 Mr C Consultant
And the DATA table look like this (if only 001 email was created):
ID Date Person Title Yes/No Action 001 20/4 Mr A Consultant Email created 001 20/4 Mr B Supervisor Email created 001 20/4 Mr C Consultant Email created 004 20/4 Mr D Consultant 010 20/4 Mr E Consultant 010 20/4 Mr F Supervisor
I will need to add more data to the CONTACTS table over time, also the DATA table will be different each day so I need the coding to accomodate for both of these possibilities.
I have an access table and I want the code that will check two columns in the table "EnvelopeType" and "EnvelopeSize" and create headers in Excelsheet automatically. In the attached workbook, like in sheet1 the headers are already appeared, I want this to be done dynamically using vba code so that if new values get inserted in EnvelopeType and EnvelopeSize then we won't have to change the code to display more headers.
Please see attached workbook named Sample and Access table. E.g.
EnvelopeType EnvelopeSize TNT 2nd Class C5 PP1 2nd Class C5 PPI 1st Class A4 Recorded A4 TNT 2nd Class C5 PP1 2nd Class C5 Recorded A4 PPI 1st Class A4 Recorded C5
With the code it should display following headers in excel sheet:
TNT 2nd Class C5 PP1 2nd Class C5 PPI 1st Class A4 Recorded A4 Recorded C5
I have a dashboard that opens with my main form and it contains many textboxes with Dcounts, Dlookups and Dsums in it. I am hoping that I can put these textboxes on a separate form that is hidden unless a command button is clicked to make it visible.
I know how to do this, but my question is whether or not all of those queries will run whether or not the form is visible. Obviously if the Dsums etc.. are going to run anyway, then I won't bother.
I have a database with many Quires I want to create in the main form "welcome Form" many txt boxes cells
To show a total records in Each Quires
For Example I have the below list queries
Expired Employees PassportExpired Employees Work PermitExpired Employees VisasExpired Employees Insurance
In the "Welcome Forms" I will create a txt boxes Cells for Each one of those Queries to be Show only No "total Record in Each one". It is like a dashboard to monitor daily without opening the Report or Query...
I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?
The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.
How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks
Tim
Below is the code i'm using for defining Regular days and Sunday Working Days:
Private Sub LogDate_AfterUpdate() Dim rst As DAO.Recordset, rs As DAO.Recordset Dim db As DAO.Database Dim dteToday As Date, dtePrior As Date Dim i As Integer
Set db = CurrentDb Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
rst.FindFirst "[HolidayDate] = #" & dteToday & "#" 'See if the date entered is a holiday If rst.NoMatch Then Holiday.Value = 0 Else Holiday.Value = 1 End If If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if Dim strSQL As String 'it will be a regular day or a Sunday
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.
Public Function LeaveSettlement(ByRef Leave_Start As Date, _ ByRef Leave_End As Date _ ) As Integer ' Returns the number of weekdays in the period from Leave_Start ' to Leave_End inclusive. Returns -1 if an error occurs. ' If your weekend days do not include Saturday and Friday and ' do not total two per week in number, this function will ' require modification.
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
What I have is two fields, one is a simple date and the other is slightly more complex as it holds a date but in a different structure (a dated case number).
The case numbers are for example, 150211551223 the date being the first 6 numbers and equaling to 11/02/2015. so the code will need to grab this data, spin it around and convert it.
The formula will then be:
simpledate - convertedcasenumberdate = days between.
(simpledate will always be the latest date of the two).
I am trying to calculate the number of days between incidents.
If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.
My subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).