Access query vs. Excel query
 

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.

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.

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

Access query vs. Excel query
 

your assment of the left join is correct. my question is
why you are re-writing the query? Access and excel
understand the same sql. are you accessing a different
database?
-----Original Message-----
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.

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.

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
.