Complex design problem w/ imports, lookup, and billing

[MarcTA]

First of all, Thank you to all who are willing to help and have helped so far!

I am essentialy making a billing database for a department that charges
other departments for sevices done to equipment.

The billing works perfectly for the setup of new equipment because it gets
charged to only one department. However, charging for repairing old
equipment is difficult since it is owned/used by many different department
which must all share in the cost. Our equipment inventory is in the
thousands and kept on a seperate database (off site) that we are only able to
generate exel files from. The excel print out looks something like this:

EquipID: departmentCode:
100001 AA
100001 ZH
100001 GB
100005 SD
100005 RM
100007 SD
100007 AA
100007 LM

This equipment owner/used file would be updated periodicaly (monthly or so).

After creating a link to the excel file I was able to generate a query that
counts the # of users for each EquipmentID. How would I go about generating
a billing report that includes both new equipment service that bills one a
single department (which I already have), and current equipment service where
that cost is divided amoungst each user?

The billing report is a single report that is grouped by department.

I cannot even think of a way to design it, let alone write any code (which I
do very poorly, if at all.) So, any design help would be greatly
appreciated, and code (if needed) would be even more.

If you need any additonal details feel free to ask here or contact me via
email (marc TA Roe at g mail dot com).

 

[John Nurick]

Hi Marc,

EquipID: departmentCode:
100001 AA
100001 ZH
100001 GB
100005 SD
100005 RM

Does this mean that departments AA, ZH, GB are each responsible for one
third of the repair costs of item 100001, and SD and RM are each
responsible for 50% of item 100005? If so, things are reasonably simple.

You'll need a structure like this:

tblWorkDone (things you need to bill for)
JobID*
EquipmentID (foreign key into inventory)
WorkDone (what you did)
DateDone
Charge (the amount you're charging)

tblOwnership (imported or linked from the Excel sheet)
EquipmentID*
DepartmentCode*

Presumably you already have these and other tables even if the names are
different, e.g. a tblDepartments that maps DepartmentCode to
DepartmentName, but I won't speculate.

You can then generate your billing report from a query like this:

SELECT
tblOwnership.Department,
tblWorkDone.JobID,
tblWorkDone.EquipmentID,
tblWorkDone.WorkDone,
tblWorkDone.DateDone,
tblWorkDone.Charge AS TotalCharge,
(tblWorkDone.Charge /
(SELECT COUNT(Department) FROM tblOwnership AS B
WHERE B.EquipmentID = tblWorkDone.EquipmentID
)
) AS DepartmentCharge
FROM tblWorkDone INNER JOIN tblOwnership
ON tblWorkDone.EquipmentID = tblOwnership.EquipmentID
WHERE DateDone BETWEEN #4/01/2005# AND #4/30/2005#;

The DepartmentCharge is calculated by dividing the total charge by the
number of departments responsible (retrieved by the SELECT COUNT
subquery).

If various departments may take different shares of responsibility for
an item, you'll need to include these shares in a table, e.g.

EquipmentID DepartmentCode ShareOfCost
100001 AA 0.50
100001 ZH 0.25
100001 GB 0.25
100005 SD 0.10
100005 RM 0.90

and the subquery will have to retrieve each department's share so it can
be multiplied by the total charge to get the depatrment charge.

 

[MarcTA]

John,

Thanks for the help. I am not able to get the subquery "Count" to work,
syntax error. I am trying to pull data from two tables and a query and
things are not seeming to line up. I tried many different combinations of
code but to no avail.
Here is my SQL from a query that handles a new item perfectly, with the user
picking the Division to be charge from a table as a combbox.

SELECT DISTINCTROW Division.Divisions, [3MMasterTable].[RM #],
[3MMasterTable].Requester, [3MMasterTable].Function, [3MMasterTable].[IF New:
Type?], Sum([Employees Query].TotalTime) AS [Sum Of TotalTime],
Sum([Employees Query].EmployeeCharge) AS [Sum Of EmployeeCharge],
[3MMasterTable].DateFinalized
FROM Division INNER JOIN ([Employees Query] INNER JOIN 3MMasterTable ON
[Employees Query].PaceMasterID = [3MMasterTable].PaceMasterID) ON
Division.[DivisionID#] = [3MMasterTable].Division
GROUP BY Division.Divisions, [3MMasterTable].[RM #],
[3MMasterTable].Requester, [3MMasterTable].Function, [3MMasterTable].[IF New:
Type?], [3MMasterTable].DateFinalized
HAVING ((([3MMasterTable].DateFinalized) Between NZ([Enter Start Date
"mm/dd/yyyy"],"01/01/1900") And NZ([Enter End Date
"mm/dd/yyyy"],#1/1/3000#)))
ORDER BY [3MMasterTable].[RM #];

---------------------------------------------------------------------------
The "RM #" = "EquipmentID"
"3MMasterTable"="tblWorkDone"
"PaceMasterID"="JobID"
"Function"&"IF New: Type?" in [3MMasterTable] ="Workdone"
"DateFinalized"="DateDone"
"Employee Query.EmployeeCharge"="Charge"
"Division.Divisions"="tblOwnership.DepartmentCode*" - This is what I want
to change in order to lookup the owner. I have a linked table "tblOwnership"
with two colums "RM #" & "Division"
------------------------------------------------------------------------
For reference here is the Employee Query:
SELECT [3MMasterTable].[PaceMasterID], [3MMasterTable].[RM #],
[Employees].[Employees],
([EndTime]-[StartTime])+([EndTime2]-[StartTime2])+([EndTime3]-[StartTime3])+([EndTime4]-[StartTime4])
AS TotalTime, ([TotalTime]*24) AS TimeInHours, [Employees].[BillingRate],
([TimeInHours]*[BillingRate]) AS EmployeeCharge
FROM 3MMasterTable INNER JOIN (Employees INNER JOIN [Time Billed] ON
[Employees].[Employees]=[Time Billed].[Employees]) ON
[3MMasterTable].[PaceMasterID]=[Time Billed].[PaceMasterID]
GROUP BY [3MMasterTable].[PaceMasterID], [3MMasterTable].[RM #],
[Employees].[Employees], [Employees].[BillingRate], [Time
Billed].[StartTime], [Time Billed].[EndTime], [Time Billed].[StartTime2],
[Time Billed].[EndTime2], [Time Billed].[StartTime3], [Time
Billed].[EndTime3], [Time Billed].[StartTime4], [Time Billed].[EndTime4]
ORDER BY [3MMasterTable].[RM #];

If you are able and willing to still help, would it work better if I sent
you a copy of the DB for you to look at, and I could explain what I am trying
to do better?

Thanks,

MarcTA
(marc ta "at" g mail "dot" com)