Query with a few conditions

[mreference]

I'm trying to build a query but am having some difficulty, each part works independantly but not together in one query.

I have two tables and an optional table (details at bottom of thread for this)
tblStockItems (Fields: itemID, itemcode, quantity)
tblItemsSold (Fields: itemcode, datesold)
OPTIONAL: tblSource (Fields: itemID, CostPrice, Primary)

I want a query that will list all items from tblStockItems that have not yet been sold with a quantity over 10 as well records from tblItemsSold that have not sold for 30 days with a quantity over 10.

This will give us a list of items that haven't sold yet or have stop selling for for some reason.

OPTIONAL TABLE
If the query above can be created that to start with is perfect, following on from that, if possible I would like to add in the optional table tblSource, so that I could get a value of the stock items in question (multiplying the cost price by quantity).

However, this table may have 1 or more sources supplying the stock item to us, and all with a different price. In this instance I would need the cheapest price to be selected and calculated.

I have tried and got both working seperately but not together and have no idea how to build in the third, it threw up some ridiculous calculations.

 

[Brianwarnock]

Sometimes, actually quite often, it is not possible to do everything in one query.

without an mdb to test on I can only suggest that maybe

your 2 queries will be Unioned to get the initial result .this will be joined on itemid to the optional table were a Totals query grouped on itemid and min price will be created and this will be joined back to the stockitems table to get the stock value.

Brian

 

[plog]

I want a query that will list all items from tblStockItems that have not yet been sold with a quantity over 10 as well records from tblItemsSold that have not sold for 30 days with a quantity over 10.

Your confusing the relationship. You make it sound like the first set of data comes from tblStockItems and the second set of data comes from tblItemSold. That shouldn't be the case--this data should all come from the same source, it just has 2 different criteria.

Can you post some sample data from both tables and then what you want the result to be based on that sample data?

 

[mreference]

I have attached a sample db, I think I have captured the fields that would be relevant.

The results I would hope to see are

item qty cost of stock
iPod 49 £2,401 //primary/cheapest is £49 - as last one was sold over 30 days
iMac 20 £8,800 //primary/cheapest is £440 - none have been sold

appreciate any help you can provide

 

[plog]

As I see it, this is your criteria:

Products not sold within the last 30 days.

That correct? In your initial post you mentioned something about Quantity fields. Is that quantity sold or quantity on hand?

 

[mreference]

Products not sold within the last 30 days.

For part of the query as items that haven't sold at all will not have any history e.g. iMac

Is that quantity sold or quantity on hand?

Quantity on hand to calculate the value we are holding.

So, the query is to show what stock is not selling and is broken down into 2 parts

1. Part of the query will calculate what has not sold for 30 days based upon the ItemsSold table and in this example it would return 1 result :
(Item: iPod | Qty: 49 | Value: £2,401) as it has been sold, but over 30 days ago.

2. Where an item hasn't been sold at all (e.g.iMac) and is not in the ItemsSold table, we just need to list the stock that has a Quantity greater than 0 (Item: iMac| Qty: 20 | Value: £8,800) as some items we have sell have zero in stock as we only order in when we need it.

Hope that helps, thanks again

 

[plog]

It helps very little. So is Quantity part of the criteria or not? Or is it just a value you want displayed? What if a product was last sold 60 days ago but now has 0 in stock? Should that be displayed?

Again, should the criteria be this:

All products not sold within the last 30 days.

Before you talk about products never sold and try on keep dividing this into 2 parts, think about what I keep trying to get you to say is the criteria.

 

[mreference]

I'm not getting it, the quantity is part of the criteria as we list thousands of items in the table and hundreds of them will show zero stock so they will not need to be shown, similarly, the quantity also needs to multiplied by the cheapest supplier of the goods to give an estimate of stock we are holding.

Yes, the criteria is about products not selling in 30 days

cheers

 

[plog]

Almost there. So quantity is part of the criteria? I'm still trying to figure out if your criteria is two parts or not.

What if a product was last sold 60 days ago but now has 0 in stock? Should that be displayed?

 

[mreference]

So quantity is part of the criteria?

Definately part of criteria

What if a product was last sold 60 days ago but now has 0 in stock? Should that be displayed?

Not displayed, the results are to display what is moving slowly or not at all, so we can identify them and make some decisions.

 

[plog]

When doing something like this you need to first state it in the simplest terms possible. You trying to divide this into 2 seperate queries really made it difficult to identify what you wanted.

Here's how I would put it:

A list of items with a quantity greater than 0 that haven't sold in 30 days.

I didn't bring in all the data you wanted, but I built a query that will help you identify the items. This is that SQL:

SELECT tblStockItems.ItemCode, Min(IIf(IsNull([DateSold]),9999,DateDiff("d",[DateSold],Date()))) AS DaysSinceLastSold
FROM tblStockItems LEFT JOIN tblItemsSold ON tblStockItems.ItemCode = tblItemsSold.ItemCode
WHERE (((tblStockItems.Quantity)>0))
GROUP BY tblStockItems.ItemCode;

It produces a list of itemcodes from tblStockItems that have a quantity greater than 0 in tblStockItems and the number of days since their last sale in tblItemsSold. From it you should be able to build another query, apply the specific days since last sale criteria you want and bring in all the other pieces of data related to each itemcode.

Lastly, why do you have itemID field in tblStockItems? You are not using it, you are using the itemcode field as a foreign key in the other tables. Also, why is itemcode a text field when all itemcodes are numeric data? Do you have others that use non-numeric characters?

 

[mreference]

Awesome, appreciate the help that you provided, I have now managed to merge it into the database and it works great.

Thank you for your patience