Form based on query crashes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using this on a budget/actual form but it crashes "sometimes" when I
open the form. No consistancy as to when it crashes... any suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating, tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
 
1. Does the query alone ever cause a crash? If so, there's something wrong
with the query.

2. Describe the crash. You just might have problems with Null data or divide
by zero errors.

3. If not, there's a good chance that the form is corrupt. Delete it and
import a copy from a recent backup.

4. If #3 doesn't work, you may need to create an new database file and
import all objects from the old database into it. Whatever refuses to import
is most likely corrupt and need rebuilding if you don't have a good,
uncorrupted backup.

5. The BEST suggestion. Normalize your database structure. You are obviously
committing spreadsheet.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


lmv said:
I am using this on a budget/actual form but it crashes "sometimes" when I
open the form. No consistancy as to when it crashes... any suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating, tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
Click to expand...
 
Thanks for the response...
1. Does the query alone ever cause a crash? If so, there's something wrong
with the query.
NO

2. Describe the crash. You just might have problems with Null data or divide
by zero errors.
Click to expand...

Access stops MS gray error reporting to MS pops up... restarts to backup.mdb
3. If not, there's a good chance that the form is corrupt. Delete it and
import a copy from a recent backup.
Click to expand...

OK.. haven't tried yet... though when I open the form in design view first
then try to go back to form view it works.

4. If #3 doesn't work, you may need to create an new database file and
import all objects from the old database into it. Whatever refuses to import
is most likely corrupt and need rebuilding if you don't have a good,
uncorrupted backup.

I have backups...
5. The BEST suggestion. Normalize your database structure. You are obviously
committing spreadsheet.
Click to expand...

What about my question indicates it is not normalized to you?
Thanks
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


lmv said:
I am using this on a budget/actual form but it crashes "sometimes" when I
open the form. No consistancy as to when it crashes... any suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating, tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
Click to expand...
Click to expand...
 
Your SQL shows the tendancy to add columns across instead of rows down. Let
me ask you this: If you need to add Project Budget Account (I'm guessing
here) 740, wouldn't you add another field to the table? Then wouldn't you
need to add 740 to many of your forms, reports, and queries? If so, your
tblProjBudAct is not normalized properly.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


lmv said:
Thanks for the response...
1. Does the query alone ever cause a crash? If so, there's something wrong
with the query.
NO

2. Describe the crash. You just might have problems with Null data or divide
by zero errors.
Click to expand...

Access stops MS gray error reporting to MS pops up... restarts to backup.mdb
3. If not, there's a good chance that the form is corrupt. Delete it and
import a copy from a recent backup.
Click to expand...

OK.. haven't tried yet... though when I open the form in design view first
then try to go back to form view it works.

4. If #3 doesn't work, you may need to create an new database file and
import all objects from the old database into it. Whatever refuses to import
is most likely corrupt and need rebuilding if you don't have a good,
uncorrupted backup.

I have backups...
5. The BEST suggestion. Normalize your database structure. You are obviously
committing spreadsheet.
Click to expand...

What about my question indicates it is not normalized to you?
Thanks
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


lmv said:
I am using this on a budget/actual form but it crashes "sometimes" when I
open the form. No consistancy as to when it crashes... any suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating, tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
Click to expand...
Click to expand...
Click to expand...
 
I believe your assumption is inaccurate... the number is a Category ID
number... the Categories are finalized and will not change or be added to.
The Main Categories are Main categories for building [740] is the Tax
category number... I don't understand your assumption. Orders are sorted into
Categories/ Products(each type of lumber 2x4 etc are organized under
subcategory lumber) under Main Category Building Supplies [542] each item ie
2x4 is an autonumber... what part of this is not normalized? The query
works... it totals all of the Main Categories which have thousands of
items... giving me a sqft price for plans. How should it be set up??





--
Remember: There are suppose to be no "dumb" questions!


