blank line in a query for blank lines in a report

[Sandi]

Hello,

Sorry for bringing up an old topic but I've been searching the web for
a way to insert a blank line into a report after individual systems and
I found a very similar question posted awhile back and I believe the
answer posted will work for what I need to do. I have included the
original poster's table and posted solution.

I have replicated the original author's table but can not figure out
how to make Tom's solution work. I do not know how to add a column
into a query that is not in the table and I can not replicate the union
all. Could someone demonstrate what queries I need to make this union
all work? If I can successfully duplicate what is done in the example
I should be able to apply it to my project. My report will be used as
a worksheet for employess to work off of and they want it to replicate
the current paper form that is used. Unfortunately all the data that
goes into my report is from one table so I can not simply add spaces to
the report in design view. I am new to access and have been assigned
the task of maintaining our database without making wide scale changes.


Thank you,

Sandi

---------- Forwarded message ----------
From: Tom Ellison
Date: Wed, 29 Sep 2004 11:18:31 -0500
Subject: Re: Insert Blank Line In Query?
To:

Dear MDW:

This is hardly what queries are designed to do, and you'd be much
better off creating a report, which allows for something like this.

However, you can create another query that says:

SELECT DISTINCT DIV_CODE
FROM YourOtherQuery

Create a UNION ALL of your existing query with the above, adding the
columns STORE_NUM and NAME with blank values in them. Also add a new
column to both queries I'll call STORE_ORDER. In the existing query,
make STORE_ORDER to be the same values as STORE_NUM. In the new
query's portion of the UNION ALL, put a value like 999 in for
STORE_ORDER (or some other impossibly high value) so the new "BLANK
LINE" will appear at the end of the list for each DIV_CODE when the
whole thing is sorted by DIV_CODE. If you don't want to show the
DIV_CODE in the "BLANK LINE" then make yet another DIV_ORDER column
for sorting and leave DIV_CODE blank.

With some ingenuity, you can get queries to do stuff like this.
Still, I recommend against it. Reports are a much better way to do
this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Duane Hookom]

Maybe you should just tell us what your records look like and what you want
your report to look like.

 

[Sandi]

Duane,

The report is currently structured to look like the below with bank
being alphabetical by system and system listed alphabetically:
bank, file,time,opr,system
AAA F
BBB F
CCC F
AAA G
BBB G
CCC G
AAA H
BBB H
CCC H

What I want to do is have a space in the report between systems so that
the report will look like:
bank,file,time,opr,system
AAA F
BBB F
CCC F

AAA G
BBB G
CCC G

AAA H
BBB H
CCC H

All of the fields are from one master table. The employees will print
out the report and use it as a worksheet but I can not alter the master
table or create three separate tables by system.

Thanks for any help you can provide!
Sandi

 

[Sandi]

Sorry for the double post I forgot to include that the number of banks
varies for each system and will change as banks are added removed to
those systems. Currently there are 13 banks for F, 9 for G, and 10 for
H so I can't use the add a blank line every nth formula because it
changes so often. The employees fill in the worksheet as files come in
and enter the data into a database later on since it is an all day
process.

Thanks again,

Sandi

 

[Duane Hookom]

Where are you storing 13 for F, 9 for G, and 10 for H?