Multiple Subreports - Union Query??

[Dee]

Hi all!

Well, this is a new subject - not related to the other questions I had about
a completely different report. I hope I don't take the long way around....

Here are the tables involved:
CHCompany table (for all customer companies)
CHAddress table with a one-to-many relationship with CHCompany (for address
for companies)
CHContacts table with a one-to-many relationship with CHAddress (for
contacts at each address)
Projects table with a one-to-many relationship with CHContacts (for quotes
created for each contact)
Details Section 1 with a one-to-many relationship with Projects (Line Items
for Section 1 of the quote)
Details Section 2 with a one-to-many relationship with Projects (Line Items
for Section 2 of the quote)
Details Section 3 with a one-to-many relationship with Projects (Line Items
for Section 3 of the quote)
Details Section 4 with a one-to-many relationship with Projects (Line Items
for Section 4 of the quote)
Details Section 5 with a one-to-many relationship with Projects (Line Items
for Section 5 of the quote)
Details Section 6 with a one-to-many relationship with Projects (Line Items
for Section 6 of the quote)
Details Section 7 with a one-to-many relationship with Projects (Line Items
for Section 7 of the quote)
Details Section 8 with a one-to-many relationship with Projects (Line Items
for Section 8 of the quote)
Details Section 9 with a one-to-many relationship with Projects (Line Items
for Section 9 of the quote)
Details Section 10 with a one-to-many relationship with Projects (Line Items
for Section 10 of the quote)
(most of the time there are only 1-3 sections)
QID is the primary key for the Projects table which is linked in all of the
details tables.

I created it this way because the salesman wanted to be able to create
various sections of a quote with a title for each section and a comments
section for each Section in case they want to provide Options. For example,
they quote 1 system (maybe the cheapest option) as option one, and a second
option to that may be higher, but better on the same quote.

I didn't know how to do that all in one table. Right now, I have the
section titles in the Project tables (Section 1 title, Section 2 title, etc)
and the same for the Comments section (CommentsSec1, CommentsSec2, etc) so
as not to repeat the title in every line item of the Details Table.

