N
naigy via AccessMonster.com
Hi All,
I am trying to create a Pivot Table or Similar.
The Query has the following key fields (there are other fields but I don't
think these are really relevant here)
ServiceDate (As the report needs to be run based on quarters or months etc so
that trends can be monitored). This Field is from ServiceReports table
DeviceRepaired (As we want to look at each product costs separately). This
Field is from ServiceReports table
PartNumber (Parts required for repair). This Field is from subServiceReports
table
Quantity (Quantity of the part number used). This Field is from
subServiceReports table
PurchaseCost (This is the amount we paid for the part used). This Field is
from subServiceReports table
I would prefer a PivotTable type report just because of the Interactivity of
PivotTable / Charts of being able to Interact with it, Remove Products,
change timeline breakdowns etc.
In the Pivot Table I currently have DeviceRepaired (left column),
ServiceDatebyMonth (Top Row) and then the last 3 fields in the data area.
The problem I have is that I can not find a way to get it to subtotal
Quantity and PurchaseCost per part number in each time period but rather it
lists each part used on a separate line regardless of whether it has already
been used in that time period. How can I get it to subtotal the Quantity and
PurchaseCost against each part number. Keep in mind that the report is to
give totals in seperate months, quarters etc. but the time segments is not
always going to be the same so I don't think there is a way I can do this at
the query level. Please correct me if I am wrong
After I can accomplish the above I then also need to be able to make the
report show only the top 5 parts per product (based on cost) per time span.
Assistance with either of the above would be appreciated.
I am trying to create a Pivot Table or Similar.
The Query has the following key fields (there are other fields but I don't
think these are really relevant here)
ServiceDate (As the report needs to be run based on quarters or months etc so
that trends can be monitored). This Field is from ServiceReports table
DeviceRepaired (As we want to look at each product costs separately). This
Field is from ServiceReports table
PartNumber (Parts required for repair). This Field is from subServiceReports
table
Quantity (Quantity of the part number used). This Field is from
subServiceReports table
PurchaseCost (This is the amount we paid for the part used). This Field is
from subServiceReports table
I would prefer a PivotTable type report just because of the Interactivity of
PivotTable / Charts of being able to Interact with it, Remove Products,
change timeline breakdowns etc.
In the Pivot Table I currently have DeviceRepaired (left column),
ServiceDatebyMonth (Top Row) and then the last 3 fields in the data area.
The problem I have is that I can not find a way to get it to subtotal
Quantity and PurchaseCost per part number in each time period but rather it
lists each part used on a separate line regardless of whether it has already
been used in that time period. How can I get it to subtotal the Quantity and
PurchaseCost against each part number. Keep in mind that the report is to
give totals in seperate months, quarters etc. but the time segments is not
always going to be the same so I don't think there is a way I can do this at
the query level. Please correct me if I am wrong
After I can accomplish the above I then also need to be able to make the
report show only the top 5 parts per product (based on cost) per time span.
Assistance with either of the above would be appreciated.