General discussion


ms access repeating records

By tekkydave ·
I looked around but I didn't find any similar posts.

OK, MS ACCESS database -- I got a rather complex query that is pulling info from other tables and queries. The data it's display is correct, but due to the nature of one of the queries it is referencing, it is showing the same data over and over again (correctly). Here is an example:

Employee Name | Vacation days | etc
John Smith | 10 | blah
John Smith | 10 | blah
John Smith | 10 | blah
bob Jones | 4 | yaddah
bob jones | 4 | yaddah
bob jones | 4 | yaddah

How can I get the query to display just one instance of each repeating record? This would help my reporting greatly.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by tekkydave In reply to ms access repeating recor ...

I figured out that in the query design view, if you click the "properties" button, there is a field called, "uniqueRecord". By default this field is set to no, so I changed it to yes, and it fixed me right up. I hope this helps anyone who's having similar problems.


Collapse -

by DouglasB In reply to ms access repeating recor ...

Hey, it helped me! I was having the same problem. It sure is helpful when people post the solution, even if they figured it out themselves, helps others. Thank you.

Collapse -

by Dr Dij In reply to ms access repeating recor ...

yes, in access ( or crystal) you could also group by the order you want, (e.g. employee) and show the group totals.

then hide the detail (right click, properties) or in crystal, format detail section, this if you want to add something up for the employee for example.

Collapse -

by DeskTroll In reply to ms access repeating recor ...

in the query properties set unique values to true rather than unique records, or in the sql statment change the select to SELECT DISTINCT

Collapse -

I am having a similar problem

by SDanny In reply to

Hey guys,

I am designing a DB for a university organization and my DB pulls all the record from One table. I have several queries. There are about 5000 students records pulled by the query into a report. But for some reason, it is repeating the very first record over and over again.
I tried both "DeskTroll" suggestion of SELECT DISTINCT and "tekkydave" suggestion through properties and Unique Record but none worked for me. My unique key is the EID. In fact, I can't even see the Unique Record field in my query. Here is SQL statement is am using:

SELECT DISTINCT Biographical.UT_EID, Biographical.First_Name, Biographical.[Last Name], Fall_Year1.SemesterGPA, Fall_Year1.Major, Spring_Year1.SemesterGPA, Spring_Year1.Major, Fall_Year2.SemesterGPA, Fall_Year2.Expr1004, Spring_Year2.SemesterGPA, Spring_Year2.Major, Fall_Year3.SemesterGPA, Fall_Year3.Major, Spring_Year3.SemesterGPA, Spring_Year3.Major, Fall_Year4.SemesterGPA, Fall_Year4.Major, Spring_Year4.SemesterGPA, Spring_Year4.Major, Biographical.[FIG_All About ME]
FROM (Spring_Year1 INNER JOIN Fall_Year1 ON Spring_Year1.UT_EID = Fall_Year1.UT_EID) INNER JOIN ((((((Biographical INNER JOIN Fall_Year3 ON Biographical.UT_EID = Fall_Year3.UT_EID) INNER JOIN Fall_Year4 ON Biographical.UT_EID = Fall_Year4.UT_EID) INNER JOIN Spring_Year2 ON Biographical.UT_EID = Spring_Year2.UT_EID) INNER JOIN Spring_Year3 ON Biographical.UT_EID = Spring_Year3.UT_EID) INNER JOIN Spring_Year4 ON Biographical.UT_EID = Spring_Year4.UT_EID) INNER JOIN Fall_Year2 ON Biographical.UT_EID = Fall_Year2.UT_EID) ON Fall_Year1.UT_EID = Biographical.UT_EID;

I have spent hours on this issue now. Please help!

Related Discussions

Related Forums