Query

[malay63]

Sir, Here is my problem: I have two tables. In table 1 "n" number of dealers each showing purchase from many other dealers. In table 2, dealers are showing sales to many other dealers. Each table has various fields. Let me give the table structure: Table 1: a)Purchaser's Regn No b)Trade Name c)no of invoice d)seller's regn no e)amount f)tax Table 2: a)Seller's regn no b)Trade Name c)no of invoices d)purchaser's regn no e)amount f)tax I want to run a query to find out purchasers in Table 1 whose purchases are not supported by seller's sales in Table 2. I tried many options (like unmatch query) but not succeeded. Pl. help me out. Thanking you malay63

 

[PNGBill]

Avoid Spaces in Table and Field Names.
Amount is not a good Field Name, try InvAmount. Double Name helps avoid using a Reserved Name and allows you to identify the data easier. SaleAmount, PurAmount...

Also, please set your Table and Field Names in a column eg

tblFirstTableName
FieldName1 - any additional info ??
FieldName2

tble2ndName
FieldName1
FieldName2

Makes it much easier to understand and give advice.

Also, start your post with MS Access version. Big difference, sometimes with what version you are using.

 

[Severin]

Go into the query design grid and add your tables. Choose the fields you need from table1. Create a join between SellersRegNo in the 2 tables (PNGBill is right about the naming convention it's going to give you lots of trouble later). Right click the join and choose all records from table1. Then add the SellersRegNo from table2 and make the criteria IS NULL