Automatically calculating averages based on having the same names?

[tyrannitar]

When I put various items with the same name field into my Query or Form, I want Access to automatically calculate the average of their unit prices to use as the unit price for the item. For example, if I have two items with the name “Pen,” with the first having a unit price of $5.00 and the second having a unit price of $3.00, I want Access to automatically calculate and tell me that their average and thus their unit price is $4.00. How can do I do this? Help would be appreciated.

I use Access 2003.

 

[marlan]

I hope I'm not being nasty, but these are great SQL sites: http://www.sqlcourse.com/, http://www.sqlcourse2.com/.

 

[tyrannitar]

I tried looking through them, but I'm still a bit lost as to what to do. Where could I even put the calculated average? I don't think I could put it on the table from which the values for the calculations came from. Where do I even put the SQL code?

 

[Kryst51]

Is this on a form? Does the form display both "pen" records and you want the average on the form or in a message box? Or is this a new record and you want the average to display as the new unit price?

In any case if you go to the QBE (the query builder is easier than working with actual SQL, you can always see the SQL later) Select your Item ID, add the field that contains the word "pen", and the "unit price" field and make sure the total row is showing. You wil group by the ID, and the "pen" field, then on the unit price on the total row find "average" and voila you have calculated the average unit price for every item. You can use this query in a lot of different ways. You just need to let us know what you have and what you are trying to achieve in order for us to help you.

EDIT: DO NOT STORE CALCULATED VALUE! Unless you need it for historical purposes.

 

[tyrannitar]

Hm, I’m trying to do something like that. I’d imagine that I’d have to use a form to do this, yes. Right now, it’s within a Query.

I think I want the average to display as the new unit price. I’m thinking of creating two Inventory tables/queries: one in which all the items are listed with an ItemID as the primary key, and another in which items with the same name are grouped together, with the name acting as the primary key instead. For instance, on Inventory1, I will have 1 – Pen - $5.00, 2 – Pen - $3.00, and so on. Then, on Inventory2, I just want it to say Pen – $4.00, the average of the two unit prices for Pen from the Inventory1 Query.

To be honest, I never knew there was a QBE, haha. What do you mean when I group by the ID and pen (ItemName) field? Should I group it solely by the ItemName instead? Nonetheless, the result does sound like what I’ve been trying to achieve all week, hehe.

And yes, I will have to store the calculated value somewhere. Is this bad?

Thanks a lot for the help!

 

[Kryst51]

Hi tyrannitar,

It sounds like you might have some misconceptions concerning how you think you should design your tables. Read up on normalization. This idea of how to set up your tables is not a normalized way of doing things. Also, as said previously, you do not need to store calculated values.... You want to calculate them when you need them.

As far as the "group By" it is found in the total row.... You get that row to appear in the QBE by clicking the little "E" looking icon at the top of the screen. You can base a form or list box or combo box off of a query.

 

[tyrannitar]

So, I finally figured out how to go about doing this this morning. It's relatively simple, eh? Unfortunately, my supervisor then changed her mind, and my efforts with this whole "Average" thing proved to be completely vain.

Thanks for the help, everyone. I'm sorry if my question wasn't completely clear. I suppose I gained some valuable experience and knowledge from all of this anyways - right?

 

[Kryst51]

For the sake of others who might be looking to do something similar, what ended up working for you? I'm sorry you had to scrap it, but every experience or knowledge will help you later, and help you figure out other problems you might have too.

 

[tyrannitar]

I basically just did this:

In any case if you go to the QBE (the query builder is easier than working with actual SQL, you can always see the SQL later) Select your Item ID, add the field that contains the word "pen", and the "unit price" field and make sure the total row is showing. You wil group by the ID, and the "pen" field, then on the unit price on the total row find "average" and voila you have calculated the average unit price for every item.

I took out the ItemID field, though. I kept the ItemName field and grouped by it, and I selected "Average" in the Totals row for the unit price field. What came out was what I was looking for.

 

[Kryst51]

I basically just did this:



I took out the ItemID field, though. I kept the ItemName field and grouped by it, and I selected "Average" in the Totals row for the unit price field. What came out was what I was looking for.

Fantastic, I am glad you got it sorted , and again sorry that you then had to scrap it.

 

[tyrannitar]

Don't worry about it; thanks for the help!

I actually have a new problem now, though.

 

[Kryst51]

OK, post it on a new thread though and I know we will try to help.

 

[tyrannitar]

Haha, I actually have. It can be found here:
http://www.access-programmers.co.uk/forums/showthread.php?t=197307