Update Query

S

Sandy

I have created an update query from another query. The purpose being to
update prices from 22/12/07 onwards.

I have four fields in the update query :-
JobID - Number
Product - Text
BuyPrice - Currency
SellPrice - Currency
Date

The current values for BuyPrice and SellPrice are 3 and 6. In the
UpdateQuery I select as Criteria for the product "Grip Gents", and in the
Update To I have 4 and 7 for the new BuyPrice and SellPrice. Criteria in the
Date is >=#22/12/07#

When I click on 'Run' the message box tells me I am about to update 2
records which would be Ok. However all of my records have been changed. Why?

Sandy
 
F

fredg

I have created an update query from another query. The purpose being to
update prices from 22/12/07 onwards.

I have four fields in the update query :-
JobID - Number
Product - Text
BuyPrice - Currency
SellPrice - Currency
Date

The current values for BuyPrice and SellPrice are 3 and 6. In the
UpdateQuery I select as Criteria for the product "Grip Gents", and in the
Update To I have 4 and 7 for the new BuyPrice and SellPrice. Criteria in the
Date is >=#22/12/07#

When I click on 'Run' the message box tells me I am about to update 2
records which would be Ok. However all of my records have been changed. Why?

Sandy
Click to expand...

Sandy,
Always post the exact query SQL when asking this type of question.
You're the only one here who can see it.

Without seeing the SQL, my first reaction would be your use of Date as
a field name is the problem.
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

Also, when using query criteria, use the US date format of month/ day/
year.
 
K

Ken Sheridan

Sandy:

On the information you've given its hard to say why all rows should be
updated; it helps if you post the SQL of a query. It should look something
like this:

UPDATE YourTable
SET BuyPrice = 4, SellPrice = 7
WHERE Product = "Grip Gents"
AND [Date] >= #12/22/2007#;

Before executing the query you can check that the correct rows will be
affected by switching from design or SQL view to datasheet view.

Note that date literals should be in US format, or an otherwise
internationally unambiguous format. With 22 December the date value will be
interpreted correctly as there is no month 22, but with a date such as
#04/07/2007# this would be interpreted as 7 April not 4 July.

Also I'd advise against using Date as a column name as it could be confused
with the built in Date function which returns the current date. If you do
use it be sure to enclose it in brackets, [Date], but a more specific term
such as TransactionDate would be preferable.

Ken Sheridan
Stafford, England
 
S

Sandy

I used Date as a general term not the actual field name. Sorry guys. The SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4, [JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));

I tried by changing to a select query and it worked fine. The date I changed
to American style but it still makes no difference.

Sandy
 
J

John W. Vinson

I used Date as a general term not the actual field name. Sorry guys. The SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4, [JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));
When I click on 'Run' the message box tells me I am about to update 2
records which would be Ok. However all of my records have been changed.
Why?
Click to expand...
Click to expand...

Doublecheck. Are they actually being changed, or do you perhaps have a Form
with some unbound textboxes displaying data? What's the SQL of [JobDetails
Query]? Are the prices in fact being updated in the underlying table for
records other than "Grip Gents" December 22 orders?

John W. Vinson [MVP]
 
S

Sandy

Hi John

Ok I have copied my database and have deleted all of the forms. The same
applies - on running the (what I have now called the 'PriceUpdate Query')
all of my records (involving "Grip Gents") are changed. If I change this to
a select query then it runs as I would expect (only showing records with
dates after 22/12/07).

The SQL for the UpdateQuery remains as :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4, [JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));

The underlying query - JobDetails Query - has the following SQL :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice] AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

The only price changes taking place are for the "Grip Gents" - nothing else.

Does this help?
Sandy



John W. Vinson said:
I used Date as a general term not the actual field name. Sorry guys. The
SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4, [JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));
When I click on 'Run' the message box tells me I am about to update 2
records which would be Ok. However all of my records have been changed.
Why?
Click to expand...
Click to expand...

Doublecheck. Are they actually being changed, or do you perhaps have a
Form
with some unbound textboxes displaying data? What's the SQL of [JobDetails
Query]? Are the prices in fact being updated in the underlying table for
records other than "Grip Gents" December 22 orders?

John W. Vinson [MVP]
Click to expand...
 
K

Ken Sheridan

