Help please

J

John W. Vinson

I have two tables which are linked 1-to-many.

Is it possible to create a new table with just one record as in table 1
containing firstly the information from table 1 and then secondly the
information from up to 4 records from table 2 pertaining to the same record
from table 1?

Bob
Click to expand...

Well... yes... but why on Earth would you ever want to do so???

The new table would be drastically non-normalized.

What are you trying to *accomplish* in the real world?
 
B

Bob Matthews

I have two tables which are linked 1-to-many.

Is it possible to create a new table with just one record as in table 1
containing firstly the information from table 1 and then secondly the
information from up to 4 records from table 2 pertaining to the same record
from table 1?

Bob
 
J

John W. Vinson

The data I have is unfotunately in two tables:
Table 1: name, date died, place died etc.
Table 2: up to four records with the same ID as table 1
each record has a separate piece of additional information such as
occupation, where died etc.

I wish to combine into one new table
Click to expand...

OK... I guess...

Create a new Query joining Table1 to *four copies* of Table2, joining by ID;
if there might be ID's for which one or more records are missing from Table2,
use a Left Join ("show all records in table1 and matching records in table2").
Apply criteria to the Table2 records so that the first joined instance selects
only Occupation records; the second selects only Where died; etc.

If the table's contents don't allow you to do this unambiguously you've got
some real problems. In particular, if you're counting on the order of records
in the table to distinguish which kind of data is which, you are REALLY in
trouble.

I'm not sure this helps, because I'm not sure I understand the nature of the
tables. You have a "place died" in table1, and a "where died" in Table2. Are
they the "same"? do you want to replace one with the other?

Perhaps you could post the actual fieldnames in the format

Tablename
FirstFieldName <datatype>
SecondFieldName <datatype>
ThirdFieldName <datatype>

and perhaps some examples of the data.
 
B

Bob Matthews

The data I have is unfotunately in two tables:
Table 1: name, date died, place died etc.
Table 2: up to four records with the same ID as table 1
each record has a separate piece of additional information such as
occupation, where died etc.

I wish to combine into one new table
 
B

Bob Matthews

Table 1:
ID
Surname <text>
Christian<text>
Date Died<date>

Table 2:
ID
Notes<text>
Occupation<text>
Place Died<text>
Years<number>
Native<text>


New Table:
Surname <text>
Christian<text>
Date Died<date>
Notes<text>
Occupation<text>
Place Died<text>
Years<number>
Native<text>

Bob
 
J

John W. Vinson

Table 1:
ID
Surname <text>
Christian<text>
Date Died<date>

Table 2:
ID
Notes<text>
Occupation<text>
Place Died<text>
Years<number>
Native<text>


New Table:
Surname <text>
Christian<text>
Date Died<date>
Notes<text>
Occupation<text>
Place Died<text>
Years<number>
Native<text>
Click to expand...

Ok... I'm still confused. You say that Table 2 is in a one to many
relationship, and might have zero, one, two, or (for all I know) fifty
records, perhaps with the same, perhaps with different information. Someone
could well have had many occupations, though hopefully they only died in one
place <g>...

And you say you want the information from four records. How do you ascertain
WHICH four records? Do you want the Surname, Christian, Date Died etc.
repeated four times?

To get just ONE arbitrary record from Table2, just do a join. Create a query
with both tables, joined by ID; select all the fields; set the UNIQUE VALUES
property of the query to Yes. To select all the records from Table2, leave the
UNIQUE VALUES property unselected. You can then change this query to an append
query or a maketable query; I'd suggest creating the new table first, empty,
and using an Append query so you can have control over the datatypes and field
sizes.
 
E

Evi

John W. Vinson said:
OK... I guess...

Create a new Query joining Table1 to *four copies* of Table2, joining by ID;
if there might be ID's for which one or more records are missing from Table2,
use a Left Join ("show all records in table1 and matching records in table2").
Apply criteria to the Table2 records so that the first joined instance selects
only Occupation records; the second selects only Where died; etc.

If the table's contents don't allow you to do this unambiguously you've got
some real problems. In particular, if you're counting on the order of records
in the table to distinguish which kind of data is which, you are REALLY in
trouble.

I'm not sure this helps, because I'm not sure I understand the nature of the
tables. You have a "place died" in table1, and a "where died" in Table2. Are
they the "same"? do you want to replace one with the other?

Perhaps you could post the actual fieldnames in the format

Tablename
FirstFieldName <datatype>
SecondFieldName <datatype>
ThirdFieldName <datatype>

and perhaps some examples of the data.
Click to expand...

If necessary, and if Table 2 always shows its 1 to 4 records for each ID in
the same order (record 1 is always Occupation, Record 2 is always Place died
etc) could Bob add a field to Table2 and use recordset code to put a
sequential number in that field starting from 1 and going back to 1 when the
ID number changes? Then he could filter by this number field so that he
could add the 4 filtered queries based on Table 2 to his
Update/MakeTable/Append Query.

My guess is that Bob has inherited this mess and is trying to make some
sense of it before he creates a better version of it.

Evi
 
Top