I have a product inventory database I've been using for many years. I'm working on a SharePoint version so access by multiple users is easier. My old database has been tweaked and duct taped together over the years, so I am exporting out the current data and using that to make a fresh start.
One of the main functions of the app is to tally a monthly inventory and return totals on four departments. Not every item is on hand in all locations when any given inventory is taken.
In my old DB, I had an append query that would take all the items in the item table flagged "active" in the master list and append them to a new table with fields for various inventory locations. I know null values prevent calculation, so each field had a default value of zero, and I had it set up to suppress the zeros in the forms based on that table. Then the user could input item counts in the form, unwittingly replacing the zeros as he goes.
I'm trying to accomplish the same thing, but I am hitting two obstacles.
1. Since append queries are not available in Access web apps, I can't use the same method I did before to start with a "clean sheet" each month, with every field pre-populated with zeroes. I could include the inventory count fields in the item table, but I also can't do an update query to change the values back to zero each month.
2. Even if I had zeros in all fields, I can't figure out how to suppress them in the datasheet view I have set up to enter the inventory.
I feel like I am missing something basic.