Copy Query Results to Table (add)

[Spyhunter]

I need to copy the records of a query result to an existibng table and have the code below but it keeps error'ing with Run time error 3061, Two few parameters, expected two.

Any ideas where I am going wrong please - my code is this:

CurrentDb.Execute "Insert Into TBL_Balance SELECT * FROM Qry_DDRListSelected"

With TBL_Balance the table I need to copy the records from using the query (Qry_DDRListSelected) which links to another table TBL_DDR.

[OBP]

Do you still need help with this?

[Spyhunter]

I have now solved this by using an append query, thank you.

Although now I have the problem of having a running sum based on what records are in the query shown (each record has amount and totalsum). The amount is either a DB or CR and based on this field will either add the amount to the previous record or reduce it. Any suggestions please?

[OBP]

I don't actually use queries to update table data, I use VBA code and one or more recordsets.
The problem with using a query is that if a correction is required it is very difficult to make that correction with a query.
Have you worked with recordsets?

[Spyhunter]

OK I'm open to suggestions on either. Can you explain more please, much appreciated.

[OBP]

It would help if you explained what you are actually doing with the data.
A VBA recordset works directly with the table, as in stck taking or sales and stock etc.
But I am not sure what you are doing with your data.

ps som edummy data and an example would help a lot.
I can look at it tomorrow.

[Spyhunter]

OK I have a bank balance sheet that shows Direct Debits in date order (not ID order). I want to display these based on a query (criteria changes at top for date range, paid, etc) and show a running total from the previous record (ddrdate) to the next, so if I had the following this is what it would look like:

Opening Balance: £1.00
Payment 3 - 1/1/20 - £1.00 Running Sum: £2.00
Payment 2 - 21/1/20 - £2.00 Running Sum: £4.00
Payment 1 - 2/2/20 - £5.00 Running Sum: £9.00

Hope that makes sense.

the previous append query issue was basically I wanted to add the DDR List (eg: Payment 2 and 3) to the TBL_Balance Sheet as above. So the DDRs are kept seperate and I only add them to the main table when required per month.

[OBP]

I am not sure what version of Access you are using but the majority of versions do not have a "running sum" function in the query design functions.
I have some SQL that is supposed to do it but it is not very reliable.
However both Forms in continuous mode and Reports have a running sum ability, so if you want to print the output a report is best and if you want to view it a form is best.

[Spyhunter]

Im using latest Access. Where is the running sum ? How can I use this in a form please.

[OBP]

Sorry for the delay, my wife has been using the computer and I have been doing some research in to your original idea of using a query.
Which I now have working, please see the attached database.
If you want to have a form based VBA version let me know.

[Spyhunter]

Thanks thats helpful. What happens if the hardware id is out of order though? Like my DDRs are, they are not linked in ID order, etc

[OBP]

Can you create some data in a table and post it on here for me play with?

ps That query will also work on another query, ie a query that orders the DDRs first.

[Spyhunter]

OK I have changed the query to this...
Total: DSum("[PayeeAmt]","TBL_Balance","DDRDate]<=" & [DDRDate])
Where PayeeAmt is my figure, TBL_Balance is my table and DDRDate is the date field within the table.
But it shows '#Error?'

[OBP]

