A really really simple report

[Matty F]

I have an Access database that someone else has set up, and I wish to
print a report from it, based on selecting only the records that have
a certain field = non-zero.
I've looked at many tutorials without success and tried to wade
through Microsoft's Help. But the writers of those just don't think
the same way that I do. They don't seem to start at the beginning.
They use terms without defining them. They say to do something without
specifying which of the dozens of menus the function is hidden on. Or
there is a magic but meaningless-looking button on a toolbar
somewhere, and the help window for that button has a delay, and then
disappears while I am reading it. Or the button is greyed out for some
mysterious reason. But enough of that rant.

The main problem is that I want to do a very simple report but the
tutorials have thousands of complicated functions that I don't want to
do. I can usually figure out how to use software without even reading
any manual.

The term "Select" that I would use to select records seems to mean
something else in Access. After hours of looking I think the word I
need is "Filter".

I have managed to run a filter on my database to create what I want.
But when I want to save that selection (dammit I want to use that
word), it tells me that I have to save the original database first.
Well I have not changed the original database except to type ">0" into
the desired selection field. Do I need to open the database in a
different way in order to do a filter? How do I save the resulting
selection?
If anyone is capable of explaining how I can do what I want without
missing out some vital steps I would be grateful.

 

[Duane Hookom]

First, you use "database" with two different meanings. You first use "Access
database" which is the MDB file then "filter on my database" where I think
you actually mean a table or query.

I assume you have a report with a record source of a table or query or SQL
statement. You can modify the record source to apply a filter or criteria. If
your query (record source) is in design view, you can enter an expression in
the criteria under any field/column. In your case, you would enter the
following under [a certain field]
<>0
Then exit the design view and save it. Then preview and save your report.

If you can't figure this out, tell us about the record source of your
report. It might also be import to know if you want the same report to
display with other filter/criteria and if your [a certain field] might
contain nulls (no value) and if these should be included in your report.

 

[Jeff Boyce]

Matty

You may have already tried this, too...

Create a new query in design view.

Add the table that has the field that you want to select for non-zero.

Add the field to the query grid. Beneath it, in the Selection Criterion
"row", add "<>0" (without the quotes).

This assumes the field is a numeric data type.

Run the query. You should see all the values of that field that are <>0.

Add more fields that you want to see.

Save the query.

Create a new report, and, when prompted, base it on the query you just
created.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[AndyB via AccessMonster.com]

Hi Matty,

From the info you've given, I think you are trying to print a view of the
filtered table rather than create an actual report on the table.

