You'll need to describe what you want in more detail. A "tagged field" in which table? Parent or Child? A page_full field in what table?
Best guess is that if you are dealing with this relationship:
Your report should be a list view layout based on the Child table with fields from Parent included in Header, Footer, grand summary or sub summary layout parts.
Go To related Records, a scripted find, Sorting the records can all be used to control which Child records will be included in the report and how this data is presented in that report. This process can be scripted.
Parent and child table ITEMS table OPTIONS table page_full (keyfield) page_full sku sku description description cost cost tagged tagged
The user goes thru the list and will "tag" those items and/or options that they would like listed in the report. This puts a value of "1" in the tagged field(s).
I will try your suggestion to base the report on the options table. One question: If a item is tagged but there are no matching options tagged, would the item still show up in the header?
Thank you, as always for your help.
I don't wee why you have sku, description and cost in both tables. Nor why tagged would be defined in the items table.
What is the nature of this relationship? Your last post appears to indicate that Page_full is the key field, but can you describe in detail how your relationship is supposed to work here? Is it truly a one to many from parent to child--which is the most common relationship when those terms are used to describe both tables? (And that is what I meant by my notation: Parent-----<Child.)
This is a database of furniture. Below is an example of one item and some of the options that go with it.
Table Page_full SKU Description Cost tagged ITEMS 23a ECA140BT Nightstand with drawer 400 Options 23a ECAORT reverse top on nightstand 0 Options 23a ECO2T Two-tone 29 Options 23a MEONL Nightlight 30
What is the purpose of Page_Full in this relationship?
What does a value of "1 in the tagged field" represent?
I would have expected a relationship that matched a SKU in Items to a new field, ParentSKU, in options or, if an option can be listed for more than one item, a join table would be used to link items and options in a many to many relationship.
And it is very possible that the records in items and options could be listed in a single common table with a self join relationship used to link items to options.
Page_full represents the catalog page where the item is found. Tagged field just reflects that that particular item or option should be added to the report. Perhaps I should rethink combining the tables into one table. That may solve my report issues. I will play with this suggestion today.
Does that mean that you have only one item on a page?
I strongly recommend defining an auto-entered serial number field in Items for uniquely identifying each record.That can then directly or indirectly link to options for that item. (link directly if every option can be linked to one and only one item, link indirectly via a join table if a given option can be linked to more than one item.)
I'd use a different field for the catalog page as that sounds like a value that could change from time to time.
Does the fact that you have a tag field in both items and options mean that you can select an item or options for that item to include in a given report?
(And please note that if these are temporary selection fields just for the 'report of the moment', two users tagging different records will end up with the records tagged by both users in many cases.)
Sorry for taking so long to get back. Day jobs can so get in the way. :)
I do have an auto-entered serial number. I should have included it in my list of fields. Thanks for the tip on the tag field. Although I don't anticipate having the users access the database over a network, I should still take that into consideration.
I did take your advice and combine the items and options tables. This version seems to be stable now.
Thanks again for the advice.
Oops, didn't mean to pick my own post as best answer.