How do I get YTD totals from 1 of 13 records having the same date?

[Guest]

My tables are designed as follows:
Table 1
dtmDate (Primary Key)
strMeter (Primary Key)
curPostageAdd
curPostageUsed
lngPieces
Table 2
dtmDate (Primary Key)
lngUPS
lngFedex
and it goes on and on...This is a parameter query with many columns and
several expressions. I'll try to be brief: Table 1 has 13 records with the
same date, Tables 2 - 5 only have 1 record per date. My subreport within my
report looks great, but my YTD totals are multiplied by 13. I tried dividing
each field by 13, as well as my expressions, but my numbers were slightly
off.
Let's say I want the YTD total for UPS pieces metered on 10/1/04, the over
all total is 21 pieces, Access displays 21 pieces 13 times and when I sum the
field I get 273. I only want 21. Does this make any sense?

 

[Ken Snell [MVP]]

Post the SQL statement of the query you're trying to use. I'm guessing that
you haven't properly joined the tables.

 

[Guest]

This is pretty long...
SELECT Sum(tblSpecialServicesVolumeTable.lngAverittPieces) AS
SumOflngAverittPieces, Sum(tblSpecialServicesVolumeTable.lngCertifiedPieces)
AS SumOflngCertifiedPieces, Sum(tblSpecialServicesVolumeTable.lngDHLPieces)
AS SumOflngDHLPieces, Sum(tblSpecialServicesVolumeTable.lngFedExPieces) AS
SumOflngFedExPieces, Sum(tblSpecialServicesVolumeTable.lngUPSPieces) AS
SumOflngUPSPieces, Sum(tblSpecialServicesVolumeTable.lngVelocityPieces) AS
SumOflngVelocityPieces, Sum(tblSpecialServicesVolumeTable.lngBranchOffice) AS
SumOflngBranchOffice, Sum(tblSpecialServicesVolumeTable.lngInterOfficeMail)
AS SumOflngInterOfficeMail, Sum(tblPermitAccounts.lngPermit116Pieces) AS
SumOflngPermit116Pieces, Sum(tblPermitAccounts.lngPermit2465Pieces) AS
SumOflngPermit2465Pieces, Sum(tblSorterTotals.lng5DigitAutomated) AS
SumOflng5DigitAutomated, Sum(tblSorterTotals.lng3DigitAutomated) AS
SumOflng3DigitAutomated, Sum(tblSorterTotals.lng3DigitBasic) AS
SumOflng3DigitBasic, Sum(tblSorterTotals.lngPresorted) AS SumOflngPresorted,
Sum(tblSorterTotals.lngSinglePiece) AS SumOflngSinglePiece,
Sum(tblSorterTotals.lng1stClassTotalPieces) AS SumOflng1stClassTotalPieces,
Sum(tblSorterTotals.lngDiscountedPiecesTotal) AS
SumOflngDiscountedPiecesTotal,
Avg(tblSpecialServicesVolumeTable.lngIncentiveAreasMet) AS
AvgOflngIncentiveAreasMet
FROM tblSpecialServicesVolumeTable RIGHT JOIN (tblSorterTotals RIGHT JOIN
(tblPermitAccounts INNER JOIN tblDailyMeterReconciliation ON
tblPermitAccounts.dtmEntryDate = tblDailyMeterReconciliation.dtmEntryDate) ON
tblSorterTotals.dtmEntryDate = tblDailyMeterReconciliation.dtmEntryDate) ON
tblSpecialServicesVolumeTable.dtmEntryDate =
tblDailyMeterReconciliation.dtmEntryDate;

 

[Guest]

I'm not sure if this will help, but here is the main query SQL that I am
using for weekly reporting. I used this data to get the SQL statement I sent
you earlier.
Thanks for helping.

