SQL Query

[skipwater]

You will need a combination of the below sql to achieve what you want but this will get you started.

SELECT COUNT( DISTINCT `customers`.`customers_id` ) AS distinct_names, `customers`.`customers_firstname` , `customers`.`customers_lastname` , `customers`.`customers_email_address` , `customers`.`customers_telephone` , `address_book`.`entry_street_address` , `address_book`.`entry_suburb` , `address_book`.`entry_postcode` , `address_book`.`entry_city` , `address_book`.`entry_state`
FROM `address_book` `address_book`
INNER JOIN `customers` `customers` ON ( `customers`.`customers_id` = `address_book`.`customers_id` )
GROUP BY `customers`.`customers_email_address`
HAVING distinct_names = 1

And if this is going to be daily app why not modify Export invoice totals to .csv file for Excel use for your use?

[hugo13]

without order table

SELECT address_book.entry_company, address_book.entry_firstname, address_book.entry_lastname, address_book.entry_suburb, address_book.entry_city, address_book.entry_state, address_book.entry_postcode, address_book.entry_country_id, countries.countries_name, customers.customers_telephone, customers.customers_email_address
FROM countries INNER JOIN (address_book INNER JOIN customers ON (address_book.address_book_id = customers.customers_default_address_id) AND (address_book.customers_id = customers.customers_id)) ON countries.countries_id = address_book.entry_country_id;

with order table

SELECT orders.customers_name, orders.customers_company, address_book.entry_firstname, address_book.entry_lastname, address_book.entry_street_address, address_book.entry_suburb, address_book.entry_city, address_book.entry_state, address_book.entry_postcode, address_book.entry_country_id, orders.customers_telephone, orders.customers_email_address
FROM (address_book INNER JOIN orders ON address_book.customers_id = orders.customers_id) INNER JOIN customers ON (address_book.address_book_id = customers.customers_default_address_id) AND (address_book.customers_id = customers.customers_id);

because I'm lazzy, all is done via ms-access && that works very well in phpMyAdmin

[confused_aswell]

Hi Hugo

Thanks very much for your answer it has eliminated the additional addresses from the one customer who entered them. There is one small problem, if a exsisting customer has come back another day to buy something else, then that entry also gets added to the results, so you will have the customer in one line and then again further down the list.

Would it be too much to ask if you could solve that problem too?

Thanks,

Phil

[confused_aswell]

Hi

Actually don't worry about it, the accounts package takes care of duplicate entries.

Thanks,

Phil

[Minnie Mouse]

a friendly recommendation: if you are going to be querying the database, it would be a good thing to learn mysql. it is pretty easy and you can teach it to yourself (i did). get a book and a gui interface. I use navicat (for mac). it allows you to run queries and tells you your mistakes... also has a helper to help build them (although i have never quite figured out how that works). phpmyadmin is fine if you know what you are doing but wont help you learn.

I cant tell you how valuable it has been to know how to write these myself, or to be able to find what i need in the code and edit...

[kobra]

"Minnie Mouse" - - Another NaviCat advocate!!!

[Minnie Mouse]

THE BEST SOFTWARE EVER -- seriously, i learned mysql using it... !! i love it!!
i have MAMP running on my mac. then i use navicat and CODA (also amazing). wow, what a combo is all i can say. (and inexpensive too!)