query very slow on small quotes table

[Chris]

I'm trying to build a database to track quotes made on projects, following
the layout shown in the MS Northwind Database. But the form based on the
following query is very slow to open when there is only 12 records in the
Quote Details table. The Quote Details table is made unique by having
primary keys for four fields QuoteID, ProductID, BldgID and Floor all of
which allow duplicates.
The table structure is as follows

Projects - Prmiay Key ProjectID
Products - Primary Key ProductID
Quote - Primary Key QuoteID
Building - Primary Key BldgID, other fiedls are BldgDescrp
QuoteDetail - Primay Key QuoteID, Secondary Keys ProductID, BldgID and Floor

There are one to many relationships made between the relevant tables.

SELECT QuoteDetails.QuoteID, QuoteDetails.ProductID, QuoteDetails.BldgID,
QuoteDetails.Floor, AtlasProducts.ProductNo, QuoteDetails.FixedCost,
QuoteDetails.UnitPrice, QuoteDetails.Quantity, QuoteDetails.MarkUp,
CCur([QuoteDetails].[UnitPrice]*[Quantity]*(1+[MarkUp])+[QuoteDetails].[Fixe
dCost]) AS ExtendedPrice, ProjectQuotes.ProjectID, Building.BldgDescrp
FROM ProjectQuotes INNER JOIN (AtlasProducts INNER JOIN (Building RIGHT JOIN
QuoteDetails ON Building.BldgID = QuoteDetails.BldgID) ON
AtlasProducts.ProductID = QuoteDetails.ProductID) ON ProjectQuotes.QuoteID =
QuoteDetails.QuoteID;

Obviously I have made things to complicated and hope someone can have a
suggestion on how to improve on the speed or troubleshoot the tables or
query to make it run faster.

Thanks for any help
Chris

 

[John Viescas]

Do you have indexes defined on ProductID, BldgID and Floor in Quote Details?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

I'm trying to build a database to track quotes made on projects, following
the layout shown in the MS Northwind Database. But the form based on the
following query is very slow to open when there is only 12 records in the
Quote Details table. The Quote Details table is made unique by having
primary keys for four fields QuoteID, ProductID, BldgID and Floor all of
which allow duplicates.
The table structure is as follows

Projects - Prmiay Key ProjectID
Products - Primary Key ProductID
Quote - Primary Key QuoteID
Building - Primary Key BldgID, other fiedls are BldgDescrp
QuoteDetail - Primay Key QuoteID, Secondary Keys ProductID, BldgID and Floor

There are one to many relationships made between the relevant tables.

SELECT QuoteDetails.QuoteID, QuoteDetails.ProductID, QuoteDetails.BldgID,
QuoteDetails.Floor, AtlasProducts.ProductNo, QuoteDetails.FixedCost,
QuoteDetails.UnitPrice, QuoteDetails.Quantity, QuoteDetails.MarkUp,
CCur([QuoteDetails].[UnitPrice]*[Quantity]*(1+[MarkUp])+[QuoteDetails].[Fixe
dCost]) AS ExtendedPrice, ProjectQuotes.ProjectID, Building.BldgDescrp
FROM ProjectQuotes INNER JOIN (AtlasProducts INNER JOIN (Building RIGHT JOIN
QuoteDetails ON Building.BldgID = QuoteDetails.BldgID) ON
AtlasProducts.ProductID = QuoteDetails.ProductID) ON ProjectQuotes.QuoteID =
QuoteDetails.QuoteID;

Obviously I have made things to complicated and hope someone can have a
suggestion on how to improve on the speed or troubleshoot the tables or
query to make it run faster.

Thanks for any help
Chris

 

[CP]

yep they are all indexed and duplicates are allowed.
Thanks
Chris

Do you have indexes defined on ProductID, BldgID and Floor in Quote Details?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

I'm trying to build a database to track quotes made on projects, following
the layout shown in the MS Northwind Database. But the form based on the
following query is very slow to open when there is only 12 records in the
Quote Details table. The Quote Details table is made unique by having
primary keys for four fields QuoteID, ProductID, BldgID and Floor all of
which allow duplicates.
The table structure is as follows

Projects - Prmiay Key ProjectID
Products - Primary Key ProductID
Quote - Primary Key QuoteID
Building - Primary Key BldgID, other fiedls are BldgDescrp
QuoteDetail - Primay Key QuoteID, Secondary Keys ProductID, BldgID and Floor

There are one to many relationships made between the relevant tables.

SELECT QuoteDetails.QuoteID, QuoteDetails.ProductID, QuoteDetails.BldgID,
QuoteDetails.Floor, AtlasProducts.ProductNo, QuoteDetails.FixedCost,
QuoteDetails.UnitPrice, QuoteDetails.Quantity, QuoteDetails.MarkUp,

CCur([QuoteDetails].[UnitPrice]*[Quantity]*(1+[MarkUp])+[QuoteDetails].[Fixe

dCost]) AS ExtendedPrice, ProjectQuotes.ProjectID, Building.BldgDescrp
FROM ProjectQuotes INNER JOIN (AtlasProducts INNER JOIN (Building RIGHT JOIN
QuoteDetails ON Building.BldgID = QuoteDetails.BldgID) ON
AtlasProducts.ProductID = QuoteDetails.ProductID) ON

ProjectQuotes.QuoteID

=
QuoteDetails.QuoteID;

Obviously I have made things to complicated and hope someone can have a
suggestion on how to improve on the speed or troubleshoot the tables or
query to make it run faster.

Thanks for any help
Chris

 

[John Viescas]

Does the query itself run slowly, or is it slow only when opened in the
form? What are you editing in the outer form, and what are the settings for
Link Child and Link Master in the subform control?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

