Select data based on common fields

[rlp]

I have two tables with a common field:

Tbl Inv Amt
Inv_No
Inv_Amt

Tbl Cust
Inv_No
Cust_No
Cust_State

How do I create a query to give me Inv_No, Cust_No, Cust_State, Inv_Amt. I thought I could join the tables and just pull in those fields into one query from the two tables but it lists each invoice twice because it is listed in both tables - I just want to extract data from Tbl Cust. I think it may be a Lookup function but I can't figure out how to do that.

Thanks for any suggestions.

 

[mas5]

Select tblcust.inv_no, tblcust.cust_no, tblcust.cust_state, tblInvAmt.inv_amt
from tblInvAmt, tblcust
where
tblcust.inv_no = tblInvAmt.Inv_no

should do it.

 

[rlp]

This sounds like it should give me what I want, but I ran into a complication. The Inv Amt Tbl (actually a query) I have extracted data so each item is only listed once. In the Cust Inv table, an item may be listed more than once, but the basic data I am extracting will always be the same for each record. I got a message that items appeared more than once - I think that is what it meant. So I tried to create a union query to pull in the information needed from two sources and create a query where everything is listed only once. I have created the following in a query field definition and get a message that this operation is not allowed in subqueries.

Expr1: (select [Doc No] From [1 Trx Mstr CY D] UNION select [SOPNUBE] from [SOP30200 Inv His D])

[1 Trx Mstr CY D] is a query where I have extracted data from several tables. [SOP30200 Inv His D] is a table from our accounting system. I do not have the two sources joined. What I want this to do is create a query that will list all Doc No's from the financial side and all SOP Nos from the sales side so I can have a comprehensive list of document numbers (I will pull in other fields as well once I get this to work). Then I will go back to your first suggestion. Can you tell me what I am doing wrong?

Many thanks.

 

[mas5]

I'm not sure I understand completely, but in looking at your query I'm confused by Expr1: . What is that for? I think if you eliminate the expr1: and the parentheses then you won't get the subquery error message. Also, if you want to get each item only once you can try using select distinct instead of select. The distinct clause should eliminate any duplicates.

 

[rlp]

Based on your response, I determined that I was not working in the right place. I was in the screen for "Dummies" and the Expr and ( ) were inserted by Access each time I finished the statement. I looked harder and got into the SQL view and now should be able to to use some of your suggestions. Thanks for the help.