Insert blank line when updating table (1 Viewer)

[y2k]

I have a delete query and an append query which deletes old data from a table and updates it with data from another table. the first field is sorted ascending, and more than one record will have the same value in this field. For example, the first five might say 001, then the next 2 might say 002... etc... you get the picture? what I want to do is, each time this field changes, enter a blank line, so that it will have all the 001's, then a blank link, all the 002's etc. The reason why is because the table is used as a source for a report and the report would look better if these blank lines were included. Is it possible to modify the update query to do this? I'm guessing there's no control that I can put in place on the report to do this either?

One other thing is though... there will not always be an occurrence of any particular value, for example, the sequence may be 001, then there may not be any with 002 so 003 would be next. So if that was the case I'd still only want one line between them, not 2.

Can anybody help? I hope I've explained this ok?

 

[cpod]

You can do this easily in your report by using "Grouping". Just go to:

View/Sorting and Grouping

In the upper pane of the Sorting and Grouping dialogue box, choose the field ( the field with 001, 002 ....), sort ascending. In the bottom pane put a "Yes" in "Group Footer" ("Group On" should be "Each Value").

This will add an empty group footer for your field. This footer will create a blank area in your report after each change in the field's value.

 

[RV]

>The reason why is because the table is used as a source for a report and the report would look better if these blank lines were included.<

Just a strong advise:

Never ever confuse databases with / try to use them as Reporting Tools!!
I've seen it happening before resulting in huge business problems (even threatening the future existance of companies.....)

RV

 

[Jerry Stoner]

RV can you expound on that a little???

 

[The_Doc_Man]

While I haven't seen the problems that RV reports, I concur that you are doing something wrong.

I am GUESSING that the problem is one of mind-set. You would do exactly what you suggested if your table were a spreadsheet. But it isn't. It is a table. I think you are applying an Excel solution to an Access problem.

What you want is for the report to automatically group things for you and build group headers that let you separate out the stuff you want in header lines. Then have indented detail lines under each different header. It LOOKS like what you want, but it doesn't come from data actually stored that way.

Leave the table alone. Try building a report with the report wizard. Tell it the fields you want to see. When it askes about grouping, tell it what you want grouped as headers. The rest will be in the detail sections.

Once the wizard has built the report, if you want some other things in the header rather than the detail section, you can cut-n-paste stuff from one section to another. Works fine.

 

[y2k]

Re: Re: Insert blank line when updating table

You can do this easily in your report by using "Grouping". Just go to:

View/Sorting and Grouping

In the upper pane of the Sorting and Grouping dialogue box, choose the field ( the field with 001, 002 ....), sort ascending. In the bottom pane put a "Yes" in "Group Footer" ("Group On" should be "Each Value").

This will add an empty group footer for your field. This footer will create a blank area in your report after each change in the field's value.

That's just what I needed... thank you very much!
Thanks for all the advice everybody