Duplicates in my Access table?

Question

All of my totals are coming out incorrectly when querying the information in my database.

I have just noticed that in the design view - some fields are Indexed as Yes (Duplicates OK).

How will this affect the numbers being output?

My apologies - just learning Access so my question is probably amature.

My table has 109 columns and 202,863 rows of data. Obviously this is my main table.

In my query I am selecting:

Supplier ID, Customer ID, Line Amount (Sales), Line Amount (Cost), Product Group, Month, Warehouse Code.

It is the Supplier ID that is showing Yes (Duplicates OK).

Otherwise I cannot figure out where the problem exists - unless perhaps in the Datawarehouse query where the information is being sourced.

Any ideas?

Answer 1

When u select Duplicates Ok, it means that if any results for that field has turned up before, Access will not filter it out from the results.

You must check to make sure that the fields that has to be unique do not have this option set.

e.g. Querying from table which contains sales record. Table fields contains:

1. Date
2. Customer ID
3. Item Description
4. Qty
5. Sales Amount

If your query is to extract out the total sales for every customer, you must not set the Duplicates OK for customer ID. This is to ensure that the results for each customer will only show up under an instance of the Customer ID.

Hope this helps.

Answer 2

that would depend on what you are totalling on.
If your totaling is on duplicated columns, then yes, they would.. else that should not impact you.
Incase you want to avoid duplicate, use a "select distinct" clause
would have helped if you had explained your question better though