Solved

MS Word Mail Merge Problem

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

831 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