Problem with SQL statement in Microsoft Access

[HB25]

Dear Friend
I am trying to perform the flowing tasks using the following SQL statement in Microsoft Access but they don’t work. I wonder if someone could help and tell me what is wrong with my Statements. Whenever I run these statements it will not create the query.

Task 1: Formulate a query and prepare a report to find order details for a selected warehouse on a specific date, this will help the warehouse gather and pack all goods ready for the courier.

SQL statement I am using is:
SELECT * FROM Order WHERE WarehouseID='Manchester' AND Date='02/06/2008';

Task2: Formulate a query and prepare a report so that a specific warehouse can check their stock if any quantities of stock fall below 5, stock description should also be listed.

SQL statement I am using is:
SELECT* Description, ProductsID FROM Products WHERE Qty < 5;

Kind Regards
HB25

[tesuji]

Dear friend

. . .
Task 1: Formulate a query and prepare a report to find order details for a selected warehouse on a specific date, this will help the warehouse gather and pack all goods ready for the courier.

Task2: Formulate a query and prepare a report so that a specific warehouse can check their stock if any quantities of stock fall below 5, stock description should also be listed.
. . .
HB25

One can help you only if you show the corresponding data model your tasks are based on!

krs,
tesu

p.s. your selects seem to be rather imperfect.

[HB25]

Thank you tesuji for your quick reply please find attached my database as requested.
Thanks for all your help
HB25

[tesuji]

Hi HB25,

Your first select should look like:

select c.CustomerID, c.Surname, c.Address, c.Town, c.Postcode, 
o.OrderID, o.Date, 
 i.ProductID, i.Qty, p.Description from Customer c 
   join Order o on c.CustomerID = o.CustomerID
     join Item i on o.OrderID = i.OrderID
       join Product p on i.ProductID = p.ProductID
         WHERE WarehouseID='Manchester' AND Date='02/06/2008'

I didn't test it. It should work on all databases which supply standard SQL 1999. As far as I know, Access also supports inner joins. If not, let me know. In this case the joins must be replaced.

Btw, there should be done some improvement of your data model:
There must be an 1:m relationship between Stock and Product: Stock ->------- Product
Primary key of Item must be ProductID and OrderID (the thing with composite and foreign keys)
What is CustID of Item?
Table name Order should be replaced by another name because ORDER is SQL word ( ORDER BY ...)

krs,
tesu

[HB25]

Hi Tesuji
I have tried your code but it does have a problem with the word (join). Any more suggestions.
Regards
HB25

[tesuji]

... I have tried your code but it does have a problem with the word (join)...
HB25

Sorry, I thought Access would have been able to handle joins. The inner joins you need can be replaced by cross products and join conditions in where clause, try this:

select c.CustomerID, c.Surname, c.Address, c.Town, c.Postcode, 
o.OrderID, o.Date, i.ProductID, i.Qty, p.Description 
 from Customer c, Order o, Item i, Product p  
   where c.CustomerID = o.CustomerID
    and  o.OrderID = i.OrderID
      and i.ProductID = p.ProductID
        and WarehouseID='Manchester' AND Date='02/06/2008'

krs,
tesu

[TCBW]

Dear Friend
Task 1: Formulate a query and prepare a report to find order details for a selected warehouse on a specific date, this will help the warehouse gather and pack all goods ready for the courier.

SQL statement I am using is:
SELECT * FROM Order WHERE WarehouseID='Manchester' AND Date='02/06/2008';

Task2: Formulate a query and prepare a report so that a specific warehouse can check their stock if any quantities of stock fall below 5, stock description should also be listed.

SQL statement I am using is:
SELECT* Description, ProductsID FROM Products WHERE Qty < 5;

Task 1

SELECT Customer.CustomerID, Customer.Surname, Customer.Address, 
    Customer.Town, Customer.[Post code], 
    Order.OrderID, Order.Date, Item.[Products ID], 
    Item.Qty, Products.Description, 
    Products.[Unit Price], Order.WarehouseID
        FROM Products 
        INNER JOIN ((Customer 
        INNER JOIN [Order] ON Customer.CustomerID = Order.CustomerID) 
        INNER JOIN Item ON Order.OrderID = Item.[Order ID]) ON Products.ProductsID = Item.[Products ID]
            WHERE (((Order.Date)=#2/6/2008#) AND ((Order.WarehouseID)="Manchester"));

You need to tell Access the format of the date. The # in the date tells the system it will be in the format MM/DD/YYYY. Also as you have used spaces in field names and reserved words (ORDER as mentioned by some one else) you need to surround these with square brackets [ & ].

Task 2

SELECT Description, ProductsID FROM Products WHERE Qty < '5';

Your table has the quantity field set as type text, so the statment where Qty <5 will not work. You can either change the type of field and use the original where clause or use the one above. Please note that the original Select had a syntax error you should drop the *.

Regards
TCBW

[HB25]

Hi TCBW
Thank you for replying to me post, I have fallowed your suggestion the query will run but it will not display any information under the CustmoerID, Surname ......... ext any Idea why? Have we forgotten anything?
Regards
HB25

[Ole Raptor]

looking in your db i noticed that you do not have any orders in February. This will be one reason why you cant pull anything from the first query. change the date to 6/2/2008 and BAM its full of data

SELECT Customer.*, Order.*
FROM Customer INNER JOIN [Order] ON Customer.CustomerID = Order.CustomerID
WHERE (((Order.Date)=#6/2/2008#) AND ((Order.WarehouseID)="manchester"));

the second query works good with this

SELECT *
FROM Products
WHERE (((Qty)<"5"));

[HB25]

Dear friend (ProfessorPC , TCBW, tesuji)

Thank you very much for all your comment and suggestion, I have managed to perform both of my tasks using the fallowing SQL command fallowing your suggestions. You guys did a fantastic job

Task 1 solution:

SELECT Customer.CustomerID, Customer.Surname, Customer.Address, Customer.Town, Customer.[Post code], Order.OrderID, Order.Date, Order.WarehouseID
FROM Customer INNER JOIN [Order] ON Customer.CustomerID = Order.CustomerID
WHERE (((Order.Date)=#6/2/2008#) AND ((Order.WarehouseID)="manchester"));

Task2 solutions:

SELECT [Stock].[WareHouseID], [Stock].[ProductsID], [Stock].[Qty], [Products].[Description]
FROM Stock INNER JOIN Products ON [Stock].[ProductsID]=[Products].[ProductsID]
WHERE ((([Stock].[Qty])<5) And (([Stock].[WarehouseID])="London"));

I have opened a new thread (http://www.daniweb.com/forums/post622145.html#post622145)regarding Report in Microsoft access I wonder if you guys could help on that as well, this will be the last requirement I have to do. Any suggestion, comment and advice will be really appreciated.

Kind Regards
HB25