Return a value from another table

  • Thread starter Thread starter DaveWaters
  • Start date Start date
D

DaveWaters

Hello,

I come from an Excel background so please excuse my limited knowledge of
Access and natural tendancy to use my Excel experience to try to resolve my
problem in Access.

I have created a Query1 from a Table1 including using various expressions to
populate calculated fields and worked out everything with the help of this
site except the following problem:

Table1 is a list of Members details. I have a Table2 which details which
members have made a payment (an amount or a zero in the respective column).
Both tables must remain seperate. There is a reference field common to both
tables identifying the member. I want to cross check the references in the
Query1 (pulled over from Table1) and return the payment amount from Table2

Can anyone help at all? I am on the verge of giving up!

Thanks in advance,

Dave
 
If both tables have a reference field that identifies the memebers, Add
table2 to your query and join the tables on that reference field and add the
payment field to your query.

If you need more detail on how to do that, post back and we can help.
 
That is a trivial operation in Access. You just need an inner join.
SQL like:

Select M.Whatever, P.Whatever from MemberDetails As M inner join
MemberPayments As P ON M.Key = P.Key

I always code in SQL and never use the query builder so cannot advise on
that but I'm sure its very easy, probably just add the other table and join
the 2 columns. If you have defined table relationships (you did didn't you?)
even that wont be needed.

Access is a very different animal to Excel.

-Dorian
 
An Inner joing would be correct if you only want members who have made
payments. If you want all members whether they have payments or not,
wouldn't it be a Left join?
 
--Add both tables to the query
--Drag from table1.referencefield to table2.referencefield
-- IF you want to get all records in table1(members) then double click on
the join line
---- Select All records in members and only matching in details
-- Add whatever fields you want to see

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top