Jerry Whittle said:
Your SQL shows the tendancy to add columns across instead of rows down. Let
me ask you this: If you need to add Project Budget Account (I'm guessing
here) 740, wouldn't you add another field to the table? Then wouldn't you
need to add 740 to many of your forms, reports, and queries? If so, your
tblProjBudAct is not normalized properly.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


lmv said:
Thanks for the response...
1. Does the query alone ever cause a crash? If so, there's something wrong
with the query.
NO

2. Describe the crash. You just might have problems with Null data or divide
by zero errors.
Click to expand...

Access stops MS gray error reporting to MS pops up... restarts to backup.mdb
3. If not, there's a good chance that the form is corrupt. Delete it and
import a copy from a recent backup.
Click to expand...

OK.. haven't tried yet... though when I open the form in design view first
then try to go back to form view it works.

4. If #3 doesn't work, you may need to create an new database file and
import all objects from the old database into it. Whatever refuses to import
is most likely corrupt and need rebuilding if you don't have a good,
uncorrupted backup.

I have backups...
5. The BEST suggestion. Normalize your database structure. You are obviously
committing spreadsheet.
Click to expand...

What about my question indicates it is not normalized to you?
Thanks
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am using this on a budget/actual form but it crashes "sometimes" when I
open the form. No consistancy as to when it crashes... any suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating, tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
Click to expand...
Click to expand...
Click to expand...
 
Any time I see expressions that add values across columns/fields rather than
across records it suggests an un-normalized database. You mention field
[740] but that field isn't even in your query.

Your issue may be attempting to use a calculated expression "EstBuildCostB"
in another expression "CCur([EstBuildCostB]/[SQFT])". You do this in at
least two places in your query.

I am not an expert on building supplies but I would think that each
individual cost amount should create a record in a related table.

--
Duane Hookom
MS Access MVP
--

lmv said:
I believe your assumption is inaccurate... the number is a Category ID
number... the Categories are finalized and will not change or be added to.
The Main Categories are Main categories for building [740] is the Tax
category number... I don't understand your assumption. Orders are sorted
into
Categories/ Products(each type of lumber 2x4 etc are organized under
subcategory lumber) under Main Category Building Supplies [542] each item
ie
2x4 is an autonumber... what part of this is not normalized? The query
works... it totals all of the Main Categories which have thousands of
items... giving me a sqft price for plans. How should it be set up??





--
Remember: There are suppose to be no "dumb" questions!


Jerry Whittle said:
Your SQL shows the tendancy to add columns across instead of rows down.
Let
me ask you this: If you need to add Project Budget Account (I'm guessing
here) 740, wouldn't you add another field to the table? Then wouldn't you
need to add 740 to many of your forms, reports, and queries? If so, your
tblProjBudAct is not normalized properly.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


lmv said:
Thanks for the response...
1. Does the query alone ever cause a crash? If so, there's something
wrong
with the query.

NO

2. Describe the crash. You just might have problems with Null data or
divide
by zero errors.

Access stops MS gray error reporting to MS pops up... restarts to
backup.mdb

3. If not, there's a good chance that the form is corrupt. Delete it
and
import a copy from a recent backup.

OK.. haven't tried yet... though when I open the form in design view
first
then try to go back to form view it works.

4. If #3 doesn't work, you may need to create an new database file and
import all objects from the old database into it. Whatever refuses to
import
is most likely corrupt and need rebuilding if you don't have a good,
uncorrupted backup.

I have backups...

5. The BEST suggestion. Normalize your database structure. You are
obviously
committing spreadsheet.

What about my question indicates it is not normalized to you?
Thanks

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am using this on a budget/actual form but it crashes "sometimes"
when I
open the form. No consistancy as to when it crashes... any
suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating,
tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

I introduced the 740 into the discussion in a "what if" scenario if it had
to be added. Maybe we're both wrong on this one. Don't know without seeing it
up close.

If the query works, it works. Except that the form that based on it is
crashing. Corruption most likely.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Duane Hookom said:
Any time I see expressions that add values across columns/fields rather than
across records it suggests an un-normalized database. You mention field
[740] but that field isn't even in your query.

