need assistance with SELECT query

[kskinne]

I am not very familiar with SQL and need some help with the following SELECT:

SELECT CpnyID, Acct, SubAcct, FiscYr, Balance
FROM AcctHist
WHERE FiscYr=2004

this sometimes returns data where there are multiple records that have the same account number (Acct) but a different subaccount number (SubAcct), for example like this:

• 
  1130.00 00.00.00 100.00
  1130.00 00.10.00 100.00
  1130.00 00.20.00 100.00

is there any way that i can restructure this query so that it combines all the amounts for the accounts that match, regardless of the subaccount, so that the above results would be displayed with a total for the account and without the subaccount, like this:

• 
  1130.00 300.00

thanks,
kevin

 

[Andrew Fergus]

Hi, I'm not familiar with SQL but have you tried editing this line :

SELECT CpnyID, Acct, SubAcct, FiscYr, Balance

to look like this :

SELECT CpnyID, Acct, FiscYr, Balance

i.e. remove the SubAcct but leave the rest the same

HTH, Andrew.

 

[Norie]

Kevin

Are you actually creating this query in the QBE (Query By Example) view of Access?

If so goto View>Totals and under the balance field select Sum from the dropdown. Also remove the SubAcct field.

 

[GuidonTBee]

I'm not sure this is correctly answering your question, but would using a GROUP BY clause help ?

->

SELECT CpnyID, Acct, FiscYr, SUM(Balance)
FROM AcctHist
WHERE FiscYr=2004
GROUP BY CpnyID, Acct, FiscYr

Just thought I'd mention it in case !

 

[kskinne]

getting rid of the sub field, doing a SUM on the balance field, and using a GROUP BY clause took care of it - thanks for the help


kevin

 

[kskinne]

need more assistance....

ok, here's another twist:

SELECT PerPost, CpnyID, Acct,
sum(Hours) as 'Hours',
sum(LaborDollars) AS 'Labor',
sum(AccLveSpreadInOH) AS 'Leave',
sum(TaxSpread) AS 'Tax',
sum(PensionSpread) AS 'Pension',
sum(InsurSpread) AS 'Insurance'
FROM xvr_XLabDetail
GROUP BY PerPost, CpnyID, Acct

now i am trying to bring in more than one summed column as shown above, but it will not allow me to do this. is there something wrong with the query syntax?

thanks,
kevin

EDIT:
more specifically, the error message reads: "parameters are not allowed in queries that cannot be displayed graphically" which leads me to believe there is something wrong with my syntax

 

[kskinne]

sorry for all the pesky questions, but here's another one. after trying to modify that query a bit, when I return the data to my sheet (I'm using Excel's query wizard to get into MS Query and manually modify the SQL statement), the columns are no longer in the same order of layout on the sheet as they are in the results window in MS Query. Any idea why, and is there a way to force the columns to appear in a certain order from left to right?

thanks in advance,
kevin

 

[kskinne]

never mind, i was able to fix it by removing the reference to CpnyID, this was screwing it up because there are multiple CpnyID's for an account, just like there are multiple sub accounts for an account.