Need help designing a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dorr@translogisticsinc.com - 28 Sep 2005 00:01 GMT

I don't have much experience writing SQL queries and am having a hard
time getting my head around this one. I am hoping that someone with
more experience will find this an easy exercise.

I have 3 tables, tblAccounts, tblOrders, and tblQuotes.

tblAccounts:
AcctNumber
AcctName
etc.

tblOrders
OrderDate
BillToAcct
ShipToAcct
etc.

tblQuotes
QuoteDate
BillToAcct
ShipToAcct
etc.

I need a list of all accounts that have been specified as a ShipTo on
an order from the last year, or have been specified as a ShipTo on a
quote from the last year where the BillTo account from either the order
or quote is equal to a known value. The result set needs to contain
information from tblAccounts.

If I am just doing orders or just doing quotes, it is simple. But
having to do both is throwing me off.

Any help in crafting the SQL query would be greatly appreciated.

TIA,

David

Reply to this Message

Dale Fye - 28 Sep 2005 02:53 GMT

Dorr,

I think I would combine the Orders and Quotes tables in one query, then link
that to tblAccounts, something like (untested):

SELECT A.*
FROM tblAccounts A
INNER JOIN
(SELECT O.ShipToAcct
FROM tblOrders
WHERE O.OrderDate > DateAdd("yyyy", -1, Date())
AND O.BillToAcct = [What Billing Account]
UNION
SELECT Q.ShipToAcct
FROM tblQuotes
WHERE Q.OrderDate > DateAdd("yyyy", -1, Date())
AND Q.BillToAcct = [What Billing Account]) as OrdersAndQuotes
ON A.AcctNumber = OrdersAndQuotes.ShipToAcct

The part of the query inside the parenthesis will give you unique ShipToAcct
numbers which meet the criterias:

1. Order Date within the last year (You might need to change the > to >=)
2. The BillToAcct value matches whatever you enter when the [What Billing
Aaccount] dialog pops up. If this is numeric, you might need to declare
this as a numeric parameter.

HTH
Dale

>I don't have much experience writing SQL queries and am having a hard
> time getting my head around this one. I am hoping that someone with
[quoted text clipped - 33 lines]
>
> David

Reply to this Message