Access overwrites duplicates

[BostjanPerdan]

Hi!

In my table with data I have sometimes identical rows (i.e. they contain the same data) and when I make a query, Access most likely overwrites the previous entry with the same data, however I do not want to do that as I later count the rows (using a summation) and get a wrong result.

How do I instruct Access not to overwrite duplicates? I have solved the issue by adding another column to all the tables to count the duplicates but there has to be a better way?

Thanks!
Bostjan

 

[dhookom]

Access doesn't overwrite duplicates. What makes you think it does? Where do you see this happening?

Duane
Hook'D on Access
MS Access MVP

 

[BostjanPerdan]

Maybe, I have used the wrong expression for what happens, so I will give an example:

I have a table with date, product number and number of cycles the product was used at that particular date. Number of cycles is always 1 and two rows are filled in if the product was used twice on the same date. This is because I generate this table using a union query from two other tables which include additional data. I refer to these two rows as duplicated rows.

Then I use another query to count the number of cycles using summation and this is where the problem occurs - the duplicated rows are counted as one. I assume there must be a way to instruct the Access to count all the rows?

Probably an alternative solution is to sum the cycles for each date when I make the first query?

 

[BostjanPerdan]

Issue solved using the proposed alternative solution above ... thanks anyway!

 

[MajP]

The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.

The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2