Query with DISTINCT

[Corinne]

I'm having a problem when trying to write this query. tblSystemHeaders is a look up table and tblEntry holds the data. I need to select all of the distinct Headings & ExpDet from tblSystemHeaders and then go & grab the detail information from tblEntry. These 2 tables have the field Code as the join. In my report I need to display the Heading and ExpDet along with the detail information from tblEntry. Heres the code so far:

SELECT DISTINCT tblSystemHeaders.ExpDet, tblEntry.CutOff, tblEntry.AcctNum, tblSystemHeaders.lblCheckNo, tblSystemHeaders.lblCreditAmt, tblSystemHeaders.Heading, tblSystemHeaders.lblCreditDate, tblSystemHeaders.lblDebitAmt, tblSystemHeaders.lblDebitDate,tblSystemHeaders.lblAmtDiff, tblSystemHeaders.lblMemo FROM tblSystemHeaders INNER JOIN tblEntry ON tblSystemHeaders.Code = tblEntry.Code WHERE tblEntry.CutOff = '" & Trim(cboCutOff) & "' AND (tblEntry.AcctNum = '" & Trim(txtAcctNum) & "')) ORDER BY tblSystemHeaders.ExpDet

This query gets me the beginning of the result set I'm trying to retreive but when I try to add the additional fields to the query I run into problems. I add in the field tblEntry.CheckNo multiplies the result. I'm just not sure if I'm writing the query wrong or if I have my tables set up wrong. Any help would be appreciated

Thanks,
Corinne

 

[MeanGreen]

This will get you your initial master-detail list I think:

select distinct head.Headings,head.ExpDet,ent.*
from tblsystemheaders head left outer join tblEntry ent
on head.Code = ent.Code

I use a left outer join in case there is no detail at this time.

Hope this helps.

 

[r937]

DISTINCT is used to eliminate entire duplicate rows

i.e. all columns in one row equal to the same columns in another row

it's a common misconception -- people think DISTINCT applies only to the first column after it in the SELECT list

so if you really want all distinct Heading and ExpDet combinations from tblSystemHeaders, then you might have to do this in a subquery

if multiple tblSystemHeaders rows have the same Heading and ExpDet combinations, and you want all combinations, regardless of whether each combination had any matching rows, then you might need a left outer join

show some sample rows (not too many!) including what kind of results you expect, and maybe it will make more sense to me

rudy

 

[Corinne]

Thanks for responding,
I'm still not understanding how to make this query return the results I need for the report. I need the heading and expdet to be distinct (yes, all columns in one row equal to the same columns in another row) and then pull the additional information (CheckNum & Amount)from another table (tblEntry) that joins tblSystemHeaders on Code and this result set be the data that shows up in my detail section of each distinct heading of the report:

RETURNED CHECKS (Heading)
These checks were returned ........... (ExpDet)
Check Num Amount
12345 35.00
67891 98.00

Hopefully this explains what I'm trying to do. I'm not sure how to do a subquery please show example.

Thanks,
Corinne

 

[Corinne]

MeanGreen

Thanks, I tried your query & it works fine until I try to add more fields from tblEntry that I need to display on the report then it begins showing duplicate rows. Any suggestions?

Thanks,
Corinne

 

[r937]

i think the "duplicate rows" you mention are just the duplicate "one" columns in the one-to-many relationship

if you want

[tt]aaaa 11111 123 abc
aaaa 11111 456 def
aaaa 11111 789 ghi
bbbb 22222 123 xyx
bbbb 22222 937 yay[/tt]

to come out as

[tt]aaaa 11111
123 abc
456 def
789 ghi
bbbb 22222
123 xyx
937 yay[/tt]

then you have to do this in your reporting program, not in sql

if that's not what the problem is, show some sample rows from the two separate tables

 

[Corinne]

r937

Yes, that is how I need the data to be returned from the query. I don't understand how to "do this in your reporting program, not in sql". Can you please explain more?

Thanks,

Corinne

 

[r937]

what are you using to display the results? that's your reporting program, except if you are running this in query analyzer or enterprise manager or whatever, and you are doing it just for yourself, and then my advice (and it is not meant to be sarcastic) is to live with it, and if you are giving the raw sql to users, teach them how to live with it

anything else is a reporting program, and will have to have logic to detect the "break" in groups while looping through the sorted results

you are talking about presentation, and presentation comes after sql

i wouldn't do a pie chart in sql either


rudy

 

[SQLBill]

A reporting program would be something like:
Crystal Reports
Visual Basic

As Rudy said, using Enterprise Manager or Query Analyzer just provides the raw data. If you want it fancied up, that's going to require a reporting program (also known as a front-end tool, since databases are commonly refered to as backend tools).

-SQLBill

 

[Corinne]

I'm using the reports in VB to display the results I know, I know they are not very ........... friendly to use. I've used Crystal before but didn't want to use it this time. I guess the problem I'm having with this is that this report is for an application that I'm rewriting in VB that was originally written in Access & the report in Access was able to sort & group the data like I've explained so as far as telling the users that they have to suck it up and like with what I give them, won't go over very well. I guess I'm still baffled that the reports in VB aren't very robust and something like this can't be done.

r937, can you give me additional information on how to "have logic to detect the "break" in groups while looping through the sorted results". Thanks so much for your help so far.

Corinne