sum net amount on report if paid ticked (1 Viewer)

[rainbows]

in the last few weeks i have tried to create an invoicing module within my database this is the report i have been trying to create to see if it all works ok or not
below the are 3 orders of which you can see the first one there are 3 line items to invoice of which the system says i have the problem i have is that the payment outstanding allthought looks correct that is becauce i have ticked the paid box so i only have the 12330 outstanding

the second order you can see i have invoiced it but again not been paid yet. when i tick that box the payment outstanding will go to 0 as it does on order no3
the reason the order no 3 is in orange is because the order has only been half full filled ie 20 required and only 10 invoiced

the problem i have is that on order one if 2 tick boxes where not ticked it only show me the 1st record ie 12300 and not 20500

the second screenshot shows you the total for them 3 invoices ie 28700 how can i get it to add up all the ones that are not ticked instead of just the first one

thanks
steve

 

[The_Doc_Man]

I am not about to delve into the details here, but that doesn't mean I can't offer advice.

There is an old rule that is relevant here, one of the "Old Programmer's Rules" that I sometimes quote. Rule #2 from that list is that Access will not tell you anything you didn't tell it first, or at least tell it HOW to find that answer. So let's look at the thought process of how to get Access to tell you something you want when and where you want it to.

How do you determine the sum of something in Access? There is either a DSum function in VBA OR you can write a summation query that uses the SQL "SUM" aggregate modifier. Now, how do you make the summation operation selective? You give it a way to decide the difference between a record you DO want vs. one you DON'T want. But mechanically, how do you do this? In DSum, use a criterion expression, or in a query use some comparison in a WHERE clause.

Therefore, this starts as a simple process of taking a summation of records where your checkmark field is checked. The question then is WHERE do you want that to appear and are there other considerations?

In a detail area, you would have a difficulty of having only on summation in a multi-line area, but in a header or footer, it is far easier to just put a DSum as the value of the control in question and just note that the criteria for the DSum is related to the criteria that provides the header's "break" of data flow, i.e. the header is related to a grouping, so the discriminating value for the group (the GROUP BY field) would have to be part of the criterion. The "WHERE checkmark=TRUE" isn't enough. You will also need " AND group-selector-field = <current-group-selection-value>" (and if there is more than one grouping involved, all of the criteria for each header or footer that encloses the place you want this result will have to be part of the criteria clause.

 

[gemma-the-husky]

It might work for you in your specialised circumstances, but generally the structure of a sales ordering system needs more tables

Not every order is necessarily delivered in one consignment.

Not every consignment is paid in one instalment.
You may issue credit notes to adjust invoices.

So you really end up needing multiple tables

Order header
Order items

Invoice header
Invoice items

Cash receipts
Cash receipt/invoice matching table.

Before you start thinking about processes, can you confirm what tables you actually have to manage your sales.

 

[rainbows]

this is the sales header and sales details forms both have seperate tables

this is the product and Bill of materials for each product also showing is the stocklist form all 3 have tables

this is my purchasing form and header for any item is require to make the sales products both
have tables

this is where i book the parts into stores and inventory with goods revived inspection this can be seen on the bottom form when i tick the stock change field

both have tables

this is where i am trying to create the invoice module . the top form is from the orders . the middle form is where i create the header for the invoices for this order weather 1 on 2 or more shipment. the bottom forms is where i enter the price and quantity being invoices both have tables


i am not that good at access but with thebhelp from everyone on here i have managed to get this basic database working albeit a bit slow

thanks

steve

 

[rainbows]

although i think not a good way of doing it but i used another 2 queries and put a subreport into my report and it worked