Save the Day for a Newbie?! Average price between two dates...

[wdrescher]

I am trying to learn a little bit of Access at work and I am stuck on what to do next. I was given an old access database to change a few boxes around and add some boxes too. I did all of the design needed and added it into the query they were using. They would like me to calculate the average cost of prices from 4 categories (easy,medium,hard,special) over a certain date range they will pick. For example they will have a section for "Average Pattern Prices" and Average Core Prices" - under those titles have a start and end date of the query and then show 4 boxes to display the average price for the easy-medium-hard-special category. I can provide anything to aid in my assistance. I appreciate the help in advanced!



What the engineer enters information on....



The main menu where I would like them to have the buttons for the report running and date ranges.... maybe also show the values?

EXAMPLE: StartDate - EndDate then show 4 boxes with the label of



Average Pattern Price

EASY [Get the Average of Pattern Shop1 + Pattern Shop2 + Pattern Shop3] -------- Show only average from quotes with the easy checkbox marked from date range
MEDIUM [Get the Average of Pattern Shop1 + Pattern Shop2 + Pattern Shop3] ---- Show only average from quotes with the medium checkbox marked from date range
HARD [Get the Average of Pattern Shop1 + Pattern Shop2 + Pattern Shop3] ------ Show only average from quotes with the hard checkbox marked from date range
SPECIAL [Get the Average of Pattern Shop1 + Pattern Shop2 + Pattern Shop3] ---- Show only average from quotes with the special checkbox marked from date range

[June7]

One method uses domain aggregate functions. These are functions that address tables/queries directly to summarize data.

Assume easy-medium-hard-special are values in a single field, expression in a textbox somthing like:

DSum("price", "tablename", "category='easy' AND datefield BETWEEN #" & datestart & "# AND #" & dateend & "#")

datestart and dateend parameters could be references to textboxes on form.

Might need some more criteria in the WHERE CONDITION argument such as customer ID and part number.

Data manipulation (especially aggregate calcs) like this is best done on reports.

[wdrescher]

I am a designer by trade so this is all new territory for me. I made out what I want the form to look like if that helps...

[June7]

Looks like another parameter for the domain aggregate function - are 'pattern' and 'core' values from the same field? What parameters dictate the level of summation - date range, customer, part, category, pattern/core? That's 5 criteria and makes for a very long DSum expression.

So far, that looks like 8 textboxes, each with a DSum expression.

You really should consider a report for this output, maybe even a crosstab query.

Getting a better handle on Access functionality wouldn't hurt. Recommend a week with a basic tutorial book, cover to cover.

[wdrescher]

Yeah I am trying to learn the basics to be dangerous and I have done a lot just hitting a wall on the approach... I would love to have a report made I just made that form to show an idea of what I am wanting. Time is also not on my side right now so frustration is not helping... Here's an explanation on what makes what...

Pat Cost + Pat Cost + Pat Cost = Pat Total

Pattern Complexity Checkbox (Easy - Medium - Hard - Special)


CB Cost + CB Cost + CB Cost = CB Total

Core Complexity Checkbox (Easy - Medium - Hard - Special)


I want to run a report to find the average of Pat Total for Easy/Medium/Hard/Special

I want to also run a report to find the average of CB Total for Easy/Medium/Hard/Special

[June7]

I can describe methods and concepts but without an intimate knowledge of your db structure and behaviors, can't give specifics.

For instance, easy-medium-hard could have only one of them selected but could also have special - as shown on the image? So some data would aggregate as both easy-medium-hard and special? How many fields are involved in just this single aspect - 4 Yes/No instead of a single text field?

Also, If you need to get the average of 3 fields, there is no built-in function for that, use expressions in query or textbox.

Example:

Nz([field1],0) + Nz([field2],0) + Nz([field3],0)

IIf(IsNumeric([field1]),1,0) + IIf(IsNumeric([field2]),1,0) + IIf(IsNumeric([field3]),1,0)

Then calculate average with those results:

textbox1 / IIf(textbox2=0,Null,1)

If you want to add up the 3 fields then average all records:

Avg(Nz([field1],0) + Nz([field2],0) + Nz([field3],0))

That expression in a textbox in form or report footer section will calculate all records included in the recordset.