I'm struggling to complete what should be a simple calculation on a report.
I'm using Access 97 on Win 98 to create a database that keeps a running
inventory on training materials. Various stores (called Locations) have to co-share pieces of various types of training equipment. I need to keep track of how many pieces of each type of equipment is at each Location and how many total pieces of each type of equipment we have in our system.
The relevant tables are Equipment, Location, Hand Receipt and Transaction. Location has a one-to-many relation with Hand Receipt. Hand Receipt and Equipment both have one-to-many relations with Transaction.
Each Hand Receipt has two Locations, indicating the Location receiving the equipment and the Location sending the equipment. The quantity of equipment in the Transaction will increase the inventory at the receiving Location and debit the inventory of the sender, but the total number in the system remains the same.
I also need the ability to add new pieces of equipment (requisition order
from the home office) and remove equipment that isn't serviceable.
OK, so how is this report calculated?
When an inventory report is generated, it needs to show how many pieces of each type of Equipment are at each Location and how many pieces are in the system total.
The Hand Receipt shows a transfer of equipment. Say Location A receives 10 widgets from Location B. The report must show that A had an increase of 10 widgets and B now has 10 less. However, the total number of widgets in the system is still the same. We merely transferred 10 widgets from A to B.
New equipment can be purchased and unserviceable pieces of equipment can be removed. This changes the total number in the system. For instance, our home office will requisition new equipment to issue out to other Locations. The home office has to be included in the inventory report, so I think I need a "dummy" Location for purchases and broken equipment that is excluded from the inventory report.
I thought I could build something with the Expression Builder like (pardon my pseudocode) =Sum[quantity]-Sum[handreceipt!locationFROM!quantity] would work. The idea was to add up the total quantities for a given piece of equipment at that Location, and subtract out I haven't gotten this to work...
the table that has the location what are the fields. if they are the location, inventoryin and inventoryout (not your names but my for less confusion) you can create a query that will group by location with a sum of each area. Then create a second query that will attach the location number with the location name and have these fields spit to a report. The report will have your normal headings etc.
the calculation on the report would be what you have to determine total at that location . The report footer then can contain the sum of all the locations (your sums added up).