Updatable Query (trying to edit records)

R

Robin

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 datasheet "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
 
L

Lord Kelvan

basiclaly put queries are not updateable there are some forms that are
but you shouldn't be using this method to update data. if you want
people to update data along this recordset use a form and a sub form
and connect the two forms using the PK-FK releationship

Regards
Kelvan
 
A

Allen Browne

Your query uses 3 instances of ClientAssessment. Without going through the
detail, I think you are doing this to get values from different records and
operate on them to get a result. Naturally enough, the query will be
read-only if it returns such a result. The simplest workaround might be to
write the results into a temporary table designed so it has fields for the
result, and you can edit that table (or JOIN it to your original table,
apply criteria, and edit the original.)

If you are not trying to read results from multiple records, you might solve
the problem by using one instance of ClientAssessment along with the Staff
table. Assuming the tables are joined on the primary key from the table on
the ONE side of the relationships, the results should be editable.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Robin said:
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 datasheet "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
Click to expand...
 
D

david

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 said:
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 datasheet "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
Click to expand...
 
R

Robin

Thank you Allen.

When I wrote the query I was pretty sure it would not work but I was
pleasantly suprised it actually gave me the data I was looking for! I believe
your temporary table might be the answer if I can figure out the second part
of the operation. I could use the Make Table Query based on the results of
my Select Query that works, and allow edits on a form based on the table.
Then I have to update the permanent tables. Using an Update Query? How do I
tell it to update certain records in the ClientAssignment table based on the
first "group" of fields and update other records based on another "group" of
fields...and so on... Would I write multiple update queries? I see a
problem in that I used PREP, REV1 and REV2 as AssignmentID examples but there
will be more assignments with other names. I can "generally" visualize how
the VBA would have to work to accomplish this, but I'm pretty sure I'm not
good enough to actually make it work...this year. :) Am I missing a simpler
way to do the update?

Actual Data:
Record1-ClientID, EngagementID, EngagementYr, AssignmentID (PREP),
CAStaffID, CABudgetHrs, Staff.BillRate + computed fields
Record2-ClientID, EngagementID, EngagementYr, AssignmentID (REV1),
CAStaffID, CABudgetHrs, Staff.BillRate + computed fields
and so on...

What I'm after:
Record1-ClientID(Co1), EngagementID, EngagementYr, AssignmentID (PREP)
CAStaffID, CABudgetHrs, Staff.BillRate, AssignmentID (REV1) CAStaffID,
CABudgetHrs, Staff.BillRate, AssignmentID (REV2) CAStaffID, CABudgetHrs,
Staff.BillRate + computed fields
Record2-ClientID(Co2), EngagementID, EngagementYr, AssignmentID (PREP)
CAStaffID, CABudgetHrs, Staff.BillRate, AssignmentID (REV1) CAStaffID,
CABudgetHrs, Staff.BillRate, AssignmentID (REV2) CAStaffID, CABudgetHrs,
Staff.BillRate + computed fields.

Thanks for your help and suggestion!
Robin

Allen Browne said:
Your query uses 3 instances of ClientAssessment. Without going through the
detail, I think you are doing this to get values from different records and
operate on them to get a result. Naturally enough, the query will be
read-only if it returns such a result. The simplest workaround might be to
write the results into a temporary table designed so it has fields for the
result, and you can edit that table (or JOIN it to your original table,
apply criteria, and edit the original.)

If you are not trying to read results from multiple records, you might solve
the problem by using one instance of ClientAssessment along with the Staff
table. Assuming the tables are joined on the primary key from the table on
the ONE side of the relationships, the results should be editable.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Robin said:
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 datasheet "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
Click to expand...
Click to expand...
 
A

Allen Browne

The temp table would only need the primary key fields for the matching
records. You can then INNER JOIN that to your original table and update the
fields of the original table directly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Robin said:
Thank you Allen.

When I wrote the query I was pretty sure it would not work but I was
pleasantly suprised it actually gave me the data I was looking for! I
believe
your temporary table might be the answer if I can figure out the second
part
of the operation. I could use the Make Table Query based on the results
of
my Select Query that works, and allow edits on a form based on the table.
Then I have to update the permanent tables. Using an Update Query? How
do I
tell it to update certain records in the ClientAssignment table based on
the
first "group" of fields and update other records based on another "group"
of
fields...and so on... Would I write multiple update queries? I see a
problem in that I used PREP, REV1 and REV2 as AssignmentID examples but
there
will be more assignments with other names. I can "generally" visualize
how
the VBA would have to work to accomplish this, but I'm pretty sure I'm not
good enough to actually make it work...this year. :) Am I missing a
simpler
way to do the update?

Actual Data:
Record1-ClientID, EngagementID, EngagementYr, AssignmentID (PREP),
CAStaffID, CABudgetHrs, Staff.BillRate + computed fields
Record2-ClientID, EngagementID, EngagementYr, AssignmentID (REV1),
CAStaffID, CABudgetHrs, Staff.BillRate + computed fields
and so on...

