Multi Column Report Help

[Chad Cameron]

Hi All,

I am entering timecards into a database. On the timecard I have Truck_Num,
Material, Loads (simplified)
I have a query that gives me the data I want on the report.

This is what the query looks like

101 A 10
101 B 5
101 C 7
102 A 5
102 C 5 etc.

Is there a way to make my report look like the following:

Truck A B C Total
101 10 5 7 22
102 0 5 5 10
Total 10 10 12 32

After typing this out, I realized that maybe excel would be better for this.

What do you think?
Chad

 

[ghetto_banjo]

you can do this in access. you want to create a CrossTab Query.

Truck_Num will be a Row Heading.
Material will be a Column Heading
Loads will be a Value (and you also want the "Total" on this set to
Sum)

 

[Jerry Whittle]

Access is the best choice. What you need is a crosstab query. The Crosstab
query wizard will help you create one. It may take a couple of tries to get
it right. Don't worry about the totals at the bottom. You can make them
happen in the report.

However making a report out of a crosstab query can be a problem. Reports
expect a certain field name. If you create a report with fields A, B, C, and
D and there isn't any data for C, the crosstab won't normally create a C.
That will cause an error in the report.

On the flip side if the crosstab comes up with a field E and the report
doesn't have a text box for it, that data won't be shown.

The solution is Field Headings in the crosstab query. Check it out in Help.
After you manually populate the field heading with all expected inputs, then
create a report based on that crosstab query.

 

[Guest]

Hi All,

I am entering timecards into a database. On the timecard I have
Truck_Num, Material, Loads (simplified)
I have a query that gives me the data I want on the report.

This is what the query looks like

101 A 10
101 B 5
101 C 7
102 A 5
102 C 5 etc.

Is there a way to make my report look like the following:

Truck A B C Total
101 10 5 7 22
102 0 5 5 10
Total 10 10 12 32

After typing this out, I realized that maybe excel would be better for
this.

What do you think?
Chad

 

[Chad Cameron]

OK, it is a bit trickier than I expected. I left out a bunch of fields in
the OP but it appears I need to show them to make my code merge.

Our database is based off of stored procedures, so Ken, it looks like I will
have to go your route,.I am still a novice at SQL. Here is the code I
started with.

ALTER PROCEDURE [MOUNTPOLLEY\ccameron].Truck_Recon

(@FromDate datetime,

@ToDate datetime,

@cShift nvarchar(1))

AS SELECT dbo.TimeCardData.Date, dbo.Shift.Shift, dbo.Crew.Crew,
dbo.Equipment.EquipmentNum AS Truck, Equipment_1.EquipmentNum AS Loader,

dbo.WorkLocation.WorkLocation AS Pit,
WorkLocation_1.WorkLocation AS DumpLoc, dbo.MaterialType.MaterialType,
dbo.TimeCardData.Loads

FROM dbo.Crew INNER JOIN

dbo.TimeCardData ON dbo.Crew.CrewID =
dbo.TimeCardData.CrewID INNER JOIN

dbo.Shift ON dbo.TimeCardData.ShiftID =
dbo.Shift.ShiftID INNER JOIN

dbo.WorkLocation ON dbo.TimeCardData.Pit_SP_AreaID =
dbo.WorkLocation.WorkLocationID INNER JOIN

dbo.WorkLocation WorkLocation_1 ON
dbo.TimeCardData.DumpingLocationID = WorkLocation_1.WorkLocationID INNER
JOIN

dbo.Equipment ON dbo.TimeCardData.EquipmentID =
dbo.Equipment.EquipmentID INNER JOIN

dbo.Equipment Equipment_1 ON
dbo.TimeCardData.LoadedBy = Equipment_1.EquipmentID INNER JOIN

dbo.MaterialType ON dbo.TimeCardData.MaterialTypeID
= dbo.MaterialType.MaterialTypeID INNER JOIN

dbo.MaterialType MaterialType_1 ON
dbo.TimeCardData.MaterialTypeID = MaterialType_1.MaterialTypeID

WHERE (dbo.TimeCardData.Date BETWEEN @FromDate AND @ToDate) AND
(dbo.Equipment.EquipmentNum BETWEEN 6500 AND 6599) AND

(dbo.TimeCardData.Loads > 0) AND (dbo.Shift.Shift =
@cShift)

ORDER BY dbo.Crew.Crew, dbo.Equipment.EquipmentNum



I tried to just sub in my code into your snippet, but it failed. It didn't
like the transform or the pivot. Also, I am using 2007 if it helps

Thanks
Chad

Chad:

Base the report on a crosstab query:

TRANSFORM NZ(SUM(Trucks.Loads),0) AS SumOfLoads
SELECT Truck_Num, SUM(Loads) AS Total
FROM Trucks
GROUP BY Trucks.Truck_Num
PIVOT Trucks.Material IN ("A", "B", "C");

In the report footer sum the values of the columns in unbound controls
with
ControlSource properties of:

=Sum([A])
=Sum()
=Sum([C])
=Sum ([Total])

You can of course layout the columns in the report so that the Total
column
appears on the right rather than as the second column as returned by the
query.

Ken Sheridan
Stafford, England

Hi All,

I am entering timecards into a database. On the timecard I have
Truck_Num,
Material, Loads (simplified)
I have a query that gives me the data I want on the report.

This is what the query looks like

101 A 10
101 B 5
101 C 7
102 A 5
102 C 5 etc.

Is there a way to make my report look like the following:

Truck A B C Total
101 10 5 7 22
102 0 5 5 10
Total 10 10 12 32

After typing this out, I realized that maybe excel would be better for
this.

What do you think?
Chad

 

[De Jager]

you can do this in access. you want to create a CrossTab Query.

Truck_Num will be a Row Heading.
Material will be a Column Heading
Loads will be a Value (and you also want the "Total" on this set to
Sum)