Combining records to make new table

[skissh]

I will receive a single table, with no relationships, from a coworker containing data that will not be changed. The table will be used to create report sheets that need to be filled out by hand. Currently each sheet will require information from 2 records in the table but it is not a summary. I don't know if this can be done. I thought that if I could recreate the table by combining those two records into one, I could run a "normal" report, 1 record generates 1 page.
How can I automate making this new table with the extra fields. The records I want to combine have corresponding fields. Specifically the record with "Tag Name" field equal to YY12345 has a corresponding record YV12345 that has the fields that want to now include in record YY12345. Any suggestions?

 

[wazz]

possible (?):
- create a new query.
- put your table into the design view twice. (the second table will have '_1' after the name).
- connect the two matching fields by dragging the "Tag Name" field to the matching field in the 2nd table. a line should show up indicating a link.
- select the fields from both/either table(s) you want to display by dbl-clicking them.
- view the results.
- you can work from this query or create a new table from this query.

 

[MagicMan]

Wazz that should work, but depending on the records, it may create 2 identical records for each tag. "IF" there are 2 distinct record types, it would be better to build two "make table queries"...one for each record type, then use an inner join on the report to connect the 2 new tables using the unique (is the tag unique) tag.

 

[skissh]

Thanks to the suggestions of Wazz and MagicMan I implemented a solution that worked. Here is what I did.

1. Added these fields to original table.
Valve Manuf (field 1 I needed from another record)
Valve Model (field 2 I needed from another record)
ValveID (foreign key from the duplicate table)
2. Ran an update query that filled in origtable.ValveID with ID of 2nd record I needed.
3. Ran a make table query to make duptable containing just the 2nd records I needed.
4. Created relationship by making ValveID a foreign key in origtable from duptable (ValveID=Tag Name)4. Ran a update query to fill in origtable.Valve Manuf and Model (feilds I needed from the 2nd record)
5. Deleted duptable and field origtable.ValveID


Thanks Dudes from skissh of California

 

[MagicMan]

Excellent, glad you got it to work.
Smiles
Bob
Sometimes just hearing ideas wakes up dead areas of my brain that should have figured out the answer in the first place.
Smiles