Query won't report

[TDC]

I have 4 tables and each record is specific to a task with a start time,
finish time, location, equipment number, and other various data specific to
that task. I also have equipment table and department table linked from
another database. Relationships were created to join the unit number from
task tables to equipment table. The realationship between the equipment and
department table already existed from it's database.

I built queries to bring in the department and calculate the minutes per
record for each task. From that query I built another to group the location,
group the department, count equipment, and sum minutes. This was done for
each task.

Now I want to generate a report grouped by location that identifies tasks
with count and times grouped by equipment type. All location have one
similar task, but 2 have an additional task. I can build a query to get me
this data, but when I try to create a report it locks up. What am I doing
wrong or is there a better way?

I am using access 2000.

 

[Jerry Whittle]

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

How many records does the query return?

How fast does the query run? Don't be fooled by how fast it starts returning
records. What counts is how long before it returns the last records. Use the
record select buttons (I call them the VCR buttons) to fast forward to the
last record and see how long that takes.

 

[TDC]

The query takes a while to run and then populate with records. There are 43
records and that is right, but I just noticed it duplicates data in records
with the same location.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

Thanks for the interest am pretty green with the access.

 

[TDC]

The query takes quite a while to run and it populates with 43 records, which
is about right. I just noticed that it duplicates some of the data in
records with the same location. I'm pretty green with Access and sure
appreciate your interest and help.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

 

[Jerry Whittle]

Of the following fields, which are the primary keys in their table?

[Tractor Wash_Flush Count and Time].Location
[Trailer Wash_Flush Count and Time].Location
[Trailer Bake Count and Time].Location
[Tractor Bake Count and Time].Location

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The query takes quite a while to run and it populates with 43 records, which
is about right. I just noticed that it duplicates some of the data in
records with the same location. I'm pretty green with Access and sure
appreciate your interest and help.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

--
TDC

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

How many records does the query return?

How fast does the query run? Don't be fooled by how fast it starts returning
records. What counts is how long before it returns the last records. Use the
record select buttons (I call them the VCR buttons) to fast forward to the
last record and see how long that takes.

 

[TDC]

Record id (auto number)

--
TDC

Of the following fields, which are the primary keys in their table?

[Tractor Wash_Flush Count and Time].Location
[Trailer Wash_Flush Count and Time].Location
[Trailer Bake Count and Time].Location
[Tractor Bake Count and Time].Location

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The query takes quite a while to run and it populates with 43 records, which
is about right. I just noticed that it duplicates some of the data in
records with the same location. I'm pretty green with Access and sure
appreciate your interest and help.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

--
TDC

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

How many records does the query return?

How fast does the query run? Don't be fooled by how fast it starts returning
records. What counts is how long before it returns the last records. Use the
record select buttons (I call them the VCR buttons) to fast forward to the
last record and see how long that takes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have 4 tables and each record is specific to a task with a start time,
finish time, location, equipment number, and other various data specific to
that task. I also have equipment table and department table linked from
another database. Relationships were created to join the unit number from
task tables to equipment table. The realationship between the equipment and
department table already existed from it's database.

I built queries to bring in the department and calculate the minutes per
record for each task. From that query I built another to group the location,
group the department, count equipment, and sum minutes. This was done for
each task.

Now I want to generate a report grouped by location that identifies tasks
with count and times grouped by equipment type. All location have one
similar task, but 2 have an additional task. I can build a query to get me
this data, but when I try to create a report it locks up. What am I doing
wrong or is there a better way?

I am using access 2000.

 

[Jerry Whittle]

That probably explains the dupes and slowdown. You need to join from a
primary key in the parent table to a foreign key in the child table. Your
only hope is that some of those Location fields are a unique index. Is that
possible?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Record id (auto number)

--
TDC

Of the following fields, which are the primary keys in their table?

[Tractor Wash_Flush Count and Time].Location
[Trailer Wash_Flush Count and Time].Location
[Trailer Bake Count and Time].Location
[Tractor Bake Count and Time].Location

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The query takes quite a while to run and it populates with 43 records, which
is about right. I just noticed that it duplicates some of the data in
records with the same location. I'm pretty green with Access and sure
appreciate your interest and help.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

--
TDC


:

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

How many records does the query return?

How fast does the query run? Don't be fooled by how fast it starts returning
records. What counts is how long before it returns the last records. Use the
record select buttons (I call them the VCR buttons) to fast forward to the
last record and see how long that takes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have 4 tables and each record is specific to a task with a start time,
finish time, location, equipment number, and other various data specific to
that task. I also have equipment table and department table linked from
another database. Relationships were created to join the unit number from
task tables to equipment table. The realationship between the equipment and
department table already existed from it's database.

I built queries to bring in the department and calculate the minutes per
record for each task. From that query I built another to group the location,
group the department, count equipment, and sum minutes. This was done for
each task.

Now I want to generate a report grouped by location that identifies tasks
with count and times grouped by equipment type. All location have one
similar task, but 2 have an additional task. I can build a query to get me
this data, but when I try to create a report it locks up. What am I doing
wrong or is there a better way?

