How to write code to create many fields (and other questions)?

[VAer]

I have not used Access for years, not quite familiar with Access either.

I want to design three tables: Employees, EmployeeSelfEntryNote, SummaryView

Table Employees: I will manually update this table periodically.

Table EmployeeSelfEntryNote: Every employee can enter one note for each day, via a Form. If the employee previously submitted note for the same date, then the new note will overwrite the previous note. I may need to write code for the feature of overwriting.

1. How to check if the note already exists for that date. If yes, overwrite the note; if not, add the new note.

Table SummaryView: My main questions are related to this table. Let us say, now the table has already fields: EmployeeUserID, FirstName, LastName, 12/13/2019 Fri, 12/14/2019 Sat, 12/15/2019 Sun, 12/17/2019 Tue, 12/18/2019 Wed. The first three fields(EmployeeUserID, FirstName, LastName) will always remain. EmployeeSelfEntryNote will be shown in SummaryView table, that is the whole purpose of project.

If I want to the fields to cover date from 12/12/2019 to 10/15/2020, then how to write code to add so many fields? I actually can break my questions into Five parts:

1. How to delete any fields(an associated data) except EmployeeUserID, FirstName, LastName, this will delete 12/13/2019 Fri, 12/14/2019 Sat, 12/15/2019 Sun, 12/17/2019 Tue, 12/18/2019 Wed
2. Refresh SummaryView table with Employees table. At this point, SummaryView table has only three fields, and I want the SummaryView records to be same as Employees table. Not sure if I can do this in Table relationship or programming.
3. Add fields(data type: short text) to SummaryView: 12/12/2019 Fri, 12/13/2019 Sat, 12/14/2019 Sun, ...... , 10/13/2020 Tue, 10/14/2020 Wed, 10/15/2020 Thu
4. How to write code to loop through EmployeeSelfEntryNote to pull Note into SummaryView table. Employees are not required to submit Note for each day.
5. In the future, if I want to delete any fields before 3/18/2020 Wed, how should I write the code?

Thanks.

[June7]

Instead of routinely adding/deleting fields and deleting records, why not normalize data structure and never delete anything?

Instead of deleting employee record, just flag as "Inactive", use a datetime field to enter date employee left.

[Micron]

I agree, but will add that you have designed this as you would a spreadsheet and that will continually present issues. Database tables should be 'tall' whereas spreadsheets are 'wide'. You might want to research 'normalization' with respect to database design. Not sure I agree with

If the employee previously submitted note for the same date, then the new note will overwrite the previous note.

but it's your db. How would you like to realize you forgot something and rather than being able to add the comment, had to re-write everything just to add that comment? Would you eventually just say 'forget it'? I would.

[ArviLaanemets]

No need for 3rd table. Instead have a crosstable query for last 7 dates [e.g. from Date()-6 to Date()] from EmployeeSelfEntryNote. When result table is displayed in form or report, use calculated unbound controls as headers to display wanted texts there.

[VAer]

Sorry that this website does not notify me of new replies. I thought it would send me email when I received new reply.

I have to manually come back and check this thread, sometimes it is off the top of my head when there is no email notification.

The question is more about code, but any suggestions are welcome too.

[VAer]

Instead of routinely adding/deleting fields and deleting records, why not normalize data structure and never delete anything?

Instead of deleting employee record, just flag as "Inactive", use a datetime field to enter date employee left.

I am not sure if it will be slow when there are too much data over a long range of time.

[VAer]

I agree, but will add that you have designed this as you would a spreadsheet and that will continually present issues. Database tables should be 'tall' whereas spreadsheets are 'wide'. You might want to research 'normalization' with respect to database design. Not sure I agree with but it's your db. How would you like to realize you forgot something and rather than being able to add the comment, had to re-write everything just to add that comment? Would you eventually just say 'forget it'? I would.

I actually can create something for each employee to look up his/her own note too. It is a good suggestion, but it is irrelevant for my current questions.

[VAer]

No need for 3rd table. Instead have a crosstable query for last 7 dates [e.g. from Date()-6 to Date()] from EmployeeSelfEntryNote. When result table is displayed in form or report, use calculated unbound controls as headers to display wanted texts there.

