I need to felect records from a group,  Case example;

A Many to many relationship exists, multiple Parts (text string) with multiple Purchase orders (text string), I want to Append to the Part record  the latest (by date) PO record for each part The result would be a single record for each part in the set.  with the patest PO (if any). If there is no PO for the Part I wish to keep the Part record with a null in the PO field>

 

Thanks in advance for any help, I am in the learn as you go phase here

 

Table 1

 

Part number   |   Requisition Number    |    Order Qty   |    Place Date

12345                 10000                              10                    10/5/2007

12345                 10001                              15                    11/30/2007

67891                 10002                              100                  10/5/2007

67891                 10003                              150                  12/10/2007

 

Table 2

 

Part number   |   PO # and Line #         |   Vendor #  |  Unit Cost |    | Date

Table 3

 

Vendor Number    |    Vendor Name

01                              ACME Supply

02                              Electronic Dist.

03                              Aero Machine

04                             #1 Machine

 

I want to associate the latest data from Table 2 (Last PO for That Part # and last paid price) along with Vendor Name from Table 3 to each record in table 1. 

 

Currently My report will output each record in table 1 duplicated for every record in table 2 joining on the Primary key:  (Part number) an example of the outpt is: