Help with Query/SQL

L

Lucien

I am running a query that includes 2 tables and another query. I have one
field in a query [New Item Tracking Run w Invoice Range+Together] linked to a
like field in a table [Safety Factors All]. I would like this to be a LEFT
join so I can display all records from the [New Item Tracking Run w Invoice
Range+Together] query and the records that match in the [Safety Factors All]
table. Access will only allow me to create an INNER join. When I try to
create a LEFT join I get an error message that says there is an ambiguous
outer join. I am kind of a new access user and I do not know what I'm doing
wrong.
Below is the SQL from this query in it's current state with the INNER join:

Any help will be greatly appreciated

SELECT [New Item Tracking Run w Invoice Range+Together].PLANT, [New Item
Tracking Run w Invoice Range+Together].ITEM, [New Item Tracking Run w Invoice
Range+Together].BOD_CODE, [New Item Tracking Run w Invoice
Range+Together].INVOICES, [New Item Tracking Run w Invoice
Range+Together].UNITS, [New Item Tracking Run w Invoice Range+Together].[Brn
Type], [New Item Tracking Run w Invoice Range+Together].[Invoice Bucket],
[Slpk_Wk Days].Slpk, [New Item Tracking Run w Invoice Range+Together].[Lead
Time], [Slpk_Wk Days].[Work Days], ([UNITS]/[Slpk])*([Lead time]/[Work Days])
AS [U/S*LT/W], [Safety Factor]^2.67 AS V, [Lead Time]^-0.283 AS L,
[INVOICES]^-0.264 AS I, 0.000083*[V]*[L]* AS LVI,
Sqr([U/S*LT/W]*(2*([UNITS])/([Slpk]*[INVOICES])-1)) AS [Safety Stock],
([Slpk]*(([U/S*LT/W]+[LVI]*([Safety Stock])))) AS [S L]
FROM [Slpk_Wk Days], [New Item Tracking Run w Invoice Range+Together] INNER
JOIN [Safety Factors All] ON [New Item Tracking Run w Invoice
Range+Together].Together = [Safety Factors All].Together;
 
K

KARL DEWEY

You will need to join [Slpk_Wk Days] in the SQL statement to make a left
join. But I do not see where the fields will relate.
 
L

Lucien

I need to make a left join between query New Item Tracking Run w Invoice
Range+Together and table Safety Factors All. I don't need to join the
Slpk_Wk Days table to anything.



KARL DEWEY said:
You will need to join [Slpk_Wk Days] in the SQL statement to make a left
join. But I do not see where the fields will relate.

Lucien said:
I am running a query that includes 2 tables and another query. I have one
field in a query [New Item Tracking Run w Invoice Range+Together] linked to a
like field in a table [Safety Factors All]. I would like this to be a LEFT
join so I can display all records from the [New Item Tracking Run w Invoice
Range+Together] query and the records that match in the [Safety Factors All]
table. Access will only allow me to create an INNER join. When I try to
create a LEFT join I get an error message that says there is an ambiguous
outer join. I am kind of a new access user and I do not know what I'm doing
wrong.
Below is the SQL from this query in it's current state with the INNER join:

Any help will be greatly appreciated

SELECT [New Item Tracking Run w Invoice Range+Together].PLANT, [New Item
Tracking Run w Invoice Range+Together].ITEM, [New Item Tracking Run w Invoice
Range+Together].BOD_CODE, [New Item Tracking Run w Invoice
Range+Together].INVOICES, [New Item Tracking Run w Invoice
Range+Together].UNITS, [New Item Tracking Run w Invoice Range+Together].[Brn
Type], [New Item Tracking Run w Invoice Range+Together].[Invoice Bucket],
[Slpk_Wk Days].Slpk, [New Item Tracking Run w Invoice Range+Together].[Lead
Time], [Slpk_Wk Days].[Work Days], ([UNITS]/[Slpk])*([Lead time]/[Work Days])
AS [U/S*LT/W], [Safety Factor]^2.67 AS V, [Lead Time]^-0.283 AS L,
[INVOICES]^-0.264 AS I, 0.000083*[V]*[L]* AS LVI,
Sqr([U/S*LT/W]*(2*([UNITS])/([Slpk]*[INVOICES])-1)) AS [Safety Stock],
([Slpk]*(([U/S*LT/W]+[LVI]*([Safety Stock])))) AS [S L]
FROM [Slpk_Wk Days], [New Item Tracking Run w Invoice Range+Together] INNER
JOIN [Safety Factors All] ON [New Item Tracking Run w Invoice
Range+Together].Together = [Safety Factors All].Together;
Click to expand...
Click to expand...
 