Sandy:

I suspect the problem might arise from the outer join in the JobDetails
Query. For the update query you don't need to use an outer join and can base
it on the tables themselves. Try this:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Ken Sheridan
Stafford, England

Sandy said:
Hi John

Ok I have copied my database and have deleted all of the forms. The same
applies - on running the (what I have now called the 'PriceUpdate Query')
all of my records (involving "Grip Gents") are changed. If I change this to
a select query then it runs as I would expect (only showing records with
dates after 22/12/07).

The SQL for the UpdateQuery remains as :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4, [JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));

The underlying query - JobDetails Query - has the following SQL :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice] AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

The only price changes taking place are for the "Grip Gents" - nothing else.

Does this help?
Sandy



John W. Vinson said:
I used Date as a general term not the actual field name. Sorry guys. The
SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4, [JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));
Click to expand...
When I click on 'Run' the message box tells me I am about to update 2
records which would be Ok. However all of my records have been changed.
Why?
Click to expand...

Doublecheck. Are they actually being changed, or do you perhaps have a
Form
with some unbound textboxes displaying data? What's the SQL of [JobDetails
Query]? Are the prices in fact being updated in the underlying table for
records other than "Grip Gents" December 22 orders?

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
S

Sandy

Hello Ken

Changed the SQL as you suggested - the query returns Error - "The specified
field 'RepProduct' could refer to more than one table listed in the FROM
clause (I didn't see one in your SQL?) of your SQL statement."

So - I have four tables :-
'Categories' - with one field
CatName - Primary Key - Text - Linked One to Many with 'RepairProduct'

'RepairProduct' -with four fields
Catname - Foreign Key - Text
RepProduct - Primary Key - Text - Primary Key - Linked One to many with
'JobDetails'
BuyPrice - Currency
SellPrice - Currency

'CustomerDetails' - with thirteen fields
JobID - Primary Key - Autonumber - Linked One to Many with 'JobDetails'
FirstName - Text
Surname - Text
TelNo - Text
CustomerRequirements - Text
DateOrdered - Date/Time
DateRequired - Date/Time
DateReady - Date/Time
DateCollected - Date/Time
WhyLate - Text
BookedInBy - Text
Paid - Yes/No
JobComplete - Yes/No

'JobDetails' - with nine fields
JobID - Number - Linked to 'CustomerDetails'
CatName - Text
RepairWhat - Text
RepProduct - Foreign Key - Text - Linked to 'RepairProduct'
Quantity - Number
Discount - Number - Percent
Repairer1 - Text
Repairer2 - Text
Repairer - Comments

And I have two queries :-
'CustomerDetails Query' - SQL as follows :-

SELECT CustomerDetails.JobID, CustomerDetails.FirstName,
CustomerDetails.Surname, CustomerDetails.TelNo,
CustomerDetails.CustomerRequirements, CustomerDetails.DateOrdered,
CustomerDetails.DateRequired, CustomerDetails.DateReady,
CustomerDetails.DateCollected, CustomerDetails.WhyLate,
CustomerDetails.BookedInBy, CustomerDetails.Paid,
CustomerDetails.JobComplete
FROM CustomerDetails;

'JobDetails Query' - SQL as follows :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice] AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

Hope this explains things a bit more.
Sandy



Ken Sheridan said:
Sandy:

I suspect the problem might arise from the outer join in the JobDetails
Query. For the update query you don't need to use an outer join and can
base
it on the tables themselves. Try this:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Ken Sheridan
Stafford, England

Sandy said:
Hi John

Ok I have copied my database and have deleted all of the forms. The same
applies - on running the (what I have now called the 'PriceUpdate Query')
all of my records (involving "Grip Gents") are changed. If I change this
to
a select query then it runs as I would expect (only showing records with
dates after 22/12/07).

The SQL for the UpdateQuery remains as :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));

The underlying query - JobDetails Query - has the following SQL :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice]
AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

The only price changes taking place are for the "Grip Gents" - nothing
else.

Does this help?
Sandy



John W. Vinson said:
On Sat, 22 Dec 2007 19:15:06 -0000, "Sandy"
<[email protected]>
wrote:

I used Date as a general term not the actual field name. Sorry guys.
The
SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));

