I thought I had it figured out, but soon realized I had no idea. I'm new to Access and having a lot of trouble figuring out the right layout for my database. My company tests metal samples. The attatched excel book shows the main categories as well as what information is in each category. The oxidation samples and alloy columns are both information about the metal sample. The other three are tests we run on each sample. Just so you understand, the sample comes from a tube, which comes from a lot, which comes from an ingot, which comes from an alloy. How would you guys go about laying out the database and what relationships should be in place? I'm sorry if this is confusing. Let me know if there is something I can better explain, or if I'm completely unclear. Thanks!
Database design
- Thread starter gmc5135
- Start date
MarkK
bit cruncher
- Local time
- Today, 14:48
- Joined
- Mar 17, 2004
- Messages
- 8,547
What are you trying to do? It's not clear to me from your spreadsheet what you want to keep track of. Is it how long a process takes? How much material gets consumed? Do you have an existing paper system? What outputs do you want your new system to produce?
G37Sam
Registered User.
- Local time
- Tomorrow, 01:48
- Joined
- Apr 23, 2008
- Messages
- 454
Aaah the good old days of Material Science and Alloys. Your structure should look this: View attachment Alloys.accdb
Let me know if I got it wrong
Let me know if I got it wrong
Your Excel file is quite clear so I created a squeleton DB to help you with the relationships.
Conventions:
1 - Table names correspond to their usage.
2 - Table names are plural or end with an extra 's'.
3 - Primary key is always autonumber, singular table name.
The attach file shows your DB. Note that the compression ring tests are stored in a single file; TestNo specifies the test number (1 or 2).
Give it a look and ask questions if necessary.
By the way, I worked 10 years for DomFer, manufacturer of metal powder for the automotive industry - ever tested Federal Moguls parts?
Good programming,
JLCantara.
Conventions:
1 - Table names correspond to their usage.
2 - Table names are plural or end with an extra 's'.
3 - Primary key is always autonumber, singular table name.
The attach file shows your DB. Note that the compression ring tests are stored in a single file; TestNo specifies the test number (1 or 2).
Give it a look and ask questions if necessary.
By the way, I worked 10 years for DomFer, manufacturer of metal powder for the automotive industry - ever tested Federal Moguls parts?
Good programming,
JLCantara.
Thanks a lot guys for helping me out.
lagbolt- We currently have thousands of metal samples laying around from as far back as the 70's. No information is recorded. I want to electronically store all the test and metallurgical information for each sample so we can basically throw it out, but still have useful information from it available.
G37Sam & JLCantara- It seems to me you guys have kind of the same idea going. Certainly not what I had, which is probably a good thing. I'm starting to get the gist of how to arrange the main topics. If we do need to use this information eventually, we will need all the other stuff as well (length, weight, wt gain, and all the other stuff I listed) to export to excel for graphs and tables. Do I have to make a table for each piece of information, or can I Make another table for each main topic, that list all of the information in that particular topic.
JLCantara- No, I don't think we have tested for Federal Mogul. We deal exclusively with the nuclear industry.
lagbolt- We currently have thousands of metal samples laying around from as far back as the 70's. No information is recorded. I want to electronically store all the test and metallurgical information for each sample so we can basically throw it out, but still have useful information from it available.
G37Sam & JLCantara- It seems to me you guys have kind of the same idea going. Certainly not what I had, which is probably a good thing. I'm starting to get the gist of how to arrange the main topics. If we do need to use this information eventually, we will need all the other stuff as well (length, weight, wt gain, and all the other stuff I listed) to export to excel for graphs and tables. Do I have to make a table for each piece of information, or can I Make another table for each main topic, that list all of the information in that particular topic.
JLCantara- No, I don't think we have tested for Federal Mogul. We deal exclusively with the nuclear industry.
Last edited:
Do not underestimate Access capability: I am using it since '97 and it's evolution has been surprising; in fact, anything available to VB is also available to Access. It's quite possible that you will not need Excel...
It's not clear to me what you really mean. Could you specify the meaning of topic and information?
Referring back to the excel sheet in the original post. The five topics being alloy, oxidation samples, high temperature oxidation, ring compression-1, and ring compression-2. To explain what I mean for information, Use the topic high temperature oxidation. Information being run #, target temp, target time, thermocouple 1, etc. The information is all data collected from the test basically. The database you provided does a great job at sorting the samples. How would you go about putting all the other information in the database so that when I find a sample, I can see all the information from each test. Hope that's clear.
OK I'm getting old and to formal; in the trade, topics are tables and informations are fields. Since your new to Access and most probably to DB design, I suggest you buy an Access xxxx book ($25 - $130): it is realy worth the investment.
I have attached a complete table for Alloys. If your not familiar with the fields update:
1 - right click the table name;
2 - Select desing view.
3 - Add fields or modify their attributes.
Allez, bonne programmation!!!
JLCantara.
I have attached a complete table for Alloys. If your not familiar with the fields update:
1 - right click the table name;
2 - Select desing view.
3 - Add fields or modify their attributes.
Allez, bonne programmation!!!
JLCantara.
