MSAccessFilter: How do I create a printable report in MS access, with a click-through interface to choose my data?

December 3, 2009 6:26 AM

MSAccessFilter: How do I create a printable report in MS access, with a click-through interface to choose my data?

Good morning,
At the office, we're using an access database that everyone is afraid to touch, but at the same time, it's not quite doing what we want it to do:

I want to take information from two or three tables (the information's all linked together), then print a summary of the day's activity (one of the fields in one of my tables is a value for the date).

---------------------------------------------------------------------------------
Can I set up a form so that all my coworkers have to do is click "create report", then it'll give them another form with a calendar to click on, maybe a drop down box of another variable or two, click "create", and a report come out that I can print/save/distribute?

(Apologies if this is basic-- my google-fu has failed me, and I think it's because I'm not phrasing any of this correctly-- this strikes me as something that should be relatively easy to set up, though probably time consuming)

posted by Seeba to Computers & Internet (4 answers total)

The tip I've learned from database design using access (and it applies to cars and such too) is that the easier it is for the end user, the harder it is for the designer.

Access gives you limitless queries, you can make a bazillion of them. Do that. Query design is super simple for MOST stuff, it only gets hard when you start comparing data and giving if/then's.

I would set individual queries for what I wanted, and then, depending on the application, either pass variables to it from a form (like date ranges, for example) or simply just have that many queries.

But the answer to your question is "yes". The "combo button" is what you're looking for, and the wizards will walk you through opening forms/reports/next record/etc, effectively they're action buttons that do or don't interact with a data set.

Seriously though, a query for MONDAY and TUESDAY etc is awesome. I can send you over an ugly access 2007 db that includes a form that lets users pick a date range and then gives them the option of opening the query datasheet or printing individual reports that meet the date range, if you want to see how that much works.
posted by TomMelee at 7:23 AM on December 3, 2009

At the office, we're using an access database that everyone is afraid to touch, but at the same time, it's not quite doing what we want it to do

While I do so abhor Access, I can't tell you how much money I've made off people in exactly your situation! A mixed blessing, in many ways.

That being said, the best solution for you would be to:

• Create a form in which one enters parameters for a query
• Create a query that takes those given parameters, and link it to the report you want to produce
• Make the form invoke the report once all the parameters are entered.

Thus, first, decide what parameters you need in your query. Things such as "date" or whatnot. Create a form with a field for each parameter, and make sure you name your fields intelligently. (Note that, if you want a calendar to pick a date, if you're using Access 2007, if you set the data type of a textbox to "Date", you can set the "Show date picker" property to "Always", and a small calendar is going to appear next to the textbox when you enter data into it. If you're not using Access 2007, you're outta luck.) Make OK and Cancel buttons.

Then, make a query that obtains the data you want to show in your report. I can't tell from your question whether you already have this query or not. If you don't, make it using the Access query thing, and set your criteria to the values entered into your form like Forms!ReportGenerator!Date, where ReportGenerator is the name of the form you just created and date is the name of the field containing the parameter you're referencing.

Once that's done, make a report using the fields from the query you just created. No magic needs to happen: just stick the fields where they need to go, as you would with a regular, non-magical query.

Finally, set it so that your Form invokes the report when the OK button is clicked. You do that through the OnClick event property. The report will use the query to obtain its data, and the query will obtain its parameters from the form.

If you want more info on this, check out this link. There are a lot of really good Access tutorials which are appropriate for a moderately skilled user.

On preview: I must respectfully disagree with TomMelee's advice. On the one hand, yes, it is trivial creating a billion nearly identical queries. The problem happens when you want to modify the queries and you have to trod through a plethora of them, changing each one manually. Inevitably, you'll miss one, or you'll make a mistake somewhere, and you might not notice this mistake immediately. An elemental bit of advice in programming is DRY, and modifying an Access database is as close to programming as you can get without starting to watch obscure Anime.
posted by Mons Veneris at 7:36 AM on December 3, 2009

Does it need to be calendar and drop down box? If it just needs user input, and it doesn't have to be pretty, I suggest using a parameter query. When you open a parameter query, a dialog box will pop up asking you to enter a value and then the query will run using that value.

You write them the same way you would write a regular select query, except in the variable field, you enclose text in brackets. This text is what will display in the box and prompt the user to enter a value.

For example, "[Date:]" will prompt the user to enter a date. You can get fancy with the criteria as well, like ">[Date:]" will give you everything greater than the date you enter. The best part is that you can have parameters for the user to enter. Just put "[Date:]" in the Date criteria and "[Name:]" in the Name field and they'll be prompted for both.

The users will have to spell the name exactly right, of course.
posted by soelo at 8:45 AM on December 3, 2009

"The best part is that you can have *multiple* parameters for the user to enter."
posted by soelo at 8:46 AM on December 3, 2009