Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Need help building a form that allows for several rows of entries per column (Excel style)

Copper Contributor

I am creating a database through Access for employee end of shift reports. Currently these reports are not being stored in a database and instead are filled out on an Excel file. The Excel format works well for these specific reports. Employees enter multiple rows of data with column values such as Task Completed, Time Completed, Room #, etc. 

Being new to Access, I am having issues recreating this kind of format in Excel. I don't know how to allow an employee to enter multiple times in one field (column) under one form. I have learned about the multivalue field option, however many of the items employees must enter are free-response short text, not Lookupwizard drop down menus. Secondly, these multiple values for each field must be associated with other fields that need multiple values (like the row function in Excel, each task has a time and room # associated with it and there are multiple tasks). Is there an eloquent solution to this problem I am having? It has been hard to research the answer on my own because I don't know how to ask the question. 

2 Replies

@Emilyharp The "elegant solution" to which you refer is, in fact, the process of creating a relational database application. You will need to invest some time learning about the process of Normalization, which lays out the guidelines for designing the various tables needed.


As you've already learned, Excel and Access are two very different things. Unfortunately, much of what we come to rely on in Excel is flat out contrary to good relational database application design. 

Start with a search for database normalization. Learn about it. Develop the tables you need. The forms will come later.  

And PLEASE do NOT fall for the trap of Multi-Value fields in tables. That way there be dragons.