Really need help with a query

[Cam]

Hello,

I have a query that return the following result below and would like to add
a row called "Spare" on program field. The Spare will be 5% of the total sum
of Hrs/mth. I don't know how to go about achieving this. The number of
program vary with diferent machines. The program are 373, 474, 676, 777 and
878, so would like to add Spare. Thanks

Sample:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 2600 474 05.4
2MK 2600 676 01.2
2MK 2600 878 15.4

Result Wanted:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 8610 Spare 1.13 ((12.5+10.1)*.05)
2MK 2600 474 5.4
2MK 2600 676 1.2
2MK 2600 878 15.4
2MK 2600 Spare 1.1 ((5.4+1.2+15.4)*.05)

 

[KARL DEWEY]

The easiest way is to do it in a report grouping footer. Create your report
and group on WC & Mach (one group with both fields) and then in group footer
put =Sum([Hrs/mth])*0.5 as source for a text box.

 

[Cam]

Karl,

Thank for the help, but I am not creating this in report. I am doing this in
query, then all the data and chart from this is done in Excel. Any tip in
query?

The easiest way is to do it in a report grouping footer. Create your report
and group on WC & Mach (one group with both fields) and then in group footer
put =Sum([Hrs/mth])*0.5 as source for a text box.

Hello,

I have a query that return the following result below and would like to add
a row called "Spare" on program field. The Spare will be 5% of the total sum
of Hrs/mth. I don't know how to go about achieving this. The number of
program vary with diferent machines. The program are 373, 474, 676, 777 and
878, so would like to add Spare. Thanks

Sample:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 2600 474 05.4
2MK 2600 676 01.2
2MK 2600 878 15.4

Result Wanted:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 8610 Spare 1.13 ((12.5+10.1)*.05)
2MK 2600 474 5.4
2MK 2600 676 1.2
2MK 2600 878 15.4
2MK 2600 Spare 1.1 ((5.4+1.2+15.4)*.05)

 

[KARL DEWEY]

The only way I know how in a query does not add a new row in the query output
but another fields where the information is duplicated for every row of WC
and Mach.
You can either use DSum, a subquery, or a totals query joined on WC & Mach.

I had another thought but will let you work on it if you wish. You might be
able to create a Ranking in a Group query and then add a zero rank record or
maximum rank plus one record for the Spare data.

Karl,

Thank for the help, but I am not creating this in report. I am doing this in
query, then all the data and chart from this is done in Excel. Any tip in
query?

The easiest way is to do it in a report grouping footer. Create your report
and group on WC & Mach (one group with both fields) and then in group footer
put =Sum([Hrs/mth])*0.5 as source for a text box.

Hello,

I have a query that return the following result below and would like to add
a row called "Spare" on program field. The Spare will be 5% of the total sum
of Hrs/mth. I don't know how to go about achieving this. The number of
program vary with diferent machines. The program are 373, 474, 676, 777 and
878, so would like to add Spare. Thanks

Sample:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 2600 474 05.4
2MK 2600 676 01.2
2MK 2600 878 15.4

Result Wanted:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 8610 Spare 1.13 ((12.5+10.1)*.05)
2MK 2600 474 5.4
2MK 2600 676 1.2
2MK 2600 878 15.4
2MK 2600 Spare 1.1 ((5.4+1.2+15.4)*.05)

 

[John Spencer MVP]

Use a UNION Query like the one below.

SELECT WC, Mach, Program, [Hrs/Mth]
FROM [Your Table]
UNION ALL
SELECT WC, Mach, "SPARE", Sum([Hrs/Mth]) * .05
FROM [Your Table]
GROUP BY WC, Mach, "SPARE"
ORDER By WC, Mach, Program

If the order is wrong, you can add a calculated field to help you get the
order correct


SELECT WC, Mach, Program, [Hrs/Mth], "A" as SpecialOrder
FROM [Your Table]
UNION ALL
SELECT WC, Mach, "SPARE", Sum([Hrs/Mth]) * .05, "B"
FROM [Your Table]
GROUP BY WC, Mach, "SPARE", "B"
ORDER By WC, Mach, SpecialOrder, Program


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Karl,

Thank for the help, but I am not creating this in report. I am doing this in
query, then all the data and chart from this is done in Excel. Any tip in
query?

The easiest way is to do it in a report grouping footer. Create your report
and group on WC & Mach (one group with both fields) and then in group footer
put =Sum([Hrs/mth])*0.5 as source for a text box.

Hello,

I have a query that return the following result below and would like to add
a row called "Spare" on program field. The Spare will be 5% of the total sum
of Hrs/mth. I don't know how to go about achieving this. The number of
program vary with diferent machines. The program are 373, 474, 676, 777 and
878, so would like to add Spare. Thanks

Sample:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 2600 474 05.4
2MK 2600 676 01.2
2MK 2600 878 15.4

Result Wanted:
WC Mach Program Hrs/mth
2MK 8610 373 12.5
2MK 8610 777 10.1
2MK 8610 Spare 1.13 ((12.5+10.1)*.05)
2MK 2600 474 5.4
2MK 2600 676 1.2
2MK 2600 878 15.4
2MK 2600 Spare 1.1 ((5.4+1.2+15.4)*.05)