I presume you've filtered the dat within the table view. By saving at this
point, access is basically asking if you want to save the table with the new
filter in place. If you save, the next time you open the table only the
filtered data will be available. (You can of course, remove the filter to
restore all date.

This is generally not the best way of doing things as you need to be sure all
the fields in the table are stretched large enough to contain the info within,
and if the print-out of the table exceeds the page-width of your print-out,
then it will print over multiple pages.

If this is a report you need on a regular basis, I would recommend creating
an actual report which filters the info you need.

Before we go any further Ineed answers to a few of questions.
Do you need this report in hard-copy or electronic (and in what format)?
Does it need to be pretty, or just a print of the table?
Is this a report you'll need on a regular basis?
Is the info for the report contained within a single table?

Andy B

 

[Matty F]

Hi Matty,

From the info you've given, I think you are trying to print a view of the
filtered table rather than create an actual report on the table.

Unfortunately I don't have Access on this computer. The computer with
Access and the database is a half hour drive away, so I'm just relying
on my memory for what shows on the screen. I have not found a single
tutorial which shows, for example, the screen that I see after I click
on the MDB file. I think it says things like TABLE, FORM, QUERY etc
and allows me to choose an MDB file. There is only one of those on the
machine.

These words mean nothing to me, except that I am able to update the
database and print a full list or single pages etc. I even figured out
how to print labels depending on the values in one of the fields. For
some odd reason that needed me to start from MS Word!

I presume you've filtered the dat within the table view. By saving at this
point, access is basically asking if you want to save the table with the new
filter in place. If you save, the next time you open the table only the
filtered data will be available. (You can of course, remove the filter to
restore all date.

Yes probably I have filtered using table view, only because I can't
see how to use Filter in any other way.

If this is a report you need on a regular basis, I would recommend creating
an actual report which filters the info you need.

I think my biggest problem is that words such as "Report" have a
different meaning to me than to Access. A report to me is a printed
document with headings etc.

Perhaps I can rephrase everything in terms that I understand.
I have an MDB file of people's names, addresses and about 30 other
details including "license number". Let's call that MAINFILE.
I wish to filter that file to make a subset of people who have a non-
zero license number. I wish to save that subset and create a variety
of reports from it every month. Let's call that LICENSEFILE. Naturally
I want LICENSEFILE to have the latest information from MAINFILE if the
latter is updated.
Having done one such report I should be able to work out how to do
other filters and reports.

Before we go any further Ineed answers to a few of questions.
Do you need this report in hard-copy or electronic (and in what format)?
Does it need to be pretty, or just a print of the table?
Is this a report you'll need on a regular basis?
Is the info for the report contained within a single table?

I will want to print the reports on paper regularly and they need to
be pretty, with headings etc. All info is in a single table.
I'd also like to print a single page per person, which I can do now in
the Access default layout but I want to lay the data out in a
different manner.

 

[Duane Hookom]

A few generally accepted definitions with MS Access:

Database: an MDB file that can contain/store tables, queries, forms,
reports, macros, code, etc. You generally see the "database window" when you
open an MDB unless the developer has provided an alternative.

Table: an object that stores records much like a list

Field: a field is like a column of the table/list.

Record: a row in a table/list

Query: There are several types of queries. I expect you are most interested
in a "Select" query. Select queries allow you to create a "list" of one or
more tables. This list can use fields/columns from any of the tables as well
as calculated values. A query can also have a criteria to limit the records
displayed. Queries also allow you to specify a sort order and aggregation of
records.

Form: a screen type object that is generally used to display/edit records
and/or control navigation or other user interface. A form has a Record Source
property that can be set to a table or query.

Report: an object much like a form although it is used to publish your
information to print (or pdf or similar). A report has a Record Source
property that can be set to a table or query.

If I were you, I would create a query that filters your table(s) and then
build a report based on the query.

 

[Matty F]

If I were you, I would create a query that filters your table(s) and then
build a report based on the query.

The problem that I have been having is that nobody around here could
figure out exactly how to do that. People kept getting to the point
where it was not clear what to do next, or were confronted with a list
of options, none of which seemed relevant or with unknown functions.
In my original request, I wanted the *exact* steps that need to be
taken in order to create a query. For example, why is "Run Query"
hidden up in a menu when it should be a button immediately after the
fields selection and criteria? I still can't see the word "Filter"
anywhere during this process. However I can now produce my report and
any number of similar reports using sorting and filtering.

I did use Access in about 1993, and another database system with an
SQL capability in 1987. In this present case I recommended the use of
Access for our needs.

So for the benefit of people unfamiliar with Access, here are the
steps needed to create a Query that can be run at any time later. This
is the answer that I was hoping that someone would tell me. I have
found no tutorials or Help pages that show the information in the form
that I have shown it, which is all that is needed for a simple report.

Using: Access 2003

My Data Sources, click on MDB file, Open
Queries, Create query in Design view
Select MDB file, Add, Close
Choose desired fields, put filter formula in Criteria, Sort
Query menu, Run
File menu, Save As, Choose name, as Query

 

[Duane Hookom]

So, what program are you starting out in? When you state "My Data Sources,
click on MDB file, Open" it appears you are not in Access since to create a
query in Access, you just click on "Queries" in the list of Objects and then
click the "New". This will open a query wizard dialog with options for
"Design View", "Simple Query Wizard", and 3 others.

There is no "My Data Source" that I am aware of in Access 2003. If you use
the wizard to create a report, you be presented with a dialog to select
several options as well as the table or query to base your report on.

Are you actually trying to create a report in Crystal Reports or some other
application?

 

[Matty F]

So, what program are you starting out in? When you state "My Data Sources,
click on MDB file, Open" it appears you are not in Access since to create a
query in Access, you just click on "Queries" in the list of Objects and then
click the "New". This will open a query wizard dialog with options for
"Design View", "Simple Query Wizard", and 3 others.

There is no "My Data Source" that I am aware of in Access 2003. If you use
the wizard to create a report, you be presented with a dialog to select
several options as well as the table or query to base your report on.

Are you actually trying to create a report in Crystal Reports or some other
application?

As I do with most files, I just click on the file name, and the file
type association takes me to the correct application to handle that
file, i.e. Access for a .MDB extension.
I have been told that an Access database *must* exist in the "My Data
Sources" directory (or Folder as MS decided to rename Directories).
That's probably correct as it certainly doesn't work if it's put
elsewhere.

 

[AndyB via AccessMonster.com]

Hi Matty,

I'm wondering if the person who set up this database has set it up so it
locks-down certain features on startup.

To be on the safe side, close the database, close access, then in windows
explorer, go to your 'My Data Sources' directory, then while holding down the
shift key, double-click your MDB file.

This will force the database to override any startup settings and open the
mdb in design mode.

You should now see the database window, with 'Open' 'Design' and 'New'
buttons on it's top toolbar and a list of objects down the left hand side.
(Tables, Queries, Forms, Reports, Pages, Macros & Modules).

Click on Queries in the object list. You'll be presented with two options in
the main window.
(Create query in design view and Create query using wizard)

Click Create query using wizard.

A dialog will appear. Select your table from the Tables/Queries drop-down
list and all available fields will appear in the 'Available Fields' box.
Use the double right arrow between the boxes to transfer all fields to the
'Selected Fields' box.
Then click Next.

In the next dialog, name your query, then select modify the query design and
click 'Finish'.

You will be presented with your query in design view, with the selected table
field list in the top half of the query and each field in it's own column in
a table-type view in the lower half.

The query is currently showing all fields.
If there's any fields you don't need in your report, un-check them in the
'Show' row of the query table.

Now look for your License number column where you want to set the 'Non-Zero'
criteria and in the Criteria row for this column, type in your criteria <>"0"

Now click 'Save' and close the query.

You should be back at the database window, with the new query showing beneath
the two 'Create Query' options.

Click on your new query and it will open in datasheet view, showing all
records with a non-zer license number.

Once you've got this far, post back & we'll look at the report(s) you wish to
create.

Andy B

So, what program are you starting out in? When you state "My Data Sources,
click on MDB file, Open" it appears you are not in Access since to create a

[quoted text clipped - 8 lines]

Are you actually trying to create a report in Crystal Reports or some other
application?

As I do with most files, I just click on the file name, and the file
type association takes me to the correct application to handle that
file, i.e. Access for a .MDB extension.
I have been told that an Access database *must* exist in the "My Data
Sources" directory (or Folder as MS decided to rename Directories).
That's probably correct as it certainly doesn't work if it's put
elsewhere.

 

[Matty F]

Hi Matty,

I'm wondering if the person who set up this database has set it up so it
locks-down certain features on startup.

To be on the safe side, close the database, close access, then in windows
explorer, go to your 'My Data Sources' directory, then while holding down the
shift key, double-click your MDB file.

This will force the database to override any startup settings and open the
mdb in design mode.

You should now see the database window, with 'Open' 'Design' and 'New'
buttons on it's top toolbar and a list of objects down the left hand side.
(Tables, Queries, Forms, Reports, Pages, Macros & Modules).

Yes, that is what I see.

Click on Queries in the object list. You'll be presented with two options in
the main window.
(Create query in design view and Create query using wizard)

Click Create query using wizard.

What does the wizard do, since I achieved my reports by going directly
to Design view. I tried the wizard and kept getting one line of user
data, and nowhere to enter a filter. Very strange.

A dialog will appear. Select your table from the Tables/Queries drop-down
list and all available fields will appear in the 'Available Fields' box.
Use the double right arrow between the boxes to transfer all fields to the
'Selected Fields' box.
Then click Next.

In the next dialog, name your query, then select modify the query design and
click 'Finish'.

You will be presented with your query in design view, with the selected table
field list in the top half of the query and each field in it's own column in
a table-type view in the lower half.

The query is currently showing all fields.
If there's any fields you don't need in your report, un-check them in the
'Show' row of the query table.

Now look for your License number column where you want to set the 'Non-Zero'
criteria and in the Criteria row for this column, type in your criteria <>"0"

Now click 'Save' and close the query.

You should be back at the database window, with the new query showing beneath
the two 'Create Query' options.

Click on your new query and it will open in datasheet view, showing all
records with a non-zer license number.

Once you've got this far, post back & we'll look at the report(s) you wish to
create.

My reports are all done. As I said above "However I can now produce my
report and any number of similar reports using sorting and
filtering.".
All I wanted to know is what you have just done, so thanks for that.
However I would have understood my 6 lines of abbreviated instructions
just as well.
It was really a matter of which options to choose when it's not at all
clear what Access thinks the words mean.
For example I had just done a query that produced a report on the
screen. When I used "Save As" it said "Save Query As" and 4 options:
Query, Form, Report, Data Access Page. Since I was wanting to save
the report I selected Report, and got the message "You must save a
table or query before you can create a new object based on it".
So why show "Report" if that is not relevant?
I suggest that it would be useful to show rather a lot of context
sensitive help, and suggestions of what to do next.
If you are familiar with Access or even designed or wrote the
software, you will not notice the problems that new users face.
Everybody else at work has given up on Access totally. But I am happy
enough now.

 

[AndyB via AccessMonster.com]

Hi Matty,

Glad you got it sorted. Access certainly has its peculiarities.

I must admit, I don't normally use the wizard, it just seemed simpler than
explaining the use of design view.

Why the wizard doesn't give you a full design view I don't know, unless
there's some wierd user group permissions set up on it.
When you say it gives you a single line of user data, is this a text string?
which would probably mean it's being viewed in SQL mode or is there a
datasheet type layout?

With regard to 'Saving the Query as a report', that's not quite what it does
(or means).
What it actually does is generate a report (a new database object) based on
the query, but the query must be saved before it can generate the report.

So, if you save the query as a query first, then it'll let you save as a
report. (Meaning generate a separate report).

Confusing ain't it.

Sorry if my instructions were a bit laboured, but I've found in the past, if
you drop back to basics & spell it out, there's no room for confusion - and
you have to admit - it achied the goal.

Regards,

Andy

Hi Matty,

[quoted text clipped - 11 lines]

buttons on it's top toolbar and a list of objects down the left hand side.
(Tables, Queries, Forms, Reports, Pages, Macros & Modules).

Yes, that is what I see.

Click on Queries in the object list. You'll be presented with two options in
the main window.
(Create query in design view and Create query using wizard)

Click Create query using wizard.

What does the wizard do, since I achieved my reports by going directly
to Design view. I tried the wizard and kept getting one line of user
data, and nowhere to enter a filter. Very strange.

A dialog will appear. Select your table from the Tables/Queries drop-down
list and all available fields will appear in the 'Available Fields' box.

[quoted text clipped - 26 lines]

Once you've got this far, post back & we'll look at the report(s) you wish to
create.

My reports are all done. As I said above "However I can now produce my
report and any number of similar reports using sorting and
filtering.".
All I wanted to know is what you have just done, so thanks for that.
However I would have understood my 6 lines of abbreviated instructions
just as well.
It was really a matter of which options to choose when it's not at all
clear what Access thinks the words mean.
For example I had just done a query that produced a report on the
screen. When I used "Save As" it said "Save Query As" and 4 options:
Query, Form, Report, Data Access Page. Since I was wanting to save
the report I selected Report, and got the message "You must save a
table or query before you can create a new object based on it".
So why show "Report" if that is not relevant?
I suggest that it would be useful to show rather a lot of context
sensitive help, and suggestions of what to do next.
If you are familiar with Access or even designed or wrote the
software, you will not notice the problems that new users face.
Everybody else at work has given up on Access totally. But I am happy
enough now.

 

[Duane Hookom]

I don't know where you heard this:
"I have been told that an Access database *must* exist in the "My Data
Sources" directory (or Folder as MS decided to rename Directories).
That's probably correct as it certainly doesn't work if it's put
elsewhere."

Clearly you have been mislead. An Access MDB file can be located almost
anywhere on your PC or network (just like an Excel or Word file).

When I stated:
"Database: an MDB file that can contain/store tables, queries, forms,
reports, macros, code, etc. You generally see the 'database window' when you
open an MDB unless the developer has provided an alternative."
I would have expected a reply like "When I open my MDB, I don't see the
database window".


--
Duane Hookom
Microsoft Access MVP

Hi Matty,

Glad you got it sorted. Access certainly has its peculiarities.

I must admit, I don't normally use the wizard, it just seemed simpler than
explaining the use of design view.

Why the wizard doesn't give you a full design view I don't know, unless
there's some wierd user group permissions set up on it.
When you say it gives you a single line of user data, is this a text string?
which would probably mean it's being viewed in SQL mode or is there a
datasheet type layout?

With regard to 'Saving the Query as a report', that's not quite what it does
(or means).
What it actually does is generate a report (a new database object) based on
the query, but the query must be saved before it can generate the report.

So, if you save the query as a query first, then it'll let you save as a
report. (Meaning generate a separate report).

Confusing ain't it.

Sorry if my instructions were a bit laboured, but I've found in the past, if
you drop back to basics & spell it out, there's no room for confusion - and
you have to admit - it achied the goal.

Regards,

Andy

Hi Matty,

[quoted text clipped - 11 lines]

buttons on it's top toolbar and a list of objects down the left hand side.
(Tables, Queries, Forms, Reports, Pages, Macros & Modules).

Yes, that is what I see.

Click on Queries in the object list. You'll be presented with two options in
the main window.
(Create query in design view and Create query using wizard)

Click Create query using wizard.

What does the wizard do, since I achieved my reports by going directly
to Design view. I tried the wizard and kept getting one line of user
data, and nowhere to enter a filter. Very strange.

A dialog will appear. Select your table from the Tables/Queries drop-down
list and all available fields will appear in the 'Available Fields' box.

[quoted text clipped - 26 lines]

Once you've got this far, post back & we'll look at the report(s) you wish to
create.

My reports are all done. As I said above "However I can now produce my
report and any number of similar reports using sorting and
filtering.".
All I wanted to know is what you have just done, so thanks for that.
However I would have understood my 6 lines of abbreviated instructions
just as well.
It was really a matter of which options to choose when it's not at all
clear what Access thinks the words mean.
For example I had just done a query that produced a report on the
screen. When I used "Save As" it said "Save Query As" and 4 options:
Query, Form, Report, Data Access Page. Since I was wanting to save
the report I selected Report, and got the message "You must save a
table or query before you can create a new object based on it".
So why show "Report" if that is not relevant?
I suggest that it would be useful to show rather a lot of context
sensitive help, and suggestions of what to do next.
If you are familiar with Access or even designed or wrote the
software, you will not notice the problems that new users face.
Everybody else at work has given up on Access totally. But I am happy
enough now.

 

[Matty F]

I don't know where you heard this:
"I have been told that an Access database *must* exist in the "My Data
Sources" directory (or Folder as MS decided to rename Directories).
That's probably correct as it certainly doesn't work if it's put
elsewhere."

Clearly you have been mislead. An Access MDB file can be located almost
anywhere on your PC or network (just like an Excel or Word file).

I'm quite certain that I got a Microsoft error message telling me
that, when I attempted to do a label print. I'll check again but it
will be another week before I see the machine with Access on it.

 

[Cheese_whiz]

My guess is the whole 'my data sources' is related to the OP using windows
vista, and the 'you must have your .mdb file located in my data sources'
relates to windows vista security, which has some default 'trusted' locations
and, outside of them, gives you a headache every time you want to open one of
your own files (unless you set up a particular location as trusted).

CW

I don't know where you heard this:
"I have been told that an Access database *must* exist in the "My Data
Sources" directory (or Folder as MS decided to rename Directories).
That's probably correct as it certainly doesn't work if it's put
elsewhere."

Clearly you have been mislead. An Access MDB file can be located almost
anywhere on your PC or network (just like an Excel or Word file).

When I stated:
"Database: an MDB file that can contain/store tables, queries, forms,
reports, macros, code, etc. You generally see the 'database window' when you
open an MDB unless the developer has provided an alternative."
I would have expected a reply like "When I open my MDB, I don't see the
database window".


--
Duane Hookom
Microsoft Access MVP

Hi Matty,

Glad you got it sorted. Access certainly has its peculiarities.

I must admit, I don't normally use the wizard, it just seemed simpler than
explaining the use of design view.

Why the wizard doesn't give you a full design view I don't know, unless
there's some wierd user group permissions set up on it.
When you say it gives you a single line of user data, is this a text string?
which would probably mean it's being viewed in SQL mode or is there a
datasheet type layout?

With regard to 'Saving the Query as a report', that's not quite what it does
(or means).
What it actually does is generate a report (a new database object) based on
the query, but the query must be saved before it can generate the report.

So, if you save the query as a query first, then it'll let you save as a
report. (Meaning generate a separate report).

Confusing ain't it.

Sorry if my instructions were a bit laboured, but I've found in the past, if
you drop back to basics & spell it out, there's no room for confusion - and
you have to admit - it achied the goal.

Regards,

Andy

Hi Matty,

[quoted text clipped - 11 lines]
buttons on it's top toolbar and a list of objects down the left hand side.
(Tables, Queries, Forms, Reports, Pages, Macros & Modules).

Yes, that is what I see.

Click on Queries in the object list. You'll be presented with two options in
the main window.
(Create query in design view and Create query using wizard)

Click Create query using wizard.

What does the wizard do, since I achieved my reports by going directly
to Design view. I tried the wizard and kept getting one line of user
data, and nowhere to enter a filter. Very strange.

A dialog will appear. Select your table from the Tables/Queries drop-down
list and all available fields will appear in the 'Available Fields' box.
[quoted text clipped - 26 lines]
Once you've got this far, post back & we'll look at the report(s) you wish to
create.

My reports are all done. As I said above "However I can now produce my
report and any number of similar reports using sorting and
filtering.".
All I wanted to know is what you have just done, so thanks for that.
However I would have understood my 6 lines of abbreviated instructions
just as well.
It was really a matter of which options to choose when it's not at all
clear what Access thinks the words mean.
For example I had just done a query that produced a report on the
screen. When I used "Save As" it said "Save Query As" and 4 options:
Query, Form, Report, Data Access Page. Since I was wanting to save
the report I selected Report, and got the message "You must save a
table or query before you can create a new object based on it".
So why show "Report" if that is not relevant?
I suggest that it would be useful to show rather a lot of context
sensitive help, and suggestions of what to do next.
If you are familiar with Access or even designed or wrote the
software, you will not notice the problems that new users face.
Everybody else at work has given up on Access totally. But I am happy
enough now.

 

[Matty F]

My guess is the whole 'my data sources' is related to the OP using windows
vista, and the 'you must have your .mdb file located in my data sources'
relates to windows vista security, which has some default 'trusted' locations
and, outside of them, gives you a headache every time you want to open one of
your own files (unless you set up a particular location as trusted).

The machine was using XP Home Edition, and that was replaced by XP
Professional. The existing "My Data Sources" folder, together with the
MDB file that I was using, became just another folder for one user,
me. When I tried to do a label print that used the MDB file, then I
got the message that there was nothing in the "My Data Sources"
folder.
I copied the MDB file to the top level "My Data Sources" folder for
the machine, and the label print started working.

 

[AndyB via AccessMonster.com]

I would guess the programme you're using to do label prints was something
other than access linking to the MDB or another database using linked tables.

Opening the MDB itself should be possible wherever it is on the hard drive.

Andy

 

[Matty F]

I would guess the programme you're using to do label prints was something
other than access linking to the MDB or another database using linked tables.

Opening the MDB itself should be possible wherever it is on the hard drive.

Correct, I can open the MDB anywhere, even on a flash drive.
To do the label print I was told to start with MS Word. That did a
great job of printing. But if the label stationery changes (as I use
up old stock) it's a really complex and lengthy process to go through
all the steps to get the job done. Word seems to have a preconceived
notion about what the fields to be printed should be called and every
time the label type changes I have to go through a process of linking
the fields, e.g. "Address1" = "Address" etc.
Also we don't have Zip codes or States here in New Zealand!