Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum Subdatasheet (2002)

    I have 2 tables: tblChartOfAccounts and tblChartOfAccounts-LineItems joined by AccountNumber.
    tblChartOfAccounts-LineItems is a subdatasheet.
    I want to have tblChartOfAccounts act as a summary table and tblChartOfAccounts-LineItems have records of invoice sales.
    Additionally, I want to append records to tblChartOfAccounts-LineItems that have Account Number already filled in and then sort it to tblChartOfAccounts Account Number. tblChartOfAccounts has a list of accounts.
    I need the code to put in tblChartOfAccounts that summarizes the records in tblChartOfAccounts-LineItems where tblChartOfAccount AccountNumber = tblChartOfAccount-LineItems AccountNumber.
    Thanks
    Frank Hoeffer

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum Subdatasheet (2002)

    1) Subdatasheets are a sort of query. They are a means of displaying related data from another table, but they are not themselves tables.

    2) Sorting is fairly useless when you do it in the table itself. Records appear in the order dictated by the table's primary key. Access doesn't care what order the records are in and neither should you unless you're working with a recordset for a form or the sorting and grouping for a report., and you don't do that at the table level.

    3) Unless you're storing snapshot values (which doesn't sound like what you're describing), it doesn't make any sense to store data you can calculate on the fly. It becomes a maintenance issue if you store it like that. Then if you change the detail data, you have to be sure and update the summary data as well. You can force a form to do that, but all it takes is for one change to be made at the table level, and you have an immediate data problem.

    Why don't you describe the purpose of what you're trying to do. That might spark someone else's thought processes.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Subdatasheet (2002)

    Thanks Charlotte,
    Got it working.
    Basically, I'm developing an app for a client that has various tables and forms to log in a new customer, new job, create an invoice and then transfer invoice line Items, expenses etc. to a Chart of Accounts made using a query: tblChartOfAccounts and the linked table tblChartOfAccounts-LineItems. (Primary key = Account Number) ie Cash = 120, Sales Receivalbe = 210 etc.)
    The query displays a the main datasheet and a subdatasheet: tblChartOfAccount-LineItems,
    When not collasped the chart of accounts lists the balances of each account and expanded lists the associated line items. This is done with the ChartOfAccount Query having the field Balance: Extention table item is CharofAccounts-LineItems and then grouped as a sum.

Posting Permissions

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