Word & Access Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Yorke - 17 Nov 2006 19:09 GMT

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

Reply to this Message

Doug Robbins - Word MVP - 17 Nov 2006 20:42 GMT

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.

Signature

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

> In our Database we have lookup tables for city and state. example
>
[quoted text clipped - 19 lines]
> Thanks
> Ian

Reply to this Message

Ian Yorke - 17 Nov 2006 20:58 GMT

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

Signature

Thanks
Ian

> 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.
[quoted text clipped - 23 lines]
> > Thanks
> > Ian

Reply to this Message

Peter Jamieson - 17 Nov 2006 21:26 GMT

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

> Hi Doug i am not sure what you mean. In the customer table the city and
> state
[quoted text clipped - 36 lines]
>> > Thanks
>> > Ian

Reply to this Message

Ian Yorke - 17 Nov 2006 22:00 GMT

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));

Signature

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
[quoted text clipped - 48 lines]
> >> > Thanks
> >> > Ian

Reply to this Message

Peter Jamieson - 17 Nov 2006 22:22 GMT

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
[quoted text clipped - 67 lines]
>> >> > Thanks
>> >> > Ian

Reply to this Message