Hi all,

I'm new to Access and to this forum, but in a short time have developed a passion to learn about and use MS Access. I am using version 2007.

I've developed two tables in Excel and imported them into Access.
The file attached is a "test" model, as the real file contains close to 10,000 entries.

I'm looking for ways to make the reporting easier. Some things I am struggling with are custom reports.
For example, I will often need tailored reports which only show the data for "Location = Building 1", or when "Location = Building 2" AND "Construction = Yes".
With my limited knowledge, the only thing I can do is create individual queries and link each query to its own report. This would result in hundreds of queries and reports, so I believe there must be an easier way to do this...

Also, is there a possibility to create data reports with a graph or pie chart embedded in them?

Looking at the sample data, are there any things I might want to change structurally to the database to make it more useful? I am open to any suggestions. Please keep the explanation simple

From the database you posted, it's hard to provide advice. The db only contains two tables: no explanation on the business it's supposed to modelize, no relationships, no queries, no forms, no reports, no modules. However:

1. You'd better stay away from including spaces or other non-alphanumeric characters in the names of objects in your database, don't use reserved words either. See for instance:
Microsoft Access Naming Conventions
Microsoft Access Naming Conventions - D. H. D'Urso and Associates
Naming Conventions for Microsoft Access

2. I know that memory and disk drives are cheap these days but there is no reason for defining almost every column as Text(255).

As an example, the table "BUILDING PUNCHLIST TEST" (I would name it "BuildingPunchlistTest" or Building_Punchlist_Test", from 1. hereabove) has a column named "Punch Closed?" (I would name it ("PunchClosed" or "Punch_Closed", or even better "IsPunchClosed" or "Is_Punch_Closed"; you're really looking for troubles with this one ) is defined as Text(255) while it obviously contains Boolean (or Logical: True/False, Yes/No) data. As it is, you can type everything you want into it as no validation rules are defined.

There are other examples, such as in the table "LOOPS PUNCHLIST TEST" where several columns are defined as Text(255) ("Closed By", "CAT", "Construction", "Drawing") while a Date format ("d-mmm-yy") is defined for them, which makes no sense.

Sooner or later, something will go wrong there.


3. Don't spend too much time defining formats in the table definitions. Tables are for storing data, not for displaying it. In a real application, users are not supposed to open a table directly: users work with Forms to view (select) create (insert), change (update), remove (delete) data from the tables, and with Reports for extracting or publishing them.
Forms and Reports are the objects where a format or any other required data transformation should be used.


4. Back to your original question, there are several methods to open a form or a report on the filtered selection of a data set, based on one or several criteria. Some of them are "native" in Access (search in Access help for DoCmd.OpenForm and DoCmd.OpenReport), while others dynamically change the SQL statement of a query before opening or re-opening it.

Thanks Sinndho,

I couldn't provide too much data as much of it is company proprietary so I only showed some dummy entries.

I've used your advice on points 1 and 2 and modified my database accordingly.
Point 4 is not yet clear to me, I tried searching but didn't come up yet with what I need. I'm sure that after some more research it'll become clear to me.

Thanks again

Let's suppose that you have a form ("Frm_CustomerData") that is bound to a table "Tbl_CustomerData". This implies that the RecordSource property of the form holds the value "Tbl_CustomerData". When the form is open, it gives access to every row of the table to which it is bound.

The most basic way to open this table using VBA code is:
Let's now imagine that there is a column named "Country_Code" in the table "Tbl_CustomerData" and that you want to open the form "Frm_CustomerData", but only for the customer rows that have the value "UK" stored in the "Country_Code" column. One possibility consists in using the DoCmd.Openform method with an additional parameters specifying that the RecordSource of the form must be restricted ("filtered") to rows matching a certain criteria (Country_Code = 'UK'), like this:
There are several solutions to reach the same goal but this one is the simplest and the same technique can be used with the DoCmd.OpenRecord method to filter a report when opening it.