3 field query/Filter that I'm a little stumped with, maybe easy

[Guest]

I'm trying to build a sales report of sorts.

First I have a client table which contains Account numbers.

Now I have a second table with all of the sales info. This includes
shipper, consignee, payor, date, and revenue.

In second table the shipper, consignee, and payor, are all represented by an
Account number.

what i am trying to do... If the account number is the shipper, consignee,
or payor
in a record then I want to include their revenue.

I am sort of able to do this but I am basically setting up three queries and
piggy backing them against each other. any one know of a better method??

 

[SusanV]

Use OR to get all the records:

SELECT * from YourTable where YourTable.Shipper = "123456" OR
YourTable.Consignee = 123546 OR YourTable.Payor = 123456

 

[Guest]

Okay that makes sense, now where i'm stuck is that is fine if there is only
one account, in your example "123456". In my case I have a another table
that contains a list of accounts I want to compare against.

 

[SusanV]

An error in my SQL - the first parameter has quotes (for text only) the
other 2 do not (numeric data type)

 

[Guest]

but what if your parameter is a list from another table??

 

[SusanV]

I'm not sure I follow - what are the tables and their relationships, and the
separate SQL you are using currently?

 

[Guest]

Acct# table contains a field named "Client No".

In the SPP Q1 Inbound table there are fields named [sh1 Shpr Acct Nbr *],
[sh1 Cons Acct Nbr *], and [sh1 Af Payor Acct Nbr *]. Each one of those
fields represents an account number which may be equal to [Acct #].[Client
No].

I am try to sum [SPP Q1 Inbound].[sc1 Usd Currency Amount], for each record
in table [SPP Q1 Inbound] where [Acct #].[Client No] is equal to sh1 Shpr
Acct Nbr *], [sh1 Cons Acct Nbr *], and [sh1 Af Payor Acct Nbr *].

here is what the sql is now:

SELECT [Acct #].[Sales Rep], [Acct #].[Client No], [SPP Q1 Inbound].[sh1
Shpr Acct Nbr *], [SPP Q1 Inbound].[sh1 Cons Acct Nbr *], [SPP Q1
Inbound].[sh1 Af Payor Acct Nbr *], [SPP Q1 Inbound].[sc1 Usd Currency Amount]

FROM [Acct #] LEFT JOIN [SPP Q1 Inbound] ON [Acct #].[Client No] = [SPP Q1
Inbound].[sh1 Shpr Acct Nbr *];

I am trying to get it to say something like

SELECT [Acct #].[Sales Rep], [Acct #].[Client No], [SPP Q1 Inbound].[sh1
Shpr Acct Nbr *], [SPP Q1 Inbound].[sh1 Cons Acct Nbr *], [SPP Q1
Inbound].[sh1 Af Payor Acct Nbr *], [SPP Q1 Inbound].[sc1 Usd Currency Amount]

FROM [Acct #] LEFT JOIN [SPP Q1 Inbound] ON [Acct #].[Client No] = [SPP Q1
Inbound].[sh1 Shpr Acct Nbr *] or [SPP Q1 Inbound].[sh1 Cons Acct Nbr *] or
[SPP Q1 Inbound].[sh1 Af Payor Acct Nbr *];

 

[Guest]

i think this is getting closer

SELECT [Acct #].[Sales Rep], [Acct #].[Client No], [Acct #].ClientName, [SPP
Q1 Inbound].[sh1 Shipment Calendar Mo Name *], [SPP Q1 Inbound].[sh1 Shipment
Calendar Mo Nbr *], [SPP Q1 Inbound].[sh1 Shipment Calendar Qtr *], [SPP Q1
Inbound].[sh1 Shipment Calendar Yr *], [SPP Q1 Inbound].[sh1 Orig Svc Cntr
City Name *], [SPP Q1 Inbound].[sh1 Orig Svc Cntr Iso Country Code *], [SPP
Q1 Inbound].[sh1 Dest Svc Cntr City Name *], [SPP Q1 Inbound].[sh1 Dest Svc
Cntr Iso Country Code *], [SPP Q1 Inbound].[sh1 Shpr Acct Nbr *], [SPP Q1
Inbound].[sh1 Shpr Name *], [SPP Q1 Inbound].[sh1 Cons Acct Nbr *], [SPP Q1
Inbound].[sh1 Cons Name *], [SPP Q1 Inbound].[sh1 Af Payor Acct Nbr *], [SPP
Q1 Inbound].[sh1 Af Payor Name *], [SPP Q1 Inbound].[sc1 Charge Description
*], [SPP Q1 Inbound].[sc1 Usd Currency Amount], [SPP Q1 Inbound].[sc1 Charge
Dom Rev Allocation *], [SPP Q1 Inbound].[sc1 Charge Intl Rev Allocation *],
[SPP Q1 Inbound].[sh1 Shipment Type *], [SPP Q1 Inbound].[sh1 Service Code
*], [SPP Q1 Inbound].[sh1 Service Code Desc *]

FROM [Acct #], [SPP Q1 Inbound]

where [Acct #].[Client No] = [SPP Q1 Inbound].[sh1 Shpr Acct Nbr *] or
[Acct #].[Client No] = [SPP Q1 Inbound].[sh1 Cons Acct Nbr *] or [Acct
#].[Client No] = [SPP Q1 Inbound].[sh1 Af Payor Acct Nbr *];