If you have all that information in the Artist table then why do you need it again in the Artwork table? You should be relating to the Artist table instead. You can put the related Artist fields on the Artwork layout.
The information is needed again in the Artwork record because it is the archive for all the Artwork that comes thru the Art Center so it needs to have the basic information about the artist.
The Artist record is created once for each artist and forms part of the documentation that is provided when the Artwork is sold.
mr_Vodka's question still stands - you can make all of the 'Artist' information appear in the Artwork file by using a relationship between the two tables. Do you simply want to achieve the effect that when you print the Artwork Record it displays all of the appropriate Artist's data too?
One advantage to setting it up this way is that any updates to artist contact information will automatically appear in all their matching artwork records.
I understand the suggestion of using a relationship between the two tables, what I am not sure of is if it will work for individual records, ie if i have create a record in the Artwork database for a painting 'X' by artist 'Y' can I use the relationship between the tables to add the rest of the fields from the record for Artist 'Y' i require in the Artwork record for painting 'X' ?
(sorry if that is confusing, I am trying to be as detailed as possible.)
I do not simply want to achieve the effect when printing, I want to add the information to the Artwork record for that specific item.
Step by step.
Let's assume a "best practice" that you have a serial number field in your artist table called ArtistID.
Create a matching field in your artworks table with the same name.
In Manage | Database | Relationships, drag from one ArtistID field to the other to create this relationship:
ArtistTable::ArtistID = ArtworksTable::ArtistID
Now, open a layout pointed at the Artworks table in layout setup... and use the Field tool to add fields from ArtistTable to your Artworks layout. As long as the artwork record contains an ArtistID number that matches a record in the AritistTable, the fields from ArtistTable you added to the layout will display data from that record.
Note: this is true for both a single file data base with two tables or a database of two files with one table in each file as long as you've got the correct data references and relationships set up.
Brilliant, thanks guys I really appreciate the speedy response to my queries, I have not used FM previously and its taking a little to get my head round it.
I am also constrained by an existing setup that seems not to be the best design but it already holds many years of data and the clients are comfortable with
how they are using it - which is very poorly IMO!
Its a steep learning curve but online support like this really helps.