Access 2000 - Queries

Results 1 to 2 of 2

Thread: Access 2000 - Queries

  1. #1
    Thex3 Guest

    Default Access 2000 - Queries

    Hi folks<BR><BR>This is not a simple query I&#039;m asking about and I&#039;ve been ripping my hair out trying to find the answer.<BR><BR>I have a database that deals with client information and then another with appointment information. There is a relationship with the two tables, a one to many with one client having many appointments. Each appointment costs so much per hour. The total is calculated and that&#039;s it right? Now what I want to do is have a field in the client query that will calculate the total of the totals for each of the appointments for that client. And to top it off, I want it to recalculate each time a new appointment is entered or as the client makes a payment. (Each record has a field for remaining balance.)<BR><BR>If you prefer to email me, please send it to <BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: Access 2000 - Queries

    Lets assume that you want to put it in a field in the same row as the new record yes ?<BR><BR>This is how I would do it - I assume you are familiar with SQL.<BR><BR>To calculate the single appointment simply use vbscript to calculate the figure before entering it into the row&#039;s field.<BR><BR>Okay so now you have done this but you have another field in the same row that you would like to have the total costs for all appointments to date. <BR><BR>You will need the KEYID of the new row just inserted (I will not say how I get it since my method is probably considered taboo - but it seems to work.) Check out if you need help doing this.<BR><BR>Now that you have the new keyid you simply use a Sum function to get the sum...<BR><BR>SELECT SUM(SubTotalsField) AS [TotalSofar]<BR>FROM Appointments<BR><BR>You can then take the answer from the above along with your keyid and update the empty totals field for that row.<BR><BR>Of course you actually don&#039;t need to even put it in the field since you can dynamically generate the answer.<BR><BR>Hope this was not too cryptic.<BR><BR>Dwane Lumley

Posting Permissions

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