merge to word shows primary key instead of value

[jazminecat]

Hi again -

I have a database that holds all the addresses for our mailings. The master address table is related to the city, state, and zip tables. The city field in the master address table, for example, is a listbox text field, with a lookup to the city table which says:
SELECT [CitiesTable].[ID], [CitiesTable].[City] FROM CitiesTable;

Bound colum is 1 (the primary key) column count = 2, column widths 0,1. So when I enter data into the forms based on this table, it's a drop down that shows the city names.

The query just pulls the info from the master address table, and it shows the values of each field, not the primary key. However, if I try to do a mail merge to word from a query based on this table, it lists the primary key numbers for the city, state, and zip, which unfortunately won't get it to it's destination. How do I change my table or query so that if I merge it will list the actual city, state, and zip code?

thanks in advance!

jazmine

 

[carrick]

Hi jazminecat,

The query that you use to merge into Word needs to include both tables, the master address table and the CitiesTable. When you add the two tables to the query, the join line should be present indicating how the two tables are joined. If the line doesn't appear, the relationship hasn't been established and the query won't work as planned.

Once you have the two tables within the query, add the city name field from the CitiesTable rather than from the master address table. This should insert the name of the city rather than its ID number.

Good luck.

 

[jazminecat]

Hm, when I do that, the query returns no records.

 

[carrick]

Can you post the SQL for your query. It may be the join type is incorrect.

 

[jazminecat]

Here's the sql that returns the information correctly, but when I use it to merge into word returns the primary keys of the city, state, and zip:

SELECT [Master Addresses].Title, [Master Addresses].FirstName, [Master Addresses].LastName, [Master Addresses].Company, [Master Addresses].Address1, [Master Addresses].Address2, [Master Addresses].City, [Master Addresses].State, [Master Addresses].Zip, [Master Addresses].[Rider Reader], [Master Addresses].[Annual Rept], [Master Addresses].[Job Title]
FROM [Master Addresses]
WHERE ((([Master Addresses].[Rider Reader])="y&quot AND (([Master Addresses].[Annual Rept])="Y&quot);


And here's the SQL where it returns nothing (I only added the Cities table, for brevity, but it does the same thing with all three):

SELECT [Master Addresses].Title, [Master Addresses].FirstName, [Master Addresses].LastName, [Master Addresses].Company, [Master Addresses].Address1, [Master Addresses].Address2, [Master Addresses].City, [Master Addresses].State, [Master Addresses].Zip, [Master Addresses].[Rider Reader], [Master Addresses].[Annual Rept], [Master Addresses].[Job Title]
FROM CitiesTable INNER JOIN [Master Addresses] ON CitiesTable.City = [Master Addresses].City
WHERE ((([Master Addresses].[Rider Reader])="y&quot AND (([Master Addresses].[Annual Rept])="Y&quot);

 

[carrick]

From what I can see, you have a couple of problems.

1) The first SQL statement only contains information from your [Master Addresses] table. As per your information in the earlier post, this table only stores the primary key number for the city, etc. Therefore, this is the only thing that it can display when you merge it with Word.

2) The second SQL statement that you provided is closer, but you are trying to link fields with very different contents, and are getting the results (which is nothing). If you notice the link between the two tables

FROM CitiesTable INNER JOIN [Master Addresses] ON CitiesTable.City = [Master Addresses].City

you'll see that you are trying to compare the CitiesTable.City (containing something like Seattle) and the [Master Addresses].City (which probably contains a number). Change the SQL in the link to CitiesTable.ID instead. This will link the number in your cities table to the number in your master addresses table.

Good luck.

 

[jazminecat]

hm, well it won't allow me to do that either. See, in the table, the Cities field is a text field, and the lookup to the City table in that field is:
SELECT [CitiesTable].[ID], [CitiesTable].[City] FROM CitiesTable;

so when I try to change the query to link the Master Addresses.Cty to the City.CityID field, I get a type mismatch in the expression.