Query/report help (1 Viewer)

scs

New member
Local time
Today, 08:15
Joined
Aug 7, 2019
Messages
4
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.
 

Attachments

  • WaterDB.JPG
    WaterDB.JPG
    47.2 KB · Views: 160
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:15
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! To properly assist you, please consider posting a copy of your db with test data for analysis.
 

June7

AWF VIP
Local time
Today, 07:15
Joined
Mar 9, 2014
Messages
5,423
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

New member
Local time
Today, 08:15
Joined
Aug 7, 2019
Messages
4
@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...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:15
Joined
Feb 19, 2002
Messages
42,970
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:
Yes.
1.Add two unbound controls to your form. Set their format to short date so that Access will know the control will hold "date" data and give you a calendar if you want to pick the date.
2. The form MUST be open when you run the report or open the form.
3. Use the form fields in the criteria:
Select ... From YourTableOrQueryName
Where PermitEndDate Between Forms!yourformname!txtFromDT AND Forms!yourformname!txtThruDT OR SomeOtherDateField Between Forms!yourformname!txtFromDT AND Forms!yourformname!txtThruDT

Beyond that I, like the others, don't know how to help you since you are posting in an Access forum but seem to be asking about a spreadsheet.

PS, your table should not look like the spreadsheet you posted. If it does, you should reconsider using Access. Access is NOT a spreadsheet and it does not work like Excel. You will be veeeeeeerrrrrrrry unhappy with Access if you think it is a spreadsheet. Access works best (like all other relational databases - this isn't just an "Access" thing), if you properly normalize the schema. Based on a cursory examination of the spreadsheet, you will need a minimum of 5 tables and rather than having multiple columns for a single type of data, you will have multiple rows. So Limits would be one row for Winter and another for Summer for each year. Same for Withdrawals. Usage will be one row per month or per day or per hour depending on how detailed you keep your records. Type of use is hard to figure without more information. Seems like you need to keep the usage, by type to fill out those columns.
 
Last edited:

scs

New member
Local time
Today, 08:15
Joined
Aug 7, 2019
Messages
4
@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.
 

Attachments

  • WaterDB2.jpg
    WaterDB2.jpg
    94.6 KB · Views: 162
  • WaterDB3.JPG
    WaterDB3.JPG
    40.2 KB · Views: 128

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:15
Joined
Feb 19, 2002
Messages
42,970
Out of curiosity, did you just make that schema based on my suggestion or did you always have it? UseTypes cannot be related to Withdrawals PK to PK. Each withdrawal would need a usetype and there could be multiple withdrawals per day. Same comment for Users. The user would need to be a FK in the Withdrawal record so you could identify the type of withdrawal and who it went to.

You can make a report that looks like the spreadsheet. But, due to the difficult of formatting Access reports with this many columns, you might just create a query with the data and export it to excel which is better at printing this wide a report.
 

scs

New member
Local time
Today, 08:15
Joined
Aug 7, 2019
Messages
4
@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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:15
Joined
Feb 19, 2002
Messages
42,970
You should try to make the report with Access. You might be able to change the format to make it not so wide and that would work. Access doesn't have the size to page feature which Excel has so wide reports don't work well unless you can fit them in 11 inches or in 14 if you are using legal size paper.
 

sxschech

Registered User.
Local time
Today, 08:15
Joined
Mar 2, 2010
Messages
791
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".
 

Users who are viewing this thread

Top Bottom