yep they are all indexed and duplicates are allowed.
Thanks
Chris

Do you have indexes defined on ProductID, BldgID and Floor in Quote Details?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

CCur([QuoteDetails].[UnitPrice]*[Quantity]*(1+[MarkUp])+[QuoteDetails].[Fixe

dCost]) AS ExtendedPrice, ProjectQuotes.ProjectID, Building.BldgDescrp
FROM ProjectQuotes INNER JOIN (AtlasProducts INNER JOIN (Building

RIGHT
JOIN

QuoteDetails ON Building.BldgID = QuoteDetails.BldgID) ON
AtlasProducts.ProductID = QuoteDetails.ProductID) ON

ProjectQuotes.QuoteID

=
QuoteDetails.QuoteID;

Obviously I have made things to complicated and hope someone can have a
suggestion on how to improve on the speed or troubleshoot the tables or
query to make it run faster.

Thanks for any help
Chris

 

[Chris]

Runs slow when opening the form, the query itself seems fast. The subform
has the following
LinkChild = QuoteID
LinkMaster = QuoteID

Not too sure what you mean by editing in the outer form. As once the quote
is created the user will be adding products to the sub form.

In terms of fields in the main form we have

ten fields for inputting $ values for items applying to the whole quote.
There is also a txt box which makes a calculation and is probably where you
are going with editing the form. This txt box calculates the total of all
the products listed in the subform plus all of the items in the main quote.
It has the following calculation.

=nz([Expenses],0)+nz([Travel],0)+nz([frm_quotedetails].[Form]![txtTotalParts
],0)+nz([Labour],0)+nz([Freight],0)+nz([AirFare],0)+nz([CarRental],0)+nz([Me
als],0)+nz([Hotels],0)+nz([Misc],0)+nz([EngDocLog],0)

Where txtTotalParts is a txt box in the sub form for calculating the total
of all the products prior ot adding in the other items in the main form.

Thanks for your help and certainly seems to resolve around the form more
than the query itself
Hope you can see the glaring mistake I'm making that will easily speed
things up because I can't
Chris

Does the query itself run slowly, or is it slow only when opened in the
form? What are you editing in the outer form, and what are the settings for
Link Child and Link Master in the subform control?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

yep they are all indexed and duplicates are allowed.
Thanks
Chris
in
the

all

of

CCur([QuoteDetails].[UnitPrice]*[Quantity]*(1+[MarkUp])+[QuoteDetails].[Fixe

dCost]) AS ExtendedPrice, ProjectQuotes.ProjectID, Building.BldgDescrp
FROM ProjectQuotes INNER JOIN (AtlasProducts INNER JOIN (Building RIGHT
JOIN
QuoteDetails ON Building.BldgID = QuoteDetails.BldgID) ON
AtlasProducts.ProductID = QuoteDetails.ProductID) ON ProjectQuotes.QuoteID
=
QuoteDetails.QuoteID;

Obviously I have made things to complicated and hope someone can

have

a

 

[John Viescas]

So, the outer form is bound to the Quotes table? I suspect the problem lies
with the expression in the text box. Does the form flicker at all when you
open it? There can be some timing issues trying to fetch the calculated
total from the subform - and Access might actually be requerying the subform
many times trying to do the calculation. Try removing the text box
temporarily to see if that is the problem. If that fixes it, then you'll
have to consider doing the calculation in code after the form loads
correctly (perhaps the form Current event) and in the AfterUpdate of each of
the controls on the outer form that participate in the calculation. To get
the total from the subform, do an independent call to DSum filtered on
QuoteID in the code. You can't be sure that the total will be there when
your code runs.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Runs slow when opening the form, the query itself seems fast. The subform
has the following
LinkChild = QuoteID
LinkMaster = QuoteID

Not too sure what you mean by editing in the outer form. As once the quote
is created the user will be adding products to the sub form.

In terms of fields in the main form we have

ten fields for inputting $ values for items applying to the whole quote.
There is also a txt box which makes a calculation and is probably where you
are going with editing the form. This txt box calculates the total of all
the products listed in the subform plus all of the items in the main quote.
It has the following calculation.

=nz([Expenses],0)+nz([Travel],0)+nz([frm_quotedetails].[Form]![txtTotalParts

],0)+nz([Labour],0)+nz([Freight],0)+nz([AirFare],0)+nz([CarRental],0)+nz([

Me
als],0)+nz([Hotels],0)+nz([Misc],0)+nz([EngDocLog],0)

Where txtTotalParts is a txt box in the sub form for calculating the total
of all the products prior ot adding in the other items in the main form.

Thanks for your help and certainly seems to resolve around the form more
than the query itself
Hope you can see the glaring mistake I'm making that will easily speed
things up because I can't
Chris

Does the query itself run slowly, or is it slow only when opened in the
form? What are you editing in the outer form, and what are the settings for
Link Child and Link Master in the subform control?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
on
the all

CCur([QuoteDetails].[UnitPrice]*[Quantity]*(1+[MarkUp])+[QuoteDetails].[Fixe

dCost]) AS ExtendedPrice, ProjectQuotes.ProjectID, Building.BldgDescrp
FROM ProjectQuotes INNER JOIN (AtlasProducts INNER JOIN (Building RIGHT
JOIN
QuoteDetails ON Building.BldgID = QuoteDetails.BldgID) ON
AtlasProducts.ProductID = QuoteDetails.ProductID) ON
ProjectQuotes.QuoteID
=
QuoteDetails.QuoteID;

Obviously I have made things to complicated and hope someone can

have

a
suggestion on how to improve on the speed or troubleshoot the

tables
or

query to make it run faster.

Thanks for any help
Chris