chart axis labels

[MAANI]

My output report is:
WO# Inspection Type Number of Faults
1199 Pre Final 15
1199 Final 23

I need to insert a bar chart,x-axis will be the inspection type (pre
final,Final...),y-axis is the number of faults,I did that but the x-axis
shows numbers 2 and 3,instead of pre final and final (labels r numbers
instead of text) !!!! Plz help

[Duane Hookom]

I assume your issue is caused by your use of lookup fields in tables. If you
don't fully understand them, don't use them. Most of us who have been
programming for decades avoid them like the plague.

Your Row Source must include the table that actually stores the Inspection
Type title.
--
Duane Hookom
Microsoft Access MVP

[MAANI]

This is my row source for the chart:
SELECT [Inspection Type],Sum([Faults]) AS [SumOfFaults] FROM [FaultsQ] GROUP
BY [Inspection type]
In some of my tables I use lookup,but not the Inspection Type Table,note
that I have cascaded comboboxes in my DB.
I Appreciate your help

[Duane Hookom]

You state "not the Inspection Type Table" but your Row Source is from
FaultsQ. What are the significant fields and tables in this query.

[MAANI]

Ok.Don't worry about the lookup thing,coz I'm not sure if we are talking
about the same thing.Anyways,my FaultsQ has 3 fields:(WO #),(Inspection Type)
and (Inspection Type) too,the table for those is (DataEntryT) where WO # is
typed by the user,and Inspection Type is chosen from a combobox,the total in
the Q is (Group by),(Group by) and (Count).

[MAANI]

Thank you Duane for your reply.A brief of what is my DB look like:
All the tables have ID (auto number) as a primary key.
DataEntryT is a table that has some fields as text,number and cascaded
comboboxes.
Inspection Type is one of DataEntryT fields and its a combobox,I set the
lookup field properties for this field as follows (combo
box,Table/Query,InspectionTypeT,1,2,No,0";2",16,Auto,Yes,No,blank,No).
To make Inspection Type a combobox I created a table InspectionTypeT,with
two fields,ID and Inspection type.

"Duane Hookom" wrote:

> Again, what are the significant fields and tables? Do you have a table where
> Inspection Type might be a primary key? If so, what is the structure of this
> table and how does it relate to DataEntryT?

[Duane Hookom]

Apparently DataEntryT has a lookup field which I feel is just plain a bad bad
idea. If you want your chart to display the inspection type title, then you
need to get the InspectionTypeT into Row Source so you can use its
[Inspection Type] field.

It's really confusing is you use [Inspection Type] as a field name in
DataEntryT when it actually stores the value from the ID field in
InspectionTypeT. In addition the InspectionTypeT has a field named
[Inspection Type]. I use a strick naming convention where the structure of
InspectionTypeT would be:
ityITyID autonumber primary key
ityTitle values like "Final" and "Pre Final"
Then your DataEntryT would have a field related to ityITyID named datITyID.

I also don't care much for DataEntryT since it doesn't say much about what
it is actually storing. I would think a name like InspectionResultsT might be
more appropriate but then I just get to be a bit of a naming freak at times
;-)

[MAANI]

Duane,
Now you keep mentioning the lookup thing,I use it so that the DataentryT
stores the value I choose from the Inspection Type combobox.
How do I get the InspectionTypeT into Row Source?What is the code for that?
I changed names as u suggested,same thing,x-axis shows the ID not the value.
Is it that difficult what I'm asking for ? Its only changing x-axis values
!!!!!

[Duane Hookom]

All you have to do is simply make sure the field that stores that actual type
title in the Row Source of your chart.

What is the Row Source of your chart?
What table and field contains the actual title value that is text?

[MAANI]

Heres the row source of my chart:
SELECT [Expr1],Sum ([CountofInspType]) AS [SumofCountofInspType] FROM
[CountofInspectionsQ] GROUP BY [Expr1];
Note that I changed names.Also,Expr1 is InspType Field.
The actual title value is in a table named InspectionTypeT,field name is
InspectionType.You mentioned "make sure the field that stores that actual
type title in the Row Source of your chart",HOW TO DO THAT?WHAT IS THE CODE?

[Duane Hookom]

There is no "code" involved, only simple queries. What is the SQL of
CountofInspectionsQ? Does CountofInspectionsQ include the InspectionTypeT in
it? All you should need to do is add the table to the query and join the
appropriate fields so you can display the InspectionType field.

[MAANI]

Here is the SQL of CountofInspectionsQ:

SELECT DataEntryT.[WO #], DataEntryT.InspectionType,
Count(DataEntryT.InspectionType) AS CountOfInspectionType
FROM DataEntryT
GROUP BY DataEntryT.[WO #], DataEntryT.InspectionType
HAVING (((DataEntryT.[WO #])=["WO #"]));

I've added the table (InspectionTypeT) to the query (CountofInspectionsQ)
and joined the appropriate fields (InspectionType),the query now shows
nothing when I run it !!!! I start to get sick of this !!

[Duane Hookom]

Did you join the numeric field from DataEntryT to the numeric field from
InspectionTypeT? I expect your DataEntryT.InspectionType field is a numeric
field. I also assume this should be joined to the InspectionTypeT.ID field.
Is that what you tried? If the primary key field from InspectionTypeT is ID
then try:

SELECT DataEntryT.[WO #], InspectionTypeT.InspectionType,
Count(DataEntryT.InspectionType) AS CountOfInspectionType
FROM DataEntryT JOIN InspectionTypeT ON DataEntry.InspectionType =
InspectionTypeT.ID
WHERE DataEntryT.[WO #]=["WO #"]
GROUP BY DataEntryT.[WO #], InspectionTypeT.InspectionType;

[MAANI]

Duane,
InspectionType Field is TEXT in both tables.
Below is my DataEntryT and InspectionType Tables:
*DataEntryT*
ID [AutoNumber] (Primary Key),WO # [Number],Date Inspected
[Date/Time],InspectionType [Text],Group [Text],GroupItems [Text],Fault
[Text],Fault Description [Text],Comments [Text]
*InspectionTypeT*
ID [AutoNumber] (Primary Key),InspectionType [Text]

[Duane Hookom]

You replied earlier:
=================================

Inspection Type is one of DataEntryT fields and its a combobox,I set the
lookup field properties for this field as follows (combo
box,Table/Query,InspectionTypeT,1,2,No,0";2",16,Auto,Yes,No,blank,No).
To make Inspection Type a combobox I created a table InspectionTypeT,with
two fields,ID and Inspection type.

=================================
The InspectionType field in DataEntryT will store the 1st field from
InspectionTypeT which is the ID field which is NUMERIC. Thus if you have set
the InspectionType field in DataEntryT to TEXT, it is wrong. Clearly the
field is storing the ID field value from InspectionTypeT which is NUMERIC.
That is why your chart shows numbers (actually stored in a text field).

I suggest you change the data type of InspectionType field in DataEntryT to
NUMERIC. It would also help if you hadn't given it the same name as the text
field in InspectionTypeT.

Then try the query that I suggested in my previous post.

Then NEVER use lookup fields in tables ever again ;-)