Queries too complicated - Is there an easier way

T

Tony Wainwright

Hi all

I am writing an application that examines sales for a company. However the
data structure is very simple but the queries are getting really
complicated - and to do the reporting that the company wants will take
around 800 queries to generate 152 reports. What I am doing at the moment
is writing 1 query each for current month, prior month, current YTD, prior
YTD, current rolling year, prior rolling year, and a query to show the
fields to show (qryAJoin). These are all used in the report query. The
Main entity (tblSoap) contains around 24,000 records across 30 attributes.
Below is a sample of one of the most complicated

CurrentMonth
==========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS CurrentMonth
FROM tblSoap
WHERE (((tblSoap.Month)=[Forms]![frmReportParameters]![txtCurrentMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup;
PriorMonth
========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS PriorMonth
FROM tblSoap
WHERE (((tblSoap.Month)=[Forms]![frmReportParameters]![txtPriorMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
CurrentYTD
=========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS CurrentYTD
FROM tblSoap
WHERE (((tblSoap.Month) Between
[Forms]![frmReportParameters]![txtCurrentYTD] And
[Forms]![frmReportParameters]![txtCurrentMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
PriorYTD
=======
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS PriorYTD
FROM tblSoap
WHERE (((tblSoap.Month) Between [Forms]![frmReportParameters]![txtPriorYTD]
And [Forms]![frmReportParameters]![txtPriorMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
CurrentRolling
==========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS CurrentRolling
FROM tblSoap
WHERE (((tblSoap.Month) Between
[Forms]![frmReportParameters]![txtCurrentRolling] And
[Forms]![frmReportParameters]![txtCurrentMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
PriorRolling
========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS PriorRolling
FROM tblSoap
WHERE (((tblSoap.Month) Between
[Forms]![frmReportParameters]![txtPriorRolling] And
[Forms]![frmReportParameters]![txtPriorMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
AJoin
====
SELECT luMarket.MarketID, luRegion.RegionID, tblSoap.AccountRef,
luDistributor.DistributorID, luMarket.Market, luRegion.RegionName,
tblSoap.Name, luDistributor.Distributor
FROM luDistributor INNER JOIN (luRegion INNER JOIN (luMarket INNER JOIN
tblSoap ON luMarket.MarketID = tblSoap.Market) ON luRegion.RegionID =
tblSoap.Region) ON luDistributor.DistributorID = tblSoap.DistGroup
GROUP BY luMarket.MarketID, luRegion.RegionID, tblSoap.AccountRef,
luDistributor.DistributorID, luMarket.Market, luRegion.RegionName,
tblSoap.Name, luDistributor.Distributor;
ReportQuery
=========
SELECT qryCustDeptDistEURegionAJoin.MarketID,
qryCustDeptDistEURegionAJoin.RegionID,
qryCustDeptDistEURegionAJoin.DistributorID,
qryCustDeptDistEURegionAJoin.AccountRef,
qryCustDeptDistEURegionAJoin.Market, qryCustDeptDistEURegionAJoin.Name,
qryCustDeptDistEURegionAJoin.RegionName,
qryCustDeptDistEURegionAJoin.Distributor,
qryCustDeptDistEURegionCurrentMonth.CurrentMonth,
qryCustDeptDistEURegionPriorMonth.PriorMonth,
qryCustDeptDistEURegionCurrentYTD.CurrentYTD,
qryCustDeptDistEURegionPriorYTD.PriorYTD,
qryCustDeptDistEURegionCurrentRolling12Month.CurrentRolling,
qryCustDeptDistEURegionPriorRolling12Month.PriorRolling
FROM (((((qryCustDeptDistEURegionAJoin LEFT JOIN
qryCustDeptDistEURegionCurrentMonth ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionCurrentMonth.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionCurrentMonth.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionCurrentMonth.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionCurrentMonth.Market)) LEFT JOIN
qryCustDeptDistEURegionPriorMonth ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionPriorMonth.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionPriorMonth.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionPriorMonth.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionPriorMonth.Market)) LEFT JOIN
qryCustDeptDistEURegionCurrentYTD ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionCurrentYTD.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionCurrentYTD.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionCurrentYTD.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionCurrentYTD.Market)) LEFT JOIN
qryCustDeptDistEURegionPriorYTD ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionPriorYTD.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionPriorYTD.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionPriorYTD.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionPriorYTD.Market)) LEFT JOIN
qryCustDeptDistEURegionCurrentRolling12Month ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionCurrentRolling12Month.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionCurrentRolling12Month.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionCurrentRolling12Month.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionCurrentRolling12Month.Market)) LEFT JOIN
qryCustDeptDistEURegionPriorRolling12Month ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionPriorRolling12Month.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionPriorRolling12Month.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionPriorRolling12Month.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionPriorRolling12Month.Market)
WHERE (((qryCustDeptDistEURegionAJoin.MarketID) Like
[Forms]![frmReportParameters]![cboEURMarket]) AND
((qryCustDeptDistEURegionAJoin.RegionID) Like
[Forms]![frmReportParameters]![cboEURDepartment]) AND
((qryCustDeptDistEURegionAJoin.DistributorID) Like
[Forms]![frmReportParameters]![cboEURProdDist]));

Is there anyway I could simplify this?

Cheers
Tony
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use conditionals in SUM functions to get the total columns you
want. E.g.:

SELECT Market, Region, AccountRef, DistGroup,
Sum(IIf([Month]=Forms!frmReportParameters!txtCurrentMonth, NetAmount) AS
CurrentMonth,
Sum(IIf([Month]=Forms!frmReportParameters!txtPriorMonth, NetAmount) AS
PriorMonth,
-- the same for all the other totals you're trying to get
FROM tblSoap

WHERE -- set the criteria from the report's very beginning date to its
very end date. The IIf() functions will separate out the sums for each
time period you are looking for.

GROUP BY Market, Region, AccountRef, DistGroup
ORDER BY Market

Note: It is a good idea not to use reserved words as the name of columns
(Day, Month, Year, etc.) to avoid confusion (both human and machine).
Notice I put square-brackets around column names that are reserved
words. This destinguishes the column name from the reserved word.

When using the IIf() function in a JET query, you don't have to have the
False evaluation expression. E.g.:

In VBA: IIf(<evaluation>, <True>, <False>)

In JET SQL: IIf(<evaluation>, <True>)
If it evaluates to false the result is NULL. Sum(NULL) returns 0.

Also, instead of using form Text Boxes to hold the CurrentMonth,
PriorMonth, etc., I'll bet you could just have one, or two, Text Boxes
holding the beginning (and ending) dates; then use calculations in the
query to define the time period. E.g. (assuming [Month] is a month
number and the control "LastDate" holds a valid date):

Sum(IIf([Month]=Month(DateAdd("m", -1, Forms!frmFormName!LastDate)),
NetAmount) As PriorMonth

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6VxoechKqOuFEgEQKlTwCg/Ksn2VW54Qpq+0CPCQHUyAipPKoAn0Mb
NTV77Bw4Pk98QtdZtqiULmiV
=WPtH
-----END PGP SIGNATURE-----


Tony said:
Hi all

I am writing an application that examines sales for a company. However the
data structure is very simple but the queries are getting really
complicated - and to do the reporting that the company wants will take
around 800 queries to generate 152 reports. What I am doing at the moment
is writing 1 query each for current month, prior month, current YTD, prior
YTD, current rolling year, prior rolling year, and a query to show the
fields to show (qryAJoin). These are all used in the report query. The
Main entity (tblSoap) contains around 24,000 records across 30 attributes.
Below is a sample of one of the most complicated

CurrentMonth
==========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS CurrentMonth
FROM tblSoap
WHERE (((tblSoap.Month)=[Forms]![frmReportParameters]![txtCurrentMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup;
PriorMonth
========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS PriorMonth
FROM tblSoap
WHERE (((tblSoap.Month)=[Forms]![frmReportParameters]![txtPriorMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
CurrentYTD
=========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS CurrentYTD
FROM tblSoap
WHERE (((tblSoap.Month) Between
[Forms]![frmReportParameters]![txtCurrentYTD] And
[Forms]![frmReportParameters]![txtCurrentMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
PriorYTD
=======
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS PriorYTD
FROM tblSoap
WHERE (((tblSoap.Month) Between [Forms]![frmReportParameters]![txtPriorYTD]
And [Forms]![frmReportParameters]![txtPriorMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
CurrentRolling
==========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS CurrentRolling
FROM tblSoap
WHERE (((tblSoap.Month) Between
[Forms]![frmReportParameters]![txtCurrentRolling] And
[Forms]![frmReportParameters]![txtCurrentMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
PriorRolling
========
SELECT tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup, Sum(tblSoap.NetAmount) AS PriorRolling
FROM tblSoap
WHERE (((tblSoap.Month) Between
[Forms]![frmReportParameters]![txtPriorRolling] And
[Forms]![frmReportParameters]![txtPriorMonth]))
GROUP BY tblSoap.Market, tblSoap.Region, tblSoap.AccountRef,
tblSoap.DistGroup
ORDER BY tblSoap.Market;
AJoin
====
SELECT luMarket.MarketID, luRegion.RegionID, tblSoap.AccountRef,
luDistributor.DistributorID, luMarket.Market, luRegion.RegionName,
tblSoap.Name, luDistributor.Distributor
FROM luDistributor INNER JOIN (luRegion INNER JOIN (luMarket INNER JOIN
tblSoap ON luMarket.MarketID = tblSoap.Market) ON luRegion.RegionID =
tblSoap.Region) ON luDistributor.DistributorID = tblSoap.DistGroup
GROUP BY luMarket.MarketID, luRegion.RegionID, tblSoap.AccountRef,
luDistributor.DistributorID, luMarket.Market, luRegion.RegionName,
tblSoap.Name, luDistributor.Distributor;
ReportQuery
=========
SELECT qryCustDeptDistEURegionAJoin.MarketID,
qryCustDeptDistEURegionAJoin.RegionID,
qryCustDeptDistEURegionAJoin.DistributorID,
qryCustDeptDistEURegionAJoin.AccountRef,
qryCustDeptDistEURegionAJoin.Market, qryCustDeptDistEURegionAJoin.Name,
qryCustDeptDistEURegionAJoin.RegionName,
qryCustDeptDistEURegionAJoin.Distributor,
qryCustDeptDistEURegionCurrentMonth.CurrentMonth,
qryCustDeptDistEURegionPriorMonth.PriorMonth,
qryCustDeptDistEURegionCurrentYTD.CurrentYTD,
qryCustDeptDistEURegionPriorYTD.PriorYTD,
qryCustDeptDistEURegionCurrentRolling12Month.CurrentRolling,
qryCustDeptDistEURegionPriorRolling12Month.PriorRolling
FROM (((((qryCustDeptDistEURegionAJoin LEFT JOIN
qryCustDeptDistEURegionCurrentMonth ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionCurrentMonth.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionCurrentMonth.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionCurrentMonth.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionCurrentMonth.Market)) LEFT JOIN
qryCustDeptDistEURegionPriorMonth ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionPriorMonth.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionPriorMonth.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionPriorMonth.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionPriorMonth.Market)) LEFT JOIN
qryCustDeptDistEURegionCurrentYTD ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionCurrentYTD.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionCurrentYTD.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionCurrentYTD.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionCurrentYTD.Market)) LEFT JOIN
qryCustDeptDistEURegionPriorYTD ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionPriorYTD.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionPriorYTD.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionPriorYTD.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionPriorYTD.Market)) LEFT JOIN
qryCustDeptDistEURegionCurrentRolling12Month ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionCurrentRolling12Month.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionCurrentRolling12Month.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionCurrentRolling12Month.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionCurrentRolling12Month.Market)) LEFT JOIN
qryCustDeptDistEURegionPriorRolling12Month ON
(qryCustDeptDistEURegionAJoin.DistributorID =
qryCustDeptDistEURegionPriorRolling12Month.DistGroup) AND
(qryCustDeptDistEURegionAJoin.AccountRef =
qryCustDeptDistEURegionPriorRolling12Month.AccountRef) AND
(qryCustDeptDistEURegionAJoin.RegionID =
qryCustDeptDistEURegionPriorRolling12Month.Region) AND
(qryCustDeptDistEURegionAJoin.MarketID =
qryCustDeptDistEURegionPriorRolling12Month.Market)
WHERE (((qryCustDeptDistEURegionAJoin.MarketID) Like
[Forms]![frmReportParameters]![cboEURMarket]) AND
((qryCustDeptDistEURegionAJoin.RegionID) Like
[Forms]![frmReportParameters]![cboEURDepartment]) AND
((qryCustDeptDistEURegionAJoin.DistributorID) Like
[Forms]![frmReportParameters]![cboEURProdDist]));

Is there anyway I could simplify this?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab with parameter doesn't work 2
Top 3 per group 3
Heavy data munching - is there a more efficient way of doing this? 8
Is there a way to do this with queries? 0
SUM in a UNION query 2
What makes a query updateable? 2
Characters found at end of sql error-Repost 2
perform INNER JOIN on query results 2

Top