Append Query Not working

[Ron]

Hi,

I am trying to append Fields from two tables :
SKU, CustFinInvoice, CustFinQty From [Order Facts] - Pri Key SKU
SalePrice from tblPrices - Pri Key SKU
WIC as a computed Value ( [Order Facts].CustFinQty *
tblPrices.SalePrice)
Into a table [Customer Details] with Order#, SKU, Qty, SalePrice,
ExtPrice - Pri Key OrderID.
The Join: [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU

When ever I do this I get a Key Violation and I do not understand why. Can
Some One Help

 

[KARL DEWEY]

It helps if you post the complete SQL.
What are you appending into [Customer Details].[OrderID]?

 

[Ron]

Here is the sql:

INSERT INTO [Customer Details] ( SKU, [Order#], Qty, SalePrice, ExtPrice )
SELECT [Order Facts].SKU, [Order Facts].CustFinalInvoice, [Order
Facts].CustFinalQty, tblPrices.SalePrice, [Order
Facts].[CustFinalQty]*[tblPrices].[SalePrice] AS WIC
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU=tblPrices.SKU
WHERE ((([Order Facts].CustFinalInvoice)=[Enter the Order Number]));


OrderID is an autonumber field used as the primery key.
Order Facts is the list of Items from a form I need to fufill a Customer's
order.
Taking the Order Facts and putting it into Customer Details allow me to
generate an Invoice and other reports later.

Does that help??
TIA

--
Ron

It helps if you post the complete SQL.
What are you appending into [Customer Details].[OrderID]?

--
KARL DEWEY
Build a little - Test a little

Hi,

I am trying to append Fields from two tables :
SKU, CustFinInvoice, CustFinQty From [Order Facts] - Pri Key SKU
SalePrice from tblPrices - Pri Key SKU
WIC as a computed Value ( [Order Facts].CustFinQty *
tblPrices.SalePrice)
Into a table [Customer Details] with Order#, SKU, Qty, SalePrice,
ExtPrice - Pri Key OrderID.
The Join: [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU

When ever I do this I get a Key Violation and I do not understand why. Can
Some One Help

 

[KARL DEWEY]

Do you have any unique indexes?
You can try appending one field at a time until it fails using far-out data
in ther other fields like 999999 or ZZZZZZZZZ. That way you can determine
which field is giving the error.
--
KARL DEWEY
Build a little - Test a little

Here is the sql:

INSERT INTO [Customer Details] ( SKU, [Order#], Qty, SalePrice, ExtPrice )
SELECT [Order Facts].SKU, [Order Facts].CustFinalInvoice, [Order
Facts].CustFinalQty, tblPrices.SalePrice, [Order
Facts].[CustFinalQty]*[tblPrices].[SalePrice] AS WIC
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU=tblPrices.SKU
WHERE ((([Order Facts].CustFinalInvoice)=[Enter the Order Number]));


OrderID is an autonumber field used as the primery key.
Order Facts is the list of Items from a form I need to fufill a Customer's
order.
Taking the Order Facts and putting it into Customer Details allow me to
generate an Invoice and other reports later.

Does that help??
TIA

--
Ron

It helps if you post the complete SQL.
What are you appending into [Customer Details].[OrderID]?

--
KARL DEWEY
Build a little - Test a little

Hi,

I am trying to append Fields from two tables :
SKU, CustFinInvoice, CustFinQty From [Order Facts] - Pri Key SKU
SalePrice from tblPrices - Pri Key SKU
WIC as a computed Value ( [Order Facts].CustFinQty *
tblPrices.SalePrice)
Into a table [Customer Details] with Order#, SKU, Qty, SalePrice,
ExtPrice - Pri Key OrderID.
The Join: [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU

When ever I do this I get a Key Violation and I do not understand why. Can
Some One Help

 

[Ron]

What I did was to delete all of the fields exceot for SKU and Order Number.
Ran the Query.
Entered the Order Number. It said I was about to append 8 Rows. Do you want
to?
I clicked yes. Then it gave me the key violation. This is the minimum I
can run the query with.
I ran it once where I did not have the Order number in the query and it did
give me a key violation.
I have taken every parameter out and left Only to enter the Order Number and
it still gves a key violation

Help
--
Ron

Do you have any unique indexes?
You can try appending one field at a time until it fails using far-out data
in ther other fields like 999999 or ZZZZZZZZZ. That way you can determine
which field is giving the error.
--
KARL DEWEY
Build a little - Test a little

Here is the sql:

INSERT INTO [Customer Details] ( SKU, [Order#], Qty, SalePrice, ExtPrice )
SELECT [Order Facts].SKU, [Order Facts].CustFinalInvoice, [Order
Facts].CustFinalQty, tblPrices.SalePrice, [Order
Facts].[CustFinalQty]*[tblPrices].[SalePrice] AS WIC
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU=tblPrices.SKU
WHERE ((([Order Facts].CustFinalInvoice)=[Enter the Order Number]));


OrderID is an autonumber field used as the primery key.
Order Facts is the list of Items from a form I need to fufill a Customer's
order.
Taking the Order Facts and putting it into Customer Details allow me to
generate an Invoice and other reports later.

Does that help??
TIA

--
Ron

It helps if you post the complete SQL.
What are you appending into [Customer Details].[OrderID]?

--
KARL DEWEY
Build a little - Test a little


:

Hi,

I am trying to append Fields from two tables :
SKU, CustFinInvoice, CustFinQty From [Order Facts] - Pri Key SKU
SalePrice from tblPrices - Pri Key SKU
WIC as a computed Value ( [Order Facts].CustFinQty *
tblPrices.SalePrice)
Into a table [Customer Details] with Order#, SKU, Qty, SalePrice,
ExtPrice - Pri Key OrderID.
The Join: [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU

When ever I do this I get a Key Violation and I do not understand why. Can
Some One Help

 

[Ron]

Hi,
I am still looking for an answer does anyone have an idea???
--
Ron

What I did was to delete all of the fields exceot for SKU and Order Number.
Ran the Query.
Entered the Order Number. It said I was about to append 8 Rows. Do you want
to?
I clicked yes. Then it gave me the key violation. This is the minimum I
can run the query with.
I ran it once where I did not have the Order number in the query and it did
give me a key violation.
I have taken every parameter out and left Only to enter the Order Number and
it still gves a key violation

Help
--
Ron

Do you have any unique indexes?
You can try appending one field at a time until it fails using far-out data
in ther other fields like 999999 or ZZZZZZZZZ. That way you can determine
which field is giving the error.
--
KARL DEWEY
Build a little - Test a little

Here is the sql:

INSERT INTO [Customer Details] ( SKU, [Order#], Qty, SalePrice, ExtPrice )
SELECT [Order Facts].SKU, [Order Facts].CustFinalInvoice, [Order
Facts].CustFinalQty, tblPrices.SalePrice, [Order
Facts].[CustFinalQty]*[tblPrices].[SalePrice] AS WIC
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU=tblPrices.SKU
WHERE ((([Order Facts].CustFinalInvoice)=[Enter the Order Number]));


OrderID is an autonumber field used as the primery key.
Order Facts is the list of Items from a form I need to fufill a Customer's
order.
Taking the Order Facts and putting it into Customer Details allow me to
generate an Invoice and other reports later.

Does that help??
TIA

--
Ron


:

It helps if you post the complete SQL.
What are you appending into [Customer Details].[OrderID]?

--
KARL DEWEY
Build a little - Test a little


:

Hi,

I am trying to append Fields from two tables :
SKU, CustFinInvoice, CustFinQty From [Order Facts] - Pri Key SKU
SalePrice from tblPrices - Pri Key SKU
WIC as a computed Value ( [Order Facts].CustFinQty *
tblPrices.SalePrice)
Into a table [Customer Details] with Order#, SKU, Qty, SalePrice,
ExtPrice - Pri Key OrderID.
The Join: [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU

When ever I do this I get a Key Violation and I do not understand why. Can
Some One Help

 

[John Spencer]

Some Possible Reasons for failure:
-- Populating a field that is a Foreign Key to another table and the key
value does not exist in the other table
-- Populating a field with a unique index where the value already exists
-- Populating a field with a validation rule where the value is outside the
specification
-- NOT populating a field that is required.

INSERT INTO [Customer Details] ( SKU, [Order#], Qty, SalePrice, ExtPrice )
SELECT [Order Facts].SKU
, [Order Facts].CustFinalInvoice
, [Order Facts].CustFinalQty
, tblPrices.SalePrice
, [Order Facts].[CustFinalQty]*[tblPrices].[SalePrice] AS WIC
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU=tblPrices.SKU
WHERE [Order Facts].CustFinalInvoice=[Enter the Order Number]

One thing you can try for testing purposes is to just run the SELECT query
and then with the query showing, try entering the records by typing them
into Customer Details. That may give you more information on why this is
failing. Just looking at what is returned by the SELECT query may show you
the problem. For instance if the query returns more records than you expect
or duplicate records.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,
I am still looking for an answer does anyone have an idea???

S N I P

Hi,

I am trying to append Fields from two tables :
SKU, CustFinInvoice, CustFinQty From [Order Facts] - Pri
Key SKU
SalePrice from tblPrices - Pri Key SKU
WIC as a computed Value ( [Order Facts].CustFinQty *
tblPrices.SalePrice)
Into a table [Customer Details] with Order#, SKU, Qty,
SalePrice,
ExtPrice - Pri Key OrderID.
The Join: [Order Facts] LEFT JOIN tblPrices ON [Order
Facts].SKU =
tblPrices.SKU

When ever I do this I get a Key Violation and I do not understand
why. Can
Some One Help