K

KARL DEWEY

You must join it. Try this --
SELECT [New Item Tracking Run w Invoice Range+Together].PLANT, [New Item
Tracking Run w Invoice Range+Together].ITEM, [New Item Tracking Run w Invoice
Range+Together].BOD_CODE, [New Item Tracking Run w Invoice
Range+Together].INVOICES, [New Item Tracking Run w Invoice
Range+Together].UNITS, [New Item Tracking Run w Invoice Range+Together].[Brn
Type], [New Item Tracking Run w Invoice Range+Together].[Invoice Bucket],
[Slpk_Wk Days].Slpk, [New Item Tracking Run w Invoice Range+Together].[Lead
Time], [Slpk_Wk Days].[Work Days], ([UNITS]/[Slpk])*([Lead time]/[Work Days])
AS [U/S*LT/W], [Safety Factor]^2.67 AS V, [Lead Time]^-0.283 AS L,
[INVOICES]^-0.264 AS I, 0.000083*[V]*[L]* AS LVI,
Sqr([U/S*LT/W]*(2*([UNITS])/([Slpk]*[INVOICES])-1)) AS [Safety Stock],
([Slpk]*(([U/S*LT/W]+[LVI]*([Safety Stock])))) AS [S L]
FROM ([New Item Tracking Run w Invoice Range+Together] LEFT JOIN [Safety
Factors All] ON [New Item Tracking Run w Invoice Range+Together].Together =
[Safety Factors All].Together) LEFT JOIN [Slpk_Wk Days] ON [Safety Factors
All].Together = [Slpk_Wk Days].Slpk;


Lucien said:
I need to make a left join between query New Item Tracking Run w Invoice
Range+Together and table Safety Factors All. I don't need to join the
Slpk_Wk Days table to anything.



KARL DEWEY said:
You will need to join [Slpk_Wk Days] in the SQL statement to make a left
join. But I do not see where the fields will relate.

Lucien said:
I am running a query that includes 2 tables and another query. I have one
field in a query [New Item Tracking Run w Invoice Range+Together] linked to a
like field in a table [Safety Factors All]. I would like this to be a LEFT
join so I can display all records from the [New Item Tracking Run w Invoice
Range+Together] query and the records that match in the [Safety Factors All]
table. Access will only allow me to create an INNER join. When I try to
create a LEFT join I get an error message that says there is an ambiguous
outer join. I am kind of a new access user and I do not know what I'm doing
wrong.
Below is the SQL from this query in it's current state with the INNER join:

Any help will be greatly appreciated

SELECT [New Item Tracking Run w Invoice Range+Together].PLANT, [New Item
Tracking Run w Invoice Range+Together].ITEM, [New Item Tracking Run w Invoice
Range+Together].BOD_CODE, [New Item Tracking Run w Invoice
Range+Together].INVOICES, [New Item Tracking Run w Invoice
Range+Together].UNITS, [New Item Tracking Run w Invoice Range+Together].[Brn
Type], [New Item Tracking Run w Invoice Range+Together].[Invoice Bucket],
[Slpk_Wk Days].Slpk, [New Item Tracking Run w Invoice Range+Together].[Lead
Time], [Slpk_Wk Days].[Work Days], ([UNITS]/[Slpk])*([Lead time]/[Work Days])
AS [U/S*LT/W], [Safety Factor]^2.67 AS V, [Lead Time]^-0.283 AS L,
[INVOICES]^-0.264 AS I, 0.000083*[V]*[L]* AS LVI,
Sqr([U/S*LT/W]*(2*([UNITS])/([Slpk]*[INVOICES])-1)) AS [Safety Stock],
([Slpk]*(([U/S*LT/W]+[LVI]*([Safety Stock])))) AS [S L]
FROM [Slpk_Wk Days], [New Item Tracking Run w Invoice Range+Together] INNER
JOIN [Safety Factors All] ON [New Item Tracking Run w Invoice
Range+Together].Together = [Safety Factors All].Together;
Click to expand...
Click to expand...
Click to expand...
 
L

Lucien

That did it for me.....I don't understand how or why that worked, but it did.

