queries freeze up

T

Ted W.

I am trying to run a query but it ends up just running indefinitely which
causes my computer to freeze up. I have run many queries with the database i
am working on and they have run fine, but this new query is attempting to
reference more than 2 tables and it will not run. I'm assuming the
complexity of adding the extra tables to search is what is causing the query
to fail. My database is essentialy set up as one Master table that just
lists the product names, id numbers and a few other generic bits of info.
This table is then tied to 4 much larger tables. these are the four cost
centers that make up the production costs. i am trying to write a query
that sums up the total cost of each cost center for each product. I can do
this with just one or two cost centers, but if i try to include three or all
four cost centers the query will never complete. I've tried mixing and
matching the tables and its not a specific table at fault. Does any have any
guess as to why the extra tables is causing the query to crash or is access
just unable to run a query with that many inputs? by the way each cost
center table has around 30,000 rows. I appreciate any thoughts or tips.
thanks
 
B

Barry Gilbert

Do you have indexes on the cost tables? I would suggest putting indexes on
the columns that contain related information. This will most likely speed
things up.

Barry
 
D

David Cox

Can you show us your queries? I suspect that you do not have the tables
joined correctly.

I would tackle this problem this way. ( and be prepared for someone to show
me a better way)

Master
id_product
product info

Cost centre1:
id_cost1
id_product
cost

Cost_centre2:
id_prod
id_cost2

etc
make 4 total queries with 4 cost total columns, each one summing just one
cost centre, and setting the others to 0:

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(T_cost1.cost) AS total1,
Sum(0) AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(0) AS total1, Sum(T_cost2.cost)
AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff;

etc

I would then do a union of these 4 queries, and sum all four cost centres.

As I said, there is probably a better way.
 
T

Ted W.

