Report is making too many numbers

[Jacques]

I tried it. It worked.
I had actually found something else wrong with my grouping that caused some
records to not show up at all.
At first I though this may have been the reason, that the overall option
wasn't working for me, but when I tried it after I fixed it, it still didn't
work.

Joining the totals query to TableA in the reports record source gave me what
I needed; I just then had to put everything in the correct place. It's
looking very good so far.

I appreciate the help and thank you for being patient with me.

Jacques Latoison

Your first totals query should be only:
SELECT LongSheetPrimContInfo.PrimeCont,
Sum(LongSheetPrimContInfo.Award) as SumOfAward
FROM LongSheetPrimContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont
ORDER BY LongSheetPrimContInfo.PrimeCont;
This should calculate all the Awards values for a single
contractor/vendor, right? If you add this query to your report's record
source query, you should have the number [SumOfAward] ready to stick in
your contractor header or footer section.


--
Duane Hookom
MS Access MVP
--

Was I correct in your report structure?
Ithought I was fairly clear in the sql of the first totals query that
should not include detail records. It should only be from the table
where the Award is stored. You included two tables. If you only wanted
to total the award field in the query, why include all those other
fields?

The first table contains all the info for the Contractors, name, site
worked, work type, and contract AWARD amount.
The second table contains all the sub-contractors that worked for them on
that site.

The report must total the award amounts per Prime Contractor (totaling
all the sites they worked) and then show a grand total of all Primes
combined at the end of the report (because all the sites and contractors
work for one company).

Everything works BUT the totalling of the AWARD in any group section.

You didn't provide the name of the totals query as I asked

LongSheet-ContractorFull

and you didn't

provide the SQL view of the report's record source which must be
different from the first totals query.

There's only one query (in the original design - not counting the changes
I made - to a copy of the DB -that you asked me to make earlier)
When I go to the report's record source it is

LongSheet-ContractorFull and the SQL is what I sent

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0)))
AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer,
Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS
DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0)))
AS DocuWBEPer, Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt,
Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS
DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;

If I'm mistunderstanding what you're asking for, or just don't know, I
apologize beforehand.

When you have the above set up, you don't Sum the award field since it
should be summed in the query.

I don't Sum the AWARD field in any section except the Report Footer. I
tried to Sum the Award field in the PrimCont section of the report to see
if the math was correct, but it wasn't

--
Duane Hookom
MS Access MVP
--

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
Here ya go. Your first eval of the situation is correct though,

Duane Hookom wrote:
I expect it's the fields from A that are too high. If A has a record
with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

---------The above statement is the issue. What you asked for is
below.

Provide for us:
1) the sql view and name of your totals query

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer,
Sum(CDbl(Nz([CDA],0))) AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS
CommDSDBEPer, Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt,
Sum(CDbl(Nz([DMP],0))) AS DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS
DocuWBEAmt, Sum(CDbl(Nz([DMP],0))) AS DocuWBEPer,
Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt, Sum(CDbl(Nz([DDP],0))) AS
DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS DocuLBEAmt,
Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;


2) the sql view of your report's record source

The above query is the report's record source (which pulls from two
tables)


3) the section, name, and control source of your control in your
report that is wrong.

Section: ReportFooter
Name: TEXT172
ControlSource: =SUM([Award])