Do I need a subquery?

C

Chippy

I have a customer orders table (Orders) which contains orders with and
without cost (Orders.Order_Cost).

I need a query to achieve the following logic:

Select all orders for a customer between start date and end date(including
those with no value) only if the customer has a least one order in that
period with value.
Repeat this for all customers.

Do I need to use a subquery? if so any help on query format would be much
appreciated. Thanks
 
M

Michel Walsh

It seems you only need a standard select query, with a criteria on the
(shipping? ) date:


BETWEEN ["enter starting date for the interval:"] AND ["enter ending date
for the interval:"]




You can ask to SORT the customers, to 'regroup' them in one block, in the
output.


Hoping it may help,
Vanderghast, Access MVP
 
L

louisjohnphillips

It seems you only need a standard select query, with a criteria on the
(shipping? ) date:

BETWEEN ["enter starting date for the interval:"] AND ["enter ending date
for the interval:"]

You can ask to SORT the customers, to 'regroup' them in one block, in the
output.

Hoping it may help,
Vanderghast, Access MVP




I have a customer orders table (Orders) which contains orders with and
without cost (Orders.Order_Cost).
Click to expand...
I need a query to achieve the following logic:
Click to expand...
Select all orders for a customer between start date and end date(including
those with no value) only if the customer has a least one order in that
period with value.
Repeat this for all customers.
Click to expand...
Do I need to use a subquery? if so any help on query format would be much
appreciated. Thanks- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

This query would list all orders for each customer, regardless of when
placed, if the customer placed any orders within the date range.

Select A.*
from Orders as A
where exists
( Select 'true'
from Orders
where CustomerID = A.CustomerID
and OrderDate >= [start date]
and OrderDate <= [end date] )

The statement of problem is ambiguous about whether to select all
orders or orders only those within the date range.
 
M

Michel Walsh

I was more thinking about:

SELECT *
FROM orders
WHERE orderDate >= [start date]
AND orderDate <= [end date]
ORDER BY customer, orderNumber



where the order by is just for esthetic.


Vanderghast, Access MVP


It seems you only need a standard select query, with a criteria on the
(shipping? ) date:

BETWEEN ["enter starting date for the interval:"] AND ["enter ending
date
for the interval:"]

You can ask to SORT the customers, to 'regroup' them in one block, in the
output.

Hoping it may help,
Vanderghast, Access MVP




I have a customer orders table (Orders) which contains orders with and
without cost (Orders.Order_Cost).
Click to expand...
I need a query to achieve the following logic:
Click to expand...
Select all orders for a customer between start date and end
date(including
those with no value) only if the customer has a least one order in that
period with value.
Repeat this for all customers.
Click to expand...
Do I need to use a subquery? if so any help on query format would be
much
appreciated. Thanks- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

This query would list all orders for each customer, regardless of when
placed, if the customer placed any orders within the date range.

Select A.*
from Orders as A
where exists
( Select 'true'
from Orders
where CustomerID = A.CustomerID
and OrderDate >= [start date]
and OrderDate <= [end date] )

The statement of problem is ambiguous about whether to select all
orders or orders only those within the date range.
Click to expand...
 
C

Chippy

Thanks for the replies, sorry if I did not make it clear but I only want to
retreive all orders for a customer if the sum value of that customer orders
is > 0.
e.g. if order table has 15 orders in total for the desired period:

Cust 123 has 1 order with Order_Cost 10.00, and 9 Orders with Order_Cost = 0
Cust 456 has 5 orders all with Order_Cost = 0

I would want returned the 10 orders for Cust 123 and nothing retutned for
Cust 456. The end use of this table is to print monthly customer invoices
detaling all orders placed in the month and I need to show all orders placed
bu the customer even if the order had no cost value. What I don't want to do
is to print invoices when there is no value to the invoice ie print an
invoice for Cust. 456. I guess at some point I need to sum the value of
Order_Cost but not quite sure.

Michel Walsh said:
I was more thinking about:

SELECT *
FROM orders
WHERE orderDate >= [start date]
AND orderDate <= [end date]
ORDER BY customer, orderNumber



where the order by is just for esthetic.


Vanderghast, Access MVP


It seems you only need a standard select query, with a criteria on the
(shipping? ) date:

BETWEEN ["enter starting date for the interval:"] AND ["enter ending
date
for the interval:"]

You can ask to SORT the customers, to 'regroup' them in one block, in the
output.

Hoping it may help,
Vanderghast, Access MVP





I have a customer orders table (Orders) which contains orders with and
without cost (Orders.Order_Cost).

I need a query to achieve the following logic:

Select all orders for a customer between start date and end
date(including
those with no value) only if the customer has a least one order in that
period with value.
Repeat this for all customers.

Do I need to use a subquery? if so any help on query format would be
much
appreciated. Thanks- Hide quoted text -

- Show quoted text -
Click to expand...

This query would list all orders for each customer, regardless of when
placed, if the customer placed any orders within the date range.

Select A.*
from Orders as A
where exists
( Select 'true'
from Orders
where CustomerID = A.CustomerID
and OrderDate >= [start date]
and OrderDate <= [end date] )

The statement of problem is ambiguous about whether to select all
orders or orders only those within the date range.
Click to expand...
Click to expand...
 
M

Michel Walsh

SELECT *

FROM orders

WHERE orderDate >= [start date]
AND orderDate <= [end date]
AND Order_Cost >0

ORDER BY customer, orderNumber




Hoping it may help,
Vanderghast, Access MVP


Chippy said:
Thanks for the replies, sorry if I did not make it clear but I only want
to
retreive all orders for a customer if the sum value of that customer
orders
is > 0.
e.g. if order table has 15 orders in total for the desired period:

Cust 123 has 1 order with Order_Cost 10.00, and 9 Orders with Order_Cost =
0
Cust 456 has 5 orders all with Order_Cost = 0

I would want returned the 10 orders for Cust 123 and nothing retutned for
Cust 456. The end use of this table is to print monthly customer invoices
detaling all orders placed in the month and I need to show all orders
placed
bu the customer even if the order had no cost value. What I don't want to
do
is to print invoices when there is no value to the invoice ie print an
invoice for Cust. 456. I guess at some point I need to sum the value of
Order_Cost but not quite sure.

Michel Walsh said:
I was more thinking about:

SELECT *
FROM orders
WHERE orderDate >= [start date]
AND orderDate <= [end date]
ORDER BY customer, orderNumber



where the order by is just for esthetic.


Vanderghast, Access MVP


On Jun 18, 7:06 am, "Michel Walsh"
It seems you only need a standard select query, with a criteria on the
(shipping? ) date:

BETWEEN ["enter starting date for the interval:"] AND ["enter
ending
date
for the interval:"]

You can ask to SORT the customers, to 'regroup' them in one block, in
the
output.

Hoping it may help,
Vanderghast, Access MVP





I have a customer orders table (Orders) which contains orders with
and
without cost (Orders.Order_Cost).

I need a query to achieve the following logic:

Select all orders for a customer between start date and end
date(including
those with no value) only if the customer has a least one order in
that
period with value.
Repeat this for all customers.

Do I need to use a subquery? if so any help on query format would be
much
appreciated. Thanks- Hide quoted text -

- Show quoted text -

This query would list all orders for each customer, regardless of when
placed, if the customer placed any orders within the date range.

Select A.*
from Orders as A
where exists
( Select 'true'
from Orders
where CustomerID = A.CustomerID
and OrderDate >= [start date]
and OrderDate <= [end date] )

The statement of problem is ambiguous about whether to select all
orders or orders only those within the date range.
Click to expand...
Click to expand...
Click to expand...
 
L

louisjohnphillips

SELECT *

FROM orders

WHERE orderDate >= [start date]
AND orderDate <= [end date]
AND Order_Cost >0

ORDER BY customer, orderNumber

Hoping it may help,
Vanderghast, Access MVP




Thanks for the replies, sorry if I did not make it clear but I only want
to
retreive all orders for a customer if the sum value of that customer
orders
is > 0.
e.g. if order table has 15 orders in total for the desired period:
Click to expand...
Cust 123 has 1 order with Order_Cost 10.00, and 9 Orders with Order_Cost =
0
Cust 456 has 5 orders all with Order_Cost = 0
Click to expand...
I would want returned the 10 orders for Cust 123 and nothing retutned for
Cust 456. The end use of this table is to print monthly customer invoices
detaling all orders placed in the month and I need to show all orders
placed
bu the customer even if the order had no cost value. What I don't want to
do
is to print invoices when there is no value to the invoice ie print an
invoice for Cust. 456. I guess at some point I need to sum the value of
Order_Cost but not quite sure.
Click to expand...
I was more thinking about:
SELECT *
FROM orders
WHERE orderDate >= [start date]
AND orderDate <= [end date]
ORDER BY customer, orderNumber
where the order by is just for esthetic.
Vanderghast, Access MVP
On Jun 18, 7:06 am, "Michel Walsh"
It seems you only need a standard select query, with a criteria on the
(shipping? ) date:
BETWEEN ["enter starting date for the interval:"] AND ["enter
ending
date
for the interval:"]
You can ask to SORT the customers, to 'regroup' them in one block, in
the
output.
Hoping it may help,
Vanderghast, Access MVP

I have a customer orders table (Orders) which contains orders with
and
without cost (Orders.Order_Cost).
I need a query to achieve the following logic:
Select all orders for a customer between start date and end
date(including
those with no value) only if the customer has a least one order in
that
period with value.
Repeat this for all customers.
Do I need to use a subquery? if so any help on query format would be
much
appreciated. Thanks- Hide quoted text -
- Show quoted text -
This query would list all orders for each customer, regardless of when
placed, if the customer placed any orders within the date range.
Select A.*
from Orders as A
where exists
( Select 'true'
from Orders
where CustomerID = A.CustomerID
and OrderDate >= [start date]
and OrderDate <= [end date] )
The statement of problem is ambiguous about whether to select all
orders or orders only those within the date range.- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
From the post at 9:24 a.m., I believe the final result set must
Click to expand...
identify all orders placed during the period--regardless of cost--for
each customer who had a non-zero total cost during the period. The
"B" projection identifies those customers and the "A" projection
identifies their orders.

Select A.*
from Orders as A,
( select CustomerID
from Orders
where OrderDate >= [start date]
and OrderDate <= [end date]
group by CustomerID
having sum( OrderCost ) <> 0 ) as B
where A.CustomerID = B.CustomerID
and A.OrderDate >= [start date]
and A.OrderDate <= [end date]
 
J

John Spencer

I would think that something like the following would give you what you want

SELECT *
FROM Orders
WHERE OrderDate>= [StartDate]
and OrderDate <=[End Date]
and Customer IN
(SELECT Customer
FROM Orders
WHERE OrderDate>= [StartDate]
AND OrderDate <=[End Date]
AND Order_Cost > 0)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SubQuery Assistance... 2
Cannot edit field in a joined table-subquery query 10
CrossTab Help! 6
SQL RollUp SubQuery 2
Page numbering with Merged Document and IF statements 1
Dates Subquery 1
Total appointments per agent 2
Auto select Billing Cost 8

Top