Reports

[Guest]

I have created 4 reports in my access database. This was created for my
mothers crafting supplies. However, when I create a form that contains a
grouping level for example, Categories, the report displays the CatergoryID
rather than the CategoryName. I've looked at just about everything that I can
think of. Does anyone have any recommendations?

 

[Jeff L]

You need a combo box for your Category. I would assume that you have a
Category table with CategoryID and CategoryName already. Your combo
box needs to use the Category table, set the number of columns to 2,
bound column to 1, and column widths to 0;1. Now when you select a
category, you should see the name of the Category, but the CategoryID
is what will be stored because it is the bound column.

Hope that helps!

 

[Guest]

I wish there was a way to send the file. Let's see if I can articulate this.
I have a table called Products which has relationships to tables called
Categories, Types, Color, and Suppliers. These relationships are
CategoryName, TypeName, ColorName, and SupplierName.
Now when I create report for just Products everything appears fine. When I
create reports that contains grouping levels for Category, Type, Color or
Supplier (each separate reports) the content is fine but the grouping level
displays the ID rather than the name.
Are you saying that in the tables for Categories, Types, Colors and
Suppliers these should contain the information you listed below?

 

[Guest]

By the way. I tried that and it didn't fix my issue.

 

[John Vinson]

I have created 4 reports in my access database. This was created for my
mothers crafting supplies. However, when I create a form that contains a
grouping level for example, Categories, the report displays the CatergoryID
rather than the CategoryName. I've looked at just about everything that I can
think of. Does anyone have any recommendations?

Sounds like you are yet another victim of the Lookup Field misfeature.
Is Category a Lookup FIeld in your table? If so, it actually CONTAINS
the CategoryID, even though what you *see* is the category name.

To solve the problem, create a Query joining your supplies table to
the Categories lookup table by CategoryID. Select the category name
from the Categories table, and the other fields from the main table.
Use this query, rather than the table, as the basis of your Report.

John W. Vinson[MVP]

 

[Guest]

John,
Let me know if I understand you correctly. I view the Products table by
Design view and select CategoryName, under the lookup tab for Row Source I
design a query with the query builder. In the first Column I select
Categories.* for Field, Categories for Table. In the second column I select
CategoryName for Field, Categories for Table. Both columns are checked show?

 

[John Vinson]

John,
Let me know if I understand you correctly. I view the Products table by
Design view and select CategoryName, under the lookup tab for Row Source I
design a query with the query builder. In the first Column I select
Categories.* for Field, Categories for Table. In the second column I select
CategoryName for Field, Categories for Table. Both columns are checked show?

NO.

Under the Lookup Tab you put NOTHING.

Lookup Fields are an invention of the devil. They are inefficient,
they are confusing, and they cause the exact problems that you are
having.

Look in your Tables. The Lookup Wizard has created a table of
Categories; you have a table of Products.

Create a new Query. Add the Products table. Add the Categories table.
If Access doesn't do so on its own, join them by CategoryID. Select
the category name from the Categories table; select the other needed
fields from the Products table.

It is *NEVER* necessary to use a Lookup Field. I confess, it can be
used to get the right results - but it is never necessary for it to be
used.

John W. Vinson[MVP]

 

[Guest]

Thanks for trying to help. What you are trying to get me to see just isn't
clicking. I can write a program in Basic, C++, Java, JavaScript and create
web pages all day but I can't seem to grasp Access and Databases.

Sheila

 

[John Vinson]

Thanks for trying to help. What you are trying to get me to see just isn't
clicking. I can write a program in Basic, C++, Java, JavaScript and create
web pages all day but I can't seem to grasp Access and Databases.

I think what's confusing you is Microsoft trying to be "too helpful".
The Lookup field is *REALLY* confusing.

Please try this again. I'm not absolutely certain of your tablenames
and fieldnames, so if it doesn't work, please post back with the names
of your tables, and the names and datatypes of each field in the
table.

Create a new Query. Add the Products table. Add the Categories table.
If Access doesn't do so on its own, drag the CategoryID field from the
Products table to the Categories. Select the category name (Category,
I'd guess) from the Categories table; select the other needed
fields from the Products table.

Open this query. Does it show you the category name along with the
Product data?

If so, create a new Report based on this Query.

You might want to read up on some of the Access orientation links,
especially the Database Design 101 links on Jeff's resources page:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Access DOES have a steep learning curve, but it is actually very
logical and powerful once you get a grasp of the basic structures!

John W. Vinson[MVP]



John W. Vinson[MVP]

 

[Guest]

John,
First - Thank you for sticking it out with me.

I have the following Tables and Fields

Products - ProductID (Auto Number), ProductName, ProductDescription,
CategoryName, TypeName, ColorName, SupplierName

Categories - CategoryID (Auto Number), CategoryName

Types - TypeID (Auto Number), TypeName

Color - ColorID (Auto Number), ColorName

Suppliers - SupplierID (Auto Number), SupplierName - Various information for
my mom that isn't included in any reports.

For my Product Form I am using combo boxes for Category Name, Type Name,
Color Name and Supplier Name.

Trying to establish the following reports.

Preview Supplies by Category
Preview Supplies by Type
Preview Supplies by Color
Preview Supplies by Supplier

Each containing ProductName (sorted ascending), ProductID, Product
Description, (if the following is not the preview topic) CategoryName,
TypeName, ColorName, SupplierName.

So, what is occuring in the reports is this

By Category

CategoryName 1 --> it's not printing the CategoryName but the ID

ProductName ProductID ProductDesription TypeName ColorName SupplierName
Tree 1 Stamp
Purple Stampinup

 

[Guest]

I figured it out John. Thanks a bunch though. What I had done with the
relationships was incorrect. I should have used the ID's rather than the Names

 

[Zac Woodall [MSFT]]

For what it is worth, Access 2007 makes this sort of thing much, much
easier.

 

[John Vinson]

I figured it out John. Thanks a bunch though. What I had done with the
relationships was incorrect. I should have used the ID's rather than the Names

aha. Good!

Correct. There actually IS no field in the Parts table for
CategoryName. What's in the Parts table is a (concealed from view!)
CategoryID. That is indeed the field that you must use for linking.

I'm looking forward to see if things get better... or worse...
(Complex Data fields :-{( ) in 2007.

John W. Vinson[MVP]

 

[Guest]

LOL great ... yet another version of the application for me to try and figure
out. Thanks for the head up

 

[Guest]

It was your previous reply that got me to thinking about it. Thanks