I am copying my query below. it was made using the Query Wizard. My SQL
skills aren't really satisfactory enough to write it on my own. Anyway, I do
have indexes in my fields as Barry suggested so so I don't think indexing is
the problem. As for the union query idea, i am a little confused on where i
create the union query. HELP is not very clear on where the union query is
actually created so if you could provide a little detail there as well. My
tables are joined but maybe not correctly. Each cost center is joined to the
master table, but not to each other. Should the cost centers be joined to
each other as well?
I really appreciate your help. again my query is below. it is only with 3
costs centers though just to make things a little simpler.
SELECT DISTINCTROW [Tail Numbers].[Tail #], Sum(Materials.INVOICE) AS [Sum
Of INVOICE], Sum(NonRoutine.[NR Total]) AS [Sum Of NR Total],
Sum(Routine.[Routine Total]) AS [Sum Of Routine Total]
FROM (([Tail Numbers] INNER JOIN NonRoutine ON [Tail Numbers].[Visit ID #] =
NonRoutine.[Visit ID #]) INNER JOIN Routine ON [Tail Numbers].[Visit ID #] =
Routine.[Visit ID #]) INNER JOIN Materials ON [Tail Numbers].[Visit ID #] =
Materials.[Visit ID #]
GROUP BY [Tail Numbers].[Tail #];
 
D

David Cox

for I = 1 to N: "I must keep my mouth shut":next

I slightly changed field names to suit me.

my final query looked like this.
SELECT q_union.Visit_Id, Sum(q_union.SumofInvoice) AS SumOfSumofInvoice,
Sum(q_union.SumOfNRTotal) AS SumOfSumOfNRTotal,
Sum(q_union.SumOfRoutineTotal) AS SumOfSumOfRoutineTotal
FROM q_union
GROUP BY q_union.Visit_Id;

and Q_union looked like this:

(SELECT [Tail_Numbers].[Visit_Id], [SumofInvoice], 0 AS [SumOfNRTotal], 0 AS
[SumOfRoutineTotal]
FROM [Q_sumofinvoice],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofinvoice].[Visit_Id])
UNION
(SELECT [Tail_Numbers].[Visit_Id], 0 as [SumofInvoice],
[Q_sumofnrtotal].[SumOfNRTotal], 0 AS SumOfRoutineTotal
FROM [Q_sumofnrtotal],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofnrtotal].[Visit_Id])
UNION (SELECT [Tail_Numbers].[Visit_Id], 0 as [SumofInvoice], 0 as
[SumOfNRTotal], [SumOfRoutineTotal]
FROM [Q_sumofroutinetotal],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofroutinetotal].[Visit_Id]);

and Q_sumofinvoice looked like this:

SELECT Materials.visit_id, Sum(Materials.Invoice) AS SumofInvoice, 0 AS
SumOfNRTotal, 0 AS SumOfRoutineTotal
FROM Materials
GROUP BY Materials.visit_id, 0, 0;

It occurs to me that the 0 as .... fields are not necessary here, as they
can be generated in the union query.

I hope this helps you, it has certainly helped me.

Ted W. said:
I am copying my query below. it was made using the Query Wizard. My SQL
skills aren't really satisfactory enough to write it on my own. Anyway, I
do
have indexes in my fields as Barry suggested so so I don't think indexing
is
the problem. As for the union query idea, i am a little confused on where
i
create the union query. HELP is not very clear on where the union query
is
actually created so if you could provide a little detail there as well.
My
tables are joined but maybe not correctly. Each cost center is joined to
the
master table, but not to each other. Should the cost centers be joined to
each other as well?
I really appreciate your help. again my query is below. it is only with 3
costs centers though just to make things a little simpler.
SELECT DISTINCTROW [Tail Numbers].[Tail #], Sum(Materials.INVOICE) AS [Sum
Of INVOICE], Sum(NonRoutine.[NR Total]) AS [Sum Of NR Total],
Sum(Routine.[Routine Total]) AS [Sum Of Routine Total]
FROM (([Tail Numbers] INNER JOIN NonRoutine ON [Tail Numbers].[Visit ID #]
=
NonRoutine.[Visit ID #]) INNER JOIN Routine ON [Tail Numbers].[Visit ID #]
=
Routine.[Visit ID #]) INNER JOIN Materials ON [Tail Numbers].[Visit ID #]
=
Materials.[Visit ID #]
GROUP BY [Tail Numbers].[Tail #];


David Cox said:
Can you show us your queries? I suspect that you do not have the tables
joined correctly.

I would tackle this problem this way. ( and be prepared for someone to
show
me a better way)

Master
id_product
product info

Cost centre1:
id_cost1
id_product
cost

Cost_centre2:
id_prod
id_cost2

etc
make 4 total queries with 4 cost total columns, each one summing just one
cost centre, and setting the others to 0:

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(T_cost1.cost) AS total1,
Sum(0) AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(0) AS total1,
Sum(T_cost2.cost)
AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff;

etc

I would then do a union of these 4 queries, and sum all four cost
centres.

As I said, there is probably a better way.
Click to expand...
Click to expand...
 
D

David Cox

I said there was a better way. A Select query can return a single field. so
we can write one, including a Totals query, where we might put a field name.

Try something like (changing names to yours):

SELECT [Tail_Numbers].[Visit_Id] AS Visit_ID,
(SELECT SUM(Materials.Invoice) AS X FROM Materials
WHERE [Tail_Numbers].[Visit_Id] = Materials.Visit_Id) AS SumOfInvoice,
(SELECT SUM(Routine.Routine_Total) AS X FROM Routine
WHERE [Tail_Numbers].[Visit_Id] = Routine.Visit_Id) AS SumOfRoutineTotal,
(SELECT SUM( Non_routine.NR_Total) AS X FROM Non_routine
WHERE [Tail_Numbers].[Visit_Id] = Non_routine.Visit_Id) AS SumOfNRTotal
FROM Tail_Numbers;

If you do try this in a limited version, as you have done with the
exisiting methods, and it works, can you come back with the time
comparisons, please.


David Cox said:
for I = 1 to N: "I must keep my mouth shut":next

I slightly changed field names to suit me.

my final query looked like this.
SELECT q_union.Visit_Id, Sum(q_union.SumofInvoice) AS SumOfSumofInvoice,
Sum(q_union.SumOfNRTotal) AS SumOfSumOfNRTotal,
Sum(q_union.SumOfRoutineTotal) AS SumOfSumOfRoutineTotal
FROM q_union
GROUP BY q_union.Visit_Id;

and Q_union looked like this:

(SELECT [Tail_Numbers].[Visit_Id], [SumofInvoice], 0 AS [SumOfNRTotal], 0
AS [SumOfRoutineTotal]
FROM [Q_sumofinvoice],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofinvoice].[Visit_Id])
UNION
(SELECT [Tail_Numbers].[Visit_Id], 0 as [SumofInvoice],
[Q_sumofnrtotal].[SumOfNRTotal], 0 AS SumOfRoutineTotal
FROM [Q_sumofnrtotal],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofnrtotal].[Visit_Id])
UNION (SELECT [Tail_Numbers].[Visit_Id], 0 as [SumofInvoice], 0 as
[SumOfNRTotal], [SumOfRoutineTotal]
FROM [Q_sumofroutinetotal],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofroutinetotal].[Visit_Id]);

and Q_sumofinvoice looked like this:

SELECT Materials.visit_id, Sum(Materials.Invoice) AS SumofInvoice, 0 AS
SumOfNRTotal, 0 AS SumOfRoutineTotal
FROM Materials
GROUP BY Materials.visit_id, 0, 0;

It occurs to me that the 0 as .... fields are not necessary here, as
they can be generated in the union query.

I hope this helps you, it has certainly helped me.

Ted W. said:
I am copying my query below. it was made using the Query Wizard. My SQL
skills aren't really satisfactory enough to write it on my own. Anyway,
I do
have indexes in my fields as Barry suggested so so I don't think indexing
is
the problem. As for the union query idea, i am a little confused on
where i
create the union query. HELP is not very clear on where the union query
is
actually created so if you could provide a little detail there as well.
My
tables are joined but maybe not correctly. Each cost center is joined to
the
master table, but not to each other. Should the cost centers be joined
to
each other as well?
I really appreciate your help. again my query is below. it is only with
3
costs centers though just to make things a little simpler.
SELECT DISTINCTROW [Tail Numbers].[Tail #], Sum(Materials.INVOICE) AS
[Sum
Of INVOICE], Sum(NonRoutine.[NR Total]) AS [Sum Of NR Total],
Sum(Routine.[Routine Total]) AS [Sum Of Routine Total]
FROM (([Tail Numbers] INNER JOIN NonRoutine ON [Tail Numbers].[Visit ID
#] =
NonRoutine.[Visit ID #]) INNER JOIN Routine ON [Tail Numbers].[Visit ID
#] =
Routine.[Visit ID #]) INNER JOIN Materials ON [Tail Numbers].[Visit ID #]
=
Materials.[Visit ID #]
GROUP BY [Tail Numbers].[Tail #];


David Cox said:
Can you show us your queries? I suspect that you do not have the tables
joined correctly.

I would tackle this problem this way. ( and be prepared for someone to
show
me a better way)

Master
id_product
product info

Cost centre1:
id_cost1
id_product
cost

Cost_centre2:
id_prod
id_cost2

etc
make 4 total queries with 4 cost total columns, each one summing just
one
cost centre, and setting the others to 0:

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(T_cost1.cost) AS total1,
Sum(0) AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(0) AS total1,
Sum(T_cost2.cost)
AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff;

etc

I would then do a union of these 4 queries, and sum all four cost
centres.

As I said, there is probably a better way.


This table is then tied to 4 much larger tables. these are the four
cost
centers that make up the production costs. i am trying to write a
query
that sums up the total cost of each cost center for each product. I
can
do
this with just one or two cost centers, but if i try to include three
or
all
four cost centers the query will never complete. I've tried mixing
and
matching the tables and its not a specific table at fault. Does any
have
any
guess as to why the extra tables is causing the query to crash or is
access
just unable to run a query with that many inputs? by the way each
cost
center table has around 30,000 rows. I appreciate any thoughts or
tips.
thanks
Click to expand...
Click to expand...
Click to expand...
 
T

Ted W.

I really Appreciate your help. your first method using the Union Query
worked well. Its a bit cumbersome i suppose but it works and that's all i
care about. So thank you for that. Your second method using the single
select query does seem simpler but i have not yet had time to try it. If i
get some spare time this week i will try that as well and let you know how
well it works.
thanks again for your help.

David Cox said:
I said there was a better way. A Select query can return a single field. so
we can write one, including a Totals query, where we might put a field name.

Try something like (changing names to yours):

SELECT [Tail_Numbers].[Visit_Id] AS Visit_ID,
(SELECT SUM(Materials.Invoice) AS X FROM Materials
WHERE [Tail_Numbers].[Visit_Id] = Materials.Visit_Id) AS SumOfInvoice,
(SELECT SUM(Routine.Routine_Total) AS X FROM Routine
WHERE [Tail_Numbers].[Visit_Id] = Routine.Visit_Id) AS SumOfRoutineTotal,
(SELECT SUM( Non_routine.NR_Total) AS X FROM Non_routine
WHERE [Tail_Numbers].[Visit_Id] = Non_routine.Visit_Id) AS SumOfNRTotal
FROM Tail_Numbers;

If you do try this in a limited version, as you have done with the
exisiting methods, and it works, can you come back with the time
comparisons, please.


David Cox said:
for I = 1 to N: "I must keep my mouth shut":next

I slightly changed field names to suit me.

my final query looked like this.
SELECT q_union.Visit_Id, Sum(q_union.SumofInvoice) AS SumOfSumofInvoice,
Sum(q_union.SumOfNRTotal) AS SumOfSumOfNRTotal,
Sum(q_union.SumOfRoutineTotal) AS SumOfSumOfRoutineTotal
FROM q_union
GROUP BY q_union.Visit_Id;

and Q_union looked like this:

(SELECT [Tail_Numbers].[Visit_Id], [SumofInvoice], 0 AS [SumOfNRTotal], 0
AS [SumOfRoutineTotal]
FROM [Q_sumofinvoice],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofinvoice].[Visit_Id])
UNION
(SELECT [Tail_Numbers].[Visit_Id], 0 as [SumofInvoice],
[Q_sumofnrtotal].[SumOfNRTotal], 0 AS SumOfRoutineTotal
FROM [Q_sumofnrtotal],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofnrtotal].[Visit_Id])
UNION (SELECT [Tail_Numbers].[Visit_Id], 0 as [SumofInvoice], 0 as
[SumOfNRTotal], [SumOfRoutineTotal]
FROM [Q_sumofroutinetotal],[Tail_Numbers]
WHERE [Tail_Numbers].[Visit_Id] = [Q_sumofroutinetotal].[Visit_Id]);

and Q_sumofinvoice looked like this:

SELECT Materials.visit_id, Sum(Materials.Invoice) AS SumofInvoice, 0 AS
SumOfNRTotal, 0 AS SumOfRoutineTotal
FROM Materials
GROUP BY Materials.visit_id, 0, 0;

It occurs to me that the 0 as .... fields are not necessary here, as
they can be generated in the union query.

I hope this helps you, it has certainly helped me.

Ted W. said:
I am copying my query below. it was made using the Query Wizard. My SQL
skills aren't really satisfactory enough to write it on my own. Anyway,
I do
have indexes in my fields as Barry suggested so so I don't think indexing
is
the problem. As for the union query idea, i am a little confused on
where i
create the union query. HELP is not very clear on where the union query
is
actually created so if you could provide a little detail there as well.
My
tables are joined but maybe not correctly. Each cost center is joined to
the
master table, but not to each other. Should the cost centers be joined
to
each other as well?
I really appreciate your help. again my query is below. it is only with
3
costs centers though just to make things a little simpler.
SELECT DISTINCTROW [Tail Numbers].[Tail #], Sum(Materials.INVOICE) AS
[Sum
Of INVOICE], Sum(NonRoutine.[NR Total]) AS [Sum Of NR Total],
Sum(Routine.[Routine Total]) AS [Sum Of Routine Total]
FROM (([Tail Numbers] INNER JOIN NonRoutine ON [Tail Numbers].[Visit ID
#] =
NonRoutine.[Visit ID #]) INNER JOIN Routine ON [Tail Numbers].[Visit ID
#] =
Routine.[Visit ID #]) INNER JOIN Materials ON [Tail Numbers].[Visit ID #]
=
Materials.[Visit ID #]
GROUP BY [Tail Numbers].[Tail #];


:

Can you show us your queries? I suspect that you do not have the tables
joined correctly.

I would tackle this problem this way. ( and be prepared for someone to
show
me a better way)

Master
id_product
product info

Cost centre1:
id_cost1
id_product
cost

Cost_centre2:
id_prod
id_cost2

etc
make 4 total queries with 4 cost total columns, each one summing just
one
cost centre, and setting the others to 0:

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(T_cost1.cost) AS total1,
Sum(0) AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff

SELECT t_prod.ID_prod, t_prod.prodstuff, Sum(0) AS total1,
Sum(T_cost2.cost)
AS total2, Sum(0) AS total3, Sum(0) AS total4
FROM t_prod INNER JOIN T_cost2 ON t_prod.ID_prod = T_cost2.id_prod
GROUP BY t_prod.ID_prod, t_prod.prodstuff;

etc

I would then do a union of these 4 queries, and sum all four cost
centres.

As I said, there is probably a better way.


This table is then tied to 4 much larger tables. these are the four
cost
centers that make up the production costs. i am trying to write a
query
that sums up the total cost of each cost center for each product. I
can
do
this with just one or two cost centers, but if i try to include three
or
all
four cost centers the query will never complete. I've tried mixing
and
matching the tables and its not a specific table at fault. Does any
have
any
guess as to why the extra tables is causing the query to crash or is
access
just unable to run a query with that many inputs? by the way each
cost
center table has around 30,000 rows. I appreciate any thoughts or
tips.
thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query data from 3 tables with similar data 0
Work Budget Salary vs Real Time Salary 0
Setting column width in a table with a macro is not working 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
List all tables, including document structure 0
Sorting Multiple Tables 1
Probably A Stupid Query Question 3
Incone & Expenses Statement 0

Top