Solved

Access 2003 - recordset question

Posted on 2008-10-19
11
401 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now