Sum Report

  • Thread starter Thread starter gr
  • Start date Start date
G

gr

Hi, I want to build a report using the following tables
tblName: NameID, Names, TRDate
tblAdmin: NameID, Activity, HoursAdmin
tblPrjs: NameID, Activity, HoursPrjs
tblSales: NameID, Activity, HoursSales
tblService: NameID, Activity, HoursService

* there are more fields, but I simplified to explain it
easier.
* They are all related by NameID

I need the report to look like this:

Day1 Day2 ..... Day31
Name1 ## ##
Name2 ## ##
Name3 ##
Name4 ##

Where ## is the sum of
HoursAdmin+HoursPrjs+HoursSales+HoursService
There might be cases in which there's no working hours for
the specified Day.

Any ideas?
 
I would first create a union query of the trhe Hours tables so you could
total hours by NameID and TRDate (there aren't dates in the "hours" tables?)
You can then create a crosstab based on your union query. Use a control on a
form for the ending date "Forms!frmRptCrit!txtEndDate".
Your crosstab Row Heading will be the Name field(s). The value will be Sum
Of Hours. The Column Heading will be
ColHead: "Day" & DateDiff("D",[TRDate], Forms!frmRptCrit!txtEndDate)
Make sure you enter query parameters
Forms!frmRptCrit!txtEndDate Date/Time
Set the column headings to a value that determines how many days you want in
your results:
Column Headings: "Day0","Day1","Day2",..."Day6"..."Day31"
Your report can then be built from a standard/static group of columns.
 
Hi, thank you for your response. I hope you follow this
thread..
I'm having a lot of problems with the union query.
Apparently it works fine, but I'm not getting all the
records, I realized this by comparing the number of
records and as well checking the sum of total hours.

the sql for the union query looks as follows:

SELECT * , "Administration" AS [Area]
FROM qryNames_Admin
UNION SELECT *, "Projects"
FROM qryNames_Prj
UNION SELECT *, "Sales"
FROM qryNames_Sales
UNION SELECT *, "Service"
FROM qryNames_Service;

The sql for qryNames_Admin looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID;

The sql for qryNames_Prj looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRPrj.PrjHours
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID;

The sql for qryNames_Sales looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRSales.Hours
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID;

The sql for qryNames_Service looks as follows

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRService.Hours
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID;

I tried as well with other query, resulting in a similar
problem. I posted it on March 2, 2004 "Query Problems" at
5:26AM

Thank you!


-----Original Message-----
I would first create a union query of the trhe Hours tables so you could
total hours by NameID and TRDate (there aren't dates in the "hours" tables?)
You can then create a crosstab based on your union query. Use a control on a
form for the ending date "Forms!frmRptCrit!txtEndDate".
Your crosstab Row Heading will be the Name field(s). The value will be Sum
Of Hours. The Column Heading will be
ColHead: "Day" & DateDiff("D",[TRDate], Forms!frmRptCrit! txtEndDate)
Make sure you enter query parameters
Forms!frmRptCrit!txtEndDate Date/Time
Set the column headings to a value that determines how many days you want in
your results:
Column Headings: "Day0","Day1","Day2",..."Day6"..."Day31"
Your report can then be built from a standard/static group of columns.

--
Duane Hookom
MS Access MVP


Hi, I want to build a report using the following tables
tblName: NameID, Names, TRDate
tblAdmin: NameID, Activity, HoursAdmin
tblPrjs: NameID, Activity, HoursPrjs
tblSales: NameID, Activity, HoursSales
tblService: NameID, Activity, HoursService

* there are more fields, but I simplified to explain it
easier.
* They are all related by NameID

I need the report to look like this:

Day1 Day2 ..... Day31
Name1 ## ##
Name2 ## ##
Name3 ##
Name4 ##

Where ## is the sum of
HoursAdmin+HoursPrjs+HoursSales+HoursService
There might be cases in which there's no working hours for
the specified Day.

Any ideas?
Click to expand...


.
Click to expand...
 
I also tried pulling directly from the tables, with the
same bad results =(

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours, "Administration" AS [Area]
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRPrj.PrjHours, "Projects"
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRSales.Hours, "Sales"
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRService.Hours, "Service"
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID
-----Original Message-----
Hi, thank you for your response. I hope you follow this
thread..
I'm having a lot of problems with the union query.
Apparently it works fine, but I'm not getting all the
records, I realized this by comparing the number of
records and as well checking the sum of total hours.

the sql for the union query looks as follows:

SELECT * , "Administration" AS [Area]
FROM qryNames_Admin
UNION SELECT *, "Projects"
FROM qryNames_Prj
UNION SELECT *, "Sales"
FROM qryNames_Sales
UNION SELECT *, "Service"
FROM qryNames_Service;

The sql for qryNames_Admin looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID;

The sql for qryNames_Prj looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRPrj.PrjHours
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID;

The sql for qryNames_Sales looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRSales.Hours
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID;

The sql for qryNames_Service looks as follows

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRService.Hours
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID;

I tried as well with other query, resulting in a similar
problem. I posted it on March 2, 2004 "Query Problems" at
5:26AM

Thank you!


-----Original Message-----
I would first create a union query of the trhe Hours tables so you could
total hours by NameID and TRDate (there aren't dates in the "hours" tables?)
You can then create a crosstab based on your union
Click to expand...
query.
Use a control on a
form for the ending date "Forms!frmRptCrit!txtEndDate".
Your crosstab Row Heading will be the Name field(s). The value will be Sum
Of Hours. The Column Heading will be
ColHead: "Day" & DateDiff("D",[TRDate], Forms!frmRptCrit! txtEndDate)
Make sure you enter query parameters
Forms!frmRptCrit!txtEndDate Date/Time
Set the column headings to a value that determines how many days you want in
your results:
Column Headings: "Day0","Day1","Day2",..."Day6"..."Day31"
Your report can then be built from a standard/static group of columns.

--
Duane Hookom
MS Access MVP


Hi, I want to build a report using the following tables
tblName: NameID, Names, TRDate
tblAdmin: NameID, Activity, HoursAdmin
tblPrjs: NameID, Activity, HoursPrjs
tblSales: NameID, Activity, HoursSales
tblService: NameID, Activity, HoursService

* there are more fields, but I simplified to explain it
easier.
* They are all related by NameID

I need the report to look like this:

Day1 Day2 ..... Day31
Name1 ## ##
Name2 ## ##
Name3 ##
Name4 ##

Where ## is the sum of
HoursAdmin+HoursPrjs+HoursSales+HoursService
There might be cases in which there's no working hours for
the specified Day.

Any ideas?
Click to expand...


.
Click to expand...
.
Click to expand...
 
I would specify the actual field names in the first UNION query you
provided. Also, if the number of records don't match, try use:
UNION ALL
rather than
UNION
I would not include joining to tblName until after the UNION query has been
created.

--
Duane Hookom
MS Access MVP


gr said:
I also tried pulling directly from the tables, with the
same bad results =(

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours, "Administration" AS [Area]
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRPrj.PrjHours, "Projects"
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRSales.Hours, "Sales"
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRService.Hours, "Service"
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID
-----Original Message-----
Hi, thank you for your response. I hope you follow this
thread..
I'm having a lot of problems with the union query.
Apparently it works fine, but I'm not getting all the
records, I realized this by comparing the number of
records and as well checking the sum of total hours.

the sql for the union query looks as follows:

SELECT * , "Administration" AS [Area]
FROM qryNames_Admin
UNION SELECT *, "Projects"
FROM qryNames_Prj
UNION SELECT *, "Sales"
FROM qryNames_Sales
UNION SELECT *, "Service"
FROM qryNames_Service;

The sql for qryNames_Admin looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID;

The sql for qryNames_Prj looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRPrj.PrjHours
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID;

The sql for qryNames_Sales looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRSales.Hours
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID;

The sql for qryNames_Service looks as follows

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRService.Hours
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID;

I tried as well with other query, resulting in a similar
problem. I posted it on March 2, 2004 "Query Problems" at
5:26AM

Thank you!


-----Original Message-----
I would first create a union query of the trhe Hours tables so you could
total hours by NameID and TRDate (there aren't dates in the "hours" tables?)
You can then create a crosstab based on your union
Click to expand...
query.
Use a control on a
form for the ending date "Forms!frmRptCrit!txtEndDate".
Your crosstab Row Heading will be the Name field(s). The value will be Sum
Of Hours. The Column Heading will be
ColHead: "Day" & DateDiff("D",[TRDate], Forms!frmRptCrit! txtEndDate)
Make sure you enter query parameters
Forms!frmRptCrit!txtEndDate Date/Time
Set the column headings to a value that determines how many days you want in
your results:
Column Headings: "Day0","Day1","Day2",..."Day6"..."Day31"
Your report can then be built from a standard/static group of columns.

--
Duane Hookom
MS Access MVP


Hi, I want to build a report using the following tables
tblName: NameID, Names, TRDate
tblAdmin: NameID, Activity, HoursAdmin
tblPrjs: NameID, Activity, HoursPrjs
tblSales: NameID, Activity, HoursSales
tblService: NameID, Activity, HoursService

* there are more fields, but I simplified to explain it
easier.
* They are all related by NameID

I need the report to look like this:

Day1 Day2 ..... Day31
Name1 ## ##
Name2 ## ##
Name3 ##
Name4 ##

Where ## is the sum of
HoursAdmin+HoursPrjs+HoursSales+HoursService
There might be cases in which there's no working hours for
the specified Day.

Any ideas?




.
Click to expand...
.
Click to expand...
Click to expand...
 
Thank you! I spent hours and just by using UNION ALL
worked perfect =)
Well still missing the report.. =/
-----Original Message-----
I would specify the actual field names in the first UNION query you
provided. Also, if the number of records don't match, try use:
UNION ALL
rather than
UNION
I would not include joining to tblName until after the UNION query has been
created.

--
Duane Hookom
MS Access MVP


I also tried pulling directly from the tables, with the
same bad results =(

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours, "Administration" AS [Area]
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRPrj.PrjHours, "Projects"
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRSales.Hours, "Sales"
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID
UNION SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRService.Hours, "Service"
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID
-----Original Message-----
Hi, thank you for your response. I hope you follow this
thread..
I'm having a lot of problems with the union query.
Apparently it works fine, but I'm not getting all the
records, I realized this by comparing the number of
records and as well checking the sum of total hours.

the sql for the union query looks as follows:

SELECT * , "Administration" AS [Area]
FROM qryNames_Admin
UNION SELECT *, "Projects"
FROM qryNames_Prj
UNION SELECT *, "Sales"
FROM qryNames_Sales
UNION SELECT *, "Service"
FROM qryNames_Service;

The sql for qryNames_Admin looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours
FROM tblName INNER JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID;

The sql for qryNames_Prj looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRPrj.PrjHours
FROM tblName INNER JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID;

The sql for qryNames_Sales looks as follows:

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRSales.Hours
FROM tblName INNER JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID;

The sql for qryNames_Service looks as follows

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRService.Hours
FROM tblName INNER JOIN tblTRService ON tblName.NameID =
tblTRService.NameID;

I tried as well with other query, resulting in a similar
problem. I posted it on March 2, 2004 "Query Problems" at
5:26AM

Thank you!



-----Original Message-----
I would first create a union query of the trhe Hours
tables so you could
total hours by NameID and TRDate (there aren't dates in
the "hours" tables?)
You can then create a crosstab based on your union query.
Use a control on a
form for the ending date "Forms!frmRptCrit! txtEndDate".
Your crosstab Row Heading will be the Name field(s). The
value will be Sum
Of Hours. The Column Heading will be
ColHead: "Day" & DateDiff("D",[TRDate], Forms! frmRptCrit!
txtEndDate)
Make sure you enter query parameters
Forms!frmRptCrit!txtEndDate Date/Time
Set the column headings to a value that determines how
many days you want in
your results:
Column Headings: "Day0","Day1","Day2",..."Day6"..."Day31"
Your report can then be built from a standard/static
group of columns.

--
Duane Hookom
MS Access MVP


message
Hi, I want to build a report using the following tables
tblName: NameID, Names, TRDate
tblAdmin: NameID, Activity, HoursAdmin
tblPrjs: NameID, Activity, HoursPrjs
tblSales: NameID, Activity, HoursSales
tblService: NameID, Activity, HoursService

* there are more fields, but I simplified to explain it
easier.
* They are all related by NameID

I need the report to look like this:

Day1 Day2 ..... Day31
Name1 ## ##
Name2 ## ##
Name3 ##
Name4 ##

Where ## is the sum of
HoursAdmin+HoursPrjs+HoursSales+HoursService
There might be cases in which there's no working hours
for
the specified Day.

Any ideas?




.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top