Due to this, I have to create 10 subforms to enter the data (6 on 1 main
form and the other four on a second form becuase Access only allows 7
subforms. The same holds true for the Quote Report itself. Also, there is
a Quote List report to create a quote at list price; a Quote Discount Report
to create a quote at a discounted price. In actuality, the calculations are
done on the subreports, not the main report. I had thought about creating a
report that changes it RecordSource at runtime depending on the options
chosen on a form with option buttons through VBA code that I found in an
Access Book. This would work if the information being changed was on a main
report, but is on 6/7 subreports.... for the first Quote. Could a union
query help to get them all on one subform to be able to this without too
much trouble?

Is there a better way to do this? Is there a better way than having 10
Detail Sections (because I would love not to have to have so many detail
tables.....? Please help.

Thanks,

Dee

 

[Duane Hookom]

Dee,
I don't understand your need to show a header for a section that contains no
records. You might try a JOIN on your Section table that includes all
records from you Section table.

 

[Dee]

Hi Duane,

I don't need the section heading to show if there is no section. Probably
you will know the answer to this right off the bat, but unfortunately I
don't. Instead of having 10 details tables for 10 sections, I would prefer
to have 1 table with all line items and something distinguishing them as
being section 1, 2, etc. I was going to put the section headings in its own
table. My problem is when I do have multiple sections on a report coming
from 1 table... how do I get the Section Heading 1 to be over only Section 1
and Section Heading 2 to be only over section 2. The sections show Section
1, then section 2 and then all of the data under both of those titles. I
want each section heading to appear over its section if it exists. I'm
trying to avoid have 7-10 subforms... and at the same time be able to create
a form that asks the user whether they want a List Quote or a Discounted
Quote. I have the code to do the prompt, but I still don't know how to do
the section headings without having so many subreports.... (access only
allows 7 in one main report anyway)...

Any ideas???

Thanks,

Dee

 

[Dee]

Thanks Duane,

I have tried that and maybe I wasn't doing it correctly because before I
didn't have a separate linked table just for section headings. I do
grouping on almost all of my reports. I'll try that now that I have
re-arranged my tables. All of this trial is in a play data database anyway
just in case. If I have any problems, I'll be sure and write back.

Thanks again,

Dee

When you combine the 10 details table into a single table, you need to have
a field that would identify the [Section]. This value is related back to
your table of Sections. Your report can then be grouped/sorted by this field
with a group heading.

--
Duane Hookom
MS Access MVP

Hi Duane,

I don't need the section heading to show if there is no section. Probably
you will know the answer to this right off the bat, but unfortunately I
don't. Instead of having 10 details tables for 10 sections, I would prefer
to have 1 table with all line items and something distinguishing them as
being section 1, 2, etc. I was going to put the section headings in its own
table. My problem is when I do have multiple sections on a report coming
from 1 table... how do I get the Section Heading 1 to be over only

Section

1
and Section Heading 2 to be only over section 2. The sections show Section
1, then section 2 and then all of the data under both of those titles. I
want each section heading to appear over its section if it exists. I'm
trying to avoid have 7-10 subforms... and at the same time be able to create
a form that asks the user whether they want a List Quote or a Discounted
Quote. I have the code to do the prompt, but I still don't know how to do
the section headings without having so many subreports.... (access only
allows 7 in one main report anyway)...

Any ideas???

Thanks,

Dee


contains

to

a

(Lin

e all
of 1
main

allows

7 in on without
too having
10

 

[Duane Hookom]

Thanks for the warning :-0

--
Duane Hookom
MS Access MVP

Thanks Duane,

I have tried that and maybe I wasn't doing it correctly because before I
didn't have a separate linked table just for section headings. I do
grouping on almost all of my reports. I'll try that now that I have
re-arranged my tables. All of this trial is in a play data database anyway
just in case. If I have any problems, I'll be sure and write back.

Thanks again,

Dee

When you combine the 10 details table into a single table, you need to have
a field that would identify the [Section]. This value is related back to
your table of Sections. Your report can then be grouped/sorted by this field
with a group heading.

--
Duane Hookom
MS Access MVP

Hi Duane,

I don't need the section heading to show if there is no section. Probably
you will know the answer to this right off the bat, but unfortunately I
don't. Instead of having 10 details tables for 10 sections, I would prefer
to have 1 table with all line items and something distinguishing them as
being section 1, 2, etc. I was going to put the section headings in

its
own

table. My problem is when I do have multiple sections on a report coming
from 1 table... how do I get the Section Heading 1 to be over only

Section

1
and Section Heading 2 to be only over section 2. The sections show Section
1, then section 2 and then all of the data under both of those titles. I
want each section heading to appear over its section if it exists. I'm
trying to avoid have 7-10 subforms... and at the same time be able to create
a form that asks the user whether they want a List Quote or a Discounted
Quote. I have the code to do the prompt, but I still don't know how

to

do

them

to

section

2 I
had (Lin

and

a have
the

on

1 allows found
in was
on

 

[Dee]

Why does everyone tell me that ? ha ha

Thanks for the warning :-0

--
Duane Hookom
MS Access MVP

Thanks Duane,

I have tried that and maybe I wasn't doing it correctly because before I
didn't have a separate linked table just for section headings. I do
grouping on almost all of my reports. I'll try that now that I have
re-arranged my tables. All of this trial is in a play data database anyway
just in case. If I have any problems, I'll be sure and write back.

Thanks again,

Dee

When you combine the 10 details table into a single table, you need to have
a field that would identify the [Section]. This value is related back to
your table of Sections. Your report can then be grouped/sorted by this field
with a group heading.

--
Duane Hookom
MS Access MVP


Hi Duane,

I don't need the section heading to show if there is no section. Probably
you will know the answer to this right off the bat, but

unfortunately

I

them

as titles.
I to them section

questions

I CHAddress
(for Projects
(Lin in
all and on

Could

a