Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Programmer
Starting Member
5 Posts |
 Posted - 2004-10-13 : 12:04:10
|
| When querying data in Access i encounter duplicates. So I use DISTINCT in my SQL statement and this takes care of the problem. But this eliminates some records too. For EX:Name/Gift Date/GiftAmount/Acct/John Smith, 10/1/1996, $100, Business Edu.Fundby using DISCTINCT it prevents duplication of his record sevral times)But I also have a person whose record has the following info.Name/GiftDate/GiftAmount/AcctJohn Doe, $180, 12/12/2004, BusinessJohn Doe, $125, 12/12/2004, BusinessJohn Doe, $180, 12/12/2004, BusinessJohn Doe, $180, 12/12/2004, Business(In this case the query is displaying John Doe's record only twice as it considers the $180 as duplicates. It displays records with $125 and $180 instead of fetching all the four. The gifts have to be processed this way for gift receipting purposes even if they go to the same account on the same day for the the same amount).So, here if i use DISTINCT it will fetch only 2 records instead of 4. But if I don't use DISTINCT it brings all the 4 records of John Doe but duplicates the rest of the other records several times...I tried not using DISTINCT and used a GROUP BY clause after FROM statement. Doesn't work... PLEASE HELP. Thanks. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-13 : 14:37:16
|
well it depends on what you count as duplicates.... and how do you know which is a duplicate and which isn't??Go with the flow & have fun! Else fight the flow  |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-13 : 14:45:39
|
| query results are supposed to duplicate header information, like a person's name. Each row belongs to the same person, and there are 4 rows for a person, so the name is repeated 4 times. if the other 3 rows didn't have their name, then they would not be able to be associated with that person. use a report to format the data the way you see fit. Report's have lots of grouping and formatting options. A query is used to retrieve data -- a report or a form is used for presenting data. Huge difference. For better or for worse, Access blurs the line between formatting and the data, so it is up to you to recognize when you need to worry about obtaining and calculating accurate data, versus displaying or presenting that data.Does this make sense? it is a key concept to being a good database programmer/developer.- Jeff |
|
|
|
|
|
|
|
|
Duplicates