A really really simple report

On Feb 19, 10:04 pm, "AndyB via AccessMonster.com" u41372@uwe wrote:
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.

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

Matty F wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200802/1

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


"AndyB via AccessMonster.com" wrote:

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

Matty F wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200802/1

On Feb 20, 4:30 am, Duane Hookom
wrote:
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.

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



"Duane Hookom" wrote:

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


"AndyB via AccessMonster.com" wrote:

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

Matty F wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200802/1

On Feb 20, 9:35 am, Cheese_whiz
wrote:
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.

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 wrote:
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.

--
Message posted via http://www.accessmonster.com

On Feb 20, 10:29 am, "AndyB via AccessMonster.com" u41372@uwe wrote:
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!