Need help with a query if another worksheet column contains a value

[cpjeffm]

Ok need some help here. I'm working on a spreadsheet to show sales in a specified date range but only if the stock number in the database table falls in a range of stock numbers from a different worksheet.

Here's the way I need it to work.

I have a StockNumbers worksheet and I'm running a query on it to pull in any stock numbers entered in a specified date range. I have a second PartSales worksheet where I'm querying all sales in a specified date range. Problem is, the sales are from all stock numbers, regardless of when they were entered so when I query the sales, I need to compare the list of stock numbers on the PartSales worksheet to the list of stock numbers on the StockNumbers worksheet and only show sales for those stock numbers.

Basically what I'm looking for is a report that I can run for stock numbers that were entered between, say 1/1/2012 and 12/31/2012 and then I want to see all my sales for parts on those stock numbers for say 1/1/2013 thru 12/31/2013.

Any help would be greatly appreciated.

 

[SkipVought]

HI,

Please post the SQL that you have for these two queries.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

Here is the SQL for the StockNumbers...

SELECT InventoryMain.Stock, InventoryMain.DateEntered,
FROM SQLUser.InventoryMain InventoryMain
WHERE (InventoryMain.DateEntered Between ? And ?)

Here is the SQL for the PartSales...

SELECT Sales.Invoice, Sales.StockNum, Sales.Part, Sales.SalePrc, Sales.RecDate,
FROM SQLUser.Sales Sales
WHERE (Sales.RecDate Between ? And ?)

 

[SkipVought]

Assuming that Stock and StockNum are relatable (same data type and logically identical)...

[pre]
SELECT InventoryMain.Stock, InventoryMain.DateEntered, Sales.Invoice, Sales.Part, Sales.SalePrc, Sales.RecDate

FROM SQLUser.InventoryMain InventoryMain, SQLUser.Sales Sales

WHERE InventoryMain.Stock = SQLUser.Sales Sales
And (InventoryMain.DateEntered Between ? And ?)
And (Sales.RecDate Between ? And ?)

[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

Problem is they want two separate lists. They want to see the Stock Numbers entered on a separate worksheet than the sales.

 

[Andrzejek]

You mean:
[tt]
WHERE InventoryMain.Stock = [highlight #FCE94F]Sales.StockNum[/highlight]
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Duh, yes!!!

I am recovering from cataract surgery and not yet seeing clearly. Thank you, Andy!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}