Getting Symbols instead of text on query

[mburgan]

I have created a table with a field that is Long Text and a form to enter this information. All data is showing correctly on both the table and the form. If I run a query on just that table all is still ok but when I run a query on that table joined with another table I am getting symbols instead of text in the Long Text field. Any ideas on why this is happening?

[ranman256]

Try setting the tables to the same font.

[mburgan]

The table I created the header and detail were different (evidently a default with access 2013). The tables and forms are now all Calibri size 11. I am still get symbols in the query.

[rpeare]

Have you got a sample database we could look at, I have never encountered this behavior.

[mburgan]

I also have never encountered this behavior. Even though I am listed as a novice I have used Access since 2007.
Unfortunately I do not and cannot recreate one. I work for a law firm and all data is confidential and one of my tables is from the firm software (it also is Access based) which is trademarked. I can tell you I have done the same thing with another table I created and that query is working fine. The only difference is that was created with an earlier version of access. This is my first creation since I upgraded to 2013.

[rpeare]

You can create a sample database and not include any private information, just have to make a copy of the database, wipe out anything not relevant to this conversation and fill in some garbage data, enough to replicate the problem.

You may need to force an import of the data rather than linking to it (if that's what you're doing) and run the query based on the imported table rather than the linked table to see if you get different results.

[mburgan]

I have created a sample. 2 tables no links. Same result.

[Micron]

I venture to say this is your problem http://allenbrowne.com/bug-18.html
While your conditions are not exactly as described, you are including a memo field in a query where 2 fields are not indexed.
I indexed the two joined fields and the problem went away, although I had to put notes in all the fields to get any notes returned in the result.

Is this a real example of how you have named and joined fields in your tables? If so, you probably often make your task much harder than it needs to be when it comes to getting reliable data in or out. I would elaborate, but maybe you are already quite proficient in normalization and simply threw this together.

[rpeare]

I agree with Micron, you should be linking tables via the primary key/foreign key, you are essentially linking the two tables on a text value, so let's say the name of the company changes you have just orphaned everything in your marketing info table. The marketinginfo table should contain the ContactID not the referral Atty. And once you add a primary key to your marketing info table the linking/memo field should work just fine.

[mburgan]

Micron, Thank you so much for the link on the bug fix. Instead of Group by I changed it to First. This fixed my results. Yes I know this is not set up quite right with primaries and indexes. The sample was stripped down and quickly put together.
rpeare, You both have a good point however I am grouping and counting contacts from the same firm so the contact ID would not work because there are several different ids involved. It is really a lot more complex as to what I am doing but was getting the same result just between 2 tables. The Marektinginfo table is the culprit of my problems. I will look at it some more as to how I can link it better.
You are both Awesome!!

[Micron]

Totally disagree with your statement. A tbl of businesses/customers with a contacts tbl is an example of normalization of multiple id's linked to a single Id. What you had to do to resolve your issue is an example of what I referred to when I said your task would be harder, and often, the deeper you go the worse it gets. I agree with rpeare that the way you linked two text fields is just wrong.
We're really all just trying to help and promote good practice so don't take this as criticism or condescending in attitude. I say this because of your point made about how long you've been working in Access.

[mburgan]

I in no way took it as criticism. I totally agree, understand and always welcome advice.
Unfortunately the contact table is in our firm database. I did not design nor can I change these tables because the program is trademarked. They did not design it so that I can tie people ids to a company/business id. All contacts stand alone with the company name on each. My headaches would be less if they had normalized them. The marketinginfo is for 1 secretary and will never go any deeper than this query so I'm not as concerned about that. I knew when I created it that it was bad practice but it was the best I could think of at the time. It was a great point about the firms changing names or even attorneys changing firms. I will have to look more into the links but for what I have to work with right now I can't think of any better way.

[rpeare]

Then you're stuck. You have to figure out a way to link the two tables based on a unique key or primary key field or you're hosed. The other option is to import one of the tables so it's considered 'local' then and have the primary key field be the company name but you'd have to re-import the table every time you opened the database to have it function correctly.

EDIT: it may be possible to build two queries, in one you add the PK of the table then link that via the PK to the appropriate record but I am not entirely sure that would work.

[Micron]

Whether or not you should do anything ought to be based on a risk/benefit basis. For a single user db with little risk to business, maybe nothing more than a simple solution if there is one. After all, it may be looked upon as though you are the only one suffering from it since you will be looking for and attempting to fix problems. If there is a significant chance that the company could take a financial or reputation hit because of lack of referential integrity, then you have a responsibility to make a case, AFAIC.

Hard to imagine that anyone passing themselves off as professional would not include a primary key in the table, or if they did, that there is no way to relate one of your marketing db tables. However, I'm saying that without knowing what you have to work with or exactly what you created. Yes, there is probably a way to automate an update of your table against the contact db (but recreating your table on the fly would not be an option), probably through linking and/or update queries. Maybe a linking table is needed too. It's all speculation on my part without a better understanding of what you have. So if you want help, suggest you dump relevant data from the other db and your table(s) into Excel and do a Find/Replace to easily eliminate sensitive data and eliminate columns (fields) that are unnecessary. Then post the workbook (each sheet tab could/should be your table names). We could import or link them into a test db and see what we come up with. It would be a bit of work and maybe worth it in the end, but then again, your risk analysis or assumption might determine that it's not worth the effort to you.