Re: Updatable Query (trying to edit records)

Tech-Archive recommends: Fix windows errors by optimizing your registry

david,

Thanks for your input. As it stands, each Assignment can have ONLY ONE
staff. (Well, for now anyway.) The only reason the Staff table is involved
(in this query) is to get the BillRate of each staff to multiply by the
CABudgetHrs to arrive at the budgeted (billable) $ for that assignment. You
are correct in that each staff will have multiple assignments, but getting
the bill rate is the only applicable reason for having the Staff table in
this query. I believe the confusion may be the understanding of my
structure. Each Client (ClientID - Client table) has multiple Engagements
(EngagementID - Engagement table) recurring yearly (EngagementYr - also in
Engagement table). Each Engagement will have multiple Assignments
(AssignmentID - Assignment table) and further, each Assignment has multiple
Segments (SegmentID - Segment table). But for this query Segments do not
come into play.

ClientTbl EngagementTbl AssignmentTbl SegmentTbl
ClientID---ClientID-------------ClientID-------------ClientID
EngagementID-----EngagementID-----EngagementID
EngagementYr-----EngagementYr-----EngagementYr
AssignmentID------AssignmentID
SegmentID

My query returns (as I wish it to) ONE "RECORD" for each Engagement showing
the multiple Assignments with their billable amounts across the row and
totalling in the far right column. Even when I take the Staff table out of
the query completely and just try to present it in the way I want but without
computations, it still becomes un-updateble as soon as I add a second
Assignment to the "record".

Any suggestions would be appreciated.

Thank you very much for your response.
Robin

"david" wrote:


If each assignment can have several staff, and each staff
can have several assignments, then you can't have an updatable
query.

If it is possible to put staff and assignments into the table that
way it doesn't work. It doesn't matter that actually you only have
one staff and one assignment entered, it still doesn't work.

This is a restriction of the Jet database engine. Jet allows you to
update multiple tables only when it can identify by design which
records are updated, which is only when there is a tree structure,
no loops.

You can sometimes work around this by using DLOOKUP or
correlated subqueries, instead of a join, so that there is only one
table in your query, plus some read-only fields.

Originally, other database systems couldn't make updatable joined
queries at all, and used update triggers to let the DBA program
referential integrity constraints and separate updates for each table.

Those other database systems have long since been extended to
support updatable joined queries and declarative referential integrity
and integrated login and the new ANSI SQL and , but while they
picked up Jet features, Jet stood still.

Which is to say that, if you want to update multiple tables, and
Jet can't work out which records to update, you will have to
either use a SQL (Express) back end with stored procedures,
or a form.

(david)



"Robin" <Robin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DF482944-1466-4D77-B9DE-9FA89747FAE9@xxxxxxxxxxxxxxxx
THIS IS PROBABLY TOO MUCH TO ASK BUT: I'm trying to design a query to view
data and be able to edit that data. I know there are restrictions on
that,
but I need to design this so as to not violate those restrictions. I have
two tables ClientAssignment and Staff. The ClientAssignment table uses
four
fields as the Primary Key (ClientID, EngagementID, EngagementYr,
AssignmentID) and a field to link to the Staff table (CAStaffID). The
Staff
table uses StaffID as the Primary Key.

Basicallly I'm trying to make a data*** "read across" by Assignment ID
so
that the PREP BillRate x BudgetHrs + REV1 BillRate x BudgetHrs + REV2
BillRate x BudgetHrs = Total Budget across 1 line for each Client
Engagement.

I've written the query two different ways but neither work.

