Getting Multiple Queries to work

R

Ron

I am trying to print Invoices
I Have:

Table:

OrderFacts - SKU (PK)(T6) - CustFinOrder (Y/N) - CustFinQty (I) -
CustFinOrdNum (T 999999\-999)
CustDet - OrdID (PK)(AN) - OrderNo (T 999999\-999) - SKU (T6) - QTY
(DG2) -SalePrice (C2) - ExtPrice (C2)
NewItems - SKU (PK)(T6) - Item (T255) - Description (T255)
tblPrices - SKU (T6) - ItemDate (DT) - Min (SGNAuto) - SalePrice (C2)
- MarkUP (SP2)

The items I want in my invoice are:

1. OrderFacts - SKU - CustFinQty - CustFinOrdNum
2. tblPrices - Min - SalePrice - Markup
3. ExtPrice: Min + (Min*MarkUP)
4. NewItems - Description

Append Query:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice,
Qty, ExtPrice )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU = tblPrices.SKU
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));

This Query Works fine to put 1,2 and3 in.

I try to add 4 in the Query Pane and I get this Query:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice, Qty,
ExtPrice, Description )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice, tblNewItems.Description
FROM ([Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU) LEFT JOIN tblNewItems ON (tblNewItems.SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND (tblNewItems.SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND ([Order Facts].SKU = tblNewItems.SKU)
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));


And the following error:


The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement.


Can Someone help me with this. Remember I'm new to queries and to put two
queries together just rattles my Jowls. Help sorta confused me

TIA
-
Ron
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Shouldn't there always be a price per item? If so, then you need an
INNER JOIN between the [Order Facts] table and the tblPrices table.
This will cure the error msg about ambiguous joins. Also, you shud join
the table tblNewItems to the table [Order Facts].SKU instead of the
tblPrices.SKU, since the [Order Facts] table is the "driving" table (if
there isn't the required data in that table, you don't want to show
anything).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR4nWJIechKqOuFEgEQIbtgCg3X2srsz8B36Sy/Koyay2pmjaJn8AniyH
FTY4ZRDqfAuRYE1SzFINb+TQ
=HQgQ
-----END PGP SIGNATURE-----
I am trying to print Invoices
I Have:

Table:

OrderFacts - SKU (PK)(T6) - CustFinOrder (Y/N) - CustFinQty (I) -
CustFinOrdNum (T 999999\-999)
CustDet - OrdID (PK)(AN) - OrderNo (T 999999\-999) - SKU (T6) - QTY
(DG2) -SalePrice (C2) - ExtPrice (C2)
NewItems - SKU (PK)(T6) - Item (T255) - Description (T255)
tblPrices - SKU (T6) - ItemDate (DT) - Min (SGNAuto) - SalePrice (C2)
- MarkUP (SP2)

The items I want in my invoice are:

1. OrderFacts - SKU - CustFinQty - CustFinOrdNum
2. tblPrices - Min - SalePrice - Markup
3. ExtPrice: Min + (Min*MarkUP)
4. NewItems - Description

Append Query:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice,
Qty, ExtPrice )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU = tblPrices.SKU
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));

This Query Works fine to put 1,2 and3 in.

I try to add 4 in the Query Pane and I get this Query:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice, Qty,
ExtPrice, Description )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice, tblNewItems.Description
FROM ([Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU) LEFT JOIN tblNewItems ON (tblNewItems.SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND (tblNewItems.SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND ([Order Facts].SKU = tblNewItems.SKU)
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));


And the following error:


The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement.


Can Someone help me with this. Remember I'm new to queries and to put two
queries together just rattles my Jowls. Help sorta confused me

TIA
-
Ron
Click to expand...
 
R

Ron

I Thought I Had the joins correct in:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice, Qty,
ExtPrice, Description )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice, tblNewItems.Description
FROM ([Order Facts] LEFT JOIN NewItems ON [Order Facts].SKU = NewItems.SKU)
LEFT JOIN tblNewItems ON ([Order Facts].SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND (tblNewItems.SKU = tblPrices.SKU) AND
([Order Facts].SKU = tblNewItems.SKU)
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));

But when I ran it I got:

Syntax error in JOIN operation.

Where?
--
Ron


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Shouldn't there always be a price per item? If so, then you need an
INNER JOIN between the [Order Facts] table and the tblPrices table.
This will cure the error msg about ambiguous joins. Also, you shud join
the table tblNewItems to the table [Order Facts].SKU instead of the
tblPrices.SKU, since the [Order Facts] table is the "driving" table (if
there isn't the required data in that table, you don't want to show
anything).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR4nWJIechKqOuFEgEQIbtgCg3X2srsz8B36Sy/Koyay2pmjaJn8AniyH
FTY4ZRDqfAuRYE1SzFINb+TQ
=HQgQ
-----END PGP SIGNATURE-----
I am trying to print Invoices
I Have:

Table:

OrderFacts - SKU (PK)(T6) - CustFinOrder (Y/N) - CustFinQty (I) -
CustFinOrdNum (T 999999\-999)
CustDet - OrdID (PK)(AN) - OrderNo (T 999999\-999) - SKU (T6) - QTY
(DG2) -SalePrice (C2) - ExtPrice (C2)
NewItems - SKU (PK)(T6) - Item (T255) - Description (T255)
tblPrices - SKU (T6) - ItemDate (DT) - Min (SGNAuto) - SalePrice (C2)
- MarkUP (SP2)

The items I want in my invoice are:

1. OrderFacts - SKU - CustFinQty - CustFinOrdNum
2. tblPrices - Min - SalePrice - Markup
3. ExtPrice: Min + (Min*MarkUP)
4. NewItems - Description

