Re: Tricky ACE report problem

Kate Juliff writes: -> ->I have a table that amongth other columns has -> ->order_no line_item_no due_date -> ->and I've been asked to do a report with the following layout -> ->For the 7 days prior to today -> ->date1 date2 date3 ... date7 -> ->order_no, line order_no, line order_no, line -> ->such that there are 7 columns, with the date headings and below each ->date a list of all items that fall due on that date. -> ->I can't see an easy way of doing this. I have ISQL. -> ->Can any one help????? Kate, You can do kind of what you want, but I wouldn't call it pretty or elegant. You would need to start be doing a retrieval into a temp table for each of your seven days: select order_no order_no1, line_item_no line_item1 from your_table where due_date = (today - 1) into temp a1 with no log; ... select order_no order_no7, line_item_no line_item7 from your_table where due_date = (today - 7) into temp a7 with no log; The hard part is to now find a way to join these seven tables so that each row of your final select contains a single order_no, line_no from each of the seven dates. You could create a table a0 containing all join possibilities from all tables like: select order_no_all, line_item_all from your_table where due_date >= (today - 7) into temp a0 with no log; select order_no1, line_item1, ..., order_no7, line_item7, order_no_all, line_item_all from a0, outer a1, outer a2, outer a3, outer a4, outer a5, outer a6, outer a7 where a0.order_no_all = a1.order_no1 and a0.line_item_all = a1.line_item1 and ... a0.order_no_all = a7.order_no1 and a0.line_item_all = a7.line_item1 order by Then in the format section, the header section should list your seven dates across the top, and the on every row section will then list order and line numbers 1 through 7. Be aware that the way I have outlined this though, you will have a lot of blanks in between your data which is obviously not what you intended. My personal wish for these types of problems is a way to assign sequential numbers (1..n) to each line of the temp tables. Then you could use these sequential numbers as keys for the joins. Or maybe some clever person does know how to do this which could then make this solution work for you. If someone does know how to do this, I would like to hear from you! Regards, - Cathy -------------------------------------------------------------------------------- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (970) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (970) 491-1205