Joining tables?

[PowerPoint Jedi]

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.

[Wayne-I-M]

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
--
Wayne
Manchester, England.

[PowerPoint Jedi]

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]

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.

--
Joseph Meehan

Dia 's Muire duit

[Wayne-I-M]

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]

On Thu, 1 Feb 2007 12:46:00 -0800, PowerPoint Jedi
<PowerPo...@discussions.microsoft.com> wrote:

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

[PowerPoint Jedi]

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.