Word & Access Problem

[Ian Yorke]

In our Database we have lookup tables for city and state. example

CityId City StateID State
10 New York 50 NY
11 Atalnta 51 GA

our customer table has many fields: FirstName, LastName, Company, City,
State, etc.

City and State uses the look up tables described above to get their values.
We created a query based on the customer table to create mailing envelopes.
the problem is that word show only the CityID and StateID in word example

John Smith
Smith Company
11 51

Instead of : Atlanta Ga

Thanks
Ian

 

[Doug Robbins - Word MVP]

You need to create a select query in Access that links the City and the
State tables to the Customer table on the City ID and State ID respectively.
Then use that query as the data source for the mailmerge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Ian Yorke]

Hi Doug i am not sure what you mean. In the customer table the city and state
tables are alredy linked. what i mean is that if i enter a new customer is
can select the city and state for the respective combo box in the customer
table. if i vew the query the report show the correct city and state

our poblem is when we use the datebase for the mail merge in Ms Word. the
information displayed on mailing labels is only Id numbers for city and state

 

[Peter Jamieson]

If your "base table" (probably "customer") has lookups defined in Access,
all bets seem to be off. In theory, if you have a query along the lines of

SELECT customer.id, customer.name, city.name, state.name
WHERE customer.cityid = city.cityid
AND city.stateid = state.stateid

you should get the sort of result you need, but Access seems to do its own
thing - can you tell us what SQL the query you are using as your data source
actually says?

Peter Jamieson

 

[Ian Yorke]

Hi peter here is the sql used for the query to get the information for the
mailing labels

SELECT Customer.Sal, Customer.[ContactFirst Name], Customer.ContactLastName,
Customer.CompanyName, Customer.Number, Customer.StreetName, Customer.Suite,
Customer.City, Customer.ProvState, Customer.PostalZip, Customer.Inactive
FROM Customer
WHERE (((Customer.Inactive)=No));

 

[Peter Jamieson]

Hi Ian,

Typically, in your Customer table, you will actually have a City /ID/ of
some kind, and perhaps a "ProvState" /ID/ of some kind, and the information
for that City will be in another table (perhaps called "City") and so on. I
can't know for sure because eevry database is dfferent.

But let's suppose you have tables for Customer, City and State.

Let's suppose that your Cusstomer table's City column actually contains the
primary key for a table called City, and that that table's primary key id
called ID. Lets' assume that the City table has a column called CityName
which contqains the City's name, which is what you actually expect to see in
"Customer.City")
Let's assume similar stuff about a table called "State"

Phew!

Then try SQL like

SELECT Customer.Sal, Customer.[ContactFirst Name], Customer.ContactLastName,
Customer.CompanyName, Customer.Number, Customer.StreetName, Customer.Suite,
City.CityName, State.StateName, Customer.PostalZip, Customer.Inactive
FROM Customer, City, State
WHERE (Customer.Inactive=No)
AND Customer.City = City.ID
AND Customer.State = StateID

Just my 2c-worth.

Peter Jamieson

Hi peter here is the sql used for the query to get the information for the
mailing labels

SELECT Customer.Sal, Customer.[ContactFirst Name],
Customer.ContactLastName,
Customer.CompanyName, Customer.Number, Customer.StreetName,
Customer.Suite,
Customer.City, Customer.ProvState, Customer.PostalZip, Customer.Inactive
FROM Customer
WHERE (((Customer.Inactive)=No));

--
Thanks
Ian

If your "base table" (probably "customer") has lookups defined in Access,
all bets seem to be off. In theory, if you have a query along the lines
of

SELECT customer.id, customer.name, city.name, state.name
WHERE customer.cityid = city.cityid
AND city.stateid = state.stateid

you should get the sort of result you need, but Access seems to do its
own
thing - can you tell us what SQL the query you are using as your data
source
actually says?

Peter Jamieson