Count number of value in a field with many duplicate ones

[Guest]

Dear all,
I am new in access. I have one question:

My database including two tables have relationship 1-N by a field CODE.

MAIN table:
Field 1: Code (unique)
Field 2: Name

INPUT table
Field 1: Code (duplicate)
Field 2: ItemAcquisition

I want to do query to list people (number of code in table MAIN) to purchase
what (ItemAcquisition in table INPUT)

I appreciate your help. Thanks!

 

[Guest]

If you want to list all the records in table Main regardless if they have
purchase items, then you need to have a Left join between the to tables, to
include all records from table MAIN with there purchase, if they have no
purcases then the fields will apear empty

SELECT Main.Code , Main.Name, INPUT.ItemAcquisition
FROM Main LEFT JOIN INPUT ON Main.UserId = INPUT.UserId


If you want to list all the records from table MAIN only if they have
purchases, then you need to link the two tables in the query with an inner
join.

SELECT Main.Code , Main.Name, INPUT.ItemAcquisition
FROM Main INNER JOIN INPUT ON Main.UserId = INPUT.UserId

 

[Guest]

Dear Ofer,
Thanks for your prompt reply. However, I have tried and still the problem
that:

The query list all of NAME with CODE with duplicate values. I would like to
have a list with unique value only

Thanks!

Thanh

 

[Guest]

The second query, the inner join, change to a group by query, and change the
ItemAcquisition from Group by, to count.
You will get all the Number unique, with the amount of ItemAcquisition.

 

[Jeff Boyce]

The word "Name" is a reserved word in Access. If you use this as a
fieldname, you will only confused both Access and yourself.

Take a look at the properties of queries -- you can set the Unique Values
property to "Yes" in your query.