Continuous forms question

  • Thread starter Thread starter Newboy
  • Start date Start date
N

Newboy

HPlease help, I have created a database with 2 tables,
Customers and Orders and they are linked as a one to
many, I have the customers sorted in a Query and then
displayed in a continuous form with a button that opens a
popup form for me to enter the orders.
The bit that has caused me so much trouble is that on the
continuous Customer form I wanted a checkbox that would
be checked if there were any Orders for that Customer.
I have done it but it is very messy, does anyone know of
a better way.
 
This SQL creates a boolean column called "HasOrders" for each customer that has at least one order

SELECT CustomerID, Name, (CountOfOrderID > 0) AS HasOrders FROM
(SELECT tblCustomers.CustomerID, tblCustomers.Name, Count(tblOrders.OrderID) AS CountOfOrderI
FROM tblCustomers LEFT JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerI
GROUP BY tblCustomers.CustomerID, tblCustomers.Name

Jeff Johnso
Advantec Information System
http://www.advantecis.com
 
Back
Top