question

FlinnRandalJ-5432 avatar image
0 Votes"
FlinnRandalJ-5432 asked

Query Design

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.

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers