Re: Report is making too many numbers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Duane,
Am I supposed to attached the new totals query to the already existing
one

i.e.

NewQuery <-> TableA -> TableB <==forces other records to be cut off in
TableA and TableB

or

NewQuery <-> TableB <=This will not give me all the information.


"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:%23%23jEGjMOGHA.2628@xxxxxxxxxxxxxxxxxxxxxxx
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
--

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
news:O%232Kd5LOGHA.3360@xxxxxxxxxxxxxxxxxxxxxxx


"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:eWwRXxLOGHA.2336@xxxxxxxxxxxxxxxxxxxxxxx
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
news:uzB7srLOGHA.2124@xxxxxxxxxxxxxxxxxxxxxxx
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.

"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:%23LF1MgLOGHA.4052@xxxxxxxxxxxxxxxxxxxxxxx
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])











.

Relevant Pages

  • Re: Report is making too many numbers
    ... Joining the totals query to TableA in the reports record source gave me what ... where the Award is stored. ... FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON ... When you have the above set up, you don't Sum the award field since it ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... Your first totals query should be only: ... If you add this query to your report's record source query, ... the Award is stored. ... FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON ...
    (microsoft.public.access.reports)
  • Re: Populate Field Based on Value of Previous Record
    ... base the data sheet on the normal query, ... The totals query is used to get the previous odo. ... Remove the field called PreviousODO - you can always find the value ...
    (microsoft.public.access.forms)
  • Re: Running Totals On QueryDef In Code
    ... with the query as a source. ... You could also use a totals query as you mentioned, but do it all in code. ... Dim DB As DAO.Database ... Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined, ...
    (comp.databases.ms-access)
  • Re: Counting 3 different fields seperately on a report
    ... I created the 'totals query' and put in the criteria Like "X" but when I go ... "Jeff Boyce" wrote: ... Access asks you for a value (via a parameter prompt) when it can't find the ...
    (microsoft.public.access.reports)