Strange issues with reports

[Guest]

So here's the deal. I just started working at an office that is making all
of their excel spread sheets into Access databases. No one here knows how to
use Access, and so they have passed it onto the new person to figure it out.
They have already created the database, and now they want me to turn their
data into a report that looks like the one they have used in Excel for years.
They have set their tables into one query that they want me to use this
query to make the report. The query pulls the category upon open and asks
you which community you want the query to cover in a pop up window. This is
problem 1.

1. When I make the report at the top ask for information from this query,
AND a separate table ask for information from this query in the same
category, upon opening the report it asks for the community name TWICE. How
do I make it only ask for this name ONCE?

As I am putting together the report, part of it is an assembly of text that
is supposed to pull numbers from the query, names from the query, and do some
simple math on the numbers to make it look as if it is a fluid sentence.
This creates the rest of the problems.

2. How do I get the text to include names and numbers from the database?

3. When I tried to use basic excel format it ruined the text, now all that
is displayed is #Name?, whether I delete the text or not this continues to
show up, how do I get rid of this?

4. How do I get the report to include simple mathematic equations and only
show the answers? Do I need to make another line in the table and have it
pull this information in the query in order to get this info? Is there an
easier way?

Please help! Access is quickly becoming the most stressful program I have
ever used.

 

[Jeff Boyce]

Access has a fairly steep learning curve, and is most definitely NOT just a
bigger spreadsheet. If all they did was import data straight from Excel in
Access, there's a very good chance that the data is structured, well, like a
spreadsheet! You (and they) won't get the best use of Access'
relationally-oriented features and functions if you (and they) insist on
feeding it 'sheet data.

In Access, for example, there is NO need to make the table structure look
like the forms you want to use, or the reports you want to print. Queries
help you extract data from related tables to display/print.

To get a single prompt for a parameter, consider using an unbound control on
a form, and referring to the contents of that control in the query's
Selection Criterion.

To include values from other tables in a report, base the report on a query,
not on a table. In the query, join related tables and add the fields that
hold the values you want to see.

Access displays "#Name" when it doesn't recognize the name of the field you
claim has the data. Sometimes this is as simple as a spelling error. You
don't provide any details so it's a bit tough to diagnose...

Don't "do math" in your table ... that's what spreadsheets do, but
relational databases don't (or rarely) need to. If you need to "do math" in
a report, create an unbound control and do the math in the control's
ControlSource property.

Did I mention that Access has a steep learning curve?<g>.

Good luck! Keep posting back with specific questions for specific answers.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I still don't understand. All the data I am supposed to use is in a query
all ready. The query is set to pull certain parts of data by asking for a
cummunity. This box pops up serveral times when opening the report. The
report pulls only from the query not from tables.

Still not sure how to get my tables and text to have summated or divided
numbers in them.

Still not sure how to pull information from my query into my text boxes to
be a part of sentences.

Still lost.

 

[Jeff Boyce]

I'm not very clear yet on what you are trying to do.

Can you post the SQL statement of the query you are using? (open the query
in design view, click on the view button and select SQL, highlight the
expression, copy, and paste into your email/response)

Regards

Jeff Boyce
Microsoft Office/Access MVP