Ensure entries are valid and do not exceed identified value in related table

[W_Green]

I have limited experience with Access but have been able to figure a few things out just through YouTube. I really hope to learn more from this site!



I know it is possible to identify the funding for a project in one table and connect it to another table which has the project's invoices. My tables are linked to spreadsheets I save from reports from our accounting system, and I can link the funding of the project from this report. But my concern is whether there is a way for me to restrict the sum of the invoices to the total funding allotted to the project it is connected to. GOAL: I do not want to create an invoice which would over-bill the project, you see? I have other steps in place to ensure this does not happen, but, I manage a database of thousands of projects and may forget to adjust the projects funding through the other fail-safes, given there is a change in funding. By introducing this new fail-safe, my Access will update from the reports I pull from the accounting system.

Thanks for all the input: any is appreciated!

[CJ_London]

whether there is a way for me to restrict the sum of the invoices to the total funding allotted to the project it is connected to.

I'm sure there is a way, but without knowing your table structure or process, not possible to suggest a way other than in the broadest of terms - i.e. add all your invoices together and deduct from the project funding budget and ensure your next invoice is less than or equal to this figure

[W_Green]

OK fair enough, I can do that without Access, and I do. But I am afraid that because I do not have a lot of experience/ knowledge of Access, I may not be able to explain "structure or process," as you put it. I was able to search Access IIF statements/ expressions, but I do not know how to apply it, though it is the only way I would conceive of accomplishing my goal, given my lack of knowledge. Would I have to run a query every time in order to identify whether the invoices exceed the total value of the project, or, if when I am entering the invoice value in the database, is there a way to identify it immediately in the present?

Bear with me here: I have two tables (one for the projects and the other with respective project invoices) connected through a relationship using the project number. This relationship is such that in the projects table, I can select a "+" symbol to expand the project relationship to identify the project's specific invoices (I believe this has to do with the project number being the primary key of that table). A couple of columns, in the projects table, are linked to spreadsheets I have saved on our share drive. I have learned how to go about updating these project columns using an update query. Please excuse my explicit lack of knowledge if this description is not what you meant.

Thanks Again.

[CJ_London]

With the additional information provided I would suggest you need two queries

the first is a 'group by' query for your invoices - it will be something like

SELECT ProjectID, Sum(InvoiceValue) as ttlInvoicevalue
FROM tblInvoices
GROUP BY ProjectID

We'll call this query 'qryInvoices'

Next you need another query which will be something like

SELECT tblProjects.ProjectID, tblProjects.ProjectValue-ttlInvoiceValue AS AvailabletoSpend
FROM tblProjects INNER JOIN qryInvoices ON tblProjecty.ProjectID=qryInvoices.ProjectID

this tells you how much funding you have left on the projects

How you apply this information depends on how you want to use it - for example are you using access to generate and invoice? if so then you would have a form to do this and you would use a variation of the query to get the figure - this could then be used to validate your input to ensure you don't enter too high a figure. Again, perhaps your invoice has one value or multiple values - you need to explain. Or perhaps you have invoices generated in another system so you need a different solution

[W_Green]

My invoices are generated in a separate accounting system. I use access to help track the invoice receipts, etc.