Form for profit and loss statement with sums and input

[Klabbe]

Hello everybody! I've searched for an answer for this but maybe I don't know how to specify my problems properly, but I would Think this is a common task for Access forms...


I want to create a P&L where all rows are present regardless if there is a value or NULL on that row. I also need sums in the form (like "Total Revenue"). The users must also be able to adjust the actuals (it's for calculating tax), so a column or two where amouns can be added or deducted from each P&L-row.
So I tried using unbound text-boxes where I put "DSum" and then a unique argument for each row, but this is pretty slow and when I have some 30 rows on the form it doesn't seem to work at all.
Any suggestions how to do this in a smart way?
Thanks!

[Bob Fitz]

IMHO

Before you concern yourself with trying to create a form, you should consider what tables and fields you will require so that you can store the data which will eventually appear in the form.

[Klabbe]

Yes, ofcourse I have set up tables and they even have fields :-)
My question is how to set up a form to display the data, even if one account from the table holding accounts would have a NULL value. And I want to be able to add a row with a sum of a couple of previous rows, like you would find on a profit and loss statement. And on top the users must be able to make adjustments, not on the data (actuals from another system) but in the column next to the actuals.
So maybe it's a mix of a report and a form, but in a report is it possible to enter data?

[CJ_London]

even if one account from the table holding accounts would have a NULL value

use a left join between the accounts table and the transactions table - assuming that is what you have

And I want to be able to add a row with a sum of a couple of previous rows

might be possible, but impossible to say how without your table and relationships design

And on top the users must be able to make adjustments, not on the data (actuals from another system) but in the column next to the actuals.

that goes against accounting practices and almost certainly not possible as such, you would need a form to add to the underlying data and requery your P&L form

but in a report is it possible to enter data?

no

[Klabbe]

What about the Dsum, is it considered a bad idea to use it on a bunch of rows in a form?
I don't know if this is any good, by I'll add a couple of screen shots from my query and form...
Thanks for your patience!

[CJ_London]

using domain functions like dsum in queries is a bad idea, using dsum in a form is OK, but will be slow.

I note your query is already grouping on all fields - the problem you will have is if Act_utfall has the same value more than once, with all the other data being the same, you will only see it once. Recommend remove the grouping if you are going to use dsum in your form. Alternatively change the group by to sum for the Act_utfall column in your query and use dlookup rather than dsum in your form.

Also, give a control name a numeric name ([5]) is a bad idea and is likely to cause you problems down the line

[Klabbe]

Thanks! I didn't know that about the grouping, just took it as it is the default in the query, after I had tried to summarize too and forgot to remove "function". I removed it now and it takes away some other behaviout in the query I didn't understand :-)
Also thanks for the tip on control name, I had some idea to use numbering to be able to loop somehow, but I'll change that.
I will try more with the DSum, because it gives me lots of possibilities to have sums and move the rows around to my liking, if it goes too slow, maybe I can set up a table query so the form only has one period too search in at a time, if that can speed up things a bit.

[CJ_London]

I had some idea to use numbering to be able to loop somehow

use a name like txt1, txt2 etc

then to loop

for i=1 to ??
   debug.print me("txt" & i)
next i

[Klabbe]

Good idea, thanks!

use a name like txt1, txt2 etc

then to loop

for i=1 to ??
   debug.print me("txt" & i)
next i

[ssanfu]

You should NOT to use spaces in object names!
Also, you should NOTuse special characters or punctuation in object names (you have a comma in some field names). If you need/want to separate words, use the underscore.

[Klabbe]

Thanks, I’m aware of that, will make a note of cleaning things up!