Hi, self-taught with access and my first time posting. This forum has helped me a lot! I'm building a database that tracks water usage from lakes (sources). For the purposes of my questions, I have the following tables: Sources, Permits, Withdrawals, and UseTypes. I am ultimately trying to create a report that will need to be in a spreadsheet format - with each source listed as a row; and the columns consisting of a source's permits, limits, monthly withdrawals, and current-month (i.e. user-defined date range) withdrawals sorted by use type. (I hope that makes sense-see attachment).
A few questions as I lead up to this.
1. Can I have the user enter a specified date range ONCE and have it apply to multiple fields? E.g. user will enter 6/1/19 through 6/30/19 and I want:
a) all sources with permits effective through that end date (columns O&Q in example, though permit dates aren't shown)
b) withdrawal totals for previous year Oct-Dec (column AD)
c) monthly withdrawals through June (column AE-AJ)
d) withdrawal totals for June grouped by type of use (columns AQ-AU)
Another goal:
2) Some sources have multiple permits. Sometimes they are "stacking" limits (Permit A allows x gallons and Permit B allows y gallons, so total allowed is x+y) and some have parallel limits (Permit A allows x gallons and Permit B allows x gallons, total allowed is x).
e) is there a way to force this tiered calculation in access? It is totally source specific. So it's necessary to stack some but not all. Ugh sorry this sounds so terrible lol.
All of this is currently accomplished in another DB (Hach WIMS if any of you know that one), using cell equations, similar to excel. How do I accomplish something like this in the Access world? The current database is terribly cumbersome and trying to move away from it into something more sustainable, and I fear I'm in over my head! HALP.
A few questions as I lead up to this.
1. Can I have the user enter a specified date range ONCE and have it apply to multiple fields? E.g. user will enter 6/1/19 through 6/30/19 and I want:
a) all sources with permits effective through that end date (columns O&Q in example, though permit dates aren't shown)
b) withdrawal totals for previous year Oct-Dec (column AD)
c) monthly withdrawals through June (column AE-AJ)
d) withdrawal totals for June grouped by type of use (columns AQ-AU)
Another goal:
2) Some sources have multiple permits. Sometimes they are "stacking" limits (Permit A allows x gallons and Permit B allows y gallons, so total allowed is x+y) and some have parallel limits (Permit A allows x gallons and Permit B allows x gallons, total allowed is x).
e) is there a way to force this tiered calculation in access? It is totally source specific. So it's necessary to stack some but not all. Ugh sorry this sounds so terrible lol.
All of this is currently accomplished in another DB (Hach WIMS if any of you know that one), using cell equations, similar to excel. How do I accomplish something like this in the Access world? The current database is terribly cumbersome and trying to move away from it into something more sustainable, and I fear I'm in over my head! HALP.
Attachments
Last edited: