Modules & VBA :: Create A Dashboard With Data From Last 7 Days?

Dec 16, 2014

I have Access table tblupdate that stores following fields:

Date,Username,To_Be_Updated,TargetDate

Now I want to create a dashboard that will show data of last 7 days from todays dates in 3 charts :

1st chart with Red colour (That means if todays date is greater than TargetDate)

2nd Chart with Green Colour (That means if todays date is less than target date)

3rd chart with AMber colour that means if todays date is equal to Date field.

View Replies


ADVERTISEMENT

How To Create A Dashboard With MS Access?

Feb 27, 2008

Hi,

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.

View 14 Replies View Related

Modules & VBA :: Splash Screen / Dashboard Progress Bar

Jul 9, 2014

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.

View 5 Replies View Related

Queries :: Create A Query That Will Count The Days Difference Between Two Dates

Jul 3, 2015

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.

View 7 Replies View Related

Modules & VBA :: How To Create Error Log For Importing Data

Mar 19, 2014

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"

[Code] .....

View 5 Replies View Related

Dashboard

Jan 3, 2006

I have created an Access Dashboard for a SQL Database. This is secured user Access User Level Security and works fine.

I am now looking to add some data from other Secured Access Databases but these are secured using different Workgroup Files.

Is there a way to do this?

View 3 Replies View Related

Modules & VBA :: Auto Create Emails In Outlook Based On Data In Access?

Apr 20, 2015

I have a table called Contacts in Access:

ID Email
001 email1
002 email2
003 email3
004 email4
008 email5
010 email6

And I have a table called Data in Access:

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.

View 9 Replies View Related

Modules & VBA :: Dynamically Create Headers In Excel Sheet Using Access Table Data

Apr 2, 2014

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

View 14 Replies View Related

Forms :: Dashboard - Calculations On Invisible Form

Apr 3, 2013

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.

Is there a better way to do this?

View 1 Replies View Related

Total Record In Each Query To Be Shown In Main Dashboard Form

May 23, 2013

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...

View 3 Replies View Related

Modules & VBA :: How To Set Date Between Two Days

Dec 22, 2013

I want set two dates by vba but i don't no how to do it ... for more understand look the simple example

If Date = #12/20/2013# Then
MsgBox "Wrong", vbExclamation, "attention"
End If

But I want set date between two days for example like that

If Date = between #12/20/2013# and #12/20/2014# Then
MsgBox "Wrong", vbExclamation, "attention"
End If

View 3 Replies View Related

Modules & VBA :: Results For Last 14 Days

Feb 27, 2014

I'd like to get results for last x days

I'm trying to use this, but it doesn't filter the results

Code:
DoCmd.OpenForm "Overview", , , "oDate" > (Date - 14)

or more specifically

Code:
DoCmd.OpenForm "Overview", , , "oOdd = '" & Me.cmbodd & "' and oDate" > (Date - 14)

i tried plenty of different variations with not much success...

View 3 Replies View Related

Queries :: Counting Days Between Dates - Vacation Period And Work Days

Sep 11, 2014

I have a form called subfrm_vactions

rowsource is a query called qryVacations

the query should calculated two things

1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)

2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)

the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want

simply i want to calculate the work days.

View 5 Replies View Related

Reports :: Count Number Of Days Each Week (7 Days) Person Has Worked

Jan 24, 2014

I have to count the number of days each week (7 days) that a person has worked - simple enough.

But - what if they come off the job within the week?

I have been using the DateDiff function which is fine.

In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?

View 2 Replies View Related

Trouble Implementing The Regular Days And Sunday Working Days?

Dec 14, 2004

Hello everyone,

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

dteToday = LogDate
dtePrior = DateAdd("d", -6, dteToday)

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

strSQL = "SELECT [LogDate] "
strSQL = strSQL & "FROM tblLog "
strSQL = strSQL & "WHERE [LogDate] >= #" & dtePrior & "#"
strSQL = strSQL & " AND [LogDate] <= #" & dteToday & "#"

'Create a recordset of records from date entered and 7 days prior
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveLast
i = rs.RecordCount 'Count the number of days between the two dates

If i = 7 Then 'If there are seven days then it is a Sunday
Sunday.Value = 1
Else
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If

Else
Sunday.Value = 0
End If

DayofWeek.Value = Format(dteToday, "dddd")

End Sub

View 1 Replies View Related

General :: Convert Weekend And Holiday Days To Work Days

Jun 14, 2013

Is there a way i can convert the weekend days into working days for example.

5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.

View 1 Replies View Related

Queries :: Number Of Days Between Two Dates With Irregular Days Worked

Apr 14, 2014

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.

View 1 Replies View Related

Queries :: Missing Days - Creating Table With 31 Days Of Zero Values

Apr 3, 2013

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?

View 4 Replies View Related

Modules & VBA :: Working Days Between Two Dates

Jan 26, 2015

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.

[code]...

View 8 Replies View Related

Date Add Problem (calendar Days Vs Working Days)

Dec 4, 2006

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.

<=DateAdd("w",-12,Date())

Can anyone help, thanks for your time!!!

View 8 Replies View Related

Date Add Problem (calendar Days Vs Working Days)

Dec 4, 2006

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.

<=DateAdd("w",-12,Date())

Can anyone help, thanks for your time!!!

View 6 Replies View Related

Modules & VBA :: Grab And Calculate Days Between Two Dates

Feb 11, 2015

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).

View 7 Replies View Related

Modules & VBA :: Calculate Number Of Unique Days

May 9, 2014

My table : Id Start End

1 2013.11.20 2014.01.10 2 2014.01.17 2014.01.30 3 2014.01.06 2014.01.27 4 2014.02.04 2014.02.23 5 2014.02.07 2014.02.17 6 2014.02.21 2014.03.08

How to calculate total number of unique days? Date ranges are overlapped.

View 7 Replies View Related

Modules & VBA :: Calculating Number Of Days Between Records

Sep 10, 2013

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.

View 14 Replies View Related

Modules & VBA :: Number Of Weekend Days Worked In A Month?

Oct 6, 2013

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:

=DateSerial([YearNo],[MonthNo]+1,1)-DateSerial([YearNo],[MonthNo],1)

in a text box, and it works fine.

But I don't know the syntax to get these numbers:

the number of weekend days worked in that month, and the number of weekend day not worked in that month My field is call App_date.

Tried this but does not work - the numbers it returns are incorrect (in every month): =Count(Weekday([App_Date]))

The subreport's is based on a select query with only the Appointment table. It only has four "fields" - that work fine.

They are: the appointment date and its date parts - defined in numbers.

App_date
MonthNo: DatePart("m",[App_Date])
YearNo: DatePart("yyyy",[App_Date])
DayValue: Weekday([App_Date],2)

how to get my weekend work and not work total day count?

View 13 Replies View Related

Modules & VBA :: Count Total Working Days Of Month

Mar 5, 2014

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).

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved