Solved

MS Word Mail Merge Problem

Posted on 2004-04-17
8
374 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now