SQL to change closing totals

What tables do I need to fix if I entered a wrong closing amount for a particular tender type?

What is the SQL to do this?

I erroneously entered $0 for checks for the day, then ran a Z-report. Thus, I was short for the day by the amount of the checks. I know I will never be able to fix the Journal, but I want to fix the database for reporting purposes.

I don't think that's fully correct. It only solves part of the problem. I need to fix the check amount entered at closing so my export to accounting will be correct and my custom reports are correct. This information is not held in the batch table it appears. The accounting export has seperate lines for each tender, right?

"Brian Bruce [MSFT]" wrote:

Jason,

Then you also need to update the TenderTotals table.

Backup First!!

First, you need to identify the proper tender type;

SELECT * from Tender

Make a note of the ID of the tender type you wish to change

Then you need to identify the Batch number;

SELECT * from Batch

Make a note of the BatchNumber you wish to change

Finally, change the closing amount for the tender type;

UPDATE TenderTotals SET [Close] = xxx.xx WHERE BatchNumber = bn AND TenderID = tid

xxx.xx is the correct amount for the tender type bn is the batch number you noted tid is the tenderID you noted The word Close must be in brackets as it is a reserved word

Backup First!!

Jason,

Then you also need to update the TenderTotals table.

Backup First!!

First, you need to identify the proper tender type;

SELECT * from Tender

Make a note of the ID of the tender type you wish to change

Then you need to identify the Batch number;

SELECT * from Batch

Make a note of the BatchNumber you wish to change

Finally, change the closing amount for the tender type;

UPDATE TenderTotals SET [Close] = xxx.xx WHERE BatchNumber = bn AND TenderID = tid

xxx.xx is the correct amount for the tender type bn is the batch number you noted tid is the tenderID you noted The word Close must be in brackets as it is a reserved word

Backup First!!

microsoft.private.directaccess.partnerfeedback.

Thank you all for the help - seems to have done the trick...

Regarding the solution, whey does this produce and error:

Select * from TenderTotals

I would like to verify that the UPDATE statement worked correctly and it would also be a very nice query to look at my tender totals over time...

Jas> Hi Jason,

Thank you all for the help - seems to have done the trick...

Regarding the solution, whey does this produce and error:

Select * from TenderTotals

I would like to verify that the UPDATE statement worked correctly and it would also be a very nice query to look at my tender totals over time...

Jas > Hi Jason, > > You were right, I was only partially correct. I thought you wanted to > update the closing total of the batch for the Z report when you entered > your closing totals. You need to update the closing total for the correct > tender as well. Turns out that you should update both the TenderTotals and > Batch tables. Thank you Jeff for expanding on my initial answer. > > Regards, > > Brian > Microsoft Online Support Engineer >

Jason, Dunno, works good here. If you are typing it, make sure that you do not enter a space in TenderTotals. Try copy and pasting.