Beginner creating accounting system (1 Viewer)

[timkim]

I'm new to Access and I'm trying to create an accounting system. I can usually find answers on Google but I'm having a hard time figuring this problem out. I'd appreciate any help with creating an Access 2010 web database that will be hosted in SharePoint 2013 so I'm going to be creating it in an Access web database.


My company hires contractors from contracting companies and we pay $x per hour. We estimate how many hours the contractor works on a project and we make "work orders" for each project. We have a pre-approved budget for each company.


I'm trying to create a system that allows the user to basically create new records containing work order info like the dollar amount spent. Each time a new work order is added, it will subtract the work order money from the approved budget.
For example:

1. Budget1 = $25,000
2. User creates WorkOrder 1 for $5,000.
3. Budget1 = $20,000
4. User createswork order for $5,000
5. Budget1= $20,000

I created a two tables with a project id column for both tables. In the budget table I created one record with project ID = 5000 and approved budget = $25,000. Then in the workorder table I created two records with project ID = 5000 and WOtotal = $5,000 (in each record). Then I created the query and selected both tables. Then I linked them by clicking on projectID in the budget table and dragging my mouse to projectID in the workorder table. However, the "Totals" button on the "Design" tab is grayed out. I tried using that formula but I got an error message that read "The expression you entered is not valid for Web-compatible queries." Does this not work on an access web database?

I'd appreciate any help or advice. I'm not even sure if I'm going about this the right way.

 

[CJ_London]

Does this not work on an access web database?

'Fraid not - neither GROUP BY nor DISTINCT is supported

Search the web for 'aggregate access 2010 web query' and you will find plenty of posts on the subject, including some possible workarounds but they may not suit your needs

 

[timkim]

I think I may have been going about doing this the wrong way.

This should kind of be like an inventory system similar to how the Northwind Traders database works. There's a product with stock (approved budget) and when you create the work order, it subtracts from the approved budget. You can also replenish stock (approved budget) by creating a purchase order that adds to the budget. The only difference should be is that it has a dollar value rather than a quantity level of stock. I've tried looking at the macros and events in the various forms in the Northwind Traders template but I'm having trouble with it.

I'd appreciate if anyone could help me or point me in the right direction.

 

[Rx_]

You are going to find the Web version is very limited in what it can do with its absence of a programming language.
We had a great presentation of at the Denver Access Users Group.
They used Access Clinet (with VBA) to create a support application with linked tables to Microsoft Azure (SQL Server on the Cloud).
The Access Web version tied to the same Azure DB was able to provide basic form lookup and limited data input. The Web version has limitations to no code, just basic Macros.
The primary application was the Client version had the full rich support of VBA.

That is just a suggestion to consider.