complex query

[koolguy]

hi,

im working on qureries in ms-access and need a way of writing a query to achieve the following:

With Reference to the table below:

Product_ID Order_date Despatch_Date
001 01/12/2007 05/12/2007
002 05/11/2007 15/11/2007
001 05/01/2008 09/01/2008
002 07/01/2008 -
003 10/02/2008 15/02/2008
004 03/01/2008 15/01/2008


can anyone help me design a query that gives me the product_ids that have been 'ordered and despatched' before Jan 2008 and have been re-ordered after jan 1st 2008 ?

The result shall be something similar to this:

Product_ID Old:Order_date Despatch_Date New:Order_date
001 01/12/2007 05/12/2007 05/01/2008
002 05/11/2007 15/11/2007 07/01/2008

Any help shall be much appreciated!

 

[KenHigg]

Assuming the dispatch date will always be equal to or after the order date, couldn't you simply ask for all records where the dispatch date is less than 1/1/2008 and the new order data is equal to or greater than 1/1/2008?

 

[DCrake]

Simple Software Solutions

You can logically remove one condition from the criterion purely on the basis of data validation.

If your have checked that you cannot despatch something before it has been ordered then you can remove this

However what type of stock system are you employing? LIFO, FIFO, AVCO,ETC?

Can you have zero stock levels?

 

[khawar]

Check out the attached sample i have added one more date to your data to show you how will it look like if products have more than one order after specified date

In this sample i used fixed dates however you can use flexible parameters to check the report for any date

if you have other questions let me know

regards

Khawar

 

[koolguy]

hi KenHigg, your suggestion is quite pleasing, but it gets very complicated as i need to work on the last despatch date of a product_id (in the previous year) and the very first order date of a product_id in the following year.


Khawar ... many thanks for the db.. the results look promising. But can you help me get the following results (as columns mentioned in the attached document) :

product_id,
order_date(dates of 2007),
despatch_date(dates of 2007)
order_date(dates of 2008)

without using the 'cross tab' option ?

 

[khawar]

Hi Dear

I have some confusion.

Your Structure is Like This


ProdId OldOrderDate DespatchDate NewDate


what if there were more than one orders before january I am assuming here that you need last OldOrderDate before a specific date

Second Confusion is whether you need a single "NewOrderDate" after that specific date or all dates of orders after that specific that

In case you need only a single new OrderDate would it be the first date after that specific that or the most recent order date

However i have worked out all the above mentioned scenerios coz tonight I am free and have a lot of time

Last thing i worked out is to put allNewDates in a single column using some VBA



Dont forget to add to my reputation if it helps you

regards

Khawar

 

[koolguy]

brilliant stuff, thanx alot khawar.. ......your db gave me the exact solution that i needed.

Once again thankyou very much!

 

[khawar]

You are always welcome KoolGuy