Solved Report not displaying the same info as query from which it was created. (1 Viewer)

[Missomissou]

Hi--I created a little query,

which work beautifully to display all of the publications produced by authors who work in my organization. It looks like this. (Note, I used lookup fields (data is housed in a separate table) to ensure consistency when names are entered. Yes, I--the developer--have entered data directly into the table. In this screenshot you can see the lookup menu. Only one value/name is allowed per record here.)

Unfortunately, the report I created from the query is displaying the StaffID number instead of the author's name. I haven't bothered spending any time tweaking it, so it's clunky, but it looks like this:

I was hoping I might be able to control data displayed by adjusting the column widths, but there's only one column, which displays the StaffID number, showing up as "FirstALWRIAuthor" here.

This isn't very useful information to report.

Any ideas?

 

[CJ_London]

That’s one of the problems with lookup fields - the solution is to include the lookup table in your query and return the details required

 

[The_Doc_Man]

CJ beat me to it. You use an external table as a lookup. That means you can do a JOIN with that table in your query to pull in the translation of the index to a text field. Then your report can include the text rather than the index.

 

[Missomissou]

Ok--I started down a new path... removing the lookup option from the original table to see if I could start from scratch. Changing that field to text box caused all the entered names to revert to the StaffID number. But it also made the associated form go bonkers and sent me down a rabbithole, trying to put a listbox on the form for data entry. I'll bang my head against this wall for a while and see whether the stars align.

 

[plog]

I'd give some thought to fixing your tables before you proceed with anything.

1. You need a seperate Authors table.

1A--Each discrete piece of data needs to go into its own field. You are jamming a bunch of data into the Authors field and losing a lot in the process. Instead of just a big text field where you allow users to type in one author after another, you need a whole new table where they can select authors to add. My guess is with some records you are bumping up against the size limits of text. You will not have that issue when you make another table. Also, and this I know because I am looking at it--you are inconsistently adding Authors. In one record I see 'Armatas, Christopher A.' and in another I see 'Armatas, C.A' and in a third 'C.A Armatas'.

1B--You don't store values in field names. You've got 3 types of authors and you are differientiating them by placing their data in different fields, that is incorrect. Instead, in the Authors table you need to build you add an additional field called 'AuthorRole' in which you designate if they are just an author, a FirstALWRI author or an ALLALWRI author. When you do that it makes it easier to search and filter your data.

2. I think you've used the wrong datatype for Years. Going by the justification on the data in the YearPublication field, I think you have that as a text field and not as a number. You need to store values in their right types so that they sort correctly and you can operate (use built in functions and operators) on them.

3. Using reserved words in names. 'Date' and 'Number' are bad field names because Access uses them internaly. When you use them as names it makes coding and querying more difficult. So instead of such generic names I suggest you prefix or suffix them with what they represent. For example, EntryDate, DateCopyright, ISDNNumber etc.

Get your tables right before you move on.

 

[Pat Hartman]

You need to revert to the copy of the schema that had the lookup fields in order to fix the problem. Once you break the lookup relationship, you make a big problem for yourself to resolve.

In order to normalize the schema, you need to extract the data from the lookup fields before you get rid of them. It is tedious but you can do it.

First you need to learn something about the special SQL needed to work with the lookup fields. You will need to create a query that returns one row for each lookup value rather than a query that returns all the lookup values mushed as a semi-colon separated string. Once you have the query that returns separate rows, then you can use that query to append the individual authors to the junction table that connects books with authors.

Maybe someone will volunteer to help with this. It won't be hard for someone who understands the specialized lookup fields SQL syntax.

Once you've created the junction table, you can delete the lookup field, but NOT before. Then on your forms, you need to replace the list box you were using to display the authors with a subform that serves the same purpose. The subform will use a combo box to allow you to select the authors one at a time. The technique is a little different from checking a box in a listbox but the result is the same. You end up with a list of authors for each publication.

 

[Missomissou]

I'd give some thought to fixing your tables before you proceed with anything.

1. You need a seperate Authors table.

1A--Each discrete piece of data needs to go into its own field. You are jamming a bunch of data into the Authors field and losing a lot in the process. Instead of just a big text field where you allow users to type in one author after another, you need a whole new table where they can select authors to add. My guess is with some records you are bumping up against the size limits of text. You will not have that issue when you make another table. Also, and this I know because I am looking at it--you are inconsistently adding Authors. In one record I see 'Armatas, Christopher A.' and in another I see 'Armatas, C.A' and in a third 'C.A Armatas'.

1B--You don't store values in field names. You've got 3 types of authors and you are differientiating them by placing their data in different fields, that is incorrect. Instead, in the Authors table you need to build you add an additional field called 'AuthorRole' in which you designate if they are just an author, a FirstALWRI author or an ALLALWRI author. When you do that it makes it easier to search and filter your data.

2. I think you've used the wrong datatype for Years. Going by the justification on the data in the YearPublication field, I think you have that as a text field and not as a number. You need to store values in their right types so that they sort correctly and you can operate (use built in functions and operators) on them.

3. Using reserved words in names. 'Date' and 'Number' are bad field names because Access uses them internaly. When you use them as names it makes coding and querying more difficult. So instead of such generic names I suggest you prefix or suffix them with what they represent. For example, EntryDate, DateCopyright, ISDNNumber etc.

Get your tables right before you move on.

This is incredibly helpful, well-written, and easy to understand. Thank you so much! I'm thinking about how/whether to proceed with a new author's table. I only need to track the products of about ~10 people who work in our org. The rest of the co-authors (crammed into that single field you noticed) are almost a blob of extraneous info. Technically we don't need to track it--it's just added information in case we need to find the full citation later, for some reason. Or... perhaps I should create this separate author's table and enter them as external collaborators--many of them are frequent co-authors--that would help if we wind up wanting to generate a comprehensive bibliography with consistently formatted citations. In any case, you've given me a lot to think about. I've been cleaning up other tables this evening and will definitely come back to this one again in the AM. I'm so grateful for the time and thought you put into this response.

 

[Missomissou]

You need to revert to the copy of the schema that had the lookup fields in order to fix the problem. Once you break the lookup relationship, you make a big problem for yourself to resolve.

In order to normalize the schema, you need to extract the data from the lookup fields before you get rid of them. It is tedious but you can do it.

First you need to learn something about the special SQL needed to work with the lookup fields. You will need to create a query that returns one row for each lookup value rather than a query that returns all the lookup values mushed as a semi-colon separated string. Once you have the query that returns separate rows, then you can use that query to append the individual authors to the junction table that connects books with authors.

Maybe someone will volunteer to help with this. It won't be hard for someone who understands the specialized lookup fields SQL syntax.

Once you've created the junction table, you can delete the lookup field, but NOT before. Then on your forms, you need to replace the list box you were using to display the authors with a subform that serves the same purpose. The subform will use a combo box to allow you to select the authors one at a time. The technique is a little different from checking a box in a listbox but the result is the same. You end up with a list of authors for each publication.

This is great information. Thank you so much for this thoughtful response. I had to generate a couple of queries with other messy (now tidied) tables for a meeting in the AM, but will spend more time with this issue in the morning. I'm so grateful for the time and thought you put into this response! Thank you!

 

[Pat Hartman]

You're welcome