First attempt which shows me the data as I like but is not updatable:

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CAStaffID, ClientAssignment.CABudgetHrs, Staff.BillRate,
CCur([ClientAssignment.CABudgetHrs]*[Staff.BillRate]) AS Expr1,
ClientAssignment_1.AssignmentID, ClientAssignment_1.CAStaffID,
ClientAssignment_1.CABudgetHrs, Staff_1.BillRate,
CCur([ClientAssignment_1.CABudgetHrs]*[Staff_1.BillRate]) AS Expr2,
ClientAssignment_2.AssignmentID, ClientAssignment_2.CAStaffID,
ClientAssignment_2.CABudgetHrs, Staff_2.BillRate,
CCur([ClientAssignment_2.CABudgetHrs]*[Staff_2.BillRate]) AS Expr3,
CCur([Expr1]+[Expr2]+[Expr3]) AS Expr4
FROM ((Staff INNER JOIN ClientAssignment ON Staff.StaffID =
ClientAssignment.CAStaffID) INNER JOIN (ClientAssignment AS
ClientAssignment_1 INNER JOIN Staff AS Staff_1 ON
ClientAssignment_1.CAStaffID = Staff_1.StaffID) ON
(ClientAssignment.EngagementYr = ClientAssignment_1.EngagementYr) AND
(ClientAssignment.EngagementID = ClientAssignment_1.EngagementID) AND
(ClientAssignment.ClientID = ClientAssignment_1.ClientID)) INNER JOIN
(ClientAssignment AS ClientAssignment_2 INNER JOIN Staff AS Staff_2 ON
ClientAssignment_2.CAStaffID = Staff_2.StaffID) ON
(ClientAssignment.EngagementYr = ClientAssignment_2.EngagementYr) AND
(ClientAssignment.EngagementID = ClientAssignment_2.EngagementID) AND
(ClientAssignment.ClientID = ClientAssignment_2.ClientID)
WHERE (((ClientAssignment.AssignmentID)="PREP") AND
((ClientAssignment_1.AssignmentID)="REV1") AND
((ClientAssignment_2.AssignmentID)="REV2"));

The second attempt, I tried doing three separate queries and then using
them
in the "top query". EACH INDIVIDUAL QUERY ALLOWS UPDATES but when I use
more
than one in the "top" query, it becomes un-updatable.

I have three queries (qBudgetWSa, b, c) with this design:

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CAStaffID, ClientAssignment.CABudgetHrs, Staff.BillRate,
CCur([CABudgetHrs]*[BillRate]) AS Expr1
FROM Staff RIGHT JOIN ClientAssignment ON Staff.StaffID =
ClientAssignment.CAStaffID
WHERE (((ClientAssignment.AssignmentID)="PREP"));

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CAStaffID, ClientAssignment.CABudgetHrs, Staff.BillRate,
CCur([CABudgetHrs]*[BillRate]) AS Expr1
FROM Staff RIGHT JOIN ClientAssignment ON Staff.StaffID =
ClientAssignment.CAStaffID
WHERE (((ClientAssignment.AssignmentID)="REV1"));

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CAStaffID, ClientAssignment.CABudgetHrs, Staff.BillRate,
CCur([CABudgetHrs]*[BillRate]) AS Expr1
FROM Staff RIGHT JOIN ClientAssignment ON Staff.StaffID =
ClientAssignment.CAStaffID
WHERE (((ClientAssignment.AssignmentID)="REV2"));

But when I JOIN any two of them (and I've tried EVERY combination of JOIN)
it no longer is updatable.

SELECT qBudgetWSa.ClientID, qBudgetWSa.EngagementID,
qBudgetWSa.EngagementYr, qBudgetWSa.AssignmentID, qBudgetWSa.CAStaffID,
qBudgetWSa.CABudgetHrs, qBudgetWSa.BillRate, qBudgetWSa.Expr1,
qBudgetWSb.AssignmentID, qBudgetWSb.CAStaffID, qBudgetWSb.CABudgetHrs,
qBudgetWSb.BillRate, qBudgetWSb.Expr1
FROM (qBudgetWSa INNER JOIN qBudgetWSb ON (qBudgetWSa.EngagementYr =
qBudgetWSb.EngagementYr) AND (qBudgetWSa.EngagementID =
qBudgetWSb.EngagementID) AND (qBudgetWSa.ClientID = qBudgetWSb.ClientID))
INNER JOIN qBudgetWSc ON (qBudgetWSa.EngagementYr =
qBudgetWSc.EngagementYr)
AND (qBudgetWSa.EngagementID = qBudgetWSc.EngagementID) AND
(qBudgetWSa.ClientID = qBudgetWSc.ClientID);

Whew! Can this even be accomplished?
Thanks
Robin





.

Quantcast