Forum Discussion
New to Access
Hello!
I am new to MS Access, and currently looking for some advice in working the program. In using Access, I am working with a team that has data in arts and crafts projects that fall under many different categories, which are being exported from Excel. I’ve attached an image of what this data looks like, but I’ve also explained it below.
- For instance, the data might be sorted into something like 5 fields, those being: project name, square inches, client name, and weight.
An example would be:
Project 1, 100 sq inches, Client A, 50 lbs
Project 2, 200 sq inches, Client B, 100 lbs
Project 3, 300 sq inches, Client C, 150 lbs
Etc.
- Then, we’d have a second collection of data that would branch off of the first set. For instance, the data might be sorted into something like 4 fields, being: brand new design, concept design, restoration, or simple cleaning.
- Finally, a third set of data would branch off of this information, detailing that each of the aforementioned categories have their own data. Such as, brand new design projects use X brand of glue, Y brand of paint, and are always Z color. Whereas, simple cleaning projects might use a totally different brand of glue, paint, and color.
Now, my question is, is it possible in MS Access to be presented with all of the data based on what I ask for? Such as, say, I want to view all projects that have used X brand of glue. Am I able to create a database that will pull that up? Or if I wanted to view all brands of paint used ONLY in cleaning projects? Essentially, is it possible to create a database that can show me any combination of information based on what I ask for, creating relationships with one another to search both forwards and backwards with simplicity?
If so, what research should I do to approach this, or what other direction do you suggest that I go in if it is not possible? I appreciate the help :)
1 Reply
The direct answer is "yes, that's how relational databases work."
But I suspect there's a deeper question behind that. You need advice on structuring your tables to make that work.
You'll need to star with learning about Database Normalization. That is the process of creating and relating tables to model your business. Here's an introductory article.
I like the YouTube videos here as well.
You need one table for each entity, or thing, you need to track. That will include Projects. The fields in that table will be the attributes of importance about each project. Name of the project, primary medium (textile, paper, wood, etc.), size, among others.
Another table will be Clients. The fields in that table will be the attributes needed to uniquely identify each client, e.g. first and last names.
Depending on the relationship between Projects and Clients, you might attach a Client attribute to the Projects table, or you might need a third table if two or more Clients can jointly purchase a project and you need to record both.
Another table will be Media. That's going to be a lookup table for the types of media your projects use, see above.Another will be additional materials. Another lookup, this one for things like glue and paint.
There will, no doubt be several other tables. I don't have enough information about the business you are modeling to know what they all would be.
Make sure you invest sufficient time and study to learn the proper, normalized table design and design a set of tables that you think you'll need. Share that with us. We'll help you refine it.
Once the tables are in place, querying against them for the kinds of information you specify will be very straightforward. But that comes only after the tables are done right.
