Trick sql or query building help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have [Acct#] table which contains a field named [Client No].

I also have a [SPP Q1 Inbound] table where 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 *].
 
I think this is getting close

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 *];
 
First things first. In the future do NOT use special characters like the * as
part of the name of a table, field, query, report, form, etc.. Not even
spaces. The only special character that I find acceptable is the _ underscore.

Second if you can or must link one table to another based on one of three
different fields, your data is not properly normalized. That's why you have
to jump through hoops like you are attempting now.

Off of my soapbox. Try the two statements below and see what happens.

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 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 *],
SUM([SPP Q1 Inbound].[sc1 Usd Currency Amount])
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 *]
GROUP BY [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 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 *]
;
Or

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 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 *],
SUM([SPP Q1 Inbound].[sc1 Usd Currency Amount])
FROM [Acct #], [SPP Q1 Inbound]
GROUP BY [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 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 *]
HAVING [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 *]
;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jkurys said:
I think this is getting close

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 *];




jkurys said:
I have [Acct#] table which contains a field named [Client No].

I also have a [SPP Q1 Inbound] table where 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 *].
Click to expand...
Click to expand...
 

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

Access Logic Puzzle....help, anyone? 5
delete query 1
Select query count function help 1
More Subtotals in a Report 3
combine 2 access queries 3
Ranking 1
Query prompts twice for same parameters 4
Multiple Dlookup in the same query ?? 1

Back
Top