Stephen Russell wrote: > On Tue, Jan 20, 2009 at 10:44 AM, Ricardo Aráoz <ricar...@gmail.com> wrote: > >> Hi all, >> had an unusual requirement regarding some sql statements and I >> wanted to share what I've learned with the list, it might be useful to >> someone. >> >> We have 3 tables, say T1, T2, T3. >> T1 has a one to many relationship to T2 and to T3. >> The customer wants to list the tables as follows. >> >> Say a record in T1 can be linked to 2 records in T2 and 5 records in T3. >> Then he would want : >> (record #s in parenthesis)(Fn = Field # n) >> >> T1.F1(1) T1.F2(1) T1.F3(1) T1.F4(1) T2.F1(1) T2.F2(1) >> T2F3(1) T3.F1(1) T3.F2(1) T3.F3(1) >> T1.F1(1) T1.F2(1) T2.F1(2) >> T2.F2(2) T2F3(2) T3.F1(2) T3.F2(2) T3.F3(2) >> T1.F1(1) T1.F2(1) >> > -------------------------------------------------------- > > If you had used XML we would understand this outcome. > LOL! Don't beat the horse. You convinced me on the uses of XML.
> Bad Steve! > > I am so lost with this description, what I see above is it 3 rows, > using T1.F1(1) as the primary indicator ? > That would be the header of the purchase orders. I can see it got harder to read because the lines were cut by the mail server. The user wants the header of the purchase order (T1) displayed in every line, then the figures and totals of the purchase order only in the first line, whilst the payments and invoices go one in every line (in two columns). I'll try and make it shorter, below when you see T3F2(4) it means table 3 field 2 recordno 4 (it is an example after all, in real life recordnos would be sparser) T1F1(1) T1F2(1) T1F3(1) T1F4(1) T2F1(1) T2F2(1) T2F3(1) T3F1(1) T3F2(1) T3F3(1) T1F1(1) T1F2(1) T2F1(2) T2F2(2) T2F3(2) T3F1(2) T3F2(2) T3F3(2) T1F1(1) T1F2(1) T3F1(3) T3F2(3) T3F3(3) T1F1(1) T1F2(1) T3F1(4) T3F2(4) T3F3(4) T1F1(1) T1F2(1) T3F1(5) T3F2(5) T3F3(5) T1F1(2) T1F2(2) T1F3(2) T1F4(2) T2F1(3) T2F2(3) T2F3(3) T3F1(6) T3F2(6) T3F3(6) T1F1(2) T1F2(2) T2F1(4) T2F2(4) T2F3(4) T3F1(7) T3F2(7) T3F3(7) T1F1(2) T1F2(2) T3F1(8) T3F2(8) T3F3(8) T1F1(3) T1F2(3) T1F3(3) T1F4(3) T2F1(5) T2F2(5) T2F3(5) T3F1(9) T3F2(9) T3F3(9) T1F1(3) T1F2(3) T2F1(6) T2F2(6) T2F3(6) T3F1(10)T3F2(10)T3F3(10) T1F1(3) T1F2(3) T2F1(7) T2F2(7) T2F3(7) T1F1(3) T1F2(3) T2F1(8) T2F2(8) T2F3(8) > It looked good to see all that SQL code. > > Are you doing that in VFP or running a SP to crank that out? > > I've coded a data driven app, in which you define user entry vars (default values, validation, type, mask, even a query for a lookup table), your queries (SQLServer, Progress, Access, and finally to glue them all VFP6), the name of the report, etc. The app will add your report to the menu where you have defined it to be, if you select the report an input window will appear asking the user for the filter vars, and then it will fire the different queries. The result will go to an Excel worksheet in one or many sheets (whatever you have specified and with the names and lengths you entered in the definition tables). The definition tables are in a VFP database. --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/497673db.9050...@gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
