Remove repeat section headers

[Starry]

My report groups data correctly but adds a new section header for each
different entry in the grouping field. I want it to display this header once
and run all records in that group as detail. This should make it clear!

tbldata
record: type1(blank) type2(blank) type3(blank) type4(stuff)
record: type1(blank) type2(blank) type3(blank) type4(stuff)
record: type1(blank) type2(blank) type3(blank) type4(morestuff)
record: type1(otherstuff) type2(blank) type3(blank) type4(blank)
record: type1(moreotherstuff) type2(blank) type3(blank) type4(blank)

report looks like this.....

type4 section header
type4(stuff)
type4(stuff)

type4 section header
type4(morestuff)

type1 section header
type1(otherstuff)

type1 section header
type1(moreotherstuff)

so within the grouping every time the data changes in my groupby field I get
a new header. Just the one PLEASE! Can I code this out? or do I have to add
a field to my table to specify what type a particular record is...surely
not?

Any help much appreciated

 

[Duane Hookom]

Could you provide the fields/expressions from your sorting and group dialog?
You might only need to change their order.

 

[Starry]

Hi Duane

The fields all have equal importance there is a higher key field (JNum) but
as the report only relates to a single JNum it makes no odds. Looking again
the groups cannot help but keep restarting. I have realised that I don't
actually want it to group within the types but rather group the entire type1
2 3 or 4...ie just to show a new header when a type has (or has not) data.

If I remove the groups i get (which is the table layout)
header
TYPE1 TYPE2 TYPE3
type1
type1
type1
type2
type2
type3
type3
etc.

but I need to have a header (and footer) for each and mess with the
positions so that we get

Header
type1
type1
type1
Header
type2
type2
Header
type3
type3

I could just shove all the fields ontop of each other in the detail section
(uggh!) but that still leaves the header/footer problem.
No matter what settings I use for group it will not leave them in a chunk. I
need the group header/footer but not the grouping!

 

[Starry]

I think I need an extra field that can be grouped on and contains (123or4)
(but I have another question on how to do this in the query by testing each
type for null). I wish I had ignored my attempts to keep the number of
tables down and stored these records in 4 seperate tables! There's enough of
them and most of each record is blank...(the example is a trifle of all
fields!) I thought that I had the design right this time too much time spent
listening to too much advice, now its a pain and I cannot go back. (Two
weeks over deadline).

Any thoughts?

 

[Duane Hookom]

If you have multiple 'type' fields then I think your structure is wrong.
Especially if 1 or more of these fields might be left blank on a regular
basis or if they contain the same basic information.

If we knew a little more about your table and how you wanted the information
presented in the report, we could provide more assistance. I think a simple
union query might resolve most of your reporting issues but I can't be sure.

 

[Starry]

ok I was attempting to present my senario simply but I have obviously just
confused. Apologies

The structure was the best that I could arrive at given the time allowed
(customising my existing app for another objective..) As with many projects
far from ideal but just how it is.

The table that most of the query runs from is a store for cost records
Much of the information in each record follows the same pattern
Job Date Week Cost etc etc

however each record CAN have data for materials,labour,labourtype2,hire etc.
These are my 'types' and as such the record would have all the standard data
in addition to info in the materials field or maybe the labour field.

To break this down is now not an option and would simply make other aspects
of the app very awkward so it hits the fan somewhere and this is the place!

So to get the info from those records that I want is easy for any given
job/date/period whatever and I suppose I could run four queries one for each
type and bring them back together so that there was a single column for the
material,labour etc info but I would still need to differentiate between
them to group them up which puts me back where I am now.

The query output is fine as is I just cannot make the report lay the results
out in the manner I want.

Thanks for sticking with me.

 

[Duane Hookom]

I'll stick with my assessment that a union query might resolve your issue.
You could easily create a "virtual" record for each different cost type.

However, haven't adequately described your table name, primary key field(s),
'Type' field names and data types, and desired display in your report.

 

[Starry]

Hi Duane

table tblJobData
ID -Auto PK
JobNum -Text (includes letters)
Itemdate - Date/Time
Matsupp - Text*
Labname - Text*
Subname - Text*
Plant - Text*
Itemcost - Currency

The four text fields marked * hold the data that I wish to keep together
report layout as follows.

JobNum (page header) single job only

Materials header

Matsupp detail
Itemdate Matsupp Itemcost

Materials footer

-----repeated for Labname,Subname and Plant

 

[Duane Hookom]

Not a good structure as expected. However, if you need to live with it for a
while:
Create a union query like:
=== quniItemCosts ===
SELECT ID, JobNum, ItemDate, MatSupp as Item, "MastSupp" as ItemType, ItemCost
FROM tblJobData
WHERE MatSupp Is Not Null
UNION ALL
SELECT ID, JobNum, ItemDate, Labname, "Labname", ItemCost
FROM tblJobData
WHERE MatSupp Is Not Null
UNION ALL
SELECT ID, JobNum, ItemDate, Subname, "Subname", ItemCost
FROM tblJobData
WHERE MatSupp Is Not Null
UNION ALL
SELECT ID, JobNum, ItemDate, Plant, "Plant", ItemCost
FROM tblJobData
WHERE MatSupp Is Not Null;

Use this union query and group by ItemTYpe or whatever...

 

[Starry]

Thanks Duane

Used and adapted as required (not had to use a Union before except for data
import a long time ago)