Joining tables?

[Guest]

Afternoon,
I currently have 2 tables.
1 table is a list of failures and dates of certain part numbers, the other
is a list of names for those part numbers. Is there any way to combime the
tables and create another column that has the correct Name for each part
number?

Notes: I am using access2003 and am still fairly new to using it.

 

[Guest]

Hi

I personaly would not do that - you may have a part that has more than one
failure and you would then need to duplicate the part details to cope with
the new failure.

The best thing would be to link the table so that you could add as many
failures and details of the failures to each part.

Each Part should have a unique identifer (normaly an autonumber - but it
could be the part number if they are "nerver" duplicated. Add a field to the
table containing the failures / dates and other details of the failure and
ensure it is the same Datatype of the unique identifer in the parts table.

Open the relation window and drag the PartID (or number) accross to the
failures table. This will create the link.

You can now - for example - create a form in single form view with the
details of each part and on this form include a subform (maybe datasheet or
continus) that would show all the failures of that specific part, etc.

Hope this helps

 

[Guest]

Maybe combining the tables was the wrong way to put it. I want to know if
there is an easy way to fill in the spoken part name next to the numeric part
number. As there are over 200,000 entries in the table already I was hoping
there was an easy way to do this since a manual cut/0paste or drag method is
quite time consuming. Below is an example of what I am talking about.(I
inherited this along with a bunch of good ideas that never got taken care of)

Table 1
Part name Part Number
Display 0001
Outer case 0002
Inner case 0003
cable 0004

Table 2
Part number Failure date
0001 fail 02 oct
0003 fail 02 oct
0001 fail 5 oct
0004 fail 6 oct

What I want
Part name part number failure date
Display 0001 fail 2 0ct
inner case 0003 fail 2 oct
diplay 0001 fail 5 oct
cables 0004 fail 6 oct

basically is there any way to get from table 1 and 2 to table 3?

 

[Joseph Meehan]

Afternoon,
I currently have 2 tables.
1 table is a list of failures and dates of certain part numbers, the
other is a list of names for those part numbers. Is there any way to
combime the tables and create another column that has the correct
Name for each part number?

Notes: I am using access2003 and am still fairly new to using it.

If I understand correctly,

Form a 1 to many relationship on the part number. You can then display
the results in a query, form or report.

 

[Guest]

Could could create a query and this would very simply show the details you
need.


Link the Part Number from both tables and bring into the query from table1
Part Name and Part Number also bring in from Table2 Failure and Date.

 

[John Vinson]

What I want
Part name part number failure date
Display 0001 fail 2 0ct
inner case 0003 fail 2 oct
diplay 0001 fail 5 oct
cables 0004 fail 6 oct

basically is there any way to get from table 1 and 2 to table 3?

Table3 does not need to - and SHOULD NOT - exist.

You're using a relational database, and this kind of joining is
exactly what it's designed to do!

Create a new Query in the query window.

Add Table1 and Table2 to the window.

If these tables are (as they should be!) related in the Relationships
window, Access will automatically put a join line between the [Part
Number] fields in the two tables. If it doesn't, drag [Part Number]
from Table1 to [Part Number] in Table2.

Select whichever fields you want to see by doubleclicking them, or
selecting them en masse with shift-click and dragging them to the
query grid.

This Query will combine the fields from both tables, and may be used
as the recordsource for a Form or Report, exported, etc.

John W. Vinson[MVP]

 

[Guest]

Thanks worked. Sad thing is I tried it but couldn't figure out how to join
the tables. After reading the post I have updated my origional table to
contain the correct part names with the part numbers.

What I want
Part name part number failure date
Display 0001 fail 2 0ct
inner case 0003 fail 2 oct
diplay 0001 fail 5 oct
cables 0004 fail 6 oct

basically is there any way to get from table 1 and 2 to table 3?

Table3 does not need to - and SHOULD NOT - exist.

You're using a relational database, and this kind of joining is
exactly what it's designed to do!

Create a new Query in the query window.

Add Table1 and Table2 to the window.

If these tables are (as they should be!) related in the Relationships
window, Access will automatically put a join line between the [Part
Number] fields in the two tables. If it doesn't, drag [Part Number]
from Table1 to [Part Number] in Table2.

Select whichever fields you want to see by doubleclicking them, or
selecting them en masse with shift-click and dragging them to the
query grid.

This Query will combine the fields from both tables, and may be used
as the recordsource for a Form or Report, exported, etc.

John W. Vinson[MVP]