Top values report

  • Thread starter Thread starter george 16-17
  • Start date Start date
G

george 16-17

Greetings,

I am relatively new to Access and I am attempting to format a top values
report to analyze the total injury count and their associated costs in an
injury db. The report is based on a totals query where the cost is summed and
the injuries are counted. They are then grouped by departments and cost
centers.

The problem that I am incurring is that I need to group by cost centers and
not by departments, as some cost centers have different names.

Example:
In design view, the text box control source is: "[Cost Ctr]&[Dept Name]"
In print view it reads:
Cost Ctr: Dept: Injury Count:
02603800 Reed 1 3
02603800 Clare 1 4
This format is not helpful as the same cost center is totaled on two
different lines.

I would like it to read:
Cost Ctr: Dept: Injury Count:
02603800 Reed 2 & Clare 1 7

If I do not include departments in the totals query and only group it by the
cost centers, then it is totaled fine, but the department names are not
included. I would like to include the department names, but on one line.


Any assistance would be greatly appreciated,
george
 
Hi Duane,

Thanks for getting me going in the right direction. That is exactly what I
needed.

Much appreciated,
george

Duane Hookom said:
There is a generic Concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Greetings,

I am relatively new to Access and I am attempting to format a top values
report to analyze the total injury count and their associated costs in an
injury db. The report is based on a totals query where the cost is summed and
the injuries are counted. They are then grouped by departments and cost
centers.

The problem that I am incurring is that I need to group by cost centers and
not by departments, as some cost centers have different names.

Example:
In design view, the text box control source is: "[Cost Ctr]&[Dept Name]"
In print view it reads:
Cost Ctr: Dept: Injury Count:
02603800 Reed 1 3
02603800 Clare 1 4
This format is not helpful as the same cost center is totaled on two
different lines.

I would like it to read:
Cost Ctr: Dept: Injury Count:
02603800 Reed 2 & Clare 1 7

If I do not include departments in the totals query and only group it by the
cost centers, then it is totaled fine, but the department names are not
included. I would like to include the department names, but on one line.


Any assistance would be greatly appreciated,
george
Click to expand...
Click to expand...
 
Hi Duane,

If you get a moment, I am having trouble with the Concatenate Function.

I have copied and pasted the basConcatenate module into my db, but I keep
getting an error message - "Data type mismatch in criteria expression".

This is my statement:

DeptNames: Concatenate("SELECT [Dept Name] FROM qrySearch WHERE [Cost Ctr]="
& [Cost Ctr])

Any assistance would be greatly appreciated,
george

george 16-17 said:
Hi Duane,

Thanks for getting me going in the right direction. That is exactly what I
needed.

Much appreciated,
george

Duane Hookom said:
There is a generic Concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Greetings,

I am relatively new to Access and I am attempting to format a top values
report to analyze the total injury count and their associated costs in an
injury db. The report is based on a totals query where the cost is summed and
the injuries are counted. They are then grouped by departments and cost
centers.

The problem that I am incurring is that I need to group by cost centers and
not by departments, as some cost centers have different names.

Example:
In design view, the text box control source is: "[Cost Ctr]&[Dept Name]"
In print view it reads:
Cost Ctr: Dept: Injury Count:
02603800 Reed 1 3
02603800 Clare 1 4
This format is not helpful as the same cost center is totaled on two
different lines.

I would like it to read:
Cost Ctr: Dept: Injury Count:
02603800 Reed 2 & Clare 1 7

If I do not include departments in the totals query and only group it by the
cost centers, then it is totaled fine, but the department names are not
included. I would like to include the department names, but on one line.


Any assistance would be greatly appreciated,
george
Click to expand...
Click to expand...
Click to expand...
 
I expect Cost Ctr is a string. If so, you must treat it like a string:
DeptNames: Concatenate("SELECT [Dept Name] FROM qrySearch WHERE [Cost
Ctr]=""" & [Cost Ctr] & """")

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,

If you get a moment, I am having trouble with the Concatenate Function.

I have copied and pasted the basConcatenate module into my db, but I keep
getting an error message - "Data type mismatch in criteria expression".

This is my statement:

DeptNames: Concatenate("SELECT [Dept Name] FROM qrySearch WHERE [Cost Ctr]="
& [Cost Ctr])

Any assistance would be greatly appreciated,
george

george 16-17 said:
Hi Duane,

Thanks for getting me going in the right direction. That is exactly what I
needed.

Much appreciated,
george

Duane Hookom said:
There is a generic Concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

Greetings,

I am relatively new to Access and I am attempting to format a top values
report to analyze the total injury count and their associated costs in an
injury db. The report is based on a totals query where the cost is summed and
the injuries are counted. They are then grouped by departments and cost
centers.

The problem that I am incurring is that I need to group by cost centers and
not by departments, as some cost centers have different names.

Example:
In design view, the text box control source is: "[Cost Ctr]&[Dept Name]"
In print view it reads:
Cost Ctr: Dept: Injury Count:
02603800 Reed 1 3
02603800 Clare 1 4
This format is not helpful as the same cost center is totaled on two
different lines.

I would like it to read:
Cost Ctr: Dept: Injury Count:
02603800 Reed 2 & Clare 1 7

If I do not include departments in the totals query and only group it by the
cost centers, then it is totaled fine, but the department names are not
included. I would like to include the department names, but on one line.


Any assistance would be greatly appreciated,
george
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

That did it. It works perfectly. I just had to add in "DISTINCT" to the
statement, as it was repeating the Dept Name for each field.

Much appreciate this was extremely helpful,
george

Duane Hookom said:
I expect Cost Ctr is a string. If so, you must treat it like a string:
DeptNames: Concatenate("SELECT [Dept Name] FROM qrySearch WHERE [Cost
Ctr]=""" & [Cost Ctr] & """")

--
Duane Hookom
Microsoft Access MVP


george 16-17 said:
Hi Duane,

If you get a moment, I am having trouble with the Concatenate Function.

I have copied and pasted the basConcatenate module into my db, but I keep
getting an error message - "Data type mismatch in criteria expression".

This is my statement:

DeptNames: Concatenate("SELECT [Dept Name] FROM qrySearch WHERE [Cost Ctr]="
& [Cost Ctr])

Any assistance would be greatly appreciated,
george

george 16-17 said:
Hi Duane,

Thanks for getting me going in the right direction. That is exactly what I
needed.

Much appreciated,
george

:

There is a generic Concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

Greetings,

I am relatively new to Access and I am attempting to format a top values
report to analyze the total injury count and their associated costs in an
injury db. The report is based on a totals query where the cost is summed and
the injuries are counted. They are then grouped by departments and cost
centers.

The problem that I am incurring is that I need to group by cost centers and
not by departments, as some cost centers have different names.

Example:
In design view, the text box control source is: "[Cost Ctr]&[Dept Name]"
In print view it reads:
Cost Ctr: Dept: Injury Count:
02603800 Reed 1 3
02603800 Clare 1 4
This format is not helpful as the same cost center is totaled on two
different lines.

I would like it to read:
Cost Ctr: Dept: Injury Count:
02603800 Reed 2 & Clare 1 7

If I do not include departments in the totals query and only group it by the
cost centers, then it is totaled fine, but the department names are not
included. I would like to include the department names, but on one line.


Any assistance would be greatly appreciated,
george
Click to expand...
Click to expand...
Click to expand...
 
Back
Top