Query help

[Krish]

I have a table with the customer #, Invoice # , Invoice date, Route #, Sales
$ and Salesmen name. In this business we deliver the products to the
customers. It may be possible in a single day, the same customer could have
multiple Invoices either due to placing orders at different time or
deliveries to multiple locations, but the delivery person makes one stop for
such deliveries with multiple Invoices. I want to find the number of stops
in a given day eliminating the multiple Invoices scenario. How could I
accomplish?

Thanks.
Krish

 

[MGFoster]

I have a table with the customer #, Invoice # , Invoice date, Route #, Sales
$ and Salesmen name. In this business we deliver the products to the
customers. It may be possible in a single day, the same customer could have
multiple Invoices either due to placing orders at different time or
deliveries to multiple locations, but the delivery person makes one stop for
such deliveries with multiple Invoices. I want to find the number of stops
in a given day eliminating the multiple Invoices scenario. How could I
accomplish?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since the Invoice date is not the delivery date, you will need a
delivery date in that table. If the Invoice date is the delivery date,
just substitute that column in the query, below. Change the
column/table names to match your column/table names.

PARAMETERS [Enter Delivery Date] Date;
SELECT Count(*) As Deliveries
FROM (SELECT DISTINCT customer_nbr FROM table_name
WHERE delivery_date = [Enter Delivery Date]) As T

If Access (JET) SQL had Count(Distinct...) you could do this:

SELECT COUNT(DISTINCT customer_nbr) As Deliveries
FROM table_name
WHERE delivery_date = [Enter Delivery Date]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJw9oIechKqOuFEgEQKU8gCfQVYbs5BP4UfJdxVTdhzdAYCLiSsAoLx7
s32jcYqgmJUnbwgtCPzuUb+E
=N5Lk
-----END PGP SIGNATURE-----