Your issue may be attempting to use a calculated expression "EstBuildCostB"
in another expression "CCur([EstBuildCostB]/[SQFT])". You do this in at
least two places in your query.

I am not an expert on building supplies but I would think that each
individual cost amount should create a record in a related table.

--
Duane Hookom
MS Access MVP
--

lmv said:
I believe your assumption is inaccurate... the number is a Category ID
number... the Categories are finalized and will not change or be added to.
The Main Categories are Main categories for building [740] is the Tax
category number... I don't understand your assumption. Orders are sorted
into
Categories/ Products(each type of lumber 2x4 etc are organized under
subcategory lumber) under Main Category Building Supplies [542] each item
ie
2x4 is an autonumber... what part of this is not normalized? The query
works... it totals all of the Main Categories which have thousands of
items... giving me a sqft price for plans. How should it be set up??





--
Remember: There are suppose to be no "dumb" questions!


Jerry Whittle said:
Your SQL shows the tendancy to add columns across instead of rows down.
Let
me ask you this: If you need to add Project Budget Account (I'm guessing
here) 740, wouldn't you add another field to the table? Then wouldn't you
need to add 740 to many of your forms, reports, and queries? If so, your
tblProjBudAct is not normalized properly.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Thanks for the response...
1. Does the query alone ever cause a crash? If so, there's something
wrong
with the query.

NO

2. Describe the crash. You just might have problems with Null data or
divide
by zero errors.

Access stops MS gray error reporting to MS pops up... restarts to
backup.mdb

3. If not, there's a good chance that the form is corrupt. Delete it
and
import a copy from a recent backup.

OK.. haven't tried yet... though when I open the form in design view
first
then try to go back to form view it works.

4. If #3 doesn't work, you may need to create an new database file and
import all objects from the old database into it. Whatever refuses to
import
is most likely corrupt and need rebuilding if you don't have a good,
uncorrupted backup.

I have backups...

5. The BEST suggestion. Normalize your database structure. You are
obviously
committing spreadsheet.

