Top values report

[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

 

[Duane Hookom]

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

 

[george 16-17]

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

 

[george 16-17]

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

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

 

[Duane Hookom]

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

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

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

 

[george 16-17]

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

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

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

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