Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    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
    FROM [tblChartOfAccounts-Items]
    WHERE ((([tblChartOfAccounts-Items].CustomerID)=1));

    Frank <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    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!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts