Dont count duplicates in a specific field

[Guest]

Please help me!! It is my first week at my first job and I have to go into a
meeting very soon to discuss some findings. I kinda told my boss that I could
create this report 'no problem'- which I thought I could. Here's my dilemma:

I need to create a summary report of an access database that I would call
huge. I have the relationship poster mounted on my partition and it is like
looking at a magic eye poster. From this I must create a one sheet summary of
the amounts of data in particular tables- which I have done. The very last
thing I need to do is a total of all records in one table that are linked to
the other, this is a one to many relationship. In order to get the
information on linked records, I must include the fields from the linked
table. So in one query I have:
item is linked to
a ABC
a BCD
b ABB
b ACC
and so on. How do I get a count of all the unique fields in the item column.
I tried to set the query to unique fields but as they each have different
links and unique ID's I cant. Forgive me if this is an elementary question,
Its just all falling apart. Fri afternoon...

 

[Marshall Barton]

Please help me!! It is my first week at my first job and I have to go into a
meeting very soon to discuss some findings. I kinda told my boss that I could
create this report 'no problem'- which I thought I could. Here's my dilemma:

I need to create a summary report of an access database that I would call
huge. I have the relationship poster mounted on my partition and it is like
looking at a magic eye poster. From this I must create a one sheet summary of
the amounts of data in particular tables- which I have done. The very last
thing I need to do is a total of all records in one table that are linked to
the other, this is a one to many relationship. In order to get the
information on linked records, I must include the fields from the linked
table. So in one query I have:
item is linked to
a ABC
a BCD
b ABB
b ACC
and so on. How do I get a count of all the unique fields in the item column.
I tried to set the query to unique fields but as they each have different
links and unique ID's I cant. Forgive me if this is an elementary question,
Its just all falling apart. Fri afternoon...

Try creating a separate query to calculate the count:

SELECT Count(*) As CountItems
FROM (SELECT DISTINCT item FROM table)

Not sure where/how you want to use that value, but if it's
just a text box in a report, then set the text box
expression to:
=DLookup("CountItems", "nameofquery")