Results 1 to 2 of 2
Thread: Query Sum (2002)
2002-10-10, 04:35 #1
- Join Date
- Feb 2001
- San Francisco, CA, USA
- Thanked 0 Times in 0 Posts
Query Sum (2002)
I have two queries: 99865-qryChartOfAccounts and 99870-qryChartOfAccounts-Items.
These are from two tables tblChartOfAccounts and tblChartOfAccounts-Items joined one-to-many.
99865-qryChartOfAccounts acts as a sum query of accounts. and 99870-qryChartOfAccounts-Items are
individual transactions in that account. The primary key is AccountNumber.
I want to use this arrangement to limit the records of transactions in 99870-qryChartOfAccounts-Items buy CustomerID thus giving me a reusable chart of accounts for each Customer.
It works fine until I put an ID in CustomerID then I get duplicates in 99865-qryChartOfAccounts,
The two queries are:
SELECT tblChartOfAccounts.AccountNumber, tblChartOfAccounts.Account, Sum([99870-qryChartOfAccounts-Items].Amount) AS SumOfAmount
FROM tblChartOfAccounts INNER JOIN [99870-qryChartOfAccounts-Items] ON tblChartOfAccounts.AccountNumber = [99870-qryChartOfAccounts-Items].AccountNumber
GROUP BY tblChartOfAccounts.AccountNumber, tblChartOfAccounts.Account;
SELECT [tblChartOfAccounts-Items].CustomerID, [tblChartOfAccounts-Items].JobID, [tblChartOfAccounts-Items].AccountNumber, [tblChartOfAccounts-Items].Account, [tblChartOfAccounts-Items].Amount
Frank <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
2002-10-10, 11:51 #2
- Join Date
- Aug 2001
- Frederick, Maryland, USA
- Thanked 2 Times in 2 Posts
Re: Query Sum (2002)
Where is the customer data stored in the database and how is the customer data linked to the Chart of Accounts?
You said that you have two tables, chart of accounts and chart of accounts items linked as a one to many. I assume you chart of accounts would have an account say 5200 - labor, and the items would be 5200-1, 5200-2, 5200-n for the types of labor (as well as others).
It sounds as if you need another table, Customer, that is joined to the table Chart of Accounts as a one to many as well. Thus, each customer could have many Accounts and each Account can have many Account items. You will need to add Customer to the Chart of Accounts table to link the Customer and Chart of Account Table. Also, if each customer uses the same Chart of Accounts but may have differen't account items, you may need to carry the Customer through to the items table as well.
Since it is unclear how you built your relationships, it sounds as if you are getting all records returned in your query or worse, a cartesian result.
(It's been a while!)