When I click on 'Run' the message box tells me I am about to update 2
records which would be Ok. However all of my records have been
changed.
Why?

Doublecheck. Are they actually being changed, or do you perhaps have a
Form
with some unbound textboxes displaying data? What's the SQL of
[JobDetails
Query]? Are the prices in fact being updated in the underlying table
for
records other than "Grip Gents" December 22 orders?

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Sandy:

Mea culpa; should have spotted that. Qualify the column name with one of
the table names:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepairProduct.RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Not connected with the problem, but I'd suggest that you create a Customers
table with one row per customer and a primary key CustomerID (e.g. an
autonumber). Then include a CustomerID foreign key column in CustomerDetails
in place of FirstName and Surname. As it stands the table is not properly
normalized and there is the possibility of inconsistency when there is more
than one row in CustomerDetails for the same customer, i.e. different first
and/or surnames could be entered for the customer. I once found 3 variations
of my name as author in a table of technical articles in one database I
worked on!

Ken Sheridan
Stafford, England

Sandy said:
Hello Ken

Changed the SQL as you suggested - the query returns Error - "The specified
field 'RepProduct' could refer to more than one table listed in the FROM
clause (I didn't see one in your SQL?) of your SQL statement."

So - I have four tables :-
'Categories' - with one field
CatName - Primary Key - Text - Linked One to Many with 'RepairProduct'

'RepairProduct' -with four fields
Catname - Foreign Key - Text
RepProduct - Primary Key - Text - Primary Key - Linked One to many with
'JobDetails'
BuyPrice - Currency
SellPrice - Currency

'CustomerDetails' - with thirteen fields
JobID - Primary Key - Autonumber - Linked One to Many with 'JobDetails'
FirstName - Text
Surname - Text
TelNo - Text
CustomerRequirements - Text
DateOrdered - Date/Time
DateRequired - Date/Time
DateReady - Date/Time
DateCollected - Date/Time
WhyLate - Text
BookedInBy - Text
Paid - Yes/No
JobComplete - Yes/No

'JobDetails' - with nine fields
JobID - Number - Linked to 'CustomerDetails'
CatName - Text
RepairWhat - Text
RepProduct - Foreign Key - Text - Linked to 'RepairProduct'
Quantity - Number
Discount - Number - Percent
Repairer1 - Text
Repairer2 - Text
Repairer - Comments

And I have two queries :-
'CustomerDetails Query' - SQL as follows :-

SELECT CustomerDetails.JobID, CustomerDetails.FirstName,
CustomerDetails.Surname, CustomerDetails.TelNo,
CustomerDetails.CustomerRequirements, CustomerDetails.DateOrdered,
CustomerDetails.DateRequired, CustomerDetails.DateReady,
CustomerDetails.DateCollected, CustomerDetails.WhyLate,
CustomerDetails.BookedInBy, CustomerDetails.Paid,
CustomerDetails.JobComplete
FROM CustomerDetails;

'JobDetails Query' - SQL as follows :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice] AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

Hope this explains things a bit more.
Sandy



Ken Sheridan said:
Sandy:

I suspect the problem might arise from the outer join in the JobDetails
Query. For the update query you don't need to use an outer join and can
base
it on the tables themselves. Try this:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Ken Sheridan
Stafford, England

Sandy said:
Hi John

Ok I have copied my database and have deleted all of the forms. The same
applies - on running the (what I have now called the 'PriceUpdate Query')
all of my records (involving "Grip Gents") are changed. If I change this
to
a select query then it runs as I would expect (only showing records with
dates after 22/12/07).

The SQL for the UpdateQuery remains as :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));

The underlying query - JobDetails Query - has the following SQL :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice]
AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

The only price changes taking place are for the "Grip Gents" - nothing
else.

Does this help?
Sandy



On Sat, 22 Dec 2007 19:15:06 -0000, "Sandy"
<[email protected]>
wrote:

