>>>select * from things ;
>>> where thing in (select dist thing from thingdocs where doc=mydoc) ;
>>> into curs temp order by thing
>>>....depending on which one you want. You need only two parameters to the
>>>routine doing this select - one to choose things on doc or doc on
>>>things, another to supply mydoc or mything.
>
>>Sorry it took me so long to reply. They're making me program in Access.
>
>We may found a Fox Lib or any other movement to defend Fox programmers
>against this kind of torture. Does your boss have all of his chips
>neatly laid into their sockets, or there may be some badly connected
>ones?
>
>>How do I apply the above SELECT so that I have a report of all the things
>>for each document, or vice versa? Ed says I should use a cursor, not a
>>view, to avoid overhead. How does the mything or mydoc parameter get
>>incremented for each thing or doc in the report?
>
>I'm scratching my head for three minutes now... took me that long to get
>the idea. The select I wrote works for one doc, but for listing more of
>them we'd really have to select from thingdocs, ordered by doc,thing,
>and have relations into both docs and things, then report from it
>grouped on docs with things in detail band (or vice versa,
>search/replace doc--thing). I also avoid relations in data entry forms,
>but for reporting they come quite handy. The usual scenario for my
>reporting routine is to issue some short form for parameters entry
>(ask for date range and maybe some other stuff), do a SQL select based
>on these parameters, set relations from the resulting cursor into
>lookup tables and run the report. I close all tables after that, so
>none of the relations remains. In VFP all of this code may go into
>report's DE.init, but then I sometimes have the parameter entry form
>where it's possible to choose between several report layouts, various
>levels of subtotalling, order of the resulting cursor etc, so I set
>it all up in a routine which calls the form, runs the resulting SQL,
>and then it runs one of the reports.

Our office likes Access because our databases are small, so they might as well use it. I don't know enough about it yet to share your dislike. If they are willing to let me learn it on their time, it suits me fine.

Don't scratch your head too long, you'll make a bald spot :)

I just made this simple query:

SELECT docs.docnum, docs.field2, things.thingnum, things.field2...things.fieldN, things.orderfield, things.filterfield FROM docs,thingdocs,things WHERE docs.dockey = thingdocs.dockey AND thingdocs.thingkey = things.thingkey AND things.filterfield = gcFilterValue INTO CURSOR reportcursor ORDER BY docs.docnum, things.orderfield

and ran the report off it. Since all docs mention things, I don't need an outer join.