Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    New York, New York, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup field in report shows number, not name (2002 (10.2627.2625))

    I need help learning how to get a report to show information from a lookup field.

    I am working on a simple database for the parents group of a public middle school in New York City. Each student is assigned an advisor. I'm trying to create a phone list report that lists students and their parents by advisor, one page per advisor. The students and parents are currently included as fields in the same table, along with contact information for the household.

    I'm stumped by the relational issues in creating a lookup field for advisors, and then trying to include the advisors names in a report. I made one table that lists all the advisors' last names, and then I created a lookup field in the student table that uses the lookup table to enter the advisor. I had no trouble setting up the lookup field, and though the table was actually storing the advisor's numeric ID, the query showed the advisor's name.

    But then when I try to use that query to prepare a report, starting a new page for each advisor, I get the advisor's number, not his/her name, at the top of each page.

    I tried creating a relationship between the student table and the advisor's table, so that I could have fields from the student table and the advisors table in the same query, but when I did that it went nuts. It somehow made some kind of double link, which multiplied the number of records in the query - there were then more than 3,000 records, instead of 216. I think that it showed each student multiple times, linked with every advisor.

    For now, I just exported the report into a Rich Text Format report, and manually entered the advisor names at the end of each page. But I'd love to know how to generate this query and report properly without going through this extra step.

    Can someone explain to me what might be going wrong here, and how I can fix it? I've tried varying the join properties, but haven't found the right combination yet.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup field in report shows number, not name (2002 (10.2627.2625))

    When you create your query, with the linked tables, in the design view, select View / Join Properties and check the option :
    Include all records from 'Student Table' and only those records from 'Advisor Table' where the joined fields are equal
    This should give you the 216 wanted records.
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup field in report shows number, not name (2002 (10.2627.2625))

    Lookup fields are really a sort of query used by the combobox in the table to show the related information. You can't do it that way in a report, because it uses the actual value of the field, not the lookup that the user sees. If you base your report on a query, you can join the student table to the advisor table on whatever the actual key field is that links the two using an OUTER JOIN, which means that if you double click on the relationship line in your query, you set the relationship to use all the records in the student table and only those in the advisor table that match. Then drag just the advisor name onto the query grid and don't use any other fields in that table in the query. Make sure the in the query properties you set the Output All Fields property to No and include only the fields you need. The sorting and grouping is handled by the report itself, not by the query.
    Charlotte

Posting Permissions

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