Add static lines to a report

[indesisiv]

I have a report that list clients that are about to enter a clinic ... this report is printed off and given to the advisor at the clinic. The problem is that people just have a habit of turning up when they are not booked in.
So ... what i need to do is have a couple of blank boxes immediatly after the list of client names.

This list of names is in the detail section of the report. My problem is that if i just add a couple of boxes to the bottom of that then they will appear after every person listed. And if i put it in the page footer then it is just at the bottom of the page and not just after the original list.

I have attached a picture to try and help explain.

But to sum it up ... is there a way to just add lines to the bottom of my row of boxes.

Thanks in advance
Steve

 

[WayneRyan]

Steve,

I haven't tried this, but I think it will work:

Select Name, ContactNumber, PostCode
From   YourTable
Order by Name 

UNION

Select TOP 3 '', '', ''
From   YourTable
Order By Name

Edited to say that "Name" is a reserved word.

Wayne

 

[Rich]

Article ID: Q119073

 

[indesisiv]

Wayne,
Not sure where exactly to put that.
My SQL for the report is a little more complex and i am not sure how to add that in.

SELECT DISTINCTROW tblClient.ClientForename, tblClient.ClientSurname, tblClient.ClientContactNumber, tblClient.ClientPostcode, tblVenue.VenueName, tblVenue.VenueID, tblVenue.VenueDate, tblVenue.VenueTime, tblVenue.MaxClients, tblVenue.SpecialistName, tblVenue.PCT FROM tblVenue LEFT JOIN tblClient ON tblVenue.VenueID = tblClient.VenueID;

Not sure if that will help you.

 

[indesisiv]

I have been trying to implement what you said Rich ... But i can't seem to be able to adapt it to my report.

Wondering if you could have a look at my report and see if it is possible to do with a report like this.

Thanks.

 

[Rich]

you'll have to post a 97ver

 

[indesisiv]

Erm

Rich,
It is in Access 97!!!

 

[Rich]

Is there a limit to the number of clients per page?

 

[indesisiv]

It is limited to the number of places at each clinic ... these numbers will vary as more or different clinics are available.

Should be a max of about 30 or so i think.

Steve

 

[indesisiv]

Still Not Working

I've had another go at it but i still can't get it to work.
I have added in the code etc but i either get rows of blank data (1 line of correct data then the rest blank) or i just get what i had to start with.
So no extra lines.

I have attached my latest botched attempt.
Any thoughts.

Steve

 

[john471]

Add a section footer

Steve,

This may help. I've used your first posted attachment, and saved a new report as "rptVenue2".
a) in report Sorting and Grouping, add in VenueName as the first field/expression, (sort ascending). Set Group Footer to 'Yes'
b) in the group footer, put in the box(es) you want - I went with three.

Preview the report.

HTH

Regards

John.

 

[indesisiv]

Thanks Jon thats a nice work around and will keep others off my back ...

I will continue trying to get the other way working aswell because i think it allows more control and the ability to add a different number of lines depending on the user.


Thanks again
Steve

 

[john471]

Another Crack At It

Steve,

I presume from your comment that a fixed number of blank rows at the end for every clinic is not the requirement. I extrapolated that to presume that your requirement was to have a total number of rows (for each clinic) = MaxClients (for that venue), with clients filling up the top rows until there are no more clients, then blank rows after that. Is that close to what you want ?

I've taken another couple of shots at if for you (I hate it when Microsoft wins!)

Report V3 utilises the suggestion Rich pointed us to (Article ID: Q119073), but has the "feature" that the total number of pages, as printed in the footer, is incorrect *if* the last page(s) only contain blank rows. If number of actual clients exceeds Max Clients, they will still print, but you get no blank rows.

Report V4 utilises WayneRyan's suggestion of a Union Query. For this one, to have a *dynamic* number of blank rows, the only way I could come up with to do this was to base the report on the same DataSource you had, and "Union" that with another query (which I've hidden from (ordinary) view by making it a System object - you can view it by changing your view options: Tools Menu, Options..., View Tab, check System Objects") . The SQL of this one is changed by code which I added to your "Preview Clinic Details" button code. This version also has a "feature". Because the source of the BlankLineSupply query is ostensibly the same as the client list for that venue/clinic, the absolute maximum number of blank lines is the same as the number of clients, regardless of the settings in the tblVenue. e.g. if MaxClients is 30, and there are only 5 clients booked, you will only get 10 rows, 5 with clients and 5 blank. However, if the the MaxClients is 30 and you have 22 clients booked, you would get your full quota of 8 blank lines. I also "gold plated" in a feature that allows you to cap the number of blank lines on All reports (to limit the number of walk-ins accepted ???) check out the code behind the "Preview Clinic Details" button for details.

Phew... I hope I've explained that OK.

Anyway. Here is the attachment. Hope it helps (even if only a little). If you decide to go with Report V3, you should get rid of the extra code I put behind the PreView Clinic Details button (it won't hurt, but it won't help either) Also delete the query (but don't delete the query unless you also delete that code; or you will get an error!).

(The attachment is set to run V4 at the moment.)

HTH

John

 

[indesisiv]

Wow

Thank you John,

Glad you could make them work ...
I'll have a look at them today but if your descriptions are correct then this will do everything that i need.
I should be able to figure out were I went wrong.

Again Thanks John

 

[john471]

Pls let me know how you go.