Transfering Tables/Queries to Mail List (before xmas!)

[Guest]

We use Access 2003 to keep client contact details. I have just updated this
with new clients (to do a xmas mail list).

When I go to merge all the client details into a "mail list", the new
clients details do not appear.

I have tried to edit the "relationships", selecting "enforce referential
integrity" and then within "Join Properties", selected type 2: include all
records..." but to no avail.

Any help would be much appreciated!

 

[Guest]

Hi Nicole,

What is a "mail list" ? Is this a query? If so, please copy the SQL View
and paste it into a reply. You can access the SQL View by opening a query in
design view, and then clicking on View > SQL View.

If this is not a query, what is it? What is the name of your table, and the
names of the fields within this table, that include the new contact records?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom, thanks so much for the quick response.

I have to admit I'm in a new job, and trying to teach myself how to use this
database...

Basically our database is a collection of all our clients business cards,
and the mail list is a cumulative list of every client and their details.
Within the mail list we can select what type of mail it is eg xmas card, new
years greeting, etc.

Under the list of "Objects", the "mail list" comes up as both a Query
(Select Query), and a Table. I think it is a Query though.

When I do a database view of the client list, my new additions are there.
But these do not appear on the mail list (as all other clients do).

I tried "link tables" to refresh, but nothing.

I tried going into relationships and making sure the tables were linked,
still nothing.

The SQL View for the "mail list" is:

SELECT DISTINCTROW CompanyMaster.Name, LocationMaster.*,
MailCheckList.ClientDataKey, MailCheckList.Send, MailCheckList.Reply,
MailCheckList.Show, LocationMaster.*
FROM (CompanyMaster INNER JOIN LocationMaster ON CompanyMaster.AutoKey =
LocationMaster.CompanyKey) INNER JOIN (ClientData INNER JOIN MailCheckList ON
ClientData.AutoKey = MailCheckList.ClientDataKey) ON LocationMaster.AutoKey =
ClientData.LocationKey;

Sorry for the ignorance - really appreciate the help!!

 

[Guest]

Hi Nicole,

Under the list of "Objects", the "mail list" comes up as both a Query
(Select Query), and a Table.

It must have a slightly different name if it is showing up as a table and a
query. The reason I say this is that it is impossible to have a table and a
query share the same exact names.

When I do a database view of the client list, my new additions are there.

I assume the "client list" is a table? Would this be a table named
"ClientData"?

Your query includes several INNER JOINs. What this means is that the query
will only return records where there are matching key fields in both tables.
My hunch is that you are missing foreign key values for the new records that
you added.

In your first message, you indicated that you tried different join properties:

I have tried to edit the "relationships", selecting "enforce referential
integrity" and then within "Join Properties", selected type 2: include all
records..." but to no avail.

These properties do not affect any existing queries. Only new queries that
are created after the join properties are changed. However, you can make a
similar change in the join properties in the query itself. Here is an article
published by Access MVP Allen Browne, which I'm thinking likely explains the
issue you are experiencing:

The Query Lost My Records! --->See "Nulls in Joins"
http://allenbrowne.com/casu-02.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom


Under the list of "Objects", the "mail list" comes up as both a Query

It must have a slightly different name if it is showing up as a table and a
query. The reason I say this is that it is impossible to have a table and a
query share the same exact names

Yes - under tables it is "MailCheckList", and under Queries it is
"qryMailCheckList".

When I do a database view of the client list, my new additions are there.

I assume the "client list" is a table? Would this be a table named
"ClientData"?

Yes.

Your query includes several INNER JOINs. What this means is that the query

will only return records where there are matching key fields in both tables.
My hunch is that you are missing foreign key values for the new records that
you added.

OK, other than re-entering, how can I get those missing foreign keys?

Thanks for the article...it made a lot more sense. But!! I went to
"double-click the line that joins the two queries, and Access will pop up a
dialog box asking what type of JOIN you want. Select the appropriate OUTER
JOIN...". Now when I go to access the qryMailCheckList, a window pops up
saying the SQV cannot be accessed because of ambiguous joins...

I have no clue how to make the suggest new query and set up the join etc.
Anyway of undoing this?

Sorry for the inane questions! Appreciate the help (and on the verge of
doing a mail merge in word!!!!)

 

[Guest]

Hi Nicole,

Is there any way that you can send me a zipped copy of your database. I
promise to treat your information as absolutely confidential. The reason I
ask is that I can likely diagnose the problem in a couple of minutes and get
you back to using Access to be productive.

I am leaving for a trip in the next few days, so tonight and tomorrow night
will be my only opportunities to try to help you out. Otherwise, someone else
will need to step in.

If you can do this, send me a private e-mail message with your attached
file. My e-mail address is available at the bottom of the contributor's page
indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________