Web Development

Ask something
Discussion Locked

ms access repeating records

0 helpful
5 replies

by tekkydave

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

5 replies

by tekkydave

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.

:)

by douglasb

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.

by dr dij

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.

by desktroll

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

by sdanny

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!

This discussion is closed to new replies.