Difficulties with getting the right responce from my queries

ignite

Registered User.
Local time
Today, 00:37
Joined
Mar 15, 2004
Messages
31
Hello,

I am having difficulties with working out how to create a correct query from my database. :confused:

These are the two results I require:

  • What strength of whisky sells the best at Christmas (How do I do an 'In-Between' date condition regardless of the year? i.e. >= #01/12/____# AND <= #31/12____#)
  • Which companies have not purchased at least one bottle from each region?
However, I am not sure how I would go about getting these specific results from the database. I have done serveral previous questions based on the database (which can be seen in the .mdb file) however these two are proving troublesome. I have attached the database to this post if anyone who is at ease with queries would be kind enough to have a quick peeky at it.

Any help would be greatly appreciated !

Note. the database information is fictisious. :rolleyes:
 

Attachments

Date query:

SELECT Order.OrderID, Order.OrderDate
FROM [Order]
WHERE (((Format([OrderDate],"mm"))=12));

This'll give you the orders in dec.

--------

SELECT Company.CompanyID, Whisky.RegionID
FROM (Region INNER JOIN Whisky ON Region.RegionID = Whisky.RegionID) INNER JOIN (Bottling INNER JOIN (((Company INNER JOIN Location ON Company.CompanyID = Location.CompanyID) INNER JOIN [Order] ON Location.LocationID = Order.LocationID) INNER JOIN OrderLine ON Order.OrderID = OrderLine.OrderID) ON Bottling.BottlingID = OrderLine.BottlingID) ON Whisky.WhiskyID = Bottling.WhiskyID
GROUP BY Company.CompanyID, Whisky.RegionID;

save as "QryRegionCount_Grouped"

Then this query will tell you which company hasn't from all regions....is based on "QryRegionCount_Grouped"....

SELECT Company.CompanyName, Count(QryRegionCount_Grouped.RegionID) AS CountOfRegionID
FROM QryRegionCount_Grouped INNER JOIN Company ON QryRegionCount_Grouped.CompanyID = Company.CompanyID
GROUP BY Company.CompanyName
HAVING (((Count(QryRegionCount_Grouped.RegionID)) Not In (SELECT Count(Region.RegionID) AS CountOfRegionID
FROM Region)));
 

Users who are viewing this thread

Back
Top Bottom