Wanted: Unique values from source column in query

[jbfraleigh]

Ok, this query is driving me crazy. I'd like to get it so that there are no duplicate values in the [T1].Data1 column. Here's what I have:

SELECT [T1].DATA1, [T1].DATA2, [T1].DATA3,
[T2].DATA1, [T2].DATA2, [T2].DATA4

FROM [T2] INNER JOIN ([T1] INNER JOIN [T3] ON [T1].DATA3 = [T3].DATA1)
ON [T2].DATA5 = [T1].DATA4

ORDER BY [T1].DATA1;

Hmmm, It's hard enough for me to read... Hopefully someone out there is good at this stuff. The problem that I'm having is that I'm getting duplicate values for [T1].DATA1... I need to get rid of these.

Any help is greatly appreciated.

Thanks!

 

[Pat Hartman]

You haven't simplified the problem by hiding the real column names. You have simply made it harder for us to evaluate the query.

1. You haven't selected any columns from T3. Do you intend for it to be in the query?
2. Whenever you join a 1-side table to a many-side table, columns selected fromt the 1-side table appear to be duplicated but in fact, this is not a duplication and it is norma. Every many-side record must be matched to its parent 1-side record. Rather than using a query to view data, you should be using a report with a subreport or a form with a subform.

 

[jbfraleigh]

You're right - It does look quite ugly. Especially without the real names. As I was studying this problem today, it occurred to me that it may be a flaw with the design of the tables. I imported from Excel and the Access Wizard seemed to do a pretty good job normalizing it so I didn't really think about it. Before I left for the day, I decided to revamp my tables a little bit.

However, some of what you said intrigued me. When I'm fresh in the morning and I have the data in front of me, I'll try to describe this in a more "user friendly" way. Some of these complex queries really drive me nuts and I need to get over that!

Thanks,

Andy

 

[Pat Hartman]

I notice that you are from Manchester, CT. Are you coming to the CtAUG meeting Wed. May 11 at the Microsoft offices in Farmington? I will be demo'ing Total Access Analyzer and I have a copy to raffle off along with a bunch of other dodads from Luke Chung at FMS.

 

[jbfraleigh]

Hi Pat,

I hadn't really thought about attending the AUG. However, considering the amount of time that I am currently spending and will be spending in the near future, it might be a good idea.

Do you have any information that you can send me or can you point me in the right direction to locate more info?

Thanks for bringing this up!

 

[Pat Hartman]

Here's a link to the website http://www.beeass.com/CaNdx1.htm