lookup in access

  • Thread starter Thread starter sheetal
  • Start date Start date
S

sheetal

I have two tables in access, with similar information. However, the
differ slightly.

Both sheets have the same date, so this is the common link between th
two. They also have a trade number and also a valuation number.

Table 1 is called current trades
Table 2 is called New trades.

I want to compare the two. So I want to look at current trades, an
see how many new trades are in there. How do I bring back a query tha
gives me this?

so effectively i want "if in current trade, display" to come back int
a table.

I also want "if not in current trade, display" to come back in t
another table.

Thanks

Sheeta
 
Dear Sheetal:

So, is it the case that a trade in "current trades" is the same trade
as a trade in "New trades" exactly when they have the same date, trade
number, and valuation number? Is is necessary to use all three to
create a match? Or would it be the case that two records are the same
trade if they have the same trade number?

I'm suggesting you determine the minimum amount of information on
which to base this equivalence. Then you should create a LEFT JOIN
between these two tables:

FROM [current trades] CT
LEFT JOIN [New trades] NT ON NT.[trade number] = CT.[trade number]

When you see the results, you will see all the current trades but only
those New trades where [trade number] matches. The [trade number] and
any other columns in New trades will be NULL for those current trades
where there is no New trade.

Here, I have assumed that matching on [trade number] is sufficient to
match a trade, though I'm not sure this is what you need.

To create such a join in the Query Design Grid, put both tables up and
drag/drop a line between the two tables on the [trade number] columns
of each. Right click on the line and display Properties. Read
carefully. You want to select the option that displays all the rows
of [current trades] and only those rows of [New trades] that match.

Please try this much and get back on how you are doing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top