Access query vs. Excel query

G

Guest

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
 
F

Frank Stone

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
Click to expand...
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,
Click to expand...
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
Click to expand...
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
Click to expand...
[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,
Click to expand...
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
Click to expand...
Delivery, EbscoJB.dbo.Job Job, EbscoJB.dbo.Material
Material, EbscoJB.dbo.Material_Location Material_Location
WHERE Job.Customer = Customer.Customer AND Delivery.Job =
Click to expand...
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
Click to expand...
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
Click to expand...
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.
 

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

Update Query from Excel VBA module 2
Can't run Query, can't build formula 2
Help with Access query from Excel 3
WinSQL Query - Syntax for use in Excel 3
Access query does not work in Excel 1
Open Access query from Excel using parameter 3
Complex Query Not Able To Get Results in Excel 2
ADO: run a stored Access query from Excel 1

Top