How to enter data

You have WeightID in the Description table, and DescriptionID in the Weight table.  Something is wrong here.  What are the objects you are dealing with here?  Whatever they are, does one of them have both a weight and a description?  In that case you can have both of those fields in the same table, or at least both ID fields.  Possibly they could be lookup fields with row sources of lookup tables.  When using lookup tables, you can add new items from the NotInList event.  Here is some sample code:

Private Sub cbo________NotInList(strNewData As String, intResponse As Integer)
'Set Limit to List to Yes
'Created by Helen Feddema 7-Apr-2010
'Last modified 7-Apr-2010
'See Add-to Combo Boxes (AA 161).mdb

On Error GoTo ErrorHandler
   
    Dim cbo As Access.ComboBox
    Dim dbs As DAO.Database
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName As String
    Dim strMsg As String
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strTable As String
    Dim strTitle As String
  
    'The name of the table that is the combo box's row source
    strTable = "________________"
  
    'The type of item to add to the table
    strEntry = "_____________"
  
    'The field in the lookup table in which the new entry is stored
    strFieldName = "_______________"
  
    'The add-to combo box
    Set cbo = Me.ActiveControl

    'Display a message box asking whether the user wants to add
    'a new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)

    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        cbo.Undo
        GoTo ErrorHandlerExit
    ElseIf intResult = vbYes Then
        'Add a new record to the lookup table.
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strTable)
        rst.AddNew
        rst(strFieldName) = strNewData
        rst.Update
        rst.Close
   
        'Continue without displaying default error message.
        intResponse = acDataErrAdded
     End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Select allOpen in new window

You have too many joins.  Remove the WeightID Join from the Relationships.  Then run this query:

SELECT DescriptionID.Description, WeightID.[Weight Value]
FROM WeightID INNER JOIN DescriptionID ON WeightID.DescriptionID = DescriptionID.DescriptionID;

BTW, it is not good practice to have a field with the same name as its table.  I suggest you use tblDescriptions and tblWeights as the two table names.

But you might need another table, with one record per the type of object you are dealing with.

Helen,
 thats how I had it first,only the one relationship, meaning weight value in one table and description in the other table.  Was not sure if that was right so I added the connection on both sides.  You are correct on the table look up.  That was my whole intent was that anytime I need to associate a weight and description, to do that via a table lookup.  I have test data...longwire test results lot, and crosswire test results lot.  Each time a test is performed certain information is entered into the test procedure...wire size,area,lot number(which is generated by the QC department to track the data), machine, and then the yield result and tensile result.  Once the lots of wire are drawn we then find out what products are going to be produced out of those lots.  This is where I wanted to be able to
have a table look up and specify what products were made out of this longwire lot, and crosswire lot.  Example, we made 40,000lbs of W3 lot 1, and used 10,000lbs of W2.5 cross wire from lot 2.   This made 10 rolls of 2231# rolls , and 10 rolls of 1972# rolls.  I did not want to have to type the description and weight in each time.

So basically, I need to just be able say in a certified report for our customers that this is one of the heat numbers used in that lot for the longwire with average of yield/tensile, and one of the heat numbers used in that lot for the crosswire with average of yield/tensile.  On the top of the report specify the product made using the description and weight.

Customer orders 10 rolls of 2231# 3X8 W3+W3 93" 1/2"+1/2" 500' STD(REG),
heat# for Longwire was 12345C.  Yield was 81.6 and tensile was 94.5.  
Heat# for Crosswire was 54321C and yiled was 78.9 and tensile was 99.6.

I do not know much about coding, but can create tables, queries, graphs easily.  We always did all the paperwork by hand.  Just trying to make it easier since the information is already going into access.  Just need to figure out how to tie things together.  It seems to me that just with a little redesign on the tables I should be able to do this.  

Are you saying that this is possible using a a table lookup for the weight and description?

 your help is greatly appreciated.  

Gray, good point on the table names, going to change that now.  Also going to try that query out as well.

Helen, if you think just one table with description and weight is fine I can make that way also. Just thought that anytime I need to throw a weight and description into a report i could do this by typing in a weight in a box and a description is returned in the box next to it.  One thing to keep in mind is that we have multiple weights that are the same weight, but the description is different.  This is why I thought I had to create the seperate tables.  I have no issue with typing in a weight and a  table pops open and lets me select which description I need if that helps.

I for got to add the sample data I have, I did redo the relationship.  I also have a query done for the averages of the yield results for both long wire and crosswire to give you a better idea of what I am working with. Expertexchange.mdb

Does the query I gave you answer the question - being able to enter data into both tables at one time without having to worry about updating the ID's?  It seems to me you have expanded the scope somewhat.  

I see little value in having a tblDescription linked to a two field table tblWeight.  Why not just include the Weight field in tblDescription?  In addition,what is the relation between these two tables and the two additional tables tblLongwire and tblCrosswire?  

yes expanded some only becuase i was trying to relay what and where the information has to go. As I mentioned there are multiple weights that are the same but the description is different. I need to be able to choose the description that goes with the particular lot of wire that was manufactured.If there is a easier way I am open to suggestions.

As far as your query I tried it, but am confused on the statement of: FROM WeightID INNER JOIN DescriptionID ON WeightID.DescriptionID = DescriptionID.DescriptionID.

I created the first statement you suggested but the INNER JOIN I am not familure with.  Expertexchange.mdb

we are constantly retyping or hand writing the description and weight on multiple forms which is time consuming and many typo errors as welll. just trying to make it so we can search for a weight in a box or field, and a description returned in box or field next to the weight that was typed in.

The query I gave you is a single statement on two lines.  It is just a way of joining two tables so that the selected fields appear in the resulting data set as if they were in one table.  You should be using a form to enter your data.  I still do not see the relationship between Description, Weight, LongWire, and CrossWire.  I imagine a given combination of Description and Weight result in a Lot - something manufactured?  the LongWire and CrossWire tables appear to be the result of a series of tests.  We need a better description of 'the process'.

Step 1:we manufacture the wire from raw materials.(this is the rod you see in the table, .563,.422 ect..)
Step 2: this rod becomes wire that we test wire and that data is stored in access.(close to 100 tests performed each shift sometimes more)  Currently there is no lot number associated with what is being tested.  I was going to add that field in the test procedure.
Step 3: a lot of longwire is put into a machine and a lot of crosswire is welded on top of it.  This now is a completed manufactured product for our company.  The product is stored outside in our inventory untill a customer orders the product.  We made 10 rolls last month and 10 rolls this month.  The weight and description is the same but the tests results are not the same and the heat numbers associated with the wire tested are different.  Reason for the lot identification.
Step 4: once shipped we are required to provide a certified report.  This report has 3 pieces of data on it. 1)-.Size of Longwire with a heat number and a yield result and tensile result  2)-Size of Crosswire with a heat number and a yield result and tensile result.  3)-Description and weight of product associated with the heat number and test results provided on the report.

We simple look on the tag and see when the product was manufactured, weight and description, and grab the correct certified report to fax to the company.  Problem is we do all this by hand.  Tests are wrote on tests reports, then we pick one random test result and heat number for each Longwire and Crosswire from the wire test report, and hand write onto a certified report.   Then we put the weight and descriptions of the products on the report as well.  This then goes upfront to be retyped into a nice certified report.

very much appreiciated thanks ray

Rich