Complicated Mail merge issues 2

[maverickmonster]

I have an access database with approximately thirty tables in it. A user can have personal Information in some / all of the screens and maybe more than one record in each.

I want to print out all the information that relates to each user (each table is related to a main table by a Unique ref number).

I was thinking about making a query for each table to pull out the relevant information, then printing all the report that had information in.

I have had a test run on a couple of tables but looks really bad the way that there is a load of wasted space. Is there anyway to combine all these reports to print a nicer looking one?

I have tried to publish to word and looks ok with some formatting, but i can conditionally publish these?! I have tried a merge but when you need display say three records it shows one set of results over three letters rather than in a table as required.

Can any one suggest a way i can conditionally merge on queries to a word doc in a table format?!

Thanks in advance

Maverick monster

 

[dhookom]

Have you considered using a main report with individual subreports for the related tables?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

can you maybe explain your data?

Person table
info1 table
etc

and then maybe the layout you are looking for?

It might help with how to advice grouping in a report or setting up a good mail merge. I am working on a mailmerge idea myself, but mine is more person address and the merge is the simple part. My problem is execution. Layout should be much easier to deal with.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[maverickmonster]

I have just tried this with two of the thirty tables and it crashes and said that there is too many fields ?!

Thanks for the reply tho

 

[maverickmonster]

I would like to list my tables in an alphabetical format but only show this if a member has the information in this table, also. My main table is called BPD and the only table where a member will have details.

Hence the BPD table always at the top then any other information in from the relevant tables would be behind that.

 

[dhookom]

What did you do with "two of the thirty tables" to cause too many fields and a crash?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[maverickmonster]

Tried making a report but looking at the query it is not what i wanted to do anyway

 

[dhookom]

Maverickmonster,
Where do your comments leave those who have replied or others that might read this thread?
Are you still looking for a solution?
Did you try some solutions?
Did you consider reviewing our questions and answering them?
Are you giving up?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

I would be interested to know as well. I know the issue that I am currently working on is a little different, but I run into mail merge stuff often. It is good to learn as much as I can.

Thank you.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[maverickmonster]

Are you still looking for a solution? Yes

Did you try some solutions? Yes I tried making a query but all the information doubles up. . . How do you make a report with sub reports ? (The sub reports would have to be conditional if there is any data in the conisponding tables)

Did you consider reviewing our questions and answering them? Yes

Are you giving up? No

Sorry I have been temp. to a different project but i still wanna complete this

 

[dhookom]

Subreports are like any other report except that they are placed on a main report like a subform is placed on a main form. Normally you would use the Link Master/Child properties of the subreport control to filter the records displayed in the subreport to a value or values in the main report (again just like forms and subforms).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

if you are concerned about not showing the labels of a subreport, if there is no data, use a trick in the label...

make them

=IIF([labels_field) is null,"", "Field Label")

Thats how I make it not show if there is no data in the table


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I use a little different syntax for the "trick label"
="Field Label " +[labels_field]
Make sure the label/text box can't grow and size it so you only see "Field Label".

Also misscrf's expression should be:
=IIF(IsNull([labels_field]),Null, "Field Label")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

thanks dhookom... I like ur trick!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[maverickmonster]

ok started down the long road of making all the sub forms thanks for the advice so far. Does anyone know of how many sub forms i can include i dont want it to say i have used up to many controls halfway through making it ?

 

[maverickmonster]

I have a question i have made some of the sub reports on the main report , How can i make a conditional statement at the end of the report to show which tables were not included because of no data

 

[dhookom]

Assuming a subreport control named "srptOne", you can add a text box to the main report:

=IIf(srptOne.Report.HasData,"","Sub Report One is Empty")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

I dont know of a limit on sub-reports, but I have heard people ask the same question. I would try a forum search on limit sub reports
or something like that. keep us updated with your progress, and dont give up.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[maverickmonster]

one of my sub reports is called "AD Query"

is that the subreport control (see example below) as it asks for the information ?

=IIf([AD Query].Report.HasData,"","Sub Report One is Empty")

 

[dhookom]

I don't know what your subreport control is named. It is usually the name of the actual subreport but might be anything else.

You can find the name of the subreport control in exactly the way you would find the name of any other control.

For this expression to work, it might need to be in the same section of the report as the subreport. I am not sure and haven't tested. Maybe you could test and report back.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]