Hi

I need some advice on a query. I need to return distinct records from 2 tables which is joined and sum the Amount values. At the moment it will return only distinct rows and not the specific fields. I have searched around on this but couldnt find any solid solution. What I have got is as follows:

Table1 which is linked to table 2 on ID
ID|Account Number|Cost Center|
1|12345|1|
2|12399|1|
3|12366|2|
4|12345|2|
Table2
ID|Period|Amount
1|1|10
1|1|20
1|2|30
2|1|50
3|1|60
4|2|80
A select from the joined tables will return:
ID|Account Number|Cost Center|Period|Amount
1|12345|1|1|10
1|12345|1|1|20
1|12345|1|2|30
etc...
I need to do a select Distinc of some type to return something like this
Cost Center|Period|Amount
12345|1|1|30
12345|1|2|30
etc...
At the moment it will not return a distinc Cost Center because the Account Number is not unique...

Any ideas would be appreciated.

Regards
Marnus

looks like maybe a group by will do what you want, something like
SELECT Account Number,Cost Center,Period,SUM(Amount)
FROM ...
GROUP BY Account Number,Cost Center,Period

Thanks for your reply.
At the moment Im trying something like this:

Select Distinct([Cost Center]), Period,Sum(Ammount) As Ammount from tbl_ZU Z Join tbl_ZU_Ammounts R ON Z.Random = R.Random Where MineCode = '4080' AND MyVersion = '4.0' AND [Fiscal Year] = '2007' AND [Cost Center] <> '' and [Cost Center] IS NOT NULL Group By [Cost Center], Period, Ammount Order By [Cost Center], Period, Ammount Asc

The data returned is as follows:
[Cost Center]|Period|Amount
4102000200 1 -53624963.6700
4102000200 1 225651.9500
4102000200 1 9867294.4800
4102000200 2 .0000
4102061007 1 -6541.2400
4102061007 1 -3039.0000
4102061007 1 -1502.0000

If I look at these they each have different Account Numbers...so I assume it does not qualify then as a distinct record even though I am not returning the Account Number in my query.
Any ideas?

What I am thinking of maybe doing is doing the sleect in this way and do a bulk insert into a temp table and the do the select distinct. In total 36000 records will be returned per period and there are 27 periods...so its allot of data. The application is running on VB6 so I need to watch for 2 many overheads...

Is it possible to so the select and insert in one go maybe?

Regards
Marnus

I am not sure I fully follow what you want. You can do a select distinct into all in one go around. If this is what you seek. If you sum the data right you will not get rows like in your example where you have multiple cost center = 4102000200 and period = 1.

You should not need a distinct if you do the sum right. You should have distinct data by Cost Center and period if you sum correctly. You should not group by amount if you want distinct data by a combination of Cost Center and period.

:) Thanks...that does the trick. Dropped the distinct and summed amount.

Regards
Marnus

for your first question: right click DESIGN QUERY
click PROPORTIES
on the 'relationship' side

select OUTPUT ALL FIELDS

I was here but now I'm gone.
I leave this messege to be carried on.
Whoever knew me, knew me well.
Whoever didn't, go to hell.