Duplicate a set of rows for each item (subdatasheet?)

[m4rz]

Hi,

I'm trying to build a staff presence tracker. I have a list of employees and I need to put 365 days next to each employee. There are a lot of employees though so this would take a lot of copying and pasting, and adding or deleting staff would be time-consuming. Here's what it's starting to look:

Name Date Presence
John. 01/01/17. Sick
John. 02/01/17. Working from home
John. 03/01/17. Present



Etc. Repeated for all staff. Essentially, I need a set of dates copied and pasted next to each employee.

Is there an easier way to do this? Can I add a subfield for date? I know putting each date as a column would eliminate this issue but this would require lots of scrolling. Any ideas would be much appreciated.

[ranman256]

Normally you enter only the 1 date the employee is sick.
Why copy ALL dates to all employees? Are they all sick on the same day?

but if you are sending all employees a day off, then make a table of the dates
then make an append query to add the dates from the date table to the tEmployeeAbsent table.

Append query , not copy/paste.

[ssanfu]

You shouldn't use "Name" or "Date" as field names. They are reserved words in Access.

I would think you would use Exception reporting. All staff is assumed to be present - if not, only record reason for absent staff.

You say 365 days. What about weekends??
Can have code that adds records with the "Presence" field defaulted to "Present", then edit the "Absent" staff.
What about terminated staff? How do you show terminations?

What do the structures of your table look like? (picture of post your dB)

[m4rz]

You shouldn't use "Name" or "Date" as field names. They are reserved words in Access.

I would think you would use Exception reporting. All staff is assumed to be present - if not, only record reason for absent staff.

You say 365 days. What about weekends??
Can have code that adds records with the "Presence" field defaulted to "Present", then edit the "Absent" staff.
What about terminated staff? How do you show terminations?

What do the structures of your table look like? (picture of post your dB)

Wow, thanks for the reply! Sorry, I had no idea I got a response as the email went to my spam folder (new user here ).

I was planning to set the default field value as 'present' and OOFH (out of office hours) on weekends. Staff wouldn't have to fill this in everyday - only on days they're absent like you say.

I haven't created the database yet but I've just been thinking about how I can do this. I've attached an image of what it would look like with my current(/terrible!) vision. I can't imagine how big this database will get and with so many employees, I'm sure this will take a really long time to set up - adding and removing employees from the list would also be a pain I imagine. I'm sure there must be a better way.

I would love to set it up so that the dates run down as rows then the names as columns - no copying and pasting that way! But there are too many employees running along the top for this (just imagine how much horizontal scrolling that would involve!). Is there maybe a way to search for a specific column name in a form so no scrolling is involved?

Thanks!

[m4rz]

Normally you enter only the 1 date the employee is sick.
Why copy ALL dates to all employees? Are they all sick on the same day?

but if you are sending all employees a day off, then make a table of the dates
then make an append query to add the dates from the date table to the tEmployeeAbsent table.

Append query , not copy/paste.

Hm, interesting. I was planning to just set it to 'present' as default. Would it still be possible for employees to add in their absences and view/edit their data in tabular form?

[ssanfu]

Still not sure the purpose of the dB.... Why have 356 days for every employee?
Can you describe what/why you are trying to do? Don't use jargon; pretend I am 10 years old.....

<snip>I haven't created the database yet but I've just been thinking about how I can do this. <snip>

Before you do anything else, I would advise working through these tutorials. Don't just look at them, actually do these step by step. I think it will help you a lot.

<snip> OOFH (out of office hours) on weekends. <snip>

Shouldn't that be "OOOH" or "OoOH"??

<snip>I would love to set it up so that the dates run down as rows then the names as columns - no copying and pasting that way! But there are too many employees running along the top for this (just imagine how much horizontal scrolling that would involve!). <snip>

This is an Excel spreadsheet layout - termed "Committing Spreadsheet".
Spreadsheets are typically short and wide.
Access tables are typically tall and narrow.
Try and forget anything you know about Excel when designing a database.




This is what I see for tables with my limited knowledge of your requirements.


You could run code to search through the employee table to select emps where the date is between the hire date and the term date, then append records in the attendance table.

[m4rz]

Still not sure the purpose of the dB.... Why have 356 days for every employee?
Can you describe what/why you are trying to do? Don't use jargon; pretend I am 10 years old.....



Before you do anything else, I would advise working through these tutorials. Don't just look at them, actually do these step by step. I think it will help you a lot.



Shouldn't that be "OOOH" or "OoOH"??



This is an Excel spreadsheet layout - termed "Committing Spreadsheet".
Spreadsheets are typically short and wide.
Access tables are typically tall and narrow.
Try and forget anything you know about Excel when designing a database.




This is what I see for tables with my limited knowledge of your requirements.


You could run code to search through the employee table to select emps where the date is between the hire date and the term date, then append records in the attendance table.

Wow, thank you so much for putting your time into this! I think you have the right sort of idea about what I need.

You're right - I don't know enough about Access and I've been trying to build this system using my knowledge of Excel. I was even planning on copying and pasting a table directly from Excel haha. I need to read these guides and gain a better understanding. I'll try to work my way up from what you've shown in your screenshot - thanks for that!

I basically just need a register that records where everyone is (working from home, present, holiday, sick, study leave, etc.). Each employee would need to fill this in when needed using a form - they need to be able to edit future and past dates so not just the current date and ideally not one day at a time (e.g. they may want to add 2 weeks of holiday in one go). They should only be able to access their own individual records. Management needs to be able to view a summarised table listing how many holidays, sick leaves, etc. an employee took.

Also, you're right - it should be 'OOOH'

[ssanfu]

Well, to start work through these 3 tutorials (actually do them - not just read) - it will help you.
http://www.rogersaccesslibrary.com/forum/forum46.html

Info about normalization
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

My PK fields are always Autonumber types, so
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

Database Table and Field Naming Suggestions
http://www.databasejournal.com/featu...uggestions.htm


Names NOT to use as field/object names: (bookmark this page for future use)
http://allenbrowne.com/AppIssueBadWord.html
example: "Name" is bad. "FName" or "FirstName" good. "Date" is bad, "BirthDate", "PaymentDate", "EntryDate" are good... plus they are descriptive.


Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.



Enough reading for you???

I basically just need a register that records where everyone is (working from home, present, holiday, sick, study leave, etc.). Each employee would need to fill this in when needed using a form - they need to be able to edit future and past dates so not just the current date and ideally not one day at a time (e.g. they may want to add 2 weeks of holiday in one go).

Easily done. Use a form to be able to add/edit records. Code would /could be used to enter 1 or more attendance records

They should only be able to access their own individual records. Management needs to be able to view a summarised table listing how many holidays, sick leaves, etc. an employee took.

Use a login form. Common topic on the forum.

Management needs to be able to view a summarised table listing how many holidays, sick leaves, etc. an employee took.

Same login form for this. Obviously, managers would not be restricted on records, forms or reports.