Help Creating Database of Resources Used for Specific Groups

[ChrisThompson]

I will preface this quesiton with A) I work in the K-12 education sector, and B) I have very limited knowledge of databases - BUT I do have a pretty strong knowledge of Microsoft Excel and some experience with Power Query. I know there is a lot of different resources regarding many of these products, but at some level I'm not sure which specific resources/training modules will serve me best for this particular issue. So I am basically requesting some insights on ways to potentially roll this project out, and secondly what trainings/resources will help me to learn how to do it.

Ultimate Goal:
We would like to create a database of various resources (i.e. Interventions) that schools in our state are using with students at varying levels of need (Tier 1; Tier 2; Tier 3), at various grade levels (K; 1st; 2nd;..12th), and by subject (Reading, Math). Ideally we would have schools provide us with this information on an annual basis so we can see who is using which intervention each year.

Typically, we receive questions along the lines of:

• "Which schools are using Read 180 in Math at the elementary school level?" OR
• "What are schools using for Reading at the elementary level for Tier 2 or Tier 3?"

Until now we have relied on what our staff have heard in conversations with schools, which is certainly not representative of all schools nor allows us to access the information across our staff ☹ In terms of the logistics of collecting this information, we would love to collect it annually, BUT it is NOT required of the schools to submit this information to us or the state. My initial thought is it would probably be ideal for each school to have its own profile so there is only one instance of information submitted each year (instead of multiple people providing conflicting information), BUT that is probably where my level of knowledge/expertise falls off haha. Alternatively, we could try to develop a form and manipulate that information to feed into a larger database using PowerQuery. The hard part is we want it to be easy to fill out for our schools, but also have the data be in a format where it can be combined and analyzed with ease My coworkers had some of our schools at a training recently fill out a table like this, which is easy to fill out, but not easy to use analyze at a larger scale. Any help on this would be greatly appreciated.

 

[Micron]

If you want guidance on an Excel based solution I have none. If by "database" you mean a relational database and intend to use Access then I have links I can post. I would not use Excel for such a project. Consider that true databases
- allow concurrent users
- can prevent data redundancy, enforce data integrity (e.g. text cannot be entered in a number field) and referential integrity between related records
- can control access to data based on user profiles
- are faster even when very large data sets are involved
- don't rely on links (which users can break) between data values (think of formulas or links that refer to other sheets/ranges)
- easily relate information from one table to another, whereas not so easy with sheets
- are not as limited as Excel in terms of the number of rows (records) that can be created
- have reporting features not found in Excel
- have much more powerful forms than Excel userforms
- etc.

I do have a pretty strong knowledge of Microsoft Excel

If you go the database route, that will actually be a detriment.

 

[ChrisThompson]

If you want guidance on an Excel based solution I have none. If by "database" you mean a relational database and intend to use Access then I have links I can post. I would not use Excel for such a project. Consider that true databases
- allow concurrent users
- can prevent data redundancy, enforce data integrity (e.g. text cannot be entered in a number field) and referential integrity between related records
- can control access to data based on user profiles
- are faster even when very large data sets are involved
- don't rely on links (which users can break) between data values (think of formulas or links that refer to other sheets/ranges)
- easily relate information from one table to another, whereas not so easy with sheets
- are not as limited as Excel in terms of the number of rows (records) that can be created
- have reporting features not found in Excel
- have much more powerful forms than Excel userforms
- etc.

If you go the database route, that will actually be a detriment.

Hi there. Thank you for reaching out. When you list out all of the positives with databases, it leads me to think that would probably be a better solution. HOWEVER, I know almost nothing about Access or where I would start to even go about creating something such as this. I'm assuming this would take a fair amount of knowledge of Access to develop this? Given that I have very limited knowledge of Access, can you point me to training tutorials and specific learning topics that would address the different knowledge areas for not only creating this type of database, but also how to manage it?

 

[Micron]

Access has a fairly steep learning curve if done well. Not so much if you just hack away with no idea of what you're doing. Here's the links. The first thing you MUST do is understand db normalization. Several of the other links will help you avoid common pitfalls.

A lot of developers start with large paper, pencil and a good eraser believe it or not, as it allows you to plan out everything from object names, data types you need and you can draw lines from one table field to another table and make sure you have all the relationships you need. When you work through scenarios as if people are using your db you can test your design in your mind. You could also research using terms that apply to your situation and find db examples to gain some ideas, but I'll bet some of those samples violate some the the design principles contained in the links.

As for user support, only the developer must have a full version of Access. Everyone else can use the free runtime version. I guess your first issue will be to decide whether or not you will go down this path, or if your school has a budget for hiring a developer, or if you'll end up using Excel in the end. As for the latter, I'd encourage you to research something like "databases versus spreadsheets" - and don't just take my word for it, because if you have to convince anyone to permit the use of resources to build a db, you'll want to be able to cite more than just me.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Naming conventions
- General: Commonly used naming conventions
- MS Access Naming Conventions

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields

Last/First/DLast/DFirst Explained
- ORDER BY - First, Last and always!

 

[alansidman]

In addition to Micron's suggestion of links, I have one more for you to look at. This is not in place of Micron's suggestion but in addition. His selections are spot on.

Access Basics

 

[Micron]

I might have seen that before where Crystal (?) is an active poster at Access World Forums (I used to be there also) but if I did, I forgot about it. I must add it to my collection - thanks!

 

[ChrisThompson]

These links are helpful, but in terms of teaching me how this type of project can be rolled out, I am wondering if anyone with some working knowledge of databases could help guide me in the creation of this? It is way easier to talk about concept development, and ask probing questions and what to avoid when developing the bones of the project, than it is to look through some canned tutorial that may not have all of the parameters for my specific project. Although, this project isn't the most straightforward application of a database, I would imagine it is pretty basic for someone who is fluent with databases.

 

[Micron]

You can probably build in stages and seek feedback and guidance as you go. I'd say that's about the extent of what a forum provides in terms of support. If you have a budget for this, you could contract it out. If not, there's fund raising if you don't have the time. You becoming experienced at Access can be a 2 edged sword. Might add to your job security, but then you'd become the go to person solutions, adding to your current load at (no doubt) no additional salary.

At this point it looks like you'd want to have these tables:

tblSchools
tblContacts (if a school can only have 1, could be in tblSchools. I would not)
tblTiers (this might only contain 3 records for Tier1 etc.)
tblLevels (or tblGrades. IMO, grades also connotates test results)

I have no idea what that stuff in row 6 is so I'm stuck beyond that.
What any forum responder would need would be a thorough synopsis that describes the process that the db needs to support. You'd want to start a new thread, methinks.

 

[ChrisThompson]

Honestly, I just need someone to bounce ideas off of. This is NOT rocket science. I am not trailblazing anything advanced, I just need help to get going and I'm saying the resources that have been shared are way too broad to be able to guide me in any direction. Guarantee a discussion on this would be like 10 minutes tops about what we are trying to do and then initial recommendation on how to go about it. Then I can learn what I need to learn.

 

[Micron]

This is NOT rocket science.

No, but it is surgery. I tried to put you on the right path, beginning with db normalization. Principles and concepts might be broad to you, but if you can't meld those concepts with the process you're trying to support then I have no other suggestions but I will wish you luck.