Query/report help

[scs]

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.

 

[theDBguy]

Hi. Welcome to AWF! To properly assist you, please consider posting a copy of your db with test data for analysis.

 

[June7]

Most anything can be accomplished with enough code, perhaps writing data to temp table.

Without knowing actual data structure, can't be more specific.

Regardless, I doubt the solution will be simple. Does look like CROSSTAB queries would be needed. Start with review of http://allenbrowne.com/ser-67.html

 

[scs]

@June7 - thanks for that, I haven't used crosstab queries before. But maybe I can modify the report based on what crosstabs can do for me. (Sorry I can't post the db - be too much work to remove proprietary info I've already added). Also I think I can do some more brainstorming on simplification. Instead of forcing Access to pull permit limits and stack them or not, I could create a combined total field on its own and simply use that as the number against with withdrawals are checked...

 

[scs]

@PatHartman - ok I will look at the date suggestion, thanks. I have multiple tables. I definitely don't want a spreadsheet, just was hoping for something similar to a spreadsheet for the monthly/annual report. The bulk of the data portion is the water use entries. There are about 100 sources, each with at least one permit. Water usage is entered daily, per source. For each date of usage entry, there is a volume, use type, and user. I took another screen grab, as well as relationships, perhaps that will better show. I definitely appreciate the help, sorry if I'm not quite speaking the right language as I ask the questions.

 

[scs]

@PatHartman no I had it from the start. I originally had the correct PK/FKs as you mentioned, but somehow after all my fiddling I screwed them up. Anyway, fixed it. Exporting to an excel file may be the best route as you say, I'll explore that a bit. Thanks.

 

[sxschech]

This is true:

Access doesn't have the size to page feature which Excel has

There is an alternative buried in Access that does allow scaling of reports, requires selecting an appropriate printer driver and requires trial and error to determine the percentage scale number to eventually get the report to "fit on the page".