You have a bracket [ missing form the first DDRDate.

[Spyhunter]

I have it working but no data is shown. I have attached the database -- no personal data within - and would like it to have a balance i type in the main window (or is linked to another table) that then pulls into the Form, so it may have 5 at top and the fist DDR is £1 so it would be £6 for the first line, does that make sense?

27239

[OBP]

OK, I have looked at what you have in your query and I need some clarification.
You want the output in Date in order?
If so you can't use Dsum, because it uses the table records in the table order.
If you use the ID it gives the correct answers, but not in date order.
Then you have the requirement of the Form, so if you want to restrict the data and use Dsum you will have to use a query to condition the data first.
I have an alternative working which uses a module and a public variable in the query, howver before the query is run each time the public variable needs setting to zero.

So can you explain what how you want the Form to interact with the query and also confirm you want the output in date order?

[Spyhunter]

Thank you ever so much.
To clarify the order should be DDR Date as I can add new DDR's next month, etc or half way throiugh the month that will be added to Balance List.
There is a Query against the SubForm (BalanceSub).

My DDR Form adds (copies) the DDR List (that changes per month, etc) to the Balance Sheet. There I want to be able to calculate the outstanding (not paid ticked) outgoings as well as add new adhoc outgoings to the Balance Sheet and have the running sum all the way to the bottom. I would like to create a small table that has the latest balance 9as of date) so I type this in at the top and it uses this as a starting point for the remaining of the balance sheet. So if I have £100 starting balance, then if I had 3 DDRS listed it would look like this:
Starting Balance: £100
RunningSum
DDR 1 - £10 £90
DDR 2 - £10 £80
Adhoc added - £-5.00 = £75
DDR 3 - £90 -£15

I do this as an excel form at the moment but would have liked to create it as a database as I can add other options into this such as Debts, etc etc as a larger storage of my finances.

Hope that makes sense. I appreciate all the help.

[OBP]

I thought you might be an Excel user due to your terminology.
The query with module works OK with the form, but I can't get the public variable to reset to zero if you want to run the form again with a different query parameter.
Is the database that you posted the basic design you are working with?
As you seem to be carrying out duplication that may or may not be necessary.

So how do you intend to use the Balance form? will you manipulate the data more than once?

Have you considered a Tabbed form rather than a menu, as it is more like EXcel to work with?

ps Looking at your table designs you do not need the Balance table as you can work with the DDR table, unless you are going to clear the DDR table for some reason.

[Spyhunter]

Thanks. Im open to suggestions at this stage.
The database was a quick mock up of how I want things to work before progressing (and learning as I go). What info is duplicated as I want to keep the DDR table data seperate from the Balance table. The Balance only gets queried on that form, but as the DDR and adhoc records get added this will mean the data changes, but the query does not. Hope that makes sense. I have created Tabbed Forms before which was one of my ideas after I got this first form working, but Im open to suggestions in any form and appreciate it. :)

I suppose I could merge the ddr table with balance and have a field that only shows on balance (and thus calculates using the query runningsum)

[OBP]

I like your DDR form, where you can select the months, but you should also select the year, so then your hard coding of the from & too dates will be a problem. You will need to change it so that it incorporates the chosen year, which can be a combo, or you could use a combo to choose both the month & Year, you would then only need one button and one lot of code.

Althought the data in the DDR and adhoc change it doesn't stop you using the Balance form with the DDR table, it is all about the queries that you use.
Querying other queries is very powerful for filtering the data the way that you want it.

I can advise you but I am not sure all the things that you have in mind.

So what is the Balance table designed to do?
Do you enter an amount in there and reduce as money goes out?
Or does it reflect what is in the DDR?
Is DDR Direct Debits?

[Spyhunter]

In reply:
So what is the Balance table designed to do?
Keep track of in and out out, which an be DIrect Debits (DDRs) or adhoc (inserted into the Balance Sheet manually)

Do you enter an amount in there and reduce as money goes out?
Yes

Or does it reflect what is in the DDR? No DDR is just an outgoing (it uses TYpe (+ or -) to calculate whether it should add or subtract the payeeamt

Is DDR Direct Debits?
That is correct.

[OBP]

OK, I need a reset here, back to your original post.
Would it make sense to have the Balance table actually have the balance in it.
ie use VBA to take the value in the DDR table and add/subtract it from the Current balance in the Balance table with the action date in both tables so you know which DDRs etc have been actioned.
Also have a Deposit Table that does the same, adds it to the current balance in the balance table.

This will give you a simple query that you can filter.

I don't know how you get the DDRs in to the DDR table are they imported?

[OBP]

Anyway, here is the database with the Public Variable and the query with module, which you may find useful in the future.
The module is in the column and form field expr1.