I am a new user to Microsoft Access and thus I am a little fuzzy on database design. I currently have several Excel spreadsheets with info on chemical concentrations at different locations my company manages. Each of these spreadsheets is organized by location name and test date. I have a spreadsheet for each of the chemicals that needs to be tracked; 7 sheets currently with more to come in the future. These sheets essentially have a column of location names on the far left and a row of dates that chemical tests were taken on the very top. So each sheet looks something like this.
June-13 July-13 Aug-13 ...
Location 1
Location 2
Location 3
...
Each Location and date is repeated on each spreadsheets; the only difference between the 7 of them is the type of chemical they track. I have already transferred each of these spreadsheets into Access tables. I have also created relationships between the location name data by creating a location table. This allowed me to create a query & form to search through the data by location name. The problem is I am inexperienced with MS Access and am not sure how to rearrange my original tables to allow for Date and Chemical type search. Ideally I would like to set up my database to allow for a query and form to let others search through this data by location name, date, and chemical type. I understand the basics of query and form design but my data does not seem to be planned out in such a way to allow me to search by anything by location name. Can anybody suggest a way I can layout my tables or create new tables to help me do this? any help is appreciated as I am very new to MS access.
Thank you.
There are two major concepts you need to grapple with before you can really get started with MS Access. The first is where Access lies in the overall space of computing. The second is the difference between tabulated data and crosstabulated data.
MS Access lies at the intersection of three major aspects of information management. They are databases, applications, and documents. An Access file is a document. It is intended to be managed much like other kinds of documents, such as Word or Excel documents. It's also very easy to move data between different kinds of documents, such as spreadsheets and Access tables. This ease can be deceptive. It's so easy that you may think you have mastered information management when you are really only ankle deep.
An Access file contains an application. Most of the application design issues are simplified by the wizards that will do a lot of the tedious work for you, such as creating a form based on one or two tables, etc. You can get started with almost no background in application design.
An Access file contains a database. The data in the database is laid out into tables, and table rows are linked to each other by a foreign key/primary key relationship. It's a lot easier to get started with Access than it is with, say, SQL Server or Oracle. But, again, the ease of starting might conceal the existence of a discipline that can require some effort to learn. Good database design isn't complicated. But it is abstract. And unless you can think at the appropriate level of abstraction, you will repeatedly shoot yourself in the foot.
Within the limits of desktop processing, you can do what needs to be done in Access, and you can get started with a small fraction of the learning curve needed with other tools. But you have to be careful.
Switching gears, I want to alert you to the difference between cross tabulated data and tabulated data. The spreadsheet you showed us, with dates spread out across the top and locations spread out down the left side, is a classic example of cross tabulated data. Spreadsheets are built to work with data in this form. Relational databases are not. The other answers are pointing you to some good designs for tabulated data, but you will not benefit from those answers until you understand the difference.
In general, transforming cross tabulated data into tabulated data is easier done with the Excel pivot table tool than with the tools in Access. I suggest that you figure out a good system of relational tables for your data, and then use Excel to transform the data before loading it into Access. When you first learn pivot tables, you generally learn how to transform tabulated data into cross tabulated data. Going the other way is a little harder, but it's doable.
In this connection, please note that there are no wizards in Access that will help you transform a set of information requirements into a system of tables. You have to do that before you start in with the first wizard.
This is just the tip of the ice berg.