Solved

MS Word Mail Merge Problem

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 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

21 Experts available now in Live!

Get 1:1 Help Now