Left JOINS in multiple table MSQuery in Excel
 

"dutty" wrote ...

Your Excel version is using an INNER JOIN (you've reinvented the old
style of inner join <g) rather than a LEFT JOIN.

From a logical point of view, to make you 'Access' version work in
Excel, it would appear you need to change the tables in your FROM
clause as follows e.g.

Customer

to

EbscoJB.dbo.Customer Customer

So I think your entire FROM clause should be:

FROM ((EbscoJB.dbo.Customer Customer INNER JOIN (EbscoJB.dbo.Job Job
INNER JOIN EbscoJB.dbo.Material Material ON Job.Part_Number =
Material.Material) ON Customer.Customer = Job.Customer) INNER JOIN
EbscoJB.dbo.Delivery Delivery ON Job.Job = Delivery.Job) LEFT JOIN
EbscoJB.dbo.Material_Location Material_Location ON Material.Material =
Material_Location.Material

Jamie.

--

I have been given an existing Access query and I am attempting to duplicate it in Excel. I have gotten close, but I have virtually no experience with SQL, and I am guessing that's where the answer lies.

The reasons for recreating are a mesh of different reasons, actually.
I have created a second sheet in an Excel workbook that includes another query for a seperate, but similar purpose. I am packaging the two together for ease of use.
At first, I copied the sql from the Access query and pasted into MSQuery to attempt to just duplicate the data there, but ran into a problem. In doing that I get an error: "Could not add the table '((Customer'."

Thus, I attempted to recreate the query as best as I knew how, and then encountered the record return problem.

Here is the SQL from the Access query:

SELECT DISTINCTROW Delivery.Promised_Date, Customer.Name, Job.Part_Number, Delivery.Promised_Quantity, Delivery.Job, Job.Customer_PO, Job.Customer_PO_LN, Job.Drawing, Delivery.Comment, Delivery.Packlist, Job.Status, Material_Location.Location_ID, Material_Location.On_Hand_Qty
FROM ((Customer INNER JOIN (Job INNER JOIN Material ON Job.Part_Number = Material.Material) ON Customer.Customer = Job.Customer) INNER JOIN Delivery ON Job.Job = Delivery.Job) LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
WHERE (((Delivery.Promised_Date) Between [Start Date] And [End Date]) AND ((Delivery.Packlist) Is Null) AND ((Job.Status)="Complete" Or (Job.Status)="Active"))
ORDER BY Delivery.Promised_Date, Customer.Name, Job.Part_Number;

And here is the SQL from the Excel query that I developed:

SELECT Delivery.Promised_Date, Delivery.Job, Customer.Name, Job.Part_Number, Material.Description, Delivery.Promised_Quantity, Material_Location.On_Hand_Qty, Material_Location.Location_ID, Material_Location.Lot, Job.Ship_Via, Job.Customer_PO, Job.Customer_PO_LN, Job.Status
FROM EbscoJB.dbo.Customer Customer, EbscoJB.dbo.Delivery Delivery, EbscoJB.dbo.Job Job, EbscoJB.dbo.Material Material, EbscoJB.dbo.Material_Location Material_Location
WHERE Job.Customer = Customer.Customer AND Delivery.Job = Job.Job AND Job.Part_Number = Material.Material AND Material.Material = Material_Location.Material AND ((Delivery.Promised_Date Between ? And ?) AND (Delivery.Packlist Is Null) AND (Job.Status In ('Active','Complete')))
ORDER BY Delivery.Promised_Date, Customer.Name, Material_Location.Location_ID

The problem I am experiencing is that the Access Query returns the
data that I need. The Excel query does not return a record if the
"On_Hand_Qty" field is blank. Thus, for the same date range, I get 36
records in Access, and 29 records in Excel. I need to see all the
data, regardless of whether the "On_Hand_Qty" field contains data or
not. The data is being pulled from the same database in both
queries.

I know the problem exists in the joins of the two queries, but I don't know how to get over this hurdle. The LEFT JOIN in the Access query is the tangible that gives me the extra 6 records. I have tried to rewrite the SQL for the Excel query using the same JOINS as the Access query, but it doesn't work.

HELP!!

d

Left JOINS in multiple table MSQuery in Excel
 

I tried editing the sql to reflect this, and I receive the error:
"Parameters are not allowed in queries that can't be displayed graphically."

Is there any other way to view a selected date range in this type of setup?

d

"Jamie Collins" wrote:

"dutty" wrote ...

Your Excel version is using an INNER JOIN (you've reinvented the old
style of inner join <g) rather than a LEFT JOIN.

