Selasa, 06 September 2011

RE: [MS_AccessPros] Attempting to print different groups in a report

 

Thanks Duane for getting back to me. Parts are in a separate table
(tblParts), but the repair related items are not in a separate table.
However, because there could be more than one part or repair item for a
service, I have placed the parts and repairs that are needed for a service
in different tables (tblParts-RepairOrder, tblRepair-RepairOrder), and
tblParts-RepairOrder is related to tblParts by PartTypeID and tblRepairOrder
(The table used to hold information relative to a repair order). However,
tblRepair-RepairOrder is only related to tblRepairOrder.

tblRepairOrder is related to customer (tblMasterCustomer) by CustomerID. I
have a lot going on here for me to keep track off, but I am also including
the SQL:

SELECT qryMasterCustomer.CustomerID, Trim([firstName]) & " " &
Trim([LastName]) AS FullName, qryMasterCustomer.FirstName,
qryMasterCustomer.LastName, Trim([Street Address-Line 1]) & " " &
Trim([Street Address-Line 2]) AS Address, Trim([City] & ", " & [State] & " "
& [ZipCode]) AS [City-State-Zip], qryMasterCustomer.[Street Address-Line 1],
qryMasterCustomer.[Street Address-Line 2], qryMasterCustomer.City,
qryMasterCustomer.State, qryMasterCustomer.ZipCode,
qryMasterCustomer.HomePhone, qryMasterCustomer.BusinessPhone,
qryMasterCustomer.MobilePhone, qryMasterCustomer.EmailAddress,
Trim([ModYear]) & " / " & Trim([VehicleMake]) & " / " & Trim([VehicleModel])
AS YearMakeModel, qryCustomerVehicles.VehicleMakeID,
qryCustomerVehicles.VehicleMake, qryCustomerVehicles.VehicleModelID,
qryCustomerVehicles.VehicleModel, qryCustomerVehicles.ModYear,
qryCustomerVehicles.Color, qryCustomerVehicles.VIN,
qryCustomerVehicles.[Tag-State], qryRepairOrder.CustomerVehicleID,
qryRepairOrder.RepairTypeID, qryRepairOrder.PartTypeID AS
qryRepairOrder_PartTypeID, [qryParts-RepairOrder].Part,
qryRepairOrder.Estimate AS qryRepairOrder_Estimate,
qryRepairOrder.RepairOrder AS qryRepairOrder_RepairOrder,
qryRepairOrder.Recommendation, qryRepairOrder.RepairDateIn,
qryRepairOrder.RepairDateOut, qryRepairOrder.RepairMilageIn,
qryRepairOrder.RepairMileageOut, qryRepairOrder.EmployeeID,
qryRepairOrder.MiscellaneousCost, [qryParts-RepairOrder].RepairOrderID AS
[qryParts-RepairOrder_RepairOrderID], [qryParts-RepairOrder].PartTypeID AS
[qryParts-RepairOrder_PartTypeID], [qryParts-RepairOrder].PartCost,
[qryParts-RepairOrder].PartQty, [qryParts-RepairOrder].PartTotal,
[qryRepair-RepairOrder].RepairOrderId AS
[qryRepair-RepairOrder_RepairOrderId], [qryRepair-RepairOrder].RepairItem,
[qryRepair-RepairOrder].RepairItemCost, [qryRepair-RepairOrder].RepairCost,
[qryRepair-RepairOrder].Estimate AS [qryRepair-RepairOrder_Estimate],
[qryRepair-RepairOrder].RepairOrder AS [qryRepair-RepairOrder_RepairOrder],
[qryRepair-RepairOrder].Recommended
FROM (qryMasterCustomer INNER JOIN ((qryRepairOrder INNER JOIN
[qryRepair-RepairOrder] ON qryRepairOrder.RepairOrderID =
[qryRepair-RepairOrder].RepairOrderId) INNER JOIN [qryParts-RepairOrder] ON
qryRepairOrder.RepairOrderID = [qryParts-RepairOrder].RepairOrderID) ON
qryMasterCustomer.CustomerID = qryRepairOrder.CustomerID) INNER JOIN
qryCustomerVehicles ON qryMasterCustomer.CustomerID =
qryCustomerVehicles.CustomerID;

Thanks for the assistance.

r/David

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Tuesday, September 06, 2011 11:27 AM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Attempting to print different groups in a
report

This sounds like you need a report with at least one subreport. It is
difficult to determine since we have no idea how your tables are set up. Are
there separate tables for repairs and parts? How are these related to
customers and services?

Duane Hookom
MS Access MVP


To: MS_Access_Professionals@yahoogroups.com
From: drtolson@verizon.net
Date: Tue, 6 Sep 2011 15:14:05 +0000
Subject: [MS_AccessPros] Attempting to print different groups in a report

Great Day, I need to print a receipt (report) for a customer that has
his/her vehicle repaired. The repair could vary where there was one repair
and one part, or numerous repairs and numerous parts. I figured I need to
created the report with two different groups for

1. The repair (description and cost); and
2. Parts (name, quantity, cost-per-item, extended cost

I have the groups summing in the footer, but the report is only displaying
one repair (+ cost) and one part (+ cost +quantity etc)

Any assistance in showing me how to get all of the parts and repair items to
print in their own detail section will be appreciate.

Thanks in advance,

David



[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.


A bad score is 579. A good idea is checking yours at freecreditscore.com.
.

__,_._,___

Tidak ada komentar:

Posting Komentar