Need way around using Distinct with Aggregate function in Access 2000

[Rmck87]

I have seen a thread with this same problem, but it hasn't helped me at all, and i was wondering if anyone could help me with this.... I need to get the Sum(Distinct e.hours_actual) But this doesn't work, and it is because distinct cannot be used with an aggregate function in JET SQL. So, is there a way around this? I have tried this...

Select sum(e.hours_actual)
From (select Distinct e.hours_actual from e)


but it does not bring back the results that i need. I have found that this works, to the effects that i need it to in SQL Server Query Analyzer...

Select(Distinct e.hours_actual)
From e

Any ideas? I need help with this!!!!!!!!!?!?!?!?!!?!?!
Thanks in advance.

-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[CosmoKramer]

Ryan,

Post an example of your data, and an example of the results you are expecting.



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Rmck87]

ok....


i need the amount of hours for a specific time period.

hours_actual date_start date_end member_id

1123.50 4/20/2002 4/27/2002 member1
354.00 7/01/2002 7/08/2002 member2
1823.75 12/16/2002 12/23/2002 member1

This is not bringing back the proper hours its as if it is adding up all of the sets of hours that are the same.

hours_actual date_start date_end member_id

8.50 4/20/2002 4/27/2002 member1
10.00 7/01/2002 7/08/2002 member2
4.75 12/16/2002 12/23/2002 member1

This is what i want. It should not be adding up all of the hours or whatever it is doing. This is just a small amount of the records that it brings back, there are a ton so i just showed an example. Just imagine like 3000 more rows. Whenever my code looks like this in Query Analyzer it works perfectly:

Select sum(distinct e.hours_actual), p.date_start, p.date_end, member_id
From e, p

Since this doesn't work in Access i tried this:

Select sum(e.hours_actual), p.date_start, p.date_end, member_id
From (Select Distinct e.hours_actual From e), p

This doesn't work tho. It brings back smaller hours that are too large, almost as if it is adding all of the hours that have the same time entry, whether or not they have the same member name.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[CosmoKramer]

OK, what do the detail records look like, and what field(s) do you want to make the record distinct??



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Rmck87]

Is this what you mean? it is a little choppy. But it is about 1/3 of my query that is not what i want....

Member_ID Company_Name Date_Start Date_End Hours Description
memberid company name 5/16/2003 5/31/2003 1865.75 Internal
memberid company name 5/16/2003 5/31/2003 2634 Sales Support
memberid company name 5/16/2003 5/31/2003 3512 Holiday
memberid company name 5/16/2003 5/31/2003 16791.75 Training
memberid company name 6/1/2003 6/15/2003 2743.75 Internal
memberid company name 6/16/2003 6/30/2003 1865.75 Internal
memberid company name 8/31/2002 9/6/2002 3512 Holiday
memberid company name 9/7/2002 9/13/2002 878 Internal
memberid company name 9/28/2002 10/4/2002 2634 Internal
memberid company name 10/26/2002 11/1/2002 3512 Vacation
memberid company name 11/16/2002 11/30/2002 7024 Holiday
memberid company name 12/16/2002 12/31/2002 1756 Sick
memberid company name 12/16/2002 12/31/2002 3512 Vacation
memberid company name 12/16/2002 12/31/2002 5707 Holiday
memberid company name 1/1/2003 1/15/2003 3512 Holiday
memberid company name 1/16/2003 1/31/2003 5268 Vacation
memberid company name 2/1/2003 2/15/2003 1756 Vacation
memberid company name 2/16/2003 2/28/2003 1756 Internal
memberid company name 3/1/2003 3/15/2003 878 Internal
memberid company name 3/16/2003 3/31/2003 878 Training
memberid company name 4/1/2003 4/15/2003 878 Training
memberid company name 4/16/2003 4/30/2003 3512 Travel
memberid company name 5/1/2003 5/15/2003 21072 Vacation
memberid company name 5/16/2003 5/31/2003 3512 Holiday
memberid company name 8/3/2002 8/9/2002 17560 Sales
memberid company name 8/10/2002 8/16/2002 2634 Marketing
memberid company name 8/10/2002 8/16/2002 14926 Sales
memberid company name 8/17/2002 8/23/2002 3512 Admin
memberid company name 8/17/2002 8/23/2002 14048 Sales
memberid company name 8/24/2002 8/30/2002 17560 Sales
memberid company name 8/31/2002 9/6/2002 3512 Holiday
memberid company name 8/31/2002 9/6/2002 14048 Sales
memberid company name 9/7/2002 9/13/2002 17560 Sales
memberid company name 9/14/2002 9/20/2002 17560 Sales
memberid company name 9/21/2002 9/27/2002 1756 Marketing
memberid company name 9/21/2002 9/27/2002 16682 Sales
memberid company name 9/28/2002 10/4/2002 1317 Marketing
memberid company name 9/28/2002 10/4/2002 17560 Sales
memberid company name 10/5/2002 10/11/2002 17560 Sales
memberid company name 10/12/2002 10/18/2002 3512 Vacation
memberid company name 10/12/2002 10/18/2002 14048 Sales
memberid company name 10/19/2002 10/25/2002 17560 Sales
memberid company name 10/26/2002 11/1/2002 17560 Sales
memberid company name 11/2/2002 11/15/2002 35120 Sales
memberid company name 11/16/2002 11/30/2002 7024 Holiday
memberid company name 11/16/2002 11/30/2002 29852 Sales
memberid company name 12/1/2002 12/15/2002 37315 Sales
memberid company name 12/16/2002 12/31/2002 7024 Holiday
memberid company name 12/16/2002 12/31/2002 8780 Vacation
memberid company name 12/16/2002 12/31/2002 27657 Sales
memberid company name 1/1/2003 1/15/2003 1317 Marketing
memberid company name 1/1/2003 1/15/2003 2634 Training
memberid company name 1/1/2003 1/15/2003 3512 Holiday
memberid company name 1/1/2003 1/15/2003 31169 Sales
memberid company name 1/16/2003 1/31/2003 48290 Sales


