Prev: Importing Excel into an Access Form
Next: how can I insert a "$' to print before my price label?
From: TDC on 18 Jun 2008 14:07
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
From: Jerry Whittle on 18 Jun 2008 14:26
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.


"TDC" wrote:

> 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
From: TDC on 18 Jun 2008 15:48
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


"Jerry Whittle" wrote:

> 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.
>
>
> "TDC" wrote:
>
> > 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
From: TDC on 18 Jun 2008 15:51
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


"Jerry Whittle" wrote:

> 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.
>
>
> "TDC" wrote:
>
> > 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
From: Jerry Whittle on 19 Jun 2008 09:21
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.


"TDC" wrote:

> 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
>
>
> "Jerry Whittle" wrote:
>
> > 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.
> >
> >
> > "TDC" wrote:
> >
> > > 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
 |  Next  |  Last
Pages: 1 2
Prev: Importing Excel into an Access Form
Next: how can I insert a "$' to print before my price label?