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 !!
:
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.
--
Duane Hookom
Microsoft Access MVP
:
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?
:
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?
--
Duane Hookom
Microsoft Access MVP
:
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
!!!!!
:
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
;-)
--
Duane Hookom
Microsoft Access MVP
:
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.
:
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
Microsoft Access MVP
:
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).
:
You state "not the Inspection Type Table" but your Row Source is from
FaultsQ. What are the significant fields and tables in this query.
--
Duane Hookom
Microsoft Access MVP
:
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
:
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
:
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