I am using access 2000.

 

[TDC]

I guess the equipment, start time, and end time would be unique within each
table. It can only be at one place (task) at a time. Is this like a
criteria for a primary key?

--
TDC

That probably explains the dupes and slowdown. You need to join from a
primary key in the parent table to a foreign key in the child table. Your
only hope is that some of those Location fields are a unique index. Is that
possible?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Record id (auto number)

--
TDC

Of the following fields, which are the primary keys in their table?

[Tractor Wash_Flush Count and Time].Location
[Trailer Wash_Flush Count and Time].Location
[Trailer Bake Count and Time].Location
[Tractor Bake Count and Time].Location

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The query takes quite a while to run and it populates with 43 records, which
is about right. I just noticed that it duplicates some of the data in
records with the same location. I'm pretty green with Access and sure
appreciate your interest and help.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

--
TDC


:

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

How many records does the query return?

How fast does the query run? Don't be fooled by how fast it starts returning
records. What counts is how long before it returns the last records. Use the
record select buttons (I call them the VCR buttons) to fast forward to the
last record and see how long that takes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have 4 tables and each record is specific to a task with a start time,
finish time, location, equipment number, and other various data specific to
that task. I also have equipment table and department table linked from
another database. Relationships were created to join the unit number from
task tables to equipment table. The realationship between the equipment and
department table already existed from it's database.

I built queries to bring in the department and calculate the minutes per
record for each task. From that query I built another to group the location,
group the department, count equipment, and sum minutes. This was done for
each task.

Now I want to generate a report grouped by location that identifies tasks
with count and times grouped by equipment type. All location have one
similar task, but 2 have an additional task. I can build a query to get me
this data, but when I try to create a report it locks up. What am I doing
wrong or is there a better way?

I am using access 2000.

 

[TDC]

Okay - I set up the unique indexes and then started from scratch on the
queries grouping by location and type. I combined those two queries to get
the stats I need reported, but I'm still getting duplicates. It is
duplicating the tractor stats and is very slow. Below is the SQL statement
of that query.

SELECT Locations.Location, [Stats_Trailers by Type and Location].[Trailer
Type], [Stats_Trailers by Type and Location].[Trailers Washed],
[Stats_Trailers by Type and Location].[Trailer Wash Efficiency],
[Stats_Trailers by Type and Location].[Trailers Baked], [Stats_Trailers by
Type and Location].[Trailer Bake Time], [Stats_Trailers by Type and
Location].[Trailer Bake Efficiency], [Stats_Tractors by Type and
Location].[Tractor Type], [Stats_Tractors by Type and Location].[Tractors
Washed], [Stats_Tractors by Type and Location].[Tractor Wash Time],
[Stats_Tractors by Type and Location].[Tractor Efficiency], [Stats_Tractors
by Type and Location].[Tractors Baked], [Stats_Tractors by Type and
Location].[Tractor Bake Time], [Stats_Tractors by Type and Location].[Tractor
Bake Efficiency]
FROM [Stats_Tractors by Type and Location] RIGHT JOIN (Locations LEFT JOIN
[Stats_Trailers by Type and Location] ON Locations.Location = [Stats_Trailers
by Type and Location].Location) ON [Stats_Tractors by Type and
Location].Location = Locations.Location;

--
TDC

I guess the equipment, start time, and end time would be unique within each
table. It can only be at one place (task) at a time. Is this like a
criteria for a primary key?

--
TDC

That probably explains the dupes and slowdown. You need to join from a
primary key in the parent table to a foreign key in the child table. Your
only hope is that some of those Location fields are a unique index. Is that
possible?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Record id (auto number)

--
TDC


:

Of the following fields, which are the primary keys in their table?

[Tractor Wash_Flush Count and Time].Location
[Trailer Wash_Flush Count and Time].Location
[Trailer Bake Count and Time].Location
[Tractor Bake Count and Time].Location

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The query takes quite a while to run and it populates with 43 records, which
is about right. I just noticed that it duplicates some of the data in
records with the same location. I'm pretty green with Access and sure
appreciate your interest and help.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

--
TDC


:

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

How many records does the query return?

How fast does the query run? Don't be fooled by how fast it starts returning
records. What counts is how long before it returns the last records. Use the
record select buttons (I call them the VCR buttons) to fast forward to the
last record and see how long that takes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have 4 tables and each record is specific to a task with a start time,
finish time, location, equipment number, and other various data specific to
that task. I also have equipment table and department table linked from
another database. Relationships were created to join the unit number from
task tables to equipment table. The realationship between the equipment and
department table already existed from it's database.

I built queries to bring in the department and calculate the minutes per
record for each task. From that query I built another to group the location,
group the department, count equipment, and sum minutes. This was done for
each task.

Now I want to generate a report grouped by location that identifies tasks
with count and times grouped by equipment type. All location have one
similar task, but 2 have an additional task. I can build a query to get me
this data, but when I try to create a report it locks up. What am I doing
wrong or is there a better way?

