I need to get a list of what products a customer has in our inventory but hasn't ordered in a certain amount of time

We provide parts to customers. Sometimes we keep parts in stock and ship them to them when they need them. I need to be able to ask my database what parts might be in stock for a particular customer that they haven't ordered in the past year (or whatever date). I have a table with my parts. I have a table with orders for those parts. How do I write my query so that I can tell it my customer, tell it the past year, and then it return all the parts that are in inventory that they haven't ordered in the amount of time I ask for?
Kevin SmithAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'd need to see your table structure, but you'd have a query something like this

SELECT * from
parts p join orders o on (p.partnumber = o.partnumber)
join customers c on (o.customernumber = c.customernumber)

p.inventory > 1
(SELECT max(orderdate) from orders where partnumber=p.partnumber) < DateAdd("yyyy", -1, Date())
Kevin SmithAuthor Commented:
It looks like this will just pick up something that was ordered before the selected date. I would need it to show only parts ordered that they haven't ordered in the past while. So if they had three parts (with one still in inventory), parta, partb and partc. They ordered parta, partb and partc over the past year, bu they ordered partb last month. If I put in 6/1/2015, I would want it to return parta and partc, but not partb.

Basically, I need it to compare all the parts for that customer against the orders for the past (insert date), and return ONLY the parts they haven't ordered since then.
Jeffrey CoachmanMIS LiasonCommented:
Then this might be a more complicated that your original question indicated...

Like mankowitz, I can get you a query that lists all the parts ordered and show the approximate time since they were last ordered.
(see the attached sample)

Do you need the answer here to be in the form of a "Complete Solution" or do you just need help with the query?

Your question is not very specific, so it is not clear how you will be specifying the Customer, the time frame, or how you are comparing the results to your existing inventory levels
For example, how are you defining a "Year"
365 days from the last OrderDate
Last Year (anything in 2014)
The same type of confusing will arise with terms like Weeks or Months.
To be on the safe side, you should simply specify the "days"
You can always create a system to translate the days into whatever date interval format you need.
(A translation table perhaps, or use a function)

You also, still have not posted your deign details, so again, it will be a lot more difficult to form a specifc "answer" here.

Here is a quick sample that lists the days since the last Order and "roughly" translates the days into Weeks, Months and years.
How this will intergrate this into your "inventory" system, may need to be an entirely different question/thread

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

I would have to test with some actual data, but the subselect

(SELECT max(orderdate) from orders where partnumber=p.partnumber)

should return null for cases where the person didn't buy anything, and a null comparison should be false. If not, you could replace it with

(SELECT count(*) from orders where partnumber=p.partnumber) > 1 AND (SELECT max(orderdate) from orders where partnumber=p.partnumber)
Jeffrey CoachmanMIS LiasonCommented:
Last Ordered
Jeffrey CoachmanMIS LiasonCommented:
Perhaps mankowitz has a better handle on this,

I will step aside now to avoid confusion.
Kevin SmithAuthor Commented:
Okay, there is very limited data in this, but I threw in some newer and older dates and a part number or two that should give a difference. Either way, here's what I need it to do.

Orders shows the customer along with the order. The OrderDet lists all parts on a particular order, as well as the DueDate for that part.

Estim shows all parts, the QtyOnHand, and the customer.

I will have a form that lets me pick a customer and put in a date to show any parts that were not ordered after that date. So if I put in SFI and 9/21/2014, I want it to look at the SFI parts on the Estim table and say "hey, here's all the parts you have in inventory SFI...here's the parts you haven't ordered since 9/21/2014." (not literally say it of course, just to return those results. Basically, it'll look at everything after 9/21/2014, then check the Estim table to see what hasn't been ordered.

I know how to do the forms and searches and all of that, so don't worry about that stuff.
Kevin SmithAuthor Commented:
Forgot to attach the file...
can you check the tables? The part numbers in estim don't correlate to the part numbers in the other tables.
I am not sure what the Estim table holds. It has qty on hand and a customer. What happens if two customers order the same part? Does it mean that the customer has this many parts on hand? How would you know that.

This query shows what parts are in the Estim table


If you want to know the parts that the customer is about to run out of (e.g. has less than 10 of), you could do this

SELECT PartNo FROM Estim WHERE CustCod="SFI" and Qty<10

This query will tell you all the parts ordered by SFI after 1/1/2015

FROM ORDERS o JOIN PartNumbersOrdered p ON (o.OrderNo=p.OrderNo)
WHERE o.CustCode="SFI" AND p.DueDate > #1/1/2015#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.