Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reporting on Access (2003)

    Ok, so before I start, I should point out that until today, I'd never really used access, so I'm a bit a of a numpty...
    I've created a database with multiple tables, and set up a form to enter my data. On this form are a number of combo boxes, that look up default information from other tables to enter into the main database. Everything works fine until I come to report on the database. USing query, crystal reports, or the built in access report tool, I get the layout of the report fine, but the fields populated using the lookup combos display the number of the record, not the text... ie, in a list of four items apple, pear, orange and lemon, if I select lemon, this would display as a number 4 in the report...

    What am I doing wrong, and how do I make it all better?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reporting on Access (2003)

    One option is to use combo boxes for the lookup fields on the report too. You can copy the combo boxes from the form into the report, then adjust their formatting as needed.

    Post back if this doesn't help.

  3. #3
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting on Access (2003)

    Hi Hans,

    I'll give that a go...

    However that wont solve the problem when using crstal reports and query.

    One of the key requirements is to produce reports that are filterably by different criteris, sorted and grouped in certain ways, and, most importantly, exportable to excel...

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reporting on Access (2003)

    The other option is to create a query based on your main table, and to add the lookup tables.
    Join the lookup tables to the main table on the appropriate fields.
    Double click each of the join lines, and select the option to return all records from the main table and only related records from the lookup table.
    Add the fields from the main table to the query grid, with the exception of the lookup fields. Instead of those, add the description field from each of the lookup tables.
    Use this query as record source for the report in Access and in Crystal Reports. (Why anybody would want to use Crystal Reports with Access beats me, but never mind)

  5. #5
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting on Access (2003)

    Hi Hans...

    Tried that, and it's still returning a number [img]/forums/images/smilies/sad.gif[/img]

    (I'm using crystal, as I know how to build quite compex reports quickly... with access an the other had, I don't lol... only started playing with it today :-) )

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reporting on Access (2003)

    You must use the description fields instead of the number fields in the report. That was the reason for adding the description fields to the query.

  7. #7
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting on Access (2003)

    ok...

    Guess I'm being a bit dim here.

    Rebuilt the query as instrucred, and built a very simpe table. in the lookup field, I just get blanks now...???

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reporting on Access (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting on Access (2003)

    Thanks, Hans [img]/forums/images/smilies/smile.gif[/img]
    Attached Files Attached Files

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reporting on Access (2003)

    I assume Hans is off fixing this but I just wanted to make comment on your database. I would fix this but it is 11.00pm and it is getting late for big fixes. There is a misconception around that ALL Access tables require a primary key, this is not the case. The time when this is acceptable is when you are using look up tables, that includes your supplier table, your status table and job role. All these tables just require one field and that tis the description. You can still link them to your forms and this will allow that value to be placed in the table when you select it from a combo. The reason you are getting numbers in your reports is because the the primary key is bound to that control.

    I would redo your tables and then recreate the forms....you will then just need to create the report again.

    Sorry, this sounds like you have to redo all this again but I strongly suggest you do as the work around can be very confusing, especially if you are new to the Access game ( I mean this nicely and not condescendingly) and this will be a foundation brick to further development
    Jerry

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reporting on Access (2003)

    There's no query in that database. What table is the main table and which one is the lookup table.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reporting on Access (2003)

    You had defined many number fields as text fields, and the relationships between the tables were on the wrong fields. You must always join tables on a key field, not on the description.

    You need an extra receipts table, and an extra products table, to implement the many-to-many relationship in sku details.

    Note that the description of all products is the same as the product code in your database. Not very "descriptive".

    See the attached version. I have modified the table structure, created relationships, created the query and modified the report.

    Note: I took the liberty of changing 'reciept' to 'receipt'.
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting on Access (2003)

    Thanks,everyone, for all your help... I've managed to sort the problem in my dtaabase. for each feild on the form, on the properties box, in the data tab, I've changed the "bind to" property to equal the field number I wanted to display... [img]/forums/images/smilies/smile.gif[/img]

    I'll work on tidying up my database now [img]/forums/images/smilies/smile.gif[/img]

    Thanks again

Posting Permissions

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