Problem Updating Fields (1 Viewer)

[jdcallejas]

Hello,

I am trying to update a field and I am having a hard time doing it.

I have two tables, tblInvoices and tblPmtDetails. I need to update the field InvPmt on the Invoices with the Sum amount of payments in tblPmtDetails "Field name AmountPmt" for that specific Invoice.

I have this done on my update query:

UPDATE Invoices RIGHT JOIN PmtDetail ON Invoices.InvID = PmtDetail.InvID SET Invoices.InvPmt = DSum("[AmountPmt]","PmtDetail","[InvID]=" & [InvID]);

Right now what it does is that it only updates the first record on the TblInvoices with the total amount of all payments.

So I have three invoices with $500 each and three payments with $500 each, what it does is adds all payments to $1500 and it puts all the amount on the first record of the Invoice table.

The problem is that is not selecting each Invoice Id for each reletaed record on the PmtDetail table.

Hope this makes sence...

Daniel

 

[HiTechCoach]

Daniel,

Normally you do not store calculated values. This violates the "rules of normalization." IMHO, the reason this is difficult is because you really shouldn't be doing it. I have been developing invoicing/accounting systems since 1979 and I have tried many methods. I have found that it really is best to calculate the total payments as needed. I generally make a stored query that is a totaling query that returns the total by invoice primary key. This query can easily be used anywhere you need the total.

 

[jdcallejas]

Thanks for the response,

Ok I have been fighting with this for months!!!! I am doing a DB for my Apt Complex. I have developed a lot of it but I have been stuck on the invoices. I have tried many ways and I dont have a clear way of doing this. This is what I have:

I have a form for invoices and a form for payments, I know that I can get the totals by running a query but I have no idea how to tell it to update that invoice if the amount of payments is equal or greater the invoice detail amount.... So I thought if I put a field name invpayment on my invoice table and than update that field with the sum of the pmtdetail table I could compare the totals and update the field InvPAid to yes..

It has been a nightmare!!!!

Here is a copy of my Database with some Data... Maybe this can help you help me some more... I have some queries that is doing some updates, dont know if its correct...

Thanks,
Daniel

 

[HiTechCoach]

I was having trouble figure out what you are trying to do.

I did make a few adjustments. See the attached

 

[jdcallejas]

Hello,

Ok, I see what you did, I understand that this is how I can get the totals from my tblPaymentDetail and if I create one for InvoiceDetails I can get the total of that too. Fron here I can create a qry that will give me the balance due too.

What I have been trying to do is something like Quickbooks payments, in quickbooks you can pick the Cient and than it would list all the clientes pending invoices, than you can put the payment type and amount and select the invoices you want to pay. This is done automaticaly. Well I am not that good so I figured I input the amount of pmt into a field called InvPmt on the Invoices. But I have to relate a payment to that invoice.

So I created a pmtDetail where I relate the InvID to the PmtId and added a field called PmtAmount. On this field I put the amount of the invoice if the amount of the payment is equal or larger the invoice amount.

If I could do this automativcaly would be better, but like I said am not that good yet..

Thanks,

Daniel

 

[jdcallejas]

Ok I did some changes to see if I could do this like that. I created two queries summing the totals of payment details and invoice details, than I created a from the other two queries to get a balancedue. It works fine, now I am trying to update the invoices to Paid if the balance is (0) and it will not do it, it gives me an error saying query is not updateable.

Here the DB with what I did...

Thanks,

Daniel