Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS Word Mail Merge Problem

Posted on 2004-04-17
8
387 Views
Last Modified: 2012-08-13
Hi,
    I'm having trouble with mergefields in Word. I cannot get the 'Next' or 'Nextif' fields to work! Background:

I have a a simple library database in Access 2K. I also working with MS Word 2K. The database has three tables: Members, Books, and Loans. the relationships are:

One       - 2 - many
Member - 2 - Loans
Loan      - 2 - Books

Now, I created a Query that gets all the data from all the tables where the DateDue<Today's Date.
then I created a mail merged Word document. I want to print the Word document so that it displays all overdue books and their details for each Member who has overdue books.

The crux of the problem is that I don't know how to display ALL the overdue books in each letter where each letter is to individual Members.

Thanks :D
0
Comment
Question by:doubleglazing2
  • 2
  • 2
8 Comments
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10851050
I think you want all the books to show up for every record.

To do this, you need to join to multiple instances of the overdue books. Estimate the maximum number of overdue books. if that number is over about 250, you're SOL as you can only get 256 fields from a query.

SELECT M.*, B1.Title, B2.Title, B3.Title, B4.Title, B5.Title .... Bn.Title
FROM Member as M inner join loans as L
on M.uniqueId = L.UniqueIdMember
INNER JOIN Books as B1
ON L.uniqueId = B1.UniqueIdLoan
INNER JOIN Books as B2
ON L.uniqueId = B2.UniqueIdLoan
INNER JOIN Books as B3
ON L.uniqueId = B3.UniqueIdLoan
...
INNER JOIN Books as Bn
ON L.uniqueId = Bn.UniqueIdLoan
WHERE (Pick first Book from B1)
and (Pick second book or NULL from B2)
and (Pick second book or NULL from B3)
...
and (Pick third book or NULL from B3)

Sorry, I can't help you with the picking the book part until we know more about what the table looks like.
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10851074
Looks like you're in the process of building this.

Add an int field to the books table called "BookNumber"

With Each loan, assign the books a number from 1 .. n (Remember to limit the number of books out at one loan to < 250 or so)
Then, when you're doing your query for books signed out, in your where condition use:

B1.BookNumber= 1
and
B2.BookNumber=2
and
B2.BookNumber=3
and
...
and
Bn.BookNumber=n

That'll do ya
0
 
LVL 1

Author Comment

by:doubleglazing2
ID: 10851125
Thanks.

Books Table:

BookNumber*
Title
Author
Cat
Certificate
LoanNumber (foreign Key)

Loans Table:

LoanNumber*
DateLoaned
DateDue
MemberNumber (foreign Key)

Members Table:

MemberNumber*
FirstName
Surname
DOB
Address1
Address2
TownCity
Postcode
PhoneNumber

--------------------------------
Relationships:

Members Table:-one to many-: Loans Table

Loans Table:-one to many-: Books Table

---------------------------------

I was hoping that those 'fields' used especially for mail-merging (In MS Word) could be used instead. The followoing seemed particularly interesting but they don't allow '{NEXT}' fields inside the braces :(

{NEXTIF}
{NEXT}
{IF}
{COMPARE}

and also these word fields (available as drop-down when mail-merging):

'If ... then ... else'

'Record...'

'Next Record If...'

'Skip Record if ...'

If I could actually compare current record's index e.g. MemberNumber with the next record's MemberNumber then i could just insert three statements comparing current MemberNumber with the next three consecutive MemberNumbers, then I could insert a statement after the conditional statements. (I say three because each person can only borrow upto four books).

Can anyone help...please?
0
 
LVL 1

Author Comment

by:doubleglazing2
ID: 10851140
Thanks for the posts. I was hoping to keep the DB as simple as possible....that's one of the reasons why I was hoping to clear this mess up at the Word-end of things. After all, it is a simple DB at the moment and I'm sure I've done this before i.e. conditional statements on the mail merged data entering Word.

:D
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 79 total points
ID: 10851176
Hmm, still trying the Member - 2 - Loans and  Loan  - 2 - Books approach.
It's easier to use a tblLoan having the keys of Book and Member and the DateStartLoan.
The due date normally is a fixed number of days so you can store that separately to enable it to be changed in the future by the user instead of having to change your code.

Having "my" approach you would just join member, loan and books thus getting all rows.
Next you need to exclude the returned books (load need to have a returndate) by selecting ReturnDate = Null and for the Overdue test the DateStartLoan < Date()-LoanTermDays.

Getting the idea ?

Nic;o)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question