Stock Transaction Query

  • Thread starter Thread starter depawl
  • Start date Start date
D

depawl

I’m trying to design a query for the cost of employee stock
transactions. In an EMPLOYEE table, I have the EMPLOYEEID, the
STOCKNUMBER, and the DATE that the stock was issued. In a PURCHASE table
I have the COST of the item and the STOCKNUMBER. I’ve created the query
by joining the STOCKNUMBERs. I’m trying to limit the query to only the
transactions. The problem I’m encountering is that if a STOCKNUMBER
appears more than once in the PURCHASE table, it gets carried over into
the query, thus giving erroneous results. In other words, the query ends
up listing more transactions than actually occurred. I’ve tried setting
left joins and right joins but can’t seem to get it to work.
 
depawl said:
I’m trying to design a query for the cost of employee stock
transactions. In an EMPLOYEE table, I have the EMPLOYEEID, the
STOCKNUMBER, and the DATE that the stock was issued. In a PURCHASE table
Click to expand...

Hmm, the EMPLOYEE Table looks more like a "STOCKTRANSACTIONS" Table, but
that aside . . .

I have the COST of the item and the STOCKNUMBER. I’ve created the query
by joining the STOCKNUMBERs. I’m trying to limit the query to only the
transactions. The problem I’m encountering is that if a STOCKNUMBER
appears more than once in the PURCHASE table, it gets carried over into
Click to expand...

Given these two Tables:

With EMPLOYEE holding:
EMPLOYEEID
STOCKNUMBER
DATE

And PURCHASE holding:
STOCKNUMBER
COST

It is a given that PURCHASE contains duplicate STOCKNUMBER values (stated
in the question). Each COST, therefore, is assumed to be different, and
together, STOCKNUMBER and COST make up the PRIMARY KEY (I'm guessing on
that).
Since there is no DATE column in the PURCHASE Table, there is no way to
know which COST to associate with the transaction date in the EMPLOYEE Table
(note that a table called "EMPLOYEE" is being used to store a "stock
transaction", refer to my note further up this post).
EMPLOYEE.DATE refers to the date that the stock transaction occured
(again, I'm guessing).
PURCHASE.DATE would refer to the cost of the stock on the date in
question.

I don't think the Query desired will be possible without a DATE column in
PURCHASE.

The Query would then match both STOCKNUMBER and DATE from both Tables to
produce the desired results.


the query, thus giving erroneous results. In other words, the query ends
up listing more transactions than actually occurred. I’ve tried setting
Click to expand...

Because there is no DATE column in PURCHASE, when the Query is run, it
matches every STOCKNUMBER row in PURCHASE with every STOCKNUMBER row in
EMPLOYEE, and I'd agree that those results are not desired.


Sincerely,

Chris O.
 
Back
Top