topgun0621
asked on
Help on consolidating data
I have a table of data with the information coming from our testing equipment. The data in this file is made up. What I need is a way to look up the information by Cert ID and be able to generate a report or form only showing Heat Number, Avg. of Yield, Avg. of Tensile, and also a box stating the bend test was formed, and welded tensile test was performed.
Example, search for Cert ID 10, and in a report/form the correct information display so that I can print it.
Also, I need to put a description with this certified, so I have a table with descriptions. Is it possible to have a feature in access that lets me have a lookup feature by weight that opens with a command buttton, and where I can scroll to the weight that I need and the description inserted on to the report. We have weights that are the same, but the descriptions are different. So making the weight the primary key would not be a good choice.
Any help appreciated..
Example, search for Cert ID 10, and in a report/form the correct information display so that I can print it.
Also, I need to put a description with this certified, so I have a table with descriptions. Is it possible to have a feature in access that lets me have a lookup feature by weight that opens with a command buttton, and where I can scroll to the weight that I need and the description inserted on to the report. We have weights that are the same, but the descriptions are different. So making the weight the primary key would not be a good choice.
Any help appreciated..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry Sudonim and Boag2000, slow fingers :)
No problem, ...the more, the merrier...
;-)
Jeff
;-)
Jeff
yeah no apology required.
ASKER
Sudonim
Here is the sample file. The tests will go to 2 tables: the test result table, and the weld shear table. We do not have to use data in the weld shear table, just the test result table. Industry specs specify that weld shear has to be performed but not reported. That is the reason for a check box idea. Some companies need the actual values but very seldom and something that we can look up easily enough.
I did a query already using averages of the yield and tensile data. We only have to report a value for cross wire and a value for longwire and provide a heat number as well. We do all this by hand on a excel speadsheet. I grouped the results by heat number as well.
Biggest problem is we draw the wire on wire drawers, and a couple days later we get the actual orders or product description to set the welders up and weld into rolls/sheets. Most of the time its for multiple descriptions such as 10 rolls@1000#(3X6 W3+W3 70" 1/2"+1/2") 500' , 15@of 2500#(3X12 W3+W2.5 82" 0"+0" 400'), 5@2210#(3X8 W3+W4 94" 1/2"+1/2" 600'). The first "W" in the description is the long wire, the second "W" is the Cross wire. These are what need to be certified using a heat number and average of yield, and average of tensile.
As far as users, those in the QC lab and people upfront need access to the database. We are set up on a server. Tests are performed all the time, but the data automatically goes into the database immediatley afterperforming the test.
Anymore questions just hollar. Thanks for your help as well. QCDataBase.accdb
Here is the sample file. The tests will go to 2 tables: the test result table, and the weld shear table. We do not have to use data in the weld shear table, just the test result table. Industry specs specify that weld shear has to be performed but not reported. That is the reason for a check box idea. Some companies need the actual values but very seldom and something that we can look up easily enough.
I did a query already using averages of the yield and tensile data. We only have to report a value for cross wire and a value for longwire and provide a heat number as well. We do all this by hand on a excel speadsheet. I grouped the results by heat number as well.
Biggest problem is we draw the wire on wire drawers, and a couple days later we get the actual orders or product description to set the welders up and weld into rolls/sheets. Most of the time its for multiple descriptions such as 10 rolls@1000#(3X6 W3+W3 70" 1/2"+1/2") 500' , 15@of 2500#(3X12 W3+W2.5 82" 0"+0" 400'), 5@2210#(3X8 W3+W4 94" 1/2"+1/2" 600'). The first "W" in the description is the long wire, the second "W" is the Cross wire. These are what need to be certified using a heat number and average of yield, and average of tensile.
As far as users, those in the QC lab and people upfront need access to the database. We are set up on a server. Tests are performed all the time, but the data automatically goes into the database immediatley afterperforming the test.
Anymore questions just hollar. Thanks for your help as well. QCDataBase.accdb
ASKER
Boaq2000,
Your right I did post multiple questions and apologize for that. Noted for future help.
My skill in Access is that I have been using it for 10 years and can make tables, even run queries, and charts with out too much trouble. Even running calculations in those queries I have done. Its putting the information together that I am having an issue with as we always have done everything by hand.
We write the wire reports by hand, weld shear reports by hand, then pull 3 pieces of data off the wire report and hand write onto a certified report and send up front so that it can be retyped into a nice certified report. Since all the data already goes into Access I am trying to make this work and make everyone's job easier.
A couple of years ago I did take the begginers and intermediate Access course in my local community college. But those classes were always using templates to teach from, nothing really from scratch. I am going to take the next Access class this summer if it is available as I am finding out there is alot I do not know.
Excellent file you sent me as an example, that maybe exactly what I need to get me going.
Thanks for your help
Your right I did post multiple questions and apologize for that. Noted for future help.
My skill in Access is that I have been using it for 10 years and can make tables, even run queries, and charts with out too much trouble. Even running calculations in those queries I have done. Its putting the information together that I am having an issue with as we always have done everything by hand.
We write the wire reports by hand, weld shear reports by hand, then pull 3 pieces of data off the wire report and hand write onto a certified report and send up front so that it can be retyped into a nice certified report. Since all the data already goes into Access I am trying to make this work and make everyone's job easier.
A couple of years ago I did take the begginers and intermediate Access course in my local community college. But those classes were always using templates to teach from, nothing really from scratch. I am going to take the next Access class this summer if it is available as I am finding out there is alot I do not know.
Excellent file you sent me as an example, that maybe exactly what I need to get me going.
Thanks for your help
ASKER
Dockieboy;
Very well laid out explanation, I will check to make sure that I have set my tables up correctly
Very well laid out explanation, I will check to make sure that I have set my tables up correctly
ASKER
Dockieboy:
I created the tables like you mentioned, weights and descriptions. I should see the description when clicking on the + symbol in the weights table right? I do not, unless I did something wrong, here is the file with the tables added if you could spot where I may have erred? QCDataBase.accdb
I created the tables like you mentioned, weights and descriptions. I should see the description when clicking on the + symbol in the weights table right? I do not, unless I did something wrong, here is the file with the tables added if you could spot where I may have erred? QCDataBase.accdb
Sorry topgun, I only use access 2003, if you can uplolad as a .mdb I would be happy to take a look, or someone with 2007 can take a look and help you out :)
< I should see the description when clicking on the + symbol in the weights table right? I do not, unless I did something wrong,>
You did not enter the corresponding WeightIDs in the description table...
;-)
You did not enter the corresponding WeightIDs in the description table...
;-)
ASKER
Yep, that worked boaq2000. I guess I didnt realize that. I was thinking that column was for the weights not the weightID. Add that I am not that profiecent at relationships either.
ASKER
Going to try to work on this more, but most likely will run into trouble again, thanks for the help all
Almost anything is possible in access within reason :)
you need a table of weights with its own weight id, and the description.
then its easy to have listboxes on a form where you select weight, and then a 2nd list box displays the descriptions for those weights... which you can finally select.
Will you store the data in access too? how often will you import the file? is this a one off only you will use, or will it become an application that you will pass on to other users?