Mail Merge from sub-reports

[bcsalt]

Hi
My database has a main project table, and each project can have multiole client, people working on it etc, so these are stored in separate tables, referenced by the Project ID
I then need to create reports from this data. This works fine, as the lists of clients etc are handled by sub reports, so i get the full list of clients for each project.
I then need to export this to Word for further editing.
If I simply export it as rtf format, I know that I lose all my formatting, but keep all the data
Is there a way of mail merging it, so that the sub-reports come out correctly.
I considered that it may be possible if I consolidate all the data into either a table or an excel spreadsheet which automatically creates fields for client1, client2 etc, and then do it in this way, but I have absolutely no idea how to do this!!!
Any help would be much appreciated
Thanks
Ben Salt

 

[Moniker]

Export the fields to a Word Template where the Template has the formatting you want already in place.

 

[bcsalt]

But how do I export the sub-report stuff?
I can get 80% of it complete, it's just the things where there are 2 clients etc for 1 project & I want both their details to come out in the report
Cheers
Ben

 

[P_Henry]

Hi,

I am also stuck with the same problem. I guess the mail merge cannot handle one to many relationship. If it is possible to create one table with all the fields, then it would be possible. This can be done by VBA but I guess you may have to limit the number of fields you can have.
I am going to try doing this and will let you know if I succedd.
Thanks,PH

 

[Moniker]

You create a separate query that contains the project information from your main form and the detail information from your subform. This isn't too complex, really. You have three tables in play here:

[u]t_Projects[/u]
[I](ProjectID is the Key)[/I]

ProjectID   ProjectName
1           FirstProject
2           SecondProject
X           XProject

[u]t_Clients[/u]
[I](ClientID is the Key)[/I]

ClientID    ClientName
1           FirstClient
2           SecondClient
X           XClient

[u]t_ProjectList[/u]

ProjectID   ClientID    
1           1
1           2
2           1
X           XClient

Note that in the t_ProjectList table, ProjectID 1 has two Clients.

When ProjectID 1 is selected on the main form, the record source for the subform becomes t_ProjectList WHERE ProjectID = 1, which supplies both clients.

When you go to export to Word, you build a simple INNER JOIN query to get the project and client names from t_Projects and t_Clients, respectively.

Note that I've left off other details you may be including in the t_Projects (start date, end date, etc.) and t_Clients (address info, etc.) tables.

I don't want to write the whole thing for you, but I think I've provided enough information here to push you in the right direction. As a final push, you can completely automate the process with a little dynamic SQL. This would apply to the INNER JOIN query I recomended earlier.

HTH