Query Design

Flinn, Randal J 281 Reputation points
2022-05-04T11:28:48.577+00:00

Hello,

I am generating a Crosstab report and what follows is my current scenario. I am running three queries as identified below. I am pulling data from two tables (IW37N & KOB1).

tblIW37N - indexed on field "Order" (does allow duplicates). Currently contains 947,029 records (will increase).
tblKOB1 is indexed on fields "Cost Element Name", Fiscal Year" and "Order" (does allow duplicates). Currently contains 3,089,926 records (will increase).

qryIW37N_WOGrp_Reactv
SELECT tblIW37N.Order, tblIW37N.Location, tblIW37N.[Order Type]
FROM tblIW37N
GROUP BY tblIW37N.Order, tblIW37N.Location, tblIW37N.[Order Type]
HAVING (((tblIW37N.[Order Type]) In ("18","18RL","AFF","OM02","RFS","UM01","UM02")));

qryBldg5
SELECT tblKOB1.[Cost Element], tblKOB1.[Posting Date], tblKOB1.[Cost element name], tblKOB1.[Valin repcur], qryIW37N_WOGrp_Reactv.Order, tblKOB1.[Fiscal Year]
FROM qryIW37N_WOGrp_Reactv LEFT JOIN tblKOB1 ON qryIW37N_WOGrp_Reactv.Order = tblKOB1.Order
WHERE (((tblKOB1.[Cost Element]) In ("800100","800101","800102","800103","800104","800105","800106","800110","800111","800112","800113","800115","800118","800120","800149","800150","800170","800172","800174","800176","800178","800185","800261","800262","800292","800294","800460")));

qryXtbCostELNamebyFY_Reactv
TRANSFORM Sum(qryBldg_5.[Valin repcur]) AS [SumOfValin repcur]
SELECT qryBldg_5.[Cost element name], Sum(qryBldg_5.[Valin repcur]) AS [Total Of Valin repcur]
FROM qryBldg_5
GROUP BY qryBldg_5.[Cost element name]
PIVOT qryBldg_5.[Fiscal Year];

The queries/report are fairly slow to process (2 min 15 sec); however, I have no concerns based on the volume of records and potential performance of my PC. My question is, I am running this as efficiently as possible or are there other methods I should use? I would also like to generate a line chart at the end of the Crosstab using qryXtbCostELNamebyFY_Reactv.

Thanks...any input would be appreciated.

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
852 questions
0 comments No comments
{count} votes