Form to update two tables

[keiath]

Hi guys

i have a subform for the accountants to enter there accruals and prepayments, once they do this not only needs to update the accruals table:-

Group Code	Expense Code	Description	Amount	Amount P/Y	A	B	C	D	E	F
A	55	Accrual	-1293.00	0.00	0.00	0.00	0.00	0.00	-1293.00	0.00
A	63	Accrual	-780.00	0.00	0.00	0.00	0.00	0.00	-780.00	0.00
A	65	Accrual	-3316.38	0.00	0.00	0.00	0.00	0.00	-3316.38	0.00

Once they enter this information those items become part of the expenses and therefore it needs to also update the invoice table (the site code is hidden on the subform)

ID	Site Code	Invoice Date	PO	Invoice	Contractor	Invoice Amount	Expense Code	Exp Description	Lease Code	Status	A	B	C	D	E	F
599	0272S	30/09/2012	99276	9001399	Rouse Partners Llp	2880.00	91		A	Authorized on 06/03/2013 by MAB, Paid on 11/03/2013	0.00	0.00	2880.00	0.00	0.00	0.00
600	0272S	30/11/2012	999		Bank Charge - Bank Charge	180.09	52		A		0.00	0.00	180.09	0.00	0.00	0.00
601	0272S	26/04/2013	999		AGM Meeting 7th May 2013 - RAD 7th may 2013	130.00	52		A		0.00	0.00	130.00	0.00	0.00	0.00

How do I do this, i am aware you use the union but I have never done this can someone please help

Thanks

[June7]

Saving aggregate data is usually a bad idea. Conventional approach is to calculate aggregate data when needed. This is what aggregate queries are for as well as reports with Grouping and Sorting and summary calcs.

[rpeare]

is he actually asking for them to aggregate? Hard to tell because the two sets of data in his example are not related but it seems like he wants to create a 1 to 1 relation between accruals and invoices.

[June7]

Right, on second look, don't know what is happening. I saw "Accrual" and immediately thought 'aggregate'.

If it's 1-to-1, why two tables?

Also, the fields A - F suggestive for not-normalized structure.

[rpeare]

Two things I was also wondering as well, just wanted to make sure we were actually working on the same problem.

[keiath]

okay the invoice table is excel import from another system.

The accruals table is required for accounting reasons as it has to be "highlighted" in the accounts and this can be accruals, reversal of accruals from previous years, also prepayments and reversal of those, unlike the invoices which auto sorts ect when imported, the accruals are a manual input by the accountant.

but once the accountant has determined his accruals they then form part of the expenses, and hence the need to import them to the invoice table, and because of the way in which i query and sort the invoices this seemed the most logical way to handle this, the A-F is how those invoices and accruals are accounted for. 95% of the invoice will fall into 'C' (the table defaults all the invoices to 'C' but the accountant can change that) the rest accruals reversals and prepayments fall into one of the others and its the accountant that decides that.

The database that I have put together with help from you guys, takes raw unsorted data, and via import produces a set of Year End Management Accounts. This is saving the accountants hours of work. (PS I am actually a Qualified Accountant myself)

hope that makes sense

[June7]

I really don't see what UNION has to do with this.

What do you mean by "update invoice table" - create new records or modify data of existing records?

[keiath]

It would be to add new data from the accruals table into the invoice table, because i need that data in 2 places, although I am sure i could problem 'query' the information out, but since i Have to add the accruals to start with its why i went with its own table

[June7]

Makes no sense to me. But if you must, there are various approaches.

Possibly an SQL action query using INSERT SELECT to select a batch of records from the Accruals table and insert into Invoices table.

Or code in the Accrual form AfterUpdate event to copy each new record singly to the Invoices table, again an INSERT action. Something like:

CurrentDb.Execute "INSERT INTO Invoices(LeaseCode, ExpenseCode) VALUES('" & Me!GroupCode & "', '" & Me!ExpenseCode & "')"