I used Date as a general term not the actual field name. Sorry guys.
The
SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND (([JobDetails
Query].DateOrdered)>=#12/22/2007#));

When I click on 'Run' the message box tells me I am about to update 2
records which would be Ok. However all of my records have been
changed.
Why?

Doublecheck. Are they actually being changed, or do you perhaps have a
Form
with some unbound textboxes displaying data? What's the SQL of
[JobDetails
Query]? Are the prices in fact being updated in the underlying table
for
records other than "Grip Gents" December 22 orders?

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
S

Sandy

Ken

Still didn't do I'm afraid.

I have taken the liberty of uploading my file (sandy-repairs.mdb) and it can
be downloaded (692kb) at :-

http://edinburghgolfrange.co.uk/edin_golf_range/sandy/sandy-repairs.mdb

I know it is near Christmas but if you have the time......

Thanks
Sandy


Ken Sheridan said:
Sandy:

Mea culpa; should have spotted that. Qualify the column name with one of
the table names:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepairProduct.RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Not connected with the problem, but I'd suggest that you create a
Customers
table with one row per customer and a primary key CustomerID (e.g. an
autonumber). Then include a CustomerID foreign key column in
CustomerDetails
in place of FirstName and Surname. As it stands the table is not properly
normalized and there is the possibility of inconsistency when there is
more
than one row in CustomerDetails for the same customer, i.e. different
first
and/or surnames could be entered for the customer. I once found 3
variations
of my name as author in a table of technical articles in one database I
worked on!

Ken Sheridan
Stafford, England

Sandy said:
Hello Ken

Changed the SQL as you suggested - the query returns Error - "The
specified
field 'RepProduct' could refer to more than one table listed in the FROM
clause (I didn't see one in your SQL?) of your SQL statement."

So - I have four tables :-
'Categories' - with one field
CatName - Primary Key - Text - Linked One to Many with
'RepairProduct'

'RepairProduct' -with four fields
Catname - Foreign Key - Text
RepProduct - Primary Key - Text - Primary Key - Linked One to many
with
'JobDetails'
BuyPrice - Currency
SellPrice - Currency

'CustomerDetails' - with thirteen fields
JobID - Primary Key - Autonumber - Linked One to Many with
'JobDetails'
FirstName - Text
Surname - Text
TelNo - Text
CustomerRequirements - Text
DateOrdered - Date/Time
DateRequired - Date/Time
DateReady - Date/Time
DateCollected - Date/Time
WhyLate - Text
BookedInBy - Text
Paid - Yes/No
JobComplete - Yes/No

'JobDetails' - with nine fields
JobID - Number - Linked to 'CustomerDetails'
CatName - Text
RepairWhat - Text
RepProduct - Foreign Key - Text - Linked to 'RepairProduct'
Quantity - Number
Discount - Number - Percent
Repairer1 - Text
Repairer2 - Text
Repairer - Comments

And I have two queries :-
'CustomerDetails Query' - SQL as follows :-

SELECT CustomerDetails.JobID, CustomerDetails.FirstName,
CustomerDetails.Surname, CustomerDetails.TelNo,
CustomerDetails.CustomerRequirements, CustomerDetails.DateOrdered,
CustomerDetails.DateRequired, CustomerDetails.DateReady,
CustomerDetails.DateCollected, CustomerDetails.WhyLate,
CustomerDetails.BookedInBy, CustomerDetails.Paid,
CustomerDetails.JobComplete
FROM CustomerDetails;

'JobDetails Query' - SQL as follows :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice]
AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

Hope this explains things a bit more.
Sandy



Ken Sheridan said:
Sandy:

I suspect the problem might arise from the outer join in the JobDetails
Query. For the update query you don't need to use an outer join and
can
base
it on the tables themselves. Try this:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Ken Sheridan
Stafford, England

:

Hi John

Ok I have copied my database and have deleted all of the forms. The
same
applies - on running the (what I have now called the 'PriceUpdate
Query')
all of my records (involving "Grip Gents") are changed. If I change
this
to
a select query then it runs as I would expect (only showing records
with
dates after 22/12/07).

The SQL for the UpdateQuery remains as :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND
(([JobDetails
Query].DateOrdered)>=#12/22/2007#));

The underlying query - JobDetails Query - has the following SQL :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity,
CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice,
[Quantity]*[SellPrice]
AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS
Total,
JobDetails.Repairer1, JobDetails.Repairer2,
JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails
ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct
=
JobDetails.RepProduct;

The only price changes taking place are for the "Grip Gents" - nothing
else.

Does this help?
Sandy



On Sat, 22 Dec 2007 19:15:06 -0000, "Sandy"
<[email protected]>
wrote:

I used Date as a general term not the actual field name. Sorry guys.
The
SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND
(([JobDetails
Query].DateOrdered)>=#12/22/2007#));

When I click on 'Run' the message box tells me I am about to
update 2
records which would be Ok. However all of my records have been
changed.
Why?

Doublecheck. Are they actually being changed, or do you perhaps have
a
Form
with some unbound textboxes displaying data? What's the SQL of
[JobDetails
Query]? Are the prices in fact being updated in the underlying table
for
records other than "Grip Gents" December 22 orders?

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Sandy:

I've had a look at your file. Its not the query that's the problem, but the
logical model, i.e. how the tables model the real world situation. The buy
and sell prices per repair product appear only once, as values at column
positions in rows in the RepairProduct table. So whatever date parameter you
use is irrelevant because, provided there is at least one row in the
CustomerDetails table with values "Grip Gents" and a date on or after the
date parameter the row in RepairProduct will be updated. The new values will
be used by any query which references that row regardless of the date.

What you have here is, I think, analogous to the situation in the sample
Northwind database, in which each product has a unit price, i.e. its current
unit price, but each invoice line also stores a unit price, i.e. the price at
the time the invoice was raised. Consequently this remains unchanged when
the current unit price is changed. Any new invoice uses the current unit
price by looking it up from the products table and assigning the value to the
invoice line. It looks to me like you need a similar arrangement so that the
'historic' buy and sell prices will remain static with each job, while the
current buy and sell prices are updated from time to time.

What you need, as far as I can see, are buy and sell price columns in the
JobDetails table. If you take a look at the Orders subform in Northwind
you'll see how this looks up the current unit price in the AfterUpdate event
procedure of the ProductID control and assigns it to the column in the
underlying table. You could do something similar.

Once you have the buy and sell price columns in JobDetails, and whenever a
new row is inserted into the table you look up and insert the current prices
from RepairProduct, then all you have to do when prices change is amend the
values in RepairProduct. Any subsequent rows inserted into JobDetails will
then get the latest prices.

Ken Sheridan
Stafford, England

Sandy said:
Ken

Still didn't do I'm afraid.

I have taken the liberty of uploading my file (sandy-repairs.mdb) and it can
be downloaded (692kb) at :-

http://edinburghgolfrange.co.uk/edin_golf_range/sandy/sandy-repairs.mdb

I know it is near Christmas but if you have the time......

Thanks
Sandy


Ken Sheridan said:
Sandy:

Mea culpa; should have spotted that. Qualify the column name with one of
the table names:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepairProduct.RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Not connected with the problem, but I'd suggest that you create a
Customers
table with one row per customer and a primary key CustomerID (e.g. an
autonumber). Then include a CustomerID foreign key column in
CustomerDetails
in place of FirstName and Surname. As it stands the table is not properly
normalized and there is the possibility of inconsistency when there is
more
than one row in CustomerDetails for the same customer, i.e. different
first
and/or surnames could be entered for the customer. I once found 3
variations
of my name as author in a table of technical articles in one database I
worked on!

Ken Sheridan
Stafford, England

Sandy said:
Hello Ken

Changed the SQL as you suggested - the query returns Error - "The
specified
field 'RepProduct' could refer to more than one table listed in the FROM
clause (I didn't see one in your SQL?) of your SQL statement."

So - I have four tables :-
'Categories' - with one field
CatName - Primary Key - Text - Linked One to Many with
'RepairProduct'

'RepairProduct' -with four fields
Catname - Foreign Key - Text
RepProduct - Primary Key - Text - Primary Key - Linked One to many
with
'JobDetails'
BuyPrice - Currency
SellPrice - Currency

'CustomerDetails' - with thirteen fields
JobID - Primary Key - Autonumber - Linked One to Many with
'JobDetails'
FirstName - Text
Surname - Text
TelNo - Text
CustomerRequirements - Text
DateOrdered - Date/Time
DateRequired - Date/Time
DateReady - Date/Time
DateCollected - Date/Time
WhyLate - Text
BookedInBy - Text
Paid - Yes/No
JobComplete - Yes/No

'JobDetails' - with nine fields
JobID - Number - Linked to 'CustomerDetails'
CatName - Text
RepairWhat - Text
RepProduct - Foreign Key - Text - Linked to 'RepairProduct'
Quantity - Number
Discount - Number - Percent
Repairer1 - Text
Repairer2 - Text
Repairer - Comments

And I have two queries :-
'CustomerDetails Query' - SQL as follows :-

SELECT CustomerDetails.JobID, CustomerDetails.FirstName,
CustomerDetails.Surname, CustomerDetails.TelNo,
CustomerDetails.CustomerRequirements, CustomerDetails.DateOrdered,
CustomerDetails.DateRequired, CustomerDetails.DateReady,
CustomerDetails.DateCollected, CustomerDetails.WhyLate,
CustomerDetails.BookedInBy, CustomerDetails.Paid,
CustomerDetails.JobComplete
FROM CustomerDetails;

'JobDetails Query' - SQL as follows :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity, CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice, [Quantity]*[SellPrice]
AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS Total,
JobDetails.Repairer1, JobDetails.Repairer2, JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct =
JobDetails.RepProduct;

Hope this explains things a bit more.
Sandy



Sandy:

I suspect the problem might arise from the outer join in the JobDetails
Query. For the update query you don't need to use an outer join and
can
base
it on the tables themselves. Try this:

UPDATE RepairProduct INNER JOIN (CustomerDetails
INNER JOIN JobDetails ON CustomerDetails.JobID = JobDetails.JobID)
ON RepairProduct.RepProduct = JobDetails.RepProduct
SET BuyPrice = 4, SellPrice = 7
WHERE RepProduct = "Grip Gents"
AND DateOrdered >= #12/22/2007#;

Ken Sheridan
Stafford, England

:

Hi John

Ok I have copied my database and have deleted all of the forms. The
same
applies - on running the (what I have now called the 'PriceUpdate
Query')
all of my records (involving "Grip Gents") are changed. If I change
this
to
a select query then it runs as I would expect (only showing records
with
dates after 22/12/07).

The SQL for the UpdateQuery remains as :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND
(([JobDetails
Query].DateOrdered)>=#12/22/2007#));

The underlying query - JobDetails Query - has the following SQL :-

SELECT JobDetails.JobID, JobDetails.CatName, JobDetails.RepairWhat,
JobDetails.RepProduct, JobDetails.Quantity,
CustomerDetails.DateOrdered,
RepairProduct.BuyPrice, RepairProduct.SellPrice,
[Quantity]*[SellPrice]
AS
SubTotal, JobDetails.Discount, [SubTotal]-[SubTotal]*[Discount] AS
Total,
JobDetails.Repairer1, JobDetails.Repairer2,
JobDetails.RepairerComments
FROM RepairProduct INNER JOIN (CustomerDetails RIGHT JOIN JobDetails
ON
CustomerDetails.JobID = JobDetails.JobID) ON RepairProduct.RepProduct
=
JobDetails.RepProduct;

The only price changes taking place are for the "Grip Gents" - nothing
else.

Does this help?
Sandy



On Sat, 22 Dec 2007 19:15:06 -0000, "Sandy"
<[email protected]>
wrote:

I used Date as a general term not the actual field name. Sorry guys.
The
SQL
is as follows :-

UPDATE [JobDetails Query] SET [JobDetails Query].BuyPrice = 4,
[JobDetails
Query].SellPrice = 7
WHERE ((([JobDetails Query].RepProduct)="Grip Gents") AND
(([JobDetails
Query].DateOrdered)>=#12/22/2007#));

When I click on 'Run' the message box tells me I am about to
update 2
records which would be Ok. However all of my records have been
changed.
Why?

Doublecheck. Are they actually being changed, or do you perhaps have
a
Form
with some unbound textboxes displaying data? What's the SQL of
[JobDetails
Query]? Are the prices in fact being updated in the underlying table
for
records other than "Grip Gents" December 22 orders?

John W. Vinson [MVP]
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

Combo box and text box dependency 2
Switch text box to combo box 3
Synchronise two combo boxes 4
Update textbox on form 4
Calculated Fields 4
Copy date from a form to a different table 10
Update of Existing Table Numeric Value Using DCount from Another T 3
Rounding in Update Query 3

Top