query with two tables

[Guest]

Dears,

I have 2 tables.

One table with details on events, containing minimum 3 important fields viz.
event name, start date, end date. Dates are in DATE format. There are few
dozen events in this table.

I have another table with details on transactions. Each transaction has a
date of execution assigned. For the same date there can be several thousand
transactions.

I wanted to know how to write a query where as a result I get a view on all
transactions that fall between all the "event dates".

The reverse is also useful, a query that provides a view on all transactions
that fall outside all the "event dates".

Many thanks in anticipation of all help.

 

[Guest]

DKS:

Ideally, there should be a direct link between the two tables, e.g. the
Trans table will have an indexed field that links it to a specific EventID.
That doesn't appear to be the case here, though. Highly recommend changing
it to be that way. (After all, that's why databases are "relational".)

The following will return all transactions occurring within the date range
of an event:
SELECT Trans.*
FROM Trans, Events
WHERE Trans.TransDate Between Events.EventStart And Events.EventEnd;

Note, however, that this will return the same transaction for two different
events if the Event dates overlap.

The following will return all events occurring on the transaction date:
SELECT Events.EventName
FROM Trans, Events
WHERE Trans.TransDate Between Events.EventStart And Events.EventEnd;

Bruce

 

[Guest]

Thanks for the help.

For info: by user-requirement, event dates cannot be overlapping --> at any
given time one and only event can happen.

Also for info: the events table simply stores info about events and have no
data on transactions to create the link between the two tables.

Thanks once again for your help.

 

[Guest]

DKS,

There would not be any transaction data in the "Events" table. What you
should do is add an "Event Key" to both the "Events" table and the
"Transactions" table. (One-to-many with one "Event" and many
"Transactions".)

Part of entering the transaction would be to link it to a specific event.
Would make entire system more usable and data more easily accessible.

Just a recommendation...

Bruce