Date sorting but only on some records (1 Viewer)

[Gavx]

My table (tblEqtyTrans) is a list of share transactions with records containing equity id and whether it was a Sell or Buy.

I want to be able to extract all equity sales for a given date range, and all the transactions for that equity through time. In excel I would have 2 sort levels, the first being the equity name, the second would be the transaction date.

Sure I can create a query that can be sorted in this manner but I cannot specify a date range for just the sales date.

I am not sure how to do this; whether it needs to involve more sophisticated queries or fields calculated at run time or even VBA.

Can someone nudge me in a direction that would help please.

thanks

 

[arnelgp]

does your Sales Date has time element on it?
you can use DateValue([dateField]) to only return the Date part
of the date field.
in query:

select * from table where datevalue([datefield]) = #10/03/#2020#

 

[Pat Hartman]

The criteria for the two sets would be something like:

Where (Type = "Sales" And SaleDate Between Forms!yourform!StartDate And Forms!Yourform!EndDate) OR (Type <> "Sales")

Pay attention to the parentheses. They are necessary to ensure that the expression is evaluated correctly.

 

[Gavx]

Along those lines it would in principle be;
Where (Type = "Sales" And SaleDate Between Forms!yourform!StartDate And Forms!Yourform!EndDate)
and for each equity returned from that query extract all records for that equity prior to the SalesDate of that equity. It is that part I am having trouble with,

 

[namliam]

So make your first query

Select equity
from yourTable 
Where (Type = "Sales" And SaleDate Between Forms!yourform!StartDate And Forms!Yourform!EndDate)

Store this query as "qrySalesPeriod"
Now make a second query:

Select *
from yourTable
where equity in ( Select equity from qrySalesPeriod)

or

Select *
from yourTable
Inner join qrySalesPeriod on yourTable.equity = qrySalesPeriod.equity

This however will also or potentialy return the future records which you may or may not want.
If you dont want this simply add in the second query
SaleDate <= Forms!Yourform!EndDate

 

[arnelgp]

SELECT * FROM yourTable WHERE yourTable.Equity IN
    (SELECT yourTable.Equity FROM yourTable Where ((yourTable.Type) = "Sales") And
        ((yourTable.SaleDate) Between Forms!yourform!StartDate And Forms!Yourform!EndDate)
        GROUP BY yourTable.Equity)
    AND SalesDate < Forms!yourform!StartDate;