Report generates ID instead of field name

[jj1]

I have 3 dynamic combo box. a combo box > filters b combo box and b filters c combo box. showing the field names whereas connected on field ID's. But my problem is when I am running reports (reports in form of charts) on the data saved in the backend through these combo boxes on the form. When I run report it shows ID's of combo box B instead of field name. Please help

[ItsMe]

Comboboxes should be used in forms to store Primary Key values in Foreign Key fields of tables. Afterwards, when you look at the table, you should see many numbers in many fields. Looking at the table should NOT be intuitive. Build a query to join tables and retrieve relative rows from related tables. Queries should be joined on the Primary Key and Foreign Key fields (the fields with all of the numbers). It is the JOIN within your query that retrieves data that is intuitive to humans. Base your reports on queries.

[jj1]

Can you simplify what you are explaining I think we are close. I am so confused as trying to solve it from last 3-4 days So to get field name on report A which run through query

SELECT qryStartificationOfRootCause.txtRootCause, Sum(qryStartificationOfRootCause.CountOftxtRootCau se) AS SumOfCountOftxtRootCause FROM qryStartificationOfRootCause GROUP BY qryStartificationOfRootCause.txtRootCause ORDER BY Sum(qryStartificationOfRootCause.CountOftxtRootCau se) DESC;

Comboboxes should be used in forms to store Primary Key values in Foreign Key fields of tables. Afterwards, when you look at the table, you should see many numbers in many fields. Looking at the table should NOT be intuitive. Build a query to join tables and retrieve relative rows from related tables. Queries should be joined on the Primary Key and Foreign Key fields (the fields with all of the numbers). It is the JOIN within your query that retrieves data that is intuitive to humans. Base your reports on queries.

[ItsMe]

Where did that SQL come from? How many columns/fields are in the SELECT statement of qryStartificationOfRootCause? Does qryStartificationOfRootCause display the literal text you are after when you open it in Datasheet view?

Perhaps qryStartificationOfRootCause is a Dynamic Parameterized query that is dependent on your Combo Box. In that case, you need another query to base your report upon. Review post #2.

[jj1]

You are correct

Where did that SQL come from? How many columns/fields are in the SELECT statement of qryStartificationOfRootCause? Does qryStartificationOfRootCause display the literal text you are after when you open it in Datasheet view?

Perhaps qryStartificationOfRootCause is a Dynamic Parameterized query that is dependent on your Combo Box. In that case, you need another query to base your report upon. Review post #2.

[jj1]

so should I join my main table with root cause table
and then the query which I mentioned will show up join if I add root cause table to query. But I donot have rootcause ID which could be foreign key in primary table (main table)

You are correct

[ItsMe]

...But I donot have rootcause ID which could be foreign key in primary table...

This could be an issue. The good news is that since you just want to run a report, you do not have to worry about editing data. The query for your report does not need to be updatable.

Suggest you take a look at some existing query objects. Perhaps you can use one of those as a subquery in a new query. Your new query might include your main table or even multiple query objects. But yah, you need to find some Key fields to get your JOINS.

[June7]

I am confused. If rootcause table does not have ID for saving into main table, what are the comboboxes saving? How are main and rootcause tables related?

Recommend not building Lookups in table. Review: http://access.mvps.org/access/lookupfields.htm

If you want to provide db for analysis, follow instructions at bottom of my post.

[ItsMe]

...Recommend not building Lookups in table...

I think June is on to something here. Plus, I am confused too.

[jj1]

Well for root cause have an ID but I have not created a field in Main table for rootcause ID


Here are my full details

Table 1 = RootCause which has ID and Text field
Table 2 = Countermeasure has ID and Text field
Table 3 is Lookuptable which has its own ID and contains rootcause ID and Countermeasure ID.
Thats how my dynamic combo boxes work

now I created a query on my main table saving data from main form which is
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]))
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));

but when I am running report over this query its giving out ID not field names

I am confused. If rootcause table does not have ID for saving into main table, what are the comboboxes saving? How are main and rootcause tables related?

Recommend not building Lookups in table. Review: http://access.mvps.org/access/lookupfields.htm

If you want to provide db for analysis, follow instructions at bottom of my post.

[June7]

You get just the ID because of the Lookup in table. In spite of what you see in Main table, the actual value is the ID, not the text. Remove the Lookup setting in table and you will see the actual stored value. Read the referenced link for more info. If you want to show text in report then the query must include the lookup tables.

Which ID(s) are you saving in Main? The ID of Lookuptable or the IDs of RootCause and Countermeasure tables?

The alternative is to set the fields in Main as text type and save the text instead of ID.

[jj1]

I was not saving any ID in main table I was saving textvalues of Rootcause and countermeasure and text values are based on the query
SELECT HERCARootCauseTypesT.atnHERCARootCauseID, HERCARootCauseTypesT.txtRootCause
FROM HERCARootCauseTypesT;
and similarly for Countermeasure

so basically I was saving for root cause and when i run the query
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]))
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));


It generate results as

Lack of skills 1
Lack of knowledge 2
etc

so in query I get the root cause field name and their count

However by running report on same query it shows up ID for root casues instead of Field name

Please help

You get just the ID because of the Lookup in table. In spite of what you see in Main table, the actual value is the ID, not the text. Remove the Lookup setting in table and you will see the actual stored value. Read the referenced link for more info. If you want to show text in report then the query must include the lookup tables.

Which ID(s) are you saving in Main? The ID of Lookuptable or the IDs of RootCause and Countermeasure tables?

The alternative is to set the fields in Main as text type and save the text instead of ID.

[June7]

You say report is based on Main table and you see ID values then you are not saving text into Main table.

If that first query is the SQL of combobox RowSource property, it includes the ID field and if combobox BoundColumn property is 1 then you are saving the ID, not the text.

Do you have Lookups set in the table? If so, remove them (change to textbox in table setup on Lookup tab). Then view the table and what do you see in the fields?

[jj1]

Yes I have combo box set up in my main table for root cause and when I changed to text box in Lookup in main table table started showing ID of root cause. I think we are close

You say report is based on Main table and you see ID values then you are not saving text into Main table.

If that first query is the SQL of combobox RowSource property, it includes the ID field and if combobox BoundColumn property is 1 then you are saving the ID, not the text.

Do you have Lookups set in the table? If so, remove them (change to textbox in table setup on Lookup tab). Then view the table and what do you see in the fields?

[June7]

Now build query that includes Main, RootCause, Countermeasure tables. Use join type "Include all records from Main ..."