Selasa, 06 September 2011

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

You can filter records in a subreport using Link Master/Child properties (much like forms/subforms).

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: drtolson@verizon.net
Date: Tue, 6 Sep 2011 17:21:26 -0400
Subject: RE: [MS_AccessPros] Attempting to print different groups in a report


Hi Duane, I have been working on the sub report. Give me some time to work
on it before responding, and I will give you an update on it. I know right
off the bat, I'll need to be able to limit the subreport to repairs/parts to
the invoice I am printing. Right now, the relationship is working on the
first subform, but it is printing all of the repair items. Challenges,
challenges are what makes one better.

Thanks again, and I'll keep you posted.

r/David

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of David Tolson
Sent: Tuesday, September 06, 2011 4:29 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:MS_Access_Professionals%40yahoogroups.com>
From: drtolson@verizon.net <mailto:drtolson%40verizon.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

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


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

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

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Tidak ada komentar:

Posting Komentar