Results 1 to 13 of 13
  1. #1
    Join Date
    May 2014
    Posts
    98

    Unanswered: Report generates ID instead of field name

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so either JOIN these tables in the query in the report
    or
    use DLookup (although for performance reasons Id be very wary of using dlookup in a report if the lookup is performed frequently
    or
    use a recordset within the report to retrieve the correct value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Posts
    98
    SELECT qryStartificationOfRootCause.txtRootCause, Sum(qryStartificationOfRootCause.CountOftxtRootCau se) AS SumOfCountOftxtRootCause
    FROM qryStartificationOfRootCause
    GROUP BY qryStartificationOfRootCause.txtRootCause
    ORDER BY Sum(qryStartificationOfRootCause.CountOftxtRootCau se) DESC;



    This is my query in report what kind record set can be used for retrieving the correct value in report

    or how should I use dlookup for getting value

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dont use dlookup, unless you have to......
    not knowing your table design its hard to say


    see:- The Tom Kyte Blog: How to ask questions
    http://www.dbforums.com/6611305-post10.html
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2014
    Posts
    98
    I am attaching table design please let me know if you need something more
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so which column as the integer (numeric value) that is causing the proble

    which table has the descriptor

    ..Im guessign you are going to reposnd by saying you've used the lookup wizard, which frankly is a really, really crappy idea from Microsoft. create a sepaparte table with the index and descriptor

    eg:-
    mylookuptable
    ID integer, PK
    description text(50)


    then you change the table design to store the integer value
    defien a refererential integrity (RI) realtionship between the table and the looklup table.

    then when you open the query add the new table, the link shoudl appear and pull the description as required
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2014
    Posts
    98
    I have done in the same way as you have mentioned however query on which my report runs is not joined with anything so i am confused on which table to join it to get Field name instead of ID

    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));


    Thanks for your help and patience

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if you are confused, then there is absolutely no chance Im going to be able to do any better

    what column has the integer value that you are expecting to see as a text value?
    what table are those values stored in?
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2014
    Posts
    98
    I know I am disgusting but thanks for your patience

    ok here are my details

    Table 1 = RootCause which has ID and Text field
    Table 2 = Countermeasure has ID and Text field
    Table 3 is Lookuptable as you mentioned 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));

    I also tried what you mention here


    create a sepaparte table with the index and descriptor

    eg:-
    mylookuptable tHIS IS MY (Rootcause-cOUNTERMEAURE tABLE WHICH HAS ID and text for Root Cause and Countermeasure )
    ID integer, PK

    description text(50)


    then you change the table design to store the integer value (I changed in Root Cause and Countermeasure to display ID's instead of text previously)

    defien a refererential integrity (RI) realtionship between the table (Which table are you referring here to) and the looklup table.

    then when you open the query (Query is the one I mentioned above) add the new table, the link shoudl appear and pull the description as required

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry that is way to complicated to describe
    Id suggest you clean your db of any sensitive intformation, then compact and repair the Access db, compress it into a zip ffile and post it here as an attachment

    I don't have a clue what what column is the the one that is reporting an integer as opposed to a text value
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2014
    Posts
    98
    Ok Let me try once more

    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));

    Properties of root cause is bound column 1
    countermeasure Bound column 2 in main form

    Root Cause Bound column 1
    Countermeasure Bound Column 2

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

  12. #12
    Join Date
    May 2014
    Posts
    98
    Please have a look over the screen shots of my database
    Attached Files Attached Files

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this is way too confusing for me.....

    what you need to do is use a join to pull the decription from the relevant table(s) using the parent/child key relationship

    looking at your screen shot it looks to me as if you may not have defined relationships, ideally as Access is a RELATIONAL db you should define relationships between tables AND enforce RI (referential integrity)

    what you are looking for in your query is somethign like

    Code:
    select t2.descriptor,  Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
    from tblMainTWTTPSheet
    left join mylookuptable as t2 on tblMainTWTTPSheet.foreignkey = t2.primarykey
    GROUP BY t2.descriptor
    HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));
    repalce mylookuptable with the name of your other table
    foreignkey with the name if the integer column that is the foreign key in the main table that 'points' to the primary key in the lookup table
    primarykey with the name of the integer column that is the primary key in the lookup table
    descriptior with the name of the description in the lookup table for the primary key.

    I can't make out (or possibly willfuly won't make out) from your table design which is which
    Last edited by healdem; 06-11-14 at 06:14.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •