How To Run a SQL Query to Create a New Column

[nouveauricheinvestments]

Hi,

I have two tables. One is my Orders, one is my rejections. The
orders table lists all orders that have been attempted, not
necessarily filled. The orders table has an order number for each
record, which can often be duplicated - when it is not filled, it will
continue to try and be filled until it eventually is accepted - it is
therefore not aprimary key. My rejections table lists all of the
orders from the orders table that have been rejected. This can also
be a duplicate value if the order is rejected multiple times and
therefore is not a primary key.

I am trying to determine how to make a query that will pull the order
from the orders table and compare it against the rejections table for
the time period specified and then create a separate column that will
tell me whether it is a Rejected trade or an Accepted trade. The
query should include all orders from the orders table and then use the
rejections table to make it's calculations for each record. This is
what I am working with right now. I'm not that familiar with SQL yet
(but getting there). I was thinking I should use a Union query. As
you can see it is very much under construction.

SELECT DISTINCT Order, Date, Time
FROM Orders
UNION Order FROM Rejections
WHERE Orders.Date > (Now()-42), Rejections.Date > (Now()-42)
ORDER BY Orders.Date, Orders.Time;

 

[KARL DEWEY]

You should not use fields named Date & Time as they are reserved words and
can cause problems.
Try this --
SELECT Order, Orders.Date, Time, Rejections.Date
FROM Orders LEFT JOIN Rejections ON Orders.Order = Rejections.Order
WHERE Orders.Date > (Now()-42), Rejections.Date > (Now()-42)
ORDER BY Orders.Date, Orders.Time;