What I'm after:
Record1-ClientID(Co1), EngagementID, EngagementYr, AssignmentID (PREP)
CAStaffID, CABudgetHrs, Staff.BillRate, AssignmentID (REV1) CAStaffID,
CABudgetHrs, Staff.BillRate, AssignmentID (REV2) CAStaffID, CABudgetHrs,
Staff.BillRate + computed fields
Record2-ClientID(Co2), EngagementID, EngagementYr, AssignmentID (PREP)
CAStaffID, CABudgetHrs, Staff.BillRate, AssignmentID (REV1) CAStaffID,
CABudgetHrs, Staff.BillRate, AssignmentID (REV2) CAStaffID, CABudgetHrs,
Staff.BillRate + computed fields.

Thanks for your help and suggestion!
Robin

Allen Browne said:
Your query uses 3 instances of ClientAssessment. Without going through
the
detail, I think you are doing this to get values from different records
and
operate on them to get a result. Naturally enough, the query will be
read-only if it returns such a result. The simplest workaround might be
to
write the results into a temporary table designed so it has fields for
the
result, and you can edit that table (or JOIN it to your original table,
apply criteria, and edit the original.)

If you are not trying to read results from multiple records, you might
solve
the problem by using one instance of ClientAssessment along with the
Staff
table. Assuming the tables are joined on the primary key from the table
on
the ONE side of the relationships, the results should be editable.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Robin said:
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 datasheet "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
Click to expand...
Click to expand...
Click to expand...
 
R

Robin

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 said:
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 said:
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 datasheet "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
Click to expand...
Click to expand...
 
D

david

Yes, I missed the simpler explanation.

If you don't need the whole thing updatable, you can change
the query so some fields are lookup fields. Otherwise you
will have to use an approach which allows you to update the
records separately

(david)


Robin said:
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 said:
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 said:
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 datasheet "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
Click to expand...
Click to expand...
Click to expand...
 
R

Robin

Thank you Allen. I got this to work and was (eventually) able to automate
it. The Temp Table was the way to go!

Thanks again,
Robin

Allen Browne said:
The temp table would only need the primary key fields for the matching
records. You can then INNER JOIN that to your original table and update the
fields of the original table directly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Robin said:
Thank you Allen.

When I wrote the query I was pretty sure it would not work but I was
pleasantly suprised it actually gave me the data I was looking for! I
believe
your temporary table might be the answer if I can figure out the second
part
of the operation. I could use the Make Table Query based on the results
of
my Select Query that works, and allow edits on a form based on the table.
Then I have to update the permanent tables. Using an Update Query? How
do I
tell it to update certain records in the ClientAssignment table based on
the
first "group" of fields and update other records based on another "group"
of
fields...and so on... Would I write multiple update queries? I see a
problem in that I used PREP, REV1 and REV2 as AssignmentID examples but
there
will be more assignments with other names. I can "generally" visualize
how
the VBA would have to work to accomplish this, but I'm pretty sure I'm not
good enough to actually make it work...this year. :) Am I missing a
simpler
way to do the update?

Actual Data:
Record1-ClientID, EngagementID, EngagementYr, AssignmentID (PREP),
CAStaffID, CABudgetHrs, Staff.BillRate + computed fields
Record2-ClientID, EngagementID, EngagementYr, AssignmentID (REV1),
CAStaffID, CABudgetHrs, Staff.BillRate + computed fields
and so on...

What I'm after:
Record1-ClientID(Co1), EngagementID, EngagementYr, AssignmentID (PREP)
CAStaffID, CABudgetHrs, Staff.BillRate, AssignmentID (REV1) CAStaffID,
CABudgetHrs, Staff.BillRate, AssignmentID (REV2) CAStaffID, CABudgetHrs,
Staff.BillRate + computed fields
Record2-ClientID(Co2), EngagementID, EngagementYr, AssignmentID (PREP)
CAStaffID, CABudgetHrs, Staff.BillRate, AssignmentID (REV1) CAStaffID,
CABudgetHrs, Staff.BillRate, AssignmentID (REV2) CAStaffID, CABudgetHrs,
Staff.BillRate + computed fields.

Thanks for your help and suggestion!
Robin

Allen Browne said:
Your query uses 3 instances of ClientAssessment. Without going through
the
detail, I think you are doing this to get values from different records
and
operate on them to get a result. Naturally enough, the query will be
read-only if it returns such a result. The simplest workaround might be
to
write the results into a temporary table designed so it has fields for
the
result, and you can edit that table (or JOIN it to your original table,
apply criteria, and edit the original.)

If you are not trying to read results from multiple records, you might
solve
the problem by using one instance of ClientAssessment along with the
Staff
table. Assuming the tables are joined on the primary key from the table
on
the ONE side of the relationships, the results should be editable.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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 datasheet "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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL SELECT WHERE Problem 1
Repost: SQL SELECT WHERE Problem 3

Top