Query Assistance

[sydeburnz]

First time poster, so I hope someone shows me the light and makes your forums shine!

I have a POS system that was designed in house which uses Access. I am trying to create some queries outside of the system to retrieve some data that has been being stored. I am basically wanting to know which items have been in stock for over 6 months and have never sold.

I have a query designed to show me this data for one store at a time, but nothing that shows me if an item is so poor it has never sold at any stores. This is my single store query:

SELECT Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.Category2
FROM Inventory_ProductTable
WHERE (((Inventory_ProductTable.Quantity)>0) AND ((Inventory_ProductTable.LastBuyDate)<Date()-"180") AND ((Inventory_ProductTable.masterStoreID)="CDT001") AND ((Inventory_ProductTable.DepartmentID)=1) AND ((Inventory_ProductTable.LastSaleDate)=""));

It is pulling the artist, title and category of the item out of the database to display the informtion that I am needing (for the most part). Quantity of 0 and last sale date "" basically means there is no record of it being sold, so this gives me my zero quantity sold criteria for anthing that has been in stock for 6 months or more (the last buy date criteria).

I am not sure what other info I need to provide, but I would be happy to do so. I am a n00b at this and really love to learn, so lay it on me!

thanks in advance.

 

[oerris]

Hi sydeburnz.

It looks to me as if all you need to do is ommit the part of the query that filters out the single store (masterStoreID and perhaps DepartmentID).

Your query should then lokk like this:

SELECT Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.Category2
FROM Inventory_ProductTable
WHERE (((Inventory_ProductTable.Quantity)>0) AND ((Inventory_ProductTable.LastBuyDate)<Date()-"180") AND ((Inventory_ProductTable.DepartmentID)=1) AND ((Inventory_ProductTable.LastSaleDate)=""));

or perhaps ...

SELECT Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.Category2
FROM Inventory_ProductTable
WHERE (((Inventory_ProductTable.Quantity)>0) AND ((Inventory_ProductTable.LastBuyDate)<Date()-"180") AND ((Inventory_ProductTable.LastSaleDate)=""));

 

[sydeburnz]

actually, the data that I am looking for is what has never sold. the first query gets me that data per store. i got the data by showing only items that have no data recorded in the 'lastBuyDate' field. If something had sold, no matter how long ago, it would have a date. This is my primitive 'dog' list.

My challenge is trying to find out which dogs are universal to all stores. Would it be easier to build this query for each store, then have another query that hits the store queries to look for duplicates?

Then my part 2 is getting the category2 data which is recorded as a number hit a seperate databases which has the text description for each category. For instance 002=Pop Rock, 004=Country, etc.

Thanks again.

 

[sydeburnz]

On the category thing, I cannot unfortunately. As a last resort, I can do a edit/replace all in Excel when I am done with the initial project

 

[sydeburnz]

uncle gizmo, you are almost with me. what I was looking for are the items that have never sold in any of the stores. Basically, if they have an item that was included in their original inventory when the store was opened and it still has not sold to date.

i did make some headway yesterday, but could still use a bit of assistance. this is what i have got:

SELECT Inventory_ProductTable.UPC, Count(Inventory_ProductTable.UPC) AS CountOfUPC, Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.DepartmentID
FROM Inventory_ProductTable
GROUP BY Inventory_ProductTable.UPC, Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.DepartmentID, Inventory_ProductTable.LastSaleDate
HAVING (((Inventory_ProductTable.Quantity)>0) AND ((Inventory_ProductTable.DepartmentID)=1) AND ((Inventory_ProductTable.LastSaleDate)=""))
ORDER BY Count(Inventory_ProductTable.UPC) DESC;

what this query gave me is a list of UPC numbers, the count tells me how many stores this UPC is present, the artist, the title, the quantity they currently have in stock and the department number. i really dont need to show the department number, but needed it to tell me what i wanted for a specific department.

since I got that, im pretty much there. what would be nice to be able to do is know which stores have each of these UPCs in stock. is there a way to add another field to the end of the query that would list multiple data? like having a field on the end for masterStoreID and instead of showing each one as a seperate row in the returned results and losing my UPC count data, having the UPC show up once, but list all the stores that contain the UPC in the masterStoreID field. I hope that makes sense.

masterStoreID
cdt001,cdt003,etc