X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Layout Advice, Access 2010    
post Mar 26 2020, 07:58 AM

Posts: 441
Joined: 9-September 03
From: UK


It's been a good while since I last designed a database but I am now being asked to put one together.

I need to report back on a daily basis where anyone within the organisation is working. So for ease of explanation, the options for working are 'Office based', 'Home based', 'Sick', 'Leave' and 'Non-working day'

The database needs to be accessed by all managers who will need to record what their staff are doing on a daily basis. They need to view a working week, so Monday to Friday, a list of all staff they uniquely manage, and then record where each staff member is per day. So for instance, 'Employee1' is Home based on Monday to Wednesday, Sick on Thursday and Office based on Friday.

On a daily basis I need to report how many are working and how many are not working.

At any given time I need to be able to look at where staff are deployed.

Each manager is responsible for a team, each team has fixed employees in them.

So for instance 'Bob' the manager might manage the Stockroom. In the stock room there a five employees. Bob needs to report on just those 5 employees.

The database will be initially accessed by a form, used selects who they are, and then they are taken to their team.

My confusion lays with how do I produce a table detailing Monday to Friday on a week commencing basis. So wc 23rd March 2020, Monday to Friday.
Next week I'm recording wc 30th March 2020. I don't necessarily need to see the previous weeks.

Could anyone advise on layout etc, as to say I'm rusty is an understatement.
Go to the top of the page
Larry Larsen
post Mar 26 2020, 08:49 AM

UA Editor + Utterly Certified
Posts: 24,526
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)

Would seem's like a large project to undertake and hopefully this link will give you some ideas about your table structure and relationships..
Employees Tables And Relations
Attached File(s)
Attached File  EmpTablesRelations.jpg ( 36.99K )Number of downloads: 0

"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
post Mar 27 2020, 12:32 AM

Posts: 31
Joined: 8-May 19
From: USA

The DatePart() and Format() functions can each give the week of the year.

You can use the DatePart() function in a query like this:

WorkWeekOfYear: DatePart("ww",[WorkDate],1)

Or use the Format() function like this:

WorkWeekOfYear: Format([WorkDate],"ww",1)

So for example a date like 1/3/2020 would show 1 and dates like 1/6/2020 and 1/9/2020 would show 2.

And here is some more info about the DatePart() function...

Access Expressions - Date and time functions - DatePart Function
Access for Office 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

And here is a related week of year bug to perhaps also keep in mind...

Format or DatePart functions can return wrong week number for last Monday in Year
Applies to: Access
If you use the Format or DatePart functions, you need to check the return value. When it is 53, run another check and force a return of 1, if necessary.

Or possibly instead you could have min and max dates on a form where the query looks for its criteria...

Create an Interactive Access Form to Filter a Query by Date Range
Posted by Dan St. Hilaire / April 23, 2012 / Access — 46 Comments ?
And can change the last part so that instead of opening the query it opens a report whose RecordSource is the query.
This post has been edited by Bullschmidt: Mar 27 2020, 12:38 AM

J. Paul Schmidt - Freelance Web and Database Developer
Sample Database on the Web
Sample Access Database

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    2nd April 2020 - 07:41 PM