Solved

Access 2003 - recordset question

Posted on 2008-10-19
11
405 Views
Last Modified: 2012-06-22
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
Comment
Question by:billium99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 30

Accepted Solution

by:
hnasr earned 250 total points
ID: 22755484
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
 
LVL 1

Author Comment

by:billium99
ID: 22756572
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
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
ID: 22757946
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:billium99
ID: 22758444
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 22828179
objection. The asker obviously did not follow the advises we gave him.
0
 
LVL 1

Author Comment

by:billium99
ID: 22828206
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 22828662
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
 
LVL 1

Author Comment

by:billium99
ID: 22830791
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
 
LVL 1

Author Closing Comment

by:billium99
ID: 31507722
THANKS!!!!!!!!!!
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 22830948
> 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
 
LVL 1

Author Comment

by:billium99
ID: 22831278
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question