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..

Who is Participating?
Hi there,

"making the weight the primary key would not be a good idea"


Use an auto number, I allmost always use an auto number as a primary key, that way you will always have a unique identification for every record.

What you are asking, from what I can understand, is certainly possible.  Sounds like you need a form to select a record (the cert id part) and then controls on the form to enter the data you wish to enter (description), then a report based on a query that will display the data that you wish to print.

Many ways to achieve this, for example;

Table that stores the data related to the cert id
Table that stores the different weights
Table that stores the different descriptions (I am assuming from the sounds of your question that the weights and descriptions are constants, as in there are pre defined weights, and pre defined descriptions)

Now, for your "we have weights that are the same, but descriptions that are different" part, look at it this way.... You only have one of each weight, but each weight could have different descriptions, One to Many relationship between the weights and descriptions tables.

Your weights table, lets say we call it tblWeights has two fields;
WeightsId - Primary Key - Auto Number
WeightValue - Number

Your Descriptions table, tblDescriptions has three fields;
DescriptionId - Primary Key - Auto Number
Desription - Text (or memo if you need more than 255 characters, unlikely)
WeightsId - Number - Indexed, duplicates Ok

You enter all your weights in tblWeights, and then enter your descriptions referencing the WeightsId for the corresponding weight in your tblDescriptions so that each description can then be linked to the appropriate weight.

Then you could have a form that will display the appropriate data when you select the cert id from a combo box and allow you to select a weight from a combo box and a description from a seperate combo box that would be filtered by the weight combo box in order to only allow users to select a description that is valid for that weight.

Once this is done, save the record and print a report based on a query that will display the data that you wish to see :)
Simon BallCommented:
can you provide a sample of the data file?

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?
Jeffrey CoachmanMIS LiasonCommented:
You seem to have multiple questions here:
Look up values
Create Reports
You are only allowed one distinct question per post here.
You also did not state your skill with Access, VBA, report/form design, ...etc

I will answer your Search and Report questions with a sample file.
In this file you can "look Up" the Order, then Print a report of that record, with certain fields.
You will have to modify my sample file to fit your needs.

This is enough for one question.
Sound fair?

Your other issues should really be dealt with in separate, subsequent questions.



Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sorry Sudonim and Boag2000, slow fingers :)
Jeffrey CoachmanMIS LiasonCommented:
No problem, ...the more, the merrier...


Simon BallCommented:
yeah no apology required.
topgun0621Author Commented:
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
topgun0621Author Commented:

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
topgun0621Author Commented:
Very well laid out explanation, I will check to make sure that I have set my tables up correctly
topgun0621Author Commented:
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  :)
Jeffrey CoachmanMIS LiasonCommented:
< 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...
topgun0621Author Commented:
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.
topgun0621Author Commented:
Going to try to work on this more, but most likely will run into trouble again, thanks for the help all
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.