Timing Update Queries

[Guest]

1st i am assuming queries can't take IF statements. I need 2 updates
depending on 2 different situations so i need 2 queries.

But, timing is what worries me. This is a update prices problem. The app
runs on a 1.00 Gbps LAN with a dozen or so users at any one time and is
split, the tables r on the server.The queries set prices based on 2 different
situations. I am thinking of running query 1 to handle case 1, then run query
2 to handle case 2 and attaching them to OnClose event of a form. This form
is heavily used and creates the 2 situations my queries address.

What happens if user 1 closes his copy, the queries run and before they end
(perhaps as long as 10 secs on a busy day) user 2 opens the same form? Will
the data be corrupted? Will user 2 see the updates done so far? Will the
updates continue to run and user 2’s copy be updated while it is still open?
Does Access control all this in the background?

 

[Guest]

Your assumption may well be wrong. IF statements won't work; however, an IIf
statement probably will or you could use a Case statement in a function that
is called by the query.

Post the SQL so that we can see what you are doing.

 

[Guest]

Ok here are the 2 queries.

1st query to set purchase price to our actual cost where we pay a finder
fee, that is some one outside the company created the deal and we pay a
"finders fee" of $300.

UPDATE tblContracts INNER JOIN Lots ON tblContracts.ContractID =
Lots.[File#] SET Lots.PurchasePrice = ([amount$sent]+[depositamount]+300),
Lots.WeOwnLot = Yes
WHERE (((tblContracts.[Amount$Sent])>0) AND ((Lots.Sold)="no") AND
((tblContracts.FinderID)<>14));



2nd query to set purchase price to our actual cost where we pay no finder
fee, a "House Deal". "House" happens to be FinderID 14 in the table Finders.

UPDATE tblContracts INNER JOIN Lots ON tblContracts.ContractID =
Lots.[File#] SET Lots.PurchasePrice = ([amount$sent]+[depositamount]),
Lots.WeOwnLot = Yes
WHERE (((tblContracts.[Amount$Sent])>0) AND ((Lots.Sold)="no") AND
((tblContracts.FinderID)=14));

 

[Guest]

Hummm. I don't see any easy way to combine the two update queries into one. I
bet a good coder could do it in a function though.

Still I doubt that it would be a problem. Possibly you can speed up the
queries by indexing.

Hopefully tblContracts.ContractID is the Primary Key for that table. With ID
in the name, it's almost certain that the field is indexed anyway. However if
tblContracts.ContractID is linked to Lots.[File#] in the Relationships window
and Referiential Integrity on, both would be indexed. In fact be careful that
tblContracts.ContractID doesn't have two indexes on it: one as PK and the
other just because it has ID in the field name.

Also index tblContracts.[Amount$Sent] and tblContracts.FinderID in one index
and see if that speeds things up.

Lots.Sold might not help from an index if it's just a "yes" or "no" field
and if it's just a 50/50 chance of being one or the other. However if most
records are "yes" and only a few "no", then an index could help.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Ok here are the 2 queries.

1st query to set purchase price to our actual cost where we pay a finder
fee, that is some one outside the company created the deal and we pay a
"finders fee" of $300.

UPDATE tblContracts INNER JOIN Lots ON tblContracts.ContractID =
Lots.[File#] SET Lots.PurchasePrice = ([amount$sent]+[depositamount]+300),
Lots.WeOwnLot = Yes
WHERE (((tblContracts.[Amount$Sent])>0) AND ((Lots.Sold)="no") AND
((tblContracts.FinderID)<>14));



2nd query to set purchase price to our actual cost where we pay no finder
fee, a "House Deal". "House" happens to be FinderID 14 in the table Finders.

UPDATE tblContracts INNER JOIN Lots ON tblContracts.ContractID =
Lots.[File#] SET Lots.PurchasePrice = ([amount$sent]+[depositamount]),
Lots.WeOwnLot = Yes
WHERE (((tblContracts.[Amount$Sent])>0) AND ((Lots.Sold)="no") AND
((tblContracts.FinderID)=14));


--
SailorMike

Your assumption may well be wrong. IF statements won't work; however, an IIf
statement probably will or you could use a Case statement in a function that
is called by the query.

Post the SQL so that we can see what you are doing.