Access Reports without an Access Table – Is this possible?

[Brad]

We have an Access 2007 application that we are building in hopes that it will
be a template for future applications.

This application displays (via MSGBOX) a number of informational messages
that we have decided to move into a single Access report. To do this, we set
up a new table which we use to store the messages for the users. We then set
an Access report to pull the data from the table.

This works nicely, but it does seem a bit cumbersome. Is there a better way
to create a report without first creating a table? Are there add-on products
that provide this facility?

Thanks,
Brad

 

[Jeff Boyce]

Brad

What are your requirements?

If you need a "blank slate" on which you put messages and print them out,
Word works fine.

If you need a way to print out a selection of messages, based on some
(selection) criteria, that sounds like the setup you now have in Access.

You tell us 'what', we'll offer ideas on 'how'...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Marshall Barton]

We have an Access 2007 application that we are building in hopes that it will
be a template for future applications.

This application displays (via MSGBOX) a number of informational messages
that we have decided to move into a single Access report. To do this, we set
up a new table which we use to store the messages for the users. We then set
an Access report to pull the data from the table.

This works nicely, but it does seem a bit cumbersome. Is there a better way
to create a report without first creating a table? Are there add-on products
that provide this facility?

That is not cumbersome. Puttin data (e.g. your messages) in
a table is a really good way to do it. You can then add new
messages and edit existing messages without messing with the
report. With the appropriate additional fields you can
select different sets of records (messages) to display in
the report, again without having to fool around in the
report.

If your messages are cast in concrete, never to be changed,
you can just add a ton (up to 750) of text boxes with a
control source expression like ="this is some message"
Note that if you ever have to change the list of messages,
you will be cursing the day you decided this is less
cumbersome than using a table.

 

[Brad]

Thanks for the advice.

We are using Access 2007 to build a bridge between our web-based orders and
our backend accounting system. Because we plan to use this system as a model
for future Access systems so we are trying real hard to do things correctly.

In this first system, there are a number of error situations that we need to
inform the users about. Because there can be quite a few of these errors, we
have migrated from using MSGBOXs to using an Access table and a report.

In the past, I worked with another tool (mainframe based) which provided a
much easier way to crank out reports. I was wondering if there is a better
method to create reports with Access 2007 other than using a table and
report. I am not very familiar with add-on products, but I thought that
perhaps someone had something to offer along these lines.

Brad

 

[Jeff Boyce]

Brad

I guess I'm too familiar with how Access does things.

If I had a table, a query against that table, and a report based on that
query, why would I need any "add-on"?

What is it that you would expect the add-on to do that Access isn't already
doing/can't do?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Brad]

Jeff,

I agree with you, if a person has a table & a query, an Access report is
very easy to create.

The situation that I am looking at does not have a table. As I process
orders, I want to report certain error conditions. I would like to create a
report simply from variables. In the past I worked with mainframe tools that
provided this capability. I was just wondering if there is any way to do
this in Access. It looks like the method of creating a table is the best way
to go. I have only scratched the surface of Access Add-ons. I thought that
perhaps there was an “Enhanced Report Writer†Add-on.

Thanks for your help. I am still learning.

Brad

 

[Marshall Barton]

I agree with you, if a person has a table & a query, an Access report is
very easy to create.

The situation that I am looking at does not have a table. As I process
orders, I want to report certain error conditions. I would like to create a
report simply from variables. In the past I worked with mainframe tools that
provided this capability. I was just wondering if there is any way to do
this in Access. It looks like the method of creating a table is the best way
to go. I have only scratched the surface of Access Add-ons. I thought that
perhaps there was an “Enhanced Report Writer” Add-on.

It is possible to create an unbound report. It's either a
trivial exercise or it can get really tricky depending on
what you are trubg to do.

Where do these "variables" get their values? And why are
you not saving those values when they are entered?

 

[Brad]

Marsh,

The data is coming from 5 sources (3 via ODBC and 2 Excel spreadsheets).
One of the ODBC tables is what we call the “Driver file†(transactions). The
other four data sources are what we call “Support Dataâ€.

The output is a newly created table that is based on the inbound ODBC
transaction table.

The data that we want to push to an error report can come from any of the
five sources plus an explanation of the error that is determined when the
data is processed. (Example – missing a record in one of the Support Data
tables).

Currently, we have set up an error table in Access which we use to store the
errors for a batch. We then pull the data from this table with a query into
a report. This works fine. We were just wondering if there is a better
method that more experienced Access users employ. It sounds like the method
that we are currently using is the best approach.

Thanks for your assistance.

Brad

 

[Marshall Barton]

The data is coming from 5 sources (3 via ODBC and 2 Excel spreadsheets).
One of the ODBC tables is what we call the “Driver file” (transactions). The
other four data sources are what we call “Support Data”.

The output is a newly created table that is based on the inbound ODBC
transaction table.

The data that we want to push to an error report can come from any of the
five sources plus an explanation of the error that is determined when the
data is processed. (Example – missing a record in one of the Support Data
tables).

Currently, we have set up an error table in Access which we use to store the
errors for a batch. We then pull the data from this table with a query into
a report. This works fine. We were just wondering if there is a better
method that more experienced Access users employ. It sounds like the method
that we are currently using is the best approach.

You need to put it somewhere and in a database system, I
can't imagine a better place to put a list of whatevers.