Create schedule from two tables
I have database that tracks the production and sale of books. There are separate tables for authors, titles, contracts, production, print run details etc. All of it works OK.
From Production you can easily generate a schedule which shows what is due when. The schedule uses fields from about three tables (eg Title from BOOKS, Printer Month from PRINT RUN DETAILS, Status from PRODUCTION). All fine.
However, I would like to be able to add in some potential publications to this schedule, without adding real records to the main database. I can easily create a separate table with these possible publications. But I don't know how to connect them. All the records I want included will share the same publication year. What I can't quite work out is how to create a schedule that grabs information from both the Production (and related) files and this Potential file, based on the publication year field, and then sort and list them by Printer month to give me a schedule.
I'm using FM 10 Advanced on a Mac (though DB also used on Windows)
Thanks for any suggestions....