Report/Query Format recommendation.

[deltaair]

This isn't that much a technical question as much as I need a recommendation for setting up queries and reports in a database.

The main table consists of 3 fields, Date, Shift, Area. and then fields for various numbers.

I need to find the best way to set up a query / reports, that calculate stuff from these numbers, but provide the user an option to choose Daily, MTD, or YTD, as well choose Shift and Area.

Any help/recommendation would be much appreciated.

 

[THWatson]

deltaair
Before I mention an approach, I want to suggest that you should rename your field called "Date" to "MyDate," or something such as that. "Date" is a reserved word in Access. (unless you were just using this as an example, and your actual field name is different)

Here's an approach...
1. Create a query, based on your table, and bring in the fields you will want for your report.
2. Create a report based on the query.
3. Create an "unbound" form that will drive the process. Let's call it frmDateSelect. On the form, put two unbound text boxes. Let's call them txtStartDate and txtEndDate. Also on the form, put a command button that opens the report in either Preview or Print mode.
4. In the query, in the "Date" column, put Between Forms!frmDateSelect!txtStartDate And Forms!frmDateSelect!txtEndDate

Then when you enter the dates to choose for the report in the two text boxes on the form, and press the command button, you will get data for the dates you entered.

This is as far as I can go without knowing more about your set-up. But hope this gives a push in the right direction.

Tom

 

[dhookom]

I generally create a form that allows users to enter criteria values into text boxes, combo boxes,... I provide a list box of all the titles of reports that are available. Users enter criteria values into controls, select a report, and click a button to open the report.

Code in the On Click of the open report button builds a where clause that is used in the DoCmd.OpenReport method.

I actually take this a step further with a table that identifies which report is filtered by which controls. When the user selects a report from the list box, there is code that loops through the criteria controls and either enables or disables them based on records in the table. My where clause builder ignores controls that are either disabled or have null values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

My solution is similar to Tom's however I try to avoid hard-coding references to controls in my queries. I like to be able to run reports from multiple forms. There are times when building a where clause won't work since the fields you might want to filter on are not in the report's record source. This is particularly true with reports from crosstabs and filtering subreports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]