Calculations, Queries...must be a better way

[gremo]

My database setup is this. It pulls the part number, costs, price etc from our ERP and allow (via a form) to fill in the Bill of Materials for the particular part. All of the components are based off of combo boxes which are based on queries from the inventory master table. The form I designed works fine, after it opens. What I am trying to do is pull the cost for each component, and calculate the cost of the component (Qty * Cost). Then total up the cost of the part.



I have the calculations in a query. The work fine and calculate correctly. The problem is that It takes forever for the form to load and worse if I try to run the query with the calculations alone. I'm not talking about a few minutes, I'm talking 10 or 15 for it to initially run, if it runs at all.

Any suggestions on how to set this up to run faster, or at least fast enough that we can use it.

[alansidman]

Have you run a compact and repair and see if that helps.

[gremo]

I have, yes.

[gremo]

I"m just trying to get the query to run so I can get the cost calculations for a spreadsheet.... It's been running 20 minutes.

[ssanfu]

OK, how are you doing the calculations?
Is it slow because of the number of records in the dB? Some of my reports were slow in a dB once the record in one table were over 1 million.... I don't remember when it started slowing down - it was a gradual process..

Do you have a lot of DLookups in the query?

What does is the SQL of the query?

[gremo]

I don't have any dlookups. I have comboboxes based of separate queries for any values I need to look up. I only have 1489 records in the table the form is based on. Why would a form load, display the correct calculations from a query, but the query will not run... I waited over an hour and gave up.

[ssanfu]

Would you post a pic of the relationship window?

Again, what is the SQL of the query?

Hard to help if we don't know how your dB id put together........