[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MS Word Mail Merge Problem

Posted on 2004-04-17
8
Medium Priority
?
412 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
[X]
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
  • 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 316 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 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