Record Selection with Specific Ranges

[keefriffhard]

[Using CR v10 with MS Access 2003 Tables including a linked table to an ODBC source]

I'm having difficulty in selecting records that meet certain date parameters and conditions. Basically, I have a table of all sales order records for our newer sales personel since their hire date. I'm comparing that table to the linked ODBC table of sales order details, which includes all company sales orders.

I need to select records by salesperson, by month, by customer and by item (I'm all set with the group structure) with the following conditions:
1. All NEW items sold to a customer since the new salesperson's hire date, from the first date of shipment and reported on going forward up to a period of 1 year after that first date of shipment.
2. All items sold by the new salesperson, where a prior sale of the same item occurred to that customer, prior to two years or more from the date of the first shipment instance by the new salesperson. These, too are to be reported on for a period of 1 year after the new salesperson's first shipment.


I did some joins in CR after creating some min max date tables in Access, and came up with the following:
ACCESSTABLE1.FIELD
NEWHIRE.NAME
NEWHIRE.HIRE-DATE
NEWHIRE.CUST_NBR
NEWHIRE.ITEM_NBR
NEWHIRE.SHIP_DATE
NEWHIRE.SHIPDATE_PLUSTWOYRS
NEWHIRE.SHIPDATE_MINUSTWOYRS

ACCESSTABLE2.FIELD
NEWHIREQRY. MinOfSHIPDATE
NEWHIREQRY. MaxOfSHIPDATE
NEWHIREQRY. MaxOfSHIPDATE_PLUSTWOYRS
NEWHIREQRY. MinOfSHIPDATE_PLUSTWOYRS

ODBCTABLE.FIELD
ORDERS.CUST_NBR
ORDERS.ITEM_NBR
ORDERS.SHIP_DATE

ORDERS INNER JOIN NEWHIRE ON
ORDERS.CUST_NBR = NEWHIRE.CUST_NBR
ORDERS.ITEM_NBR = NEWHIRE.ITEM_NBR
AND SHIP_DATE <= NEWHIRE.SHIP_DATE
INNER JOIN NEWHIREQRY ON
NEWHIRE.CUST_NBR = NEWHIREQRY.CUST_NBR
NEWHIRE.ITEM_NBR = NEWHIREQRY.ITEM_NBR

Any suggestions as to the best method to obtain the specified records from Crystal? I'd appeciate any help.

 

[paulmarr]

Instead of linking the datasources together I would use a subreport.

I hope this helps

paulmarr

 

[Madawc]

If you need to link distinct records on the same table, add that table twice. The second add will be an 'alias' and will be treated as if it were a different table.

If you're using a link that may or may not exist, use a 'left outer' link. This won't work if you also do a selection on that table.

The main report sends one request for records to the server. A subreport sends a request each time that subreport is invoked, once per detail line if it is in that section. That's a good reason to avoid them if possible.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[keefriffhard]

Thanks. I will give the table alias a try. I'd like to stay away from subreports if possible, as the record count is in the hundreds of thousands, which I would suspect might take a bit of time.

Would this info be better obtained with a SQL or ACCESS query first, and if so, being a novice at query syntax, is would the query be rather simple?