stocks not traded

[genevx]

hi,
i need to make a query of stocks that HAVEN'T been traded within a time range. There are two tables, one telling me trade transactions, and another telling me what trader is responsible for what stock. if one of the stocks on the second table doesn't show up within the time range for the trader on the first table, it should be listed down in my query. i've beaten myself to death over this, but i just can't quite grasp the logic in doing this. any ideas?

thanks!
genevx

 

[The_Doc_Man]

OK, this can be done pretty easily without code in two steps as a VERY straight-forward operation. It could be done in one query but the syntax is trickier.

I'm not clear on your table structure so I'll fudge this a bit...

If you have a table that lists stocks independent of who is responsible for them then use that table. If the trader table is set up such that you NEVER have one stock managed by two traders because that cannot happen, you could use the trader table.

Query 1: Join your stock table to your transaction table using a join that allows for an untraded stock to show up with blanks. I.e. JOIN showing ALL stocks and ANY matching transactions.

Make sure you show your stock and your transaction date. Make the transaction date field into something like

Nz( [XactDate], "#1-Jan-1900#" )

Now click the Sigma key in the query toolbar to make this a totals query. In the stock column, totals row, click GroupBy. In the transaction date column, totals row, click Max


Query 2: Use query 1 as the source. Select the stock and the transaction date. Don't click the Sigma key. Instead, put a criteria of

< # 1-Jun-2002 #

or whatever you want for your critical date.

When you open Query 2 in datasheet view, you will see only those stocks for which the last (Max) transaction date precedes your chosen date.

If that date comes from the trader table, then your trader and stocks tables would have to both be participants in the query. In that case, the criteria would be

< [trader].[critdate]

or something much like that.

 

[genevx]

hmm...not quite understanding this procedure...(not you, it's my stupidity ={ )... let me put this in another way...if the stock doesn't show up on any of the transactions on the transaction table, then it'll be listed on the report. it uses the second table (the stock table) as reference as to what stocks it should be looking for. how would i do this, without all the date stuff?
thank you sooooo much for your help! sorry for being dense!

genevx

 

[genevx]

whoo hoo...never mind...found it out... thanks! =)