I am using access 2000.

 

[TDC]

I went back to begining queries and concatonated [location] and [sanitation
type]. This eliminated the dups and gave me what I needed. The report is
slow, but it can be done.

Thanks for you help.


--
TDC

Okay - I set up the unique indexes and then started from scratch on the
queries grouping by location and type. I combined those two queries to get
the stats I need reported, but I'm still getting duplicates. It is
duplicating the tractor stats and is very slow. Below is the SQL statement
of that query.

SELECT Locations.Location, [Stats_Trailers by Type and Location].[Trailer
Type], [Stats_Trailers by Type and Location].[Trailers Washed],
[Stats_Trailers by Type and Location].[Trailer Wash Efficiency],
[Stats_Trailers by Type and Location].[Trailers Baked], [Stats_Trailers by
Type and Location].[Trailer Bake Time], [Stats_Trailers by Type and
Location].[Trailer Bake Efficiency], [Stats_Tractors by Type and
Location].[Tractor Type], [Stats_Tractors by Type and Location].[Tractors
Washed], [Stats_Tractors by Type and Location].[Tractor Wash Time],
[Stats_Tractors by Type and Location].[Tractor Efficiency], [Stats_Tractors
by Type and Location].[Tractors Baked], [Stats_Tractors by Type and
Location].[Tractor Bake Time], [Stats_Tractors by Type and Location].[Tractor
Bake Efficiency]
FROM [Stats_Tractors by Type and Location] RIGHT JOIN (Locations LEFT JOIN
[Stats_Trailers by Type and Location] ON Locations.Location = [Stats_Trailers
by Type and Location].Location) ON [Stats_Tractors by Type and
Location].Location = Locations.Location;

--
TDC

I guess the equipment, start time, and end time would be unique within each
table. It can only be at one place (task) at a time. Is this like a
criteria for a primary key?

--
TDC

That probably explains the dupes and slowdown. You need to join from a
primary key in the parent table to a foreign key in the child table. Your
only hope is that some of those Location fields are a unique index. Is that
possible?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Record id (auto number)

--
TDC


:

Of the following fields, which are the primary keys in their table?

[Tractor Wash_Flush Count and Time].Location
[Trailer Wash_Flush Count and Time].Location
[Trailer Bake Count and Time].Location
[Tractor Bake Count and Time].Location

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The query takes quite a while to run and it populates with 43 records, which
is about right. I just noticed that it duplicates some of the data in
records with the same location. I'm pretty green with Access and sure
appreciate your interest and help.


SELECT [Trailer Wash_Flush Count and Time].Location, [Trailer Wash_Flush
Count and Time].[Trailer Wash Type], [Trailer Wash_Flush Count and
Time].[Trailers Washed], [Trailer Wash_Flush Count and Time].[Trailer Wash
Time], [Tractor Wash_Flush Count and Time].[Tractor Wash Type], [Tractor
Wash_Flush Count and Time].[Tractors Washed], [Tractor Wash_Flush Count and
Time].[Tractor Wash Time], [Trailer Bake Count and Time].[Trailer Bake
Type], [Trailer Bake Count and Time].[Trailers Baked], [Trailer Bake Count
and Time].[Trailer Bake Time] AS [Trailer Bake Count and Time_Trailer Bake
Time], [Tractor Bake Count and Time].[Trailer Bake Type], [Tractor Bake Count
and Time].[Tractors Baked], [Tractor Bake Count and Time].[Trailer Bake Time]
AS [Tractor Bake Count and Time_Trailer Bake Time]
FROM [Tractor Bake Count and Time] RIGHT JOIN ([Trailer Bake Count and Time]
RIGHT JOIN ([Tractor Wash_Flush Count and Time] RIGHT JOIN [Trailer
Wash_Flush Count and Time] ON [Tractor Wash_Flush Count and Time].Location =
[Trailer Wash_Flush Count and Time].Location) ON [Trailer Bake Count and
Time].Location = [Trailer Wash_Flush Count and Time].Location) ON [Tractor
Bake Count and Time].Location = [Trailer Wash_Flush Count and Time].Location;

--
TDC


:

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

How many records does the query return?

How fast does the query run? Don't be fooled by how fast it starts returning
records. What counts is how long before it returns the last records. Use the
record select buttons (I call them the VCR buttons) to fast forward to the
last record and see how long that takes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have 4 tables and each record is specific to a task with a start time,
finish time, location, equipment number, and other various data specific to
that task. I also have equipment table and department table linked from
another database. Relationships were created to join the unit number from
task tables to equipment table. The realationship between the equipment and
department table already existed from it's database.

I built queries to bring in the department and calculate the minutes per
record for each task. From that query I built another to group the location,
group the department, count equipment, and sum minutes. This was done for
each task.

Now I want to generate a report grouped by location that identifies tasks
with count and times grouped by equipment type. All location have one
similar task, but 2 have an additional task. I can build a query to get me
this data, but when I try to create a report it locks up. What am I doing
wrong or is there a better way?

I am using access 2000.