needing query assistance

[John]

I have a query that needs to only show one of each supplier name. Currently
the sql statement (below) shows each supplier multiple times one for each
purchase order. Only want to show the supplier one time.

SELECT First(Suppliers.Name) AS FirstOfName, Suppliers.Address1,
Suppliers.Address2, Suppliers.City, Suppliers.State, Suppliers.Zip,
Suppliers.IsAClient, Suppliers.Status, Suppliers.Phone1,
Suppliers.ApprovalStatus, PurchaseOrders.Date
FROM Suppliers INNER JOIN PurchaseOrders ON Suppliers.ID =
PurchaseOrders.SupplierID
GROUP BY Suppliers.Address1, Suppliers.Address2, Suppliers.City,
Suppliers.State, Suppliers.Zip, Suppliers.IsAClient, Suppliers.Status,
Suppliers.Phone1, Suppliers.ApprovalStatus, PurchaseOrders.Date
HAVING (((Suppliers.IsAClient)=No) AND ((PurchaseOrders.Date) Between
#1/1/2008# And #1/31/2009#))
ORDER BY First(Suppliers.Name);

Any assistance is appreaciated.
.... John

 

[KARL DEWEY]

If you are going to show multiple PurchaseOrders.Date for a supplier then the
name will show for every PurchaseOrders.Date.

In a report you can set Hide Duplicates to Yes. Maybe that is what you need.

 

[John W. Vinson]

I have a query that needs to only show one of each supplier name. Currently
the sql statement (below) shows each supplier multiple times one for each
purchase order. Only want to show the supplier one time.

If you don't need to see the date field (which will presumably change for each
PO), change its Group By function on the Totals row to Where. This will
suppress its display and not group by it. The SQL would be

SELECT First(Suppliers.Name) AS FirstOfName, Suppliers.Address1,
Suppliers.Address2, Suppliers.City, Suppliers.State, Suppliers.Zip,
Suppliers.IsAClient, Suppliers.Status, Suppliers.Phone1,
Suppliers.ApprovalStatus
FROM Suppliers INNER JOIN PurchaseOrders ON Suppliers.ID =
PurchaseOrders.SupplierID
GROUP BY Suppliers.Address1, Suppliers.Address2, Suppliers.City,
Suppliers.State, Suppliers.Zip, Suppliers.IsAClient, Suppliers.Status,
Suppliers.Phone1, Suppliers.ApprovalStatus
WHERE (((Suppliers.IsAClient)=No) AND ((PurchaseOrders.Date) Between
#1/1/2008# And #1/31/2009#))
ORDER BY First(Suppliers.Name);

In addition, the WHERE clause will be applied *before* the grouping and
averaging and so on (in more complex totals queries), rather than retrieving
all records, doing calculations, grouping and then discarding most of them;
making for a more efficient query.

 

[JohnE]

Thank you both for your suggestions. I will give them a try.
.... John