Query to display first record only

[NXMold]

I list purchase orders in a subform linked by job number. The list displays items from tblPOHeader.

A second table tblPOItems contains line items, a single purchase order can contain line items for many jobs.

I am writing a query for the sub form so that the header fields are repeated for each job number.

If I have PO# 1001 with associated line items referencing job number 12800, 12801, and 12802 there should be three lines in my query. However, if I have PO# 1002 with line items referencing job number 12800 and 12800... I want a single result.

I know there is some method using Count() but I cannot find any information about it.

SELECT tblPOItems.[CMEPO#], tblPOItems.CMEJobNumber, tblPurchaseOrders.Supplier, tblPurchaseOrders.OrderDate, tblPurchaseOrders.OrderBy
FROM tblPOItems LEFT JOIN tblPurchaseOrders ON tblPOItems.[CMEPO#] = tblPurchaseOrders.[CMEPO#];

 

[NXMold]

Uhhh.. maybe I should call it a day. Seems the below query does exactly what I want, the only problem being I have to add more code to insert a new record.




SELECT tblPOItems.[CMEPO#], tblPOItems.CMEJobNumber, tblPurchaseOrders.Supplier, tblPurchaseOrders.OrderDate, tblPurchaseOrders.OrderBy
FROM tblPOItems INNER JOIN tblPurchaseOrders ON tblPOItems.[CMEPO#] = tblPurchaseOrders.[CMEPO#]
GROUP BY tblPOItems.[CMEPO#], tblPOItems.CMEJobNumber, tblPurchaseOrders.Supplier, tblPurchaseOrders.OrderDate, tblPurchaseOrders.OrderBy;

 

[lespaul]

sounds like you need a correlated sub query referencing the distinct info and then add in the additional information....it would be easier if you would post sample data and expected results....so maybe some records from tblPOHeader and tlbPOItems and what you want the results of the query to look like based on those sample records.....

Leslie

Come join me at New Mexico Linux Fest!