Solved

Help on consolidating data

Posted on 2011-03-16
14
481 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now