Thanks so much for your help, saved me hours of banging my head against the
desk!!!



KARL DEWEY said:
You must join it. Try this --
SELECT [New Item Tracking Run w Invoice Range+Together].PLANT, [New Item
Tracking Run w Invoice Range+Together].ITEM, [New Item Tracking Run w Invoice
Range+Together].BOD_CODE, [New Item Tracking Run w Invoice
Range+Together].INVOICES, [New Item Tracking Run w Invoice
Range+Together].UNITS, [New Item Tracking Run w Invoice Range+Together].[Brn
Type], [New Item Tracking Run w Invoice Range+Together].[Invoice Bucket],
[Slpk_Wk Days].Slpk, [New Item Tracking Run w Invoice Range+Together].[Lead
Time], [Slpk_Wk Days].[Work Days], ([UNITS]/[Slpk])*([Lead time]/[Work Days])
AS [U/S*LT/W], [Safety Factor]^2.67 AS V, [Lead Time]^-0.283 AS L,
[INVOICES]^-0.264 AS I, 0.000083*[V]*[L]* AS LVI,
Sqr([U/S*LT/W]*(2*([UNITS])/([Slpk]*[INVOICES])-1)) AS [Safety Stock],
([Slpk]*(([U/S*LT/W]+[LVI]*([Safety Stock])))) AS [S L]
FROM ([New Item Tracking Run w Invoice Range+Together] LEFT JOIN [Safety
Factors All] ON [New Item Tracking Run w Invoice Range+Together].Together =
[Safety Factors All].Together) LEFT JOIN [Slpk_Wk Days] ON [Safety Factors
All].Together = [Slpk_Wk Days].Slpk;


Lucien said:
I need to make a left join between query New Item Tracking Run w Invoice
Range+Together and table Safety Factors All. I don't need to join the
Slpk_Wk Days table to anything.



KARL DEWEY said:
You will need to join [Slpk_Wk Days] in the SQL statement to make a left
join. But I do not see where the fields will relate.

:

I am running a query that includes 2 tables and another query. I have one
field in a query [New Item Tracking Run w Invoice Range+Together] linked to a
like field in a table [Safety Factors All]. I would like this to be a LEFT
join so I can display all records from the [New Item Tracking Run w Invoice
Range+Together] query and the records that match in the [Safety Factors All]
table. Access will only allow me to create an INNER join. When I try to
create a LEFT join I get an error message that says there is an ambiguous
outer join. I am kind of a new access user and I do not know what I'm doing
wrong.
Below is the SQL from this query in it's current state with the INNER join:

Any help will be greatly appreciated

SELECT [New Item Tracking Run w Invoice Range+Together].PLANT, [New Item
Tracking Run w Invoice Range+Together].ITEM, [New Item Tracking Run w Invoice
Range+Together].BOD_CODE, [New Item Tracking Run w Invoice
Range+Together].INVOICES, [New Item Tracking Run w Invoice
Range+Together].UNITS, [New Item Tracking Run w Invoice Range+Together].[Brn
Type], [New Item Tracking Run w Invoice Range+Together].[Invoice Bucket],
[Slpk_Wk Days].Slpk, [New Item Tracking Run w Invoice Range+Together].[Lead
Time], [Slpk_Wk Days].[Work Days], ([UNITS]/[Slpk])*([Lead time]/[Work Days])
AS [U/S*LT/W], [Safety Factor]^2.67 AS V, [Lead Time]^-0.283 AS L,
[INVOICES]^-0.264 AS I, 0.000083*[V]*[L]* AS LVI,
Sqr([U/S*LT/W]*(2*([UNITS])/([Slpk]*[INVOICES])-1)) AS [Safety Stock],
([Slpk]*(([U/S*LT/W]+[LVI]*([Safety Stock])))) AS [S L]
FROM [Slpk_Wk Days], [New Item Tracking Run w Invoice Range+Together] INNER
JOIN [Safety Factors All] ON [New Item Tracking Run w Invoice
Range+Together].Together = [Safety Factors All].Together;
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

Help With Parameter Query 2
Pls Help - "between" Criterea returning no results 11
Customer Query 1
Help with Duplicate Output Alias Error 'Exp1' 0
Select Query with an IIf 2
Invoice Amt. - Payments = Balance 2
Problem - can create an updatable query 2
Query Problem 0

Top