What about my question indicates it is not normalized to you?
Thanks

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am using this on a budget/actual form but it crashes "sometimes"
when I
open the form. No consistancy as to when it crashes... any
suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating,
tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the input.
Sorry 740 isn't my field as Jerry said I used it becasuse I thought it was
my last field which is 720... In any case can you tell me if you think I
should split the QUERY and then make a qry that consists of 2 calc quaries
based on tblProjBudAct+ qryPlanDetailShort instead of tblProjBudAct +
qryPlanDetailShort.(Though I'm not sure how to do this) And make the form
based on the 2 calc qry? Or is it ok to do 2 different calculations in one
qry? OR

Should there be 2 tbls tblProjBud (Budget) Table and a tblProjAct (Actual)?
I could change this, the B extension which is BUDGET and the ones without is
ACTUAL
Any input is appreciated!
lmv
--
Remember: There are suppose to be no "dumb" questions!


Jerry Whittle said:
Hi Duane,

I introduced the 740 into the discussion in a "what if" scenario if it had
to be added. Maybe we're both wrong on this one. Don't know without seeing it
up close.

If the query works, it works. Except that the form that based on it is
crashing. Corruption most likely.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Duane Hookom said:
Any time I see expressions that add values across columns/fields rather than
across records it suggests an un-normalized database. You mention field
[740] but that field isn't even in your query.

Your issue may be attempting to use a calculated expression "EstBuildCostB"
in another expression "CCur([EstBuildCostB]/[SQFT])". You do this in at
least two places in your query.

I am not an expert on building supplies but I would think that each
individual cost amount should create a record in a related table.

--
Duane Hookom
MS Access MVP
--

lmv said:
I believe your assumption is inaccurate... the number is a Category ID
number... the Categories are finalized and will not change or be added to.
The Main Categories are Main categories for building [740] is the Tax
category number... I don't understand your assumption. Orders are sorted
into
Categories/ Products(each type of lumber 2x4 etc are organized under
subcategory lumber) under Main Category Building Supplies [542] each item
ie
2x4 is an autonumber... what part of this is not normalized? The query
works... it totals all of the Main Categories which have thousands of
items... giving me a sqft price for plans. How should it be set up??





--
Remember: There are suppose to be no "dumb" questions!


:

Your SQL shows the tendancy to add columns across instead of rows down.
Let
me ask you this: If you need to add Project Budget Account (I'm guessing
here) 740, wouldn't you add another field to the table? Then wouldn't you
need to add 740 to many of your forms, reports, and queries? If so, your
tblProjBudAct is not normalized properly.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Thanks for the response...
1. Does the query alone ever cause a crash? If so, there's something
wrong
with the query.

NO

2. Describe the crash. You just might have problems with Null data or
divide
by zero errors.

Access stops MS gray error reporting to MS pops up... restarts to
backup.mdb

3. If not, there's a good chance that the form is corrupt. Delete it
and
import a copy from a recent backup.

OK.. haven't tried yet... though when I open the form in design view
first
then try to go back to form view it works.

4. If #3 doesn't work, you may need to create an new database file and
import all objects from the old database into it. Whatever refuses to
import
is most likely corrupt and need rebuilding if you don't have a good,
uncorrupted backup.

I have backups...

5. The BEST suggestion. Normalize your database structure. You are
obviously
committing spreadsheet.

What about my question indicates it is not normalized to you?
Thanks

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am using this on a budget/actual form but it crashes "sometimes"
when I
open the form. No consistancy as to when it crashes... any
suggestions?
Thanks!

SELECT tblProjBudAct.ProjID, qryPlanDetailShort.SQFT,
qryPlanDetailShort.MTGRms, qryPlanDetailShort.Seating,
tblProjBudAct.PlanID,
tblProjBudAct.LastEdited, tblProjBudAct.[260], tblProjBudAct.[280],
tblProjBudAct.[320], tblProjBudAct.[360], tblProjBudAct.[380],
tblProjBudAct.[400], tblProjBudAct.[420], tblProjBudAct.[450],
tblProjBudAct.[500], tblProjBudAct.[520], tblProjBudAct.[540],
tblProjBudAct.[560], tblProjBudAct.[580], tblProjBudAct.[600],
tblProjBudAct.[610], tblProjBudAct.[620], tblProjBudAct.[630],
tblProjBudAct.[660], tblProjBudAct.[670], tblProjBudAct.[680],
tblProjBudAct.[700], tblProjBudAct.[720], tblProjBudAct.Notes,
CCur([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
CCur([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SQFTPrice,
tblProjBudAct.[260B], tblProjBudAct.[280B], tblProjBudAct.[320B],
tblProjBudAct.[360B], tblProjBudAct.[380B], tblProjBudAct.[400B],
tblProjBudAct.[420B], tblProjBudAct.[450B], tblProjBudAct.[500B],
tblProjBudAct.[520B], tblProjBudAct.[540B], tblProjBudAct.[560B],
tblProjBudAct.[580B], tblProjBudAct.[600B], tblProjBudAct.[610B],
tblProjBudAct.[620B], tblProjBudAct.[630B], tblProjBudAct.[660B],
tblProjBudAct.[670B], tblProjBudAct.[680B], tblProjBudAct.[700B],
tblProjBudAct.[720B],
CCur([260B]+[280B]+[320B]+[360B]+[380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B]+[660B]+[670B]+[680B]+[700B]+[720B])
AS TotalB,
CCur([380B]+[400B]+[420B]+[450B]+[500B]+[520B]+[540B]+[560B]+[580B]+[600B]+[610B]+[620B]+[630B])
AS EstBuildCostB, CCur([EstBuildCostB]/[SQFT]) AS SQFTPriceB
FROM qryPlanDetailShort RIGHT JOIN tblProjBudAct ON
qryPlanDetailShort.PlanID = tblProjBudAct.PlanID
ORDER BY tblProjBudAct.ProjID;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top