SELECT tblDailyMeterReconciliation.dtmEntryDate,
tblDailyMeterReconciliation.strMeterNumber,
tblSpecialServicesVolumeTable.lngDHLPieces,
tblSpecialServicesVolumeTable.lngAverittPieces,
tblSpecialServicesVolumeTable.lngCertifiedPieces,
tblSpecialServicesVolumeTable.lngFedExPieces,
tblSpecialServicesVolumeTable.lngUPSPieces,
tblSpecialServicesVolumeTable.lngVelocityPieces,
tblSpecialServicesVolumeTable.lngBranchOffice,
tblSpecialServicesVolumeTable.lngInterOfficeMail,
tblPermitAccounts.lngPermit116Pieces, tblPermitAccounts.lngPermit2465Pieces,
tblSorterTotals.lng5DigitAutomated, tblSorterTotals.lng3DigitAutomated,
tblSorterTotals.lng3DigitBasic, tblSorterTotals.lngPresorted,
tblSorterTotals.lngSinglePiece,
[lng5DigitAutomated]+[lng3DigitAutomated]+[lng3DigitBasic]+[lngPresorted] AS
[Total Discounted Pieces],
Sum((tblDailyMeterReconciliation!lngTotalPiecesMetered)) AS [Total USPS
Pieces],
[lngAverittPieces]+[lngCertifiedPieces]+[lngDHLPieces]+[lngFedExPieces]+[lngUPSPieces]+[lngVelocityPieces]+[lngBranchOffice]+[lngInterOfficeMail]+[lngPermit116Pieces]+[lngPermit2465Pieces]+[Total
USPS Pieces] AS [Total Pieces], ([Total Discounted Pieces]/[Total USPS
Pieces]) AS [% Discounted],
tblSpecialServicesVolumeTable.lngIncentiveAreasMet,
IIf(tblSpecialServicesVolumeTable!lngIncentiveAreasMet>4,"Yes","No") AS
[Standards Met]
FROM tblSorterTotals RIGHT JOIN (tblSpecialServicesVolumeTable INNER JOIN
(tblPermitAccounts INNER JOIN tblDailyMeterReconciliation ON
tblPermitAccounts.dtmEntryDate = tblDailyMeterReconciliation.dtmEntryDate) ON
tblSpecialServicesVolumeTable.dtmEntryDate =
tblDailyMeterReconciliation.dtmEntryDate) ON tblSorterTotals.dtmEntryDate =
tblDailyMeterReconciliation.dtmEntryDate
GROUP BY tblDailyMeterReconciliation.dtmEntryDate,
tblDailyMeterReconciliation.strMeterNumber,
tblSpecialServicesVolumeTable.lngDHLPieces,
tblSpecialServicesVolumeTable.lngAverittPieces,
tblSpecialServicesVolumeTable.lngCertifiedPieces,
tblSpecialServicesVolumeTable.lngFedExPieces,
tblSpecialServicesVolumeTable.lngUPSPieces,
tblSpecialServicesVolumeTable.lngVelocityPieces,
tblSpecialServicesVolumeTable.lngBranchOffice,
tblSpecialServicesVolumeTable.lngInterOfficeMail,
tblPermitAccounts.lngPermit116Pieces, tblPermitAccounts.lngPermit2465Pieces,
tblSorterTotals.lng5DigitAutomated, tblSorterTotals.lng3DigitAutomated,
tblSorterTotals.lng3DigitBasic, tblSorterTotals.lngPresorted,
tblSorterTotals.lngSinglePiece,
[lng5DigitAutomated]+[lng3DigitAutomated]+[lng3DigitBasic]+[lngPresorted],
tblSpecialServicesVolumeTable.lngIncentiveAreasMet,
IIf(tblSpecialServicesVolumeTable!lngIncentiveAreasMet>4,"Yes","No"),
tblSpecialServicesVolumeTable.lngIncentiveStandardsMet
HAVING (((tblDailyMeterReconciliation.dtmEntryDate) Between [Please Enter a
Begin Date] And [Please Enter an End Date]))
ORDER BY tblDailyMeterReconciliation.dtmEntryDate;

 

[Ken Snell [MVP]]

OK - I'm going to need more information about the structure of the tables
and their data. I don't understand what types of data you're holding in the
different tables.

Also, you stated that the subreport looks ok, but the sums are over-large
because of multiple records in "table 1". I still think that the problem is
because of how you've structured your query and/or using the wrong query to
get the YTD sums.

However, I can't figure out from the SQL statements:
(1) which data field you're summing for YTD values?
(2) which query is being used in the subreport and which is being used
in the main report (if at all!?)
(3) the relationship between the two queries that you posted

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Thanks for looking into this for me, but I finally figured out what I was
doing wrong. I should have grouped my data by date instead of by meter
number. I know to do this when working with Sybase tables, but for some
reason it did not trigger with this database. So, this blonde moment lasted
several weeks...I guess the duration for these moments increase with age.
Thanks again for taking the time to look over the SQL statement.

 

[Ken Snell [MVP]]

Glad you found the problem....

< g > I'm sure I would have figured that out sooner or later < g >

Good luck!

--

Ken Snell
<MS ACCESS MVP>