Append Query:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice,
Qty, ExtPrice )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice
FROM [Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU = tblPrices.SKU
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));

This Query Works fine to put 1,2 and3 in.

I try to add 4 in the Query Pane and I get this Query:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice, Qty,
ExtPrice, Description )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice, tblNewItems.Description
FROM ([Order Facts] LEFT JOIN tblPrices ON [Order Facts].SKU =
tblPrices.SKU) LEFT JOIN tblNewItems ON (tblNewItems.SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND (tblNewItems.SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND ([Order Facts].SKU = tblNewItems.SKU)
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));


And the following error:


The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement.


Can Someone help me with this. Remember I'm new to queries and to put two
queries together just rattles my Jowls. Help sorta confused me

TIA
-
Ron
Click to expand...
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OK, here's my version (corrected) of your query:

PARAMETERS [enter the order number] TEXT;

INSERT INTO [Customer Details]
([Order#], SKU, SalePrice, Qty, ExtPrice, Description )

SELECT F.CustFinalInvoice, F.SKU, P.SalePrice, F.CustFinalQty,
[Min]+([Min]*[Markup]) AS ExtPrice, N.Description

FROM ([Order Facts] As F LEFT JOIN NewItems As N ON F.SKU = N.SKU) INNER
JOIN tblPrices As P ON F.SKU = P.SKU

WHERE F.CustFinalInvoice = [enter the order number]
AND F.CustFinalOrder = Yes

I used aliased table names to make it easier to read the SQL.

Is that next to last line supposed to be CustFinalInvoice or
CustFinOrdNum? Since you're asking for the order number, not the
invoice number; and, you have CustFinOrderNum in your 1st post
description of the table Order Facts.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR4s3BYechKqOuFEgEQLJ3ACgsCV4+e1BPc5NZx3C6d6Ijvj9ZRYAmQH7
gijWsFcPTkk/Ran+R+MkH/hi
=CQx2
-----END PGP SIGNATURE-----
 
R

Ron

Your Interpretation worked just fine except when I ran it on a table with
data it gave me an error "......and it did not add 18 records due to key
violations ......."

I pressed the help button but it took me to page one of access help. I
typed Key Violations in the search box and got a bunch of selections, none of
which looked like Key Violations.

Where do I go now??
TIA
--
Ron


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OK, here's my version (corrected) of your query:

PARAMETERS [enter the order number] TEXT;

INSERT INTO [Customer Details]
([Order#], SKU, SalePrice, Qty, ExtPrice, Description )

SELECT F.CustFinalInvoice, F.SKU, P.SalePrice, F.CustFinalQty,
[Min]+([Min]*[Markup]) AS ExtPrice, N.Description

FROM ([Order Facts] As F LEFT JOIN NewItems As N ON F.SKU = N.SKU) INNER
JOIN tblPrices As P ON F.SKU = P.SKU

WHERE F.CustFinalInvoice = [enter the order number]
AND F.CustFinalOrder = Yes

I used aliased table names to make it easier to read the SQL.

Is that next to last line supposed to be CustFinalInvoice or
CustFinOrdNum? Since you're asking for the order number, not the
invoice number; and, you have CustFinOrderNum in your 1st post
description of the table Order Facts.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR4s3BYechKqOuFEgEQLJ3ACgsCV4+e1BPc5NZx3C6d6Ijvj9ZRYAmQH7
gijWsFcPTkk/Ran+R+MkH/hi
=CQx2
-----END PGP SIGNATURE-----

I Thought I Had the joins correct in:

INSERT INTO [Customer Details] ( [Order#], SKU, SalePrice, Qty,
ExtPrice, Description )
SELECT [Order Facts].CustFinalInvoice, [Order Facts].SKU,
tblPrices.SalePrice, [Order Facts].CustFinalQty, [Min]+([Min]*[Markup]) AS
ExtPrice, tblNewItems.Description
FROM ([Order Facts] LEFT JOIN NewItems ON [Order Facts].SKU = NewItems.SKU)
LEFT JOIN tblNewItems ON ([Order Facts].SKU = tblPrices.SKU) AND
(tblNewItems.SKU = tblPrices.SKU) AND (tblNewItems.SKU = tblPrices.SKU) AND
([Order Facts].SKU = tblNewItems.SKU)
WHERE ((([Order Facts].CustFinalInvoice)=[enter the order number]) AND
(([Order Facts].CustFinalOrder)=Yes));

But when I ran it I got:

Syntax error in JOIN operation.

Where?
Click to expand...
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Key violations mean that the query tried to insert a row of data that
was already there (the PK was duplicated, or a unique key column data
was duplicated), or, a column required data and there wasn't any.
Usually, the key violation is a duplicate key. This could be good if it
is rejecting duplicate rows (records) or bad, if you want the duplicate
data, but w/ different keys.

You'll have to research your data and see what's happening - which rows
are being rejected. Create a Duplicates query (use the Wizard) on the
source data and the target data. You can do this by using my INSERT
query - just get rid of the INSERT clause and save as a SELECT query.
Then run the Duplicates query wizard and use the SELECT query as the
comparator data to the target table.

Good luck
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR43nYYechKqOuFEgEQLZYACfae0VurJ0j1ldk1dH+ezgBEvPa8UAoPJf
DcOnhO1l2yVYmPjM1zXlkzTI
=hbA2
-----END PGP SIGNATURE-----
 
R

Ron

Thank You for all of your help. If I need further Help (Instruction), I"ll
start a new thread.

Thanks again
 

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

Append Query Not working 6

Top