From a logical point of view, to make you 'Access' version work in
Excel, it would appear you need to change the tables in your FROM
clause as follows e.g.

Customer

to

EbscoJB.dbo.Customer Customer

So I think your entire FROM clause should be:

FROM ((EbscoJB.dbo.Customer Customer INNER JOIN (EbscoJB.dbo.Job Job
INNER JOIN EbscoJB.dbo.Material Material ON Job.Part_Number =
Material.Material) ON Customer.Customer = Job.Customer) INNER JOIN
EbscoJB.dbo.Delivery Delivery ON Job.Job = Delivery.Job) LEFT JOIN
EbscoJB.dbo.Material_Location Material_Location ON Material.Material =
Material_Location.Material

Jamie.

--

I have been given an existing Access query and I am attempting to duplicate it in Excel. I have gotten close, but I have virtually no experience with SQL, and I am guessing that's where the answer lies.

The reasons for recreating are a mesh of different reasons, actually.
I have created a second sheet in an Excel workbook that includes another query for a seperate, but similar purpose. I am packaging the two together for ease of use.
At first, I copied the sql from the Access query and pasted into MSQuery to attempt to just duplicate the data there, but ran into a problem. In doing that I get an error: "Could not add the table '((Customer'."

Thus, I attempted to recreate the query as best as I knew how, and then encountered the record return problem.

Here is the SQL from the Access query:

SELECT DISTINCTROW Delivery.Promised_Date, Customer.Name, Job.Part_Number, Delivery.Promised_Quantity, Delivery.Job, Job.Customer_PO, Job.Customer_PO_LN, Job.Drawing, Delivery.Comment, Delivery.Packlist, Job.Status, Material_Location.Location_ID, Material_Location.On_Hand_Qty
FROM ((Customer INNER JOIN (Job INNER JOIN Material ON Job.Part_Number = Material.Material) ON Customer.Customer = Job.Customer) INNER JOIN Delivery ON Job.Job = Delivery.Job) LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
WHERE (((Delivery.Promised_Date) Between [Start Date] And [End Date]) AND ((Delivery.Packlist) Is Null) AND ((Job.Status)="Complete" Or (Job.Status)="Active"))
ORDER BY Delivery.Promised_Date, Customer.Name, Job.Part_Number;

And here is the SQL from the Excel query that I developed:

SELECT Delivery.Promised_Date, Delivery.Job, Customer.Name, Job.Part_Number, Material.Description, Delivery.Promised_Quantity, Material_Location.On_Hand_Qty, Material_Location.Location_ID, Material_Location.Lot, Job.Ship_Via, Job.Customer_PO, Job.Customer_PO_LN, Job.Status
FROM EbscoJB.dbo.Customer Customer, EbscoJB.dbo.Delivery Delivery, EbscoJB.dbo.Job Job, EbscoJB.dbo.Material Material, EbscoJB.dbo.Material_Location Material_Location
WHERE Job.Customer = Customer.Customer AND Delivery.Job = Job.Job AND Job.Part_Number = Material.Material AND Material.Material = Material_Location.Material AND ((Delivery.Promised_Date Between ? And ?) AND (Delivery.Packlist Is Null) AND (Job.Status In ('Active','Complete')))
ORDER BY Delivery.Promised_Date, Customer.Name, Material_Location.Location_ID

The problem I am experiencing is that the Access Query returns the
data that I need. The Excel query does not return a record if the
"On_Hand_Qty" field is blank. Thus, for the same date range, I get 36
records in Access, and 29 records in Excel. I need to see all the
data, regardless of whether the "On_Hand_Qty" field contains data or
not. The data is being pulled from the same database in both
queries.

I know the problem exists in the joins of the two queries, but I don't know how to get over this hurdle. The LEFT JOIN in the Access query is the tangible that gives me the extra 6 records. I have tried to rewrite the SQL for the Excel query using the same JOINS as the Access query, but it doesn't work.

HELP!!

d

Left JOINS in multiple table MSQuery in Excel
 

"dutty" wrote ...

I tried editing the sql to reflect this, and I receive the error:
"Parameters are not allowed in queries that can't be displayed graphically."

What has a LEFT JOIN got to do with parameters?!

Is there any other way to view a selected date range in this type of setup?

Yes. In the database, create a procedure which accepts parameters and
call the procedure from MS Query e.g. (a simpler query):

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

This can be called from MS Query using e.g.

EXEC MyStoredProc '01 JUL 2004', '31 JUL 2004'

Subject to permissions, you may even get away with running the CREATE
PROCEDURE from within MS Query!

Jamie.

--