One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[CosmoKramer]

No. The unwanted query results aren't what I'm looking for.

You already posted an example of how you would like the query's output to look. What you haven't posted is an example of the detail records that they were derived from.



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Rmck87]

I appologize, i misunderstood you. What do you mean by detail records that they were derived from.. do you mean the tables that i am drawing from in the query?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[CosmoKramer]

Yes



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Rmck87]

Well, the tables that i am drawing from are quite large, but i will tell you a little about them.

the tables:

te_period
time_sheet
time_entry
member
company
te_charge_code


the tables' fields (and what they link to):

te_period.te_period_recid (time_sheet.te_period_recid)
time_sheet.time_sheet_recid (time_entry.time_sheet_recid)
time_entry.member_recid (member.member_recid)
time_entry.company_recid (company.company_recid)
time_entry.te_charge_code_recid (te_charge_code.te_charge_code_recid)

the columns desired from the tables:

in member:
member_id
in company:
company_name
in te_charge_code:
description
in te_period:
date_start
date_end
in time_entry:
it is just a link to other tables
in time_sheet:
it is just a link to other tables

Hope this helps. Thanks for the help.

-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

oops... i forgot about the most important column!

i need hours from time_entry.hours_actual. Thats about it.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[CosmoKramer]

I think maybe you should start over....

In your first post you said "I need to get the Sum(Distinct e.hours_actual)", but then in a later post you said "It should not be adding up all of the hours or whatever it is doing"

The example you posted of your desired output contains amounts for the hours field that are small. Are you looking for a sum or not??

And again, what field, or combination of fields constitutes the record being distinct in this case??



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Rmck87]

Yes i am looking for a sum. But the sum is only going to be smaller numbers, i want it to add up the hours in the time periods where they are the same, for the same members. i'm sorry taht i am confusing you. And i do not understand this:

what field, or combination of fields constitutes the record being distinct in this case??


One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

hey, thank you for all of your help. i have figured out my problem. I ended up wiht an extra table in the from clause that when taken out, works. Thanks for the help anyways.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[lespaul]

Rmck87: If you will give us your data like this:

Table1 Name
Field1 Field2 Field3
data11 data12 data13
data21 data22 data23

Table2 Name
FieldA FieldB FieldC
Data1A Data1B Data1C
Data2A Data2B Data2C


The results should be:

Table1.Field2 Table2.FieldC
data12 Data1C
Data22 Data2C


It will be much easier to assist you. (Here are two threads that I started that I got the SQL I needed almost immediately since they have all the needed information: Thread183-597793 or Thread183-602078)



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Rmck87]

Thanks for the tip Leslie, i will use that in the future!

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.