Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Help on consolidating data

Posted on 2011-03-16
14
487 Views
Last Modified: 2013-11-29
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..



0
Comment
Question by:topgun0621
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35154618
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?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 35154627
You seem to have multiple questions here:
Look up values
Create Reports
...etc
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.

;-)

JeffCoachman


db339.mdb
0
 
LVL 3

Accepted Solution

by:
DockieBoy earned 250 total points
ID: 35154737
Hi there,

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

Correct.

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 :)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 3

Expert Comment

by:DockieBoy
ID: 35154743
Sorry Sudonim and Boag2000, slow fingers :)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35154783
No problem, ...the more, the merrier...

;-)

Jeff
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35155443
yeah no apology required.
0
 

Author Comment

by:topgun0621
ID: 35155545
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
0
 

Author Comment

by:topgun0621
ID: 35155615
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
0
 

Author Comment

by:topgun0621
ID: 35155639
Dockieboy;
Very well laid out explanation, I will check to make sure that I have set my tables up correctly
0
 

Author Comment

by:topgun0621
ID: 35158434
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
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35161151
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  :)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35161277
< 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...
;-)
0
 

Author Comment

by:topgun0621
ID: 35161941
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.
0
 

Author Comment

by:topgun0621
ID: 35161950
Going to try to work on this more, but most likely will run into trouble again, thanks for the help all
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question