General discussion

  • Creator
    Topic
  • #2295586

    Access2000 tables

    Locked

    by dliby ·

    I have table “A” that has multiple records with the same account number and different amounts of cash. The sum of these records needs to be updated to table “B” which has one record for the account number and total cash. How can I accomplish this task?

All Comments

  • Author
    Replies
    • #3322070

      Reply To: Access2000 tables

      by dklippert ·

      In reply to Access2000 tables

      Calculations should be avoided in tables. Tables should be used for raw data.
      To summerize your data, produce averages or maybe top 10 sales, use Queries.
      You can also use the query results in Reports and Forms.

    • #3343038

      Reply To: Access2000 tables

      by jleather ·

      In reply to Access2000 tables

      I agree this should be a query, actually two queries, and not another table. First query is to get a list of all unique Account Numbers from your table. The second uses your table and the first query for the desired result. I tested with a database with a table, called TableA, with two fields: Account Number (long Integer) and Amount (currency).

      To build the first query, open a new query in design view and include your table. Make sure Totals are shown (by clicking the sigma on the tool bar or through the menu View|Totals). Select the Account Number, in the Totals row have ?Group By? shown (from drop down list) and if wanted, choice the appropriate selection for the Sort row.

      The second query is built with your table and the first query (which I called AccountNumbers) and also have Totals selected as above. Choice Account Number from the AccountNumbers query and Amount from TableA. For Account number have Group By selected for the Totals row and Sum for Amount. Finally drag the Account Number field in TableA to the Account Number field in AccountNumbers query. This should result in a line (link, relationship) between the two. Again if wanted choice the appropriate selection for the Sort row.

      That should do it, just view in datasheet view!

      Hope this helps, and I have the test mdb file if you want a copy.

      John Leather

    • #3334089

      Reply To: Access2000 tables

      by parier ·

      In reply to Access2000 tables

      You can use a make table query to create your results, but I recommend following the other two suggestions.

      select distinct customer, sum(sales)
      from customers
      where sum > 0;

      New Table…
      SALES_REP SALES
      BOB 65465.56
      STEVE 55645.34
      JOHN 64545.31

      Hope this helps.

    • #3330283

      Reply To: Access2000 tables

      by dliby ·

      In reply to Access2000 tables

      This question was closed by the author

Viewing 3 reply threads