What do you mean? I just create a few days as sample question, it will be dynamic, I will create something for employee to choose date range (begin date and end date).

[June7]

What is 'too much data'? Developers here speak of dbs with millions of records in Access that perform just fine. My largest db has 65,000+ plus records in largest table and took 20 years to get that far.

"How to write code" is a very broad question. You write code by learning programming concepts and a particular language vocabulary and syntax, determine the logic of what you want to accomplish and write algorithms to do it. You have requested us to write a lot of code for you. That's not why we are here. If you really want this design, you need to study and do research, attempt code and when you have specific issue post question. Or suggest you contract with a programmer.

You can set option to receive emails in your profile settings.

[VAer]

What is 'too much data'? Developers here speak of dbs with millions of records in Access that perform just fine. My largest db has 65,000+ plus records in largest table and took 20 years to get that far.

"How to write code" is a very broad question. You write code by learning programming concepts and a particular language vocabulary and syntax, determine the logic of what you want to accomplish and write algorithms to do it. You have requested us to write a lot of code for you. That's not why we are here. If you really want this design, you need to study and do research, attempt code and when you have specific issue post question. Or suggest you contract with a programmer.

You can set option to receive emails in your profile settings.

I already have the setting, but never received one single email notification, not yesterday, not today.

Based on the table EmployeeSelfEntry, if there are 3000 employees, then it could add 3000 records per day (if every employee submit note every day). Let me assume average 1000 new records per day, then it will be 365,000 records per year.

For coding, I ask one question for EmployeeSelfEntryNot and five questions for SummaryView in the original posts.

[ssanfu]

I have a dB where 1 table has ~1.5 million record over 5 years. All other tables are smaller - a few up to 6000 records.



I think it has been mentioned that your table designs are like an Excel spreadsheet.






I would have a main form based on tblEmployees, with a sub form based on tblSummeryView (to add notes).



My 2 cents...........

[VAer]

I have a dB where 1 table has ~1.5 million record over 5 years. All other tables are smaller - a few up to 6000 records.



I think it has been mentioned that your table designs are like an Excel spreadsheet.






I would have a main form based on tblEmployees, with a sub form based on tblSummeryView (to add notes).



My 2 cents...........

Thanks for the reply, I will spend time to think about your comment. But what do you mean Excel design? It is something not able to be done in Access?

[isladogs]

Agree with previous comments about the need to restructure your table design.

The only limit in the number of records is that the maximum size of an Access database is 2GB.
I have several tables of attendance data in my schools database which have around 2 million records and my UK postcode data has almost 3 million records (each with 44 fields).
Thankfully the postcode data is read only!

The actual limit depends on the amount of fields, the datatypes used and the size of data in each field.
In one of my speed test databases http://www.mendipdatasystems.co.uk/s...s-7/4594524997, I was able to add 30 million records to a table with only two fields before hitting the size limit.
If your database is likely to approach 2GB, you should be storing the data in SQL Server rather than Access

[VAer]

Agree with previous comments about the need to restructure your table design.

The only limit in the number of records is that the maximum size of an Access database is 2GB.
I have several tables of attendance data in my schools database which have around 2 million records and my UK postcode data has almost 3 million records (each with 44 fields).
Thankfully the postcode data is read only!

The actual limit depends on the amount of fields, the datatypes used and the size of data in each field.
In one of my speed test databases http://www.mendipdatasystems.co.uk/s...s-7/4594524997, I was able to add 30 million records to a table with only two fields before hitting the size limit.
If your database is likely to approach 2GB, you should be storing the data in SQL Server rather than Access

Forget the 2GB limit for each table size. As I know, for most computers in my workplace, if an Access file is more than 30MB, it will take a long time to open, Access could freeze.

I am working on Access file, not Access server. The whole point is: I am not IT folk, it is very bureaucratic to go through something. So I just need to use whatever resource I already have.

[ArviLaanemets]

2 GB is not for table - it is for database (all tables, indexing, relations, forms, code, reports etc.). When you have database split. then limit applies for both FE and BE separately.

When 30 MB file is slow or freezes, then you probably have some issues with DB design, or with your network.