• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Access 2003 - recordset question

Hi,

Accidentally posted this under SQL 2005 - now reposting...

I have two tables - Jobs and Users.

Jobs contains all the records and $ values of each sale.

Users contains the reference to User name and UserID that I use to associate the right user name with the record in Jobs (which only keeps the userID).

I hope to have a query that SUMS and AVGs each User's sales figures, but I can't get the SQL right. My current attempt includes a bunch of Sort Bys which it appears I have to have present if I'm using SUM or AVG.

Here is my current SQL and it's only giving me the first record, with no sum or average other than just showing that first Job's $ amount:

SELECT SUM(Jobs.AcceptedPrice) AS TOTAL, AVG(Jobs.AcceptedPrice) AS AVERAGE, Jobs.JobID, Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime, Users.UserID, Users.UserFirstName
FROM Jobs RIGHT JOIN Users ON Jobs.EstimatorAssigned=Users.UserID
WHERE Users.UserType=2 GROUP BY Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime, Users.UserID, Users.UserFirstName, Jobs.JobID

I was hoping my final looped array (a DREAMWEAVER recordset so I have add other functions) would show the first user name, his total, and his average, then move to the next unique username in the list and show the same thing.

I'm keeping the date in there so I can run search queries that filter this recordset by date range (hence the need for a standard DW recordset)

Any suggestions?

Thank you

What am I doing wrong here?

Thanks
0
billium99
Asked:
billium99
  • 6
  • 4
2 Solutions
 
hnasrCommented:
Compare with this simple model:
Having Users table:
userid      username
1      n1
2      n2

And jobs table:
userid      jobid      salevalue
1      1      10
1      1      30
2      2      20
2      2      30

This query gets sum and average per user:
SELECT users.userid, Sum(jobs.salevalue) AS SumOfsalevalue, Avg(jobs.salevalue) AS AvgOfsalevalue
FROM users INNER JOIN jobs ON users.userid = jobs.userid
GROUP BY users.userid;

userid      SumOfsalevalue      AvgOfsalevalue
1      40                           20
2      50                           25

try this simple design and modify in the query design grid!
0
 
billium99Author Commented:
Hi,

The problem is that I'm creating a Recordset in Dreamweaver, so that I can add a search query (using an extension) to the page that lets the user get the SUM and AVG for a specified time period. That's why I need more than just the SUM and AVG.

This can still be some kind of stored procedure in Access, but I'm not sure how to do it.

Thanks

Bill
0
 
Vadim RappCommented:
Remove jobs.jobid, jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime from group by and from select list.

If you want to group by users, then group only by the items from the users table.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
billium99Author Commented:
OK, with a SUM or AVG operation, it appears to require that all other fields selected are part of an aggregate function. The exact error I'm getting trying to add this in the DW interface is:

"You tried to execute a query that does not include Jobs.EarliestEstimateDateTime as part of an aggregate function"

If I add it to Sort By, the query works, but only does the average and sum for the first record, then gets screwed up trying to sort by date.

Here is the updated query:

SELECT Users.UserID, Users.UserFirstName, Jobs.EarliestEstimateDateTime, Sum(Jobs.AcceptedPrice) AS SumOfsalevalue, Avg(Jobs.AcceptedPrice) AS AvgOfsalevalue
FROM Users INNER JOIN Jobs ON Users.UserID = Jobs.EstimatorAssigned
GROUP BY users.userid, Users.UserFirstName, Jobs.EarliestEstimateDateTime

Is there a way to do some kind of "embedded" select within the other select? I have to have the dates associated, so that a DW search extension I'm using can take a value from the datetime field and let the user filter (in other words, SUM and AVG for September, then October, etc.) Is there a way to get EarliestEstimateDateTime into this query in a way that doesn't screw up the SUMs and Averages?

Thanks

Bill
0
 
Vadim RappCommented:
objection. The asker obviously did not follow the advises we gave him.
0
 
billium99Author Commented:
Excuse me - please note my last comment stating that I was unable to make the previous suggestion work. There was nothing in this thread that ultimately resolved my issue. I don't care about the points, but I was advised to pull the question down.

Vadim, your suggestion was to group by fewer items, but Dreamweaver wouldn't let me not group by any nonSUM,AVG,COUNT select items.

Here is the comment that directly addressed your suggestion:

"""""OK, with a SUM or AVG operation, it appears to require that all other fields selected are part of an aggregate function.""""

If I removed any Group By items, I would get an error. No one responded to this problem.

Thanks for your time

Bill
0
 
Vadim RappCommented:
We both suggested the query that would NOT include anything non-aggregated from Jobs. You wanted to group by users, so the only non-aggregated field from Jobs table included in select list should be the one joined to UserId. Your modified query still includes EarliestEstimateDateTime which prevents aggregation in Jobs table.

Which is exactly what you said: "it appears to require that all other fields selected are part of an aggregate function." - meaning all other fields from Jobs table - yes, they should be aggregated.

I also don't care about the points, but I'm almost positive that if you tried what  hnasr suggested, i.e. had simplified query working in query designer and then developed it as neccessary also in the designer, then it would work. Same as if you removed the items I suggested and left only aggregates from Jobs. If you are sure that  you did try that and it did not work, then go ahead and delete; but from the modified query you posted it did not seem so - it still had non-aggregated field from Jobs, breaking the aggregation.
0
 
billium99Author Commented:
But I HAVE to have some of these fields. I'm not adding fields willy nilly. The date field is central to the entire operation. In Query designer I was not able to get it done for PRECISELY the same reason that brought me to E-E with this question in the first place.

I'll just accept the solution, lest I be blacklisted and no one help me in the future.
0
 
billium99Author Commented:
THANKS!!!!!!!!!!
0
 
Vadim RappCommented:
> The date field is central to the entire operation.

but you shouldn't GROUP BY that field! if you need to extract only records belonging to the certain period, then specify that in WHERE. If you put the date into select list, then each unique date forms unique group, when in fact you want one group per one user. This is exactly the core of your problem, and if you can't achieve what you want in query designer, then of course you won't achieve it in DW either - and the problem is not in DW.

f1  f2   date                 user
1   2     1/1/2008        u1
3   5     1/3/2008        u1

select user, sum(f1),avg(f2) from table group by user
u1        3    3.5

select user, sum(f1),avg(f2),date from table group by user,date
1   2     1/1/2008        u1
3   5     1/3/2008        u1


because the dates are different.

 
0
 
billium99Author Commented:
Yep - but I was already containing multiple WHERE clauses by this point - someone on another thread finally got me through this. Thanks for your time!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now