needing query assistance

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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.
 
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.
Click to expand...

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.
 
Back
Top