Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Question Unanswered: Display records that fall outside standard deviation

    Can anyone help me, I'm pretty new to access and I have been assigned a task at work to maintain a purchase history database that can also show what items in this database spike in purchases. I'm thinking I can use the standard deviation function to find the items that fall outside of the average of previous purchases in the months prior.

    so far all i have acomplished is creating a reference table that takes the data dump of our purchasing history and groups the same products regardless of manufactuer this table gives me the total count of eaches for the purchases of the same items with different manufactuers.

    now i need to create a form that defines the time period we want to query. When control button is clicked this returns a report or graphical representation of only items that have purchases greater than average of months previously.

    I can create the form but have no idea where to seat the code for designating the time period or to define criteria of results to display or where i should use the DStDev function.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding the SDev...If you put the table into a query, drag down the fields you want and click the funny looking E button in the top middle, you'll see you have a new "Total" row below the field names in the grid. The "Total" row has a dropdown box which has several functions (including SDev) you can utilize for the query. You can utilize this query to check for purchases greater than the average of months previously (you can utilize this query in another query also - ie. "nested query".) The criteria row in the query is where you can specify the date range for the date field column (ie. >[Enter Starting Date:] or Between [Enter Starting Date:] and [Enter Ending Date:] or >#01/01/03# or Between #01/01/03# and #12/31/03#).

    You may want to consider a routine which runs these queries (and perhaps a make table query as the final nested query which has the other query(s) in it)....ie. when a button is clicked on your form (On Click <Event Procedure>) have it run the query(s) and after it runs the last make-table query, have a report open based on that table.

    So for example, your first query would specify the date range of the data set and your next query might have this query in it with the SDev function and your next query compares these results from the SDev function query and another query (or table) to compare values and your last query is a make-table query which has in it the query from your comparing results query. In the case where you have "nested" queries, you only need to run the last (or make-table) query as it automatically runs the other "nested" queries.
    Last edited by pkstormy; 07-14-07 at 14:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pkstormy
    the funny looking E button
    The summation symbol
    Other than that - nice reply
    George
    Home | Blog

  4. #4
    Join Date
    Jul 2007
    Posts
    7

    Smile Thank you PKSTORMY this is great info.

    Thank you PKSTORMY this is great info.

    I'll try to do what you suggested it makes sense when I read through it. I am a real amatuer with Access and I hope I can figure it out. One question how do I create a nested table?

    Thanks again for the help.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Tell us what you mean by a nested table please
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2007
    Posts
    7

    Red face

    I'm sorry I meant to type nested "Query"

    i.e. : "first query would specify the date range of the data set and your next query might have this query in it with the SDev function and your next query compares these results from the SDev function query and another query (or table) to compare values and your last query is a make-table query which has in it the query from your comparing results query. In the case where you have "nested" queries, you only need to run the last (or make-table) query as it automatically runs the other "nested" queries." -pkstory reply

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A nested query is a where instead of adding a table to a query, you add another designed query to that query. ie. in design view of the query, when you click the + to add a table, instead of selecting a table, you simply select a query you've already designed. When you first start the design of the query, you should see a "tables" and a "query" tab. Select the "queries" tab to add a query to your query (make sense?) You can also combine mixtures of tables and other designed queries in a query.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jul 2007
    Posts
    7

    Question issues getting field total for date range

    I am having issues with my data set and the date range. I want to get a total number of eaches for a product that falls within the date range specified, however the output show a total number of eaches for the each invoice date. i.e date range 01/01/07 to 01/30/2007 will give me an output of :

    01/02/03 / Product_ID / Product_Description / eaches
    01/05/03 / Product_ID / Product_Description / eaches
    01/20/03 / Product_ID / Product_Description / eaches

    instead we need one line with total for the product ID for all purchases that fall within the time period.

    for this query i use the raw data dump table and a reference table (this reference table is used to standardize package sizes of similar products and associates a product ID) the raw data dump table holds invoice dates andthe reference table holds product ID product description and calculates the subtotal for this item.

    I know I am making this harder than it should be, any advise would be much appreciated

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think you want the summation clicked and then change the Date field column to a "where" with criteria: Between #1/1/07# and #1/30/07# (make sure you put the dates in # and uncheck the "show" checkbox in that column). Then select the field(s) you want to group by and the field(s) you want to sum or calculate. You can also put a total count by summing on an expression field (ie. TotalCount: 1 and then set it to sum on that field.) Also make sure if you use 2 or more tables that all tables are linked together in some way to one another otherwise you'll get duplicated value outputs.

    Calculating subTotals are not the easist to do in a query and sometimes are easier to do in a report due to the ability to define and total on grouping sections. But you can still do it in a query IF you can find a way to group on similiar categories (it may require a separate query though.) Unless I misunderstood you and you just want to sum the SubTotal Field.
    Last edited by pkstormy; 07-16-07 at 22:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Jul 2007
    Posts
    7

    Question

    Hi Paul,

    I used "Where" in the total row and that worked perfect, thank you! I am stuck on how to automate the make-table query for the date range that is entered on the form. If I use the make table query I have to rename the file each time or it overwrites the previous table made from the query. How do I get it create a new table everytime without overwriting, so that I have tables to reference so that I can use the Stdev function. Maybe I am going about this the wrong way. I may not be explaining myself appropriately, I have attached screen shots of what I have currently.

    If you could let me know if my logic wrong I would appreciate it.

    Thanks for all your help, I am really learning alot!

    -Lorri
    Last edited by pavo5455; 07-19-07 at 19:23.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It's difficult to say if your logic is correct or not without actually seeing it in action. There is a good reporting example in the db code bank which shows how you can automate how to do a make-table based on date criteria from a form. You might want to download it and take a look. It might give you some good ideas seeing how this works.

    Regarding the table not being overwriten each time though might present a bit of a problem. You can create an empty table (sort of like a container table) and delete and append the records to this table but I don't know if that helps verses doing a make-table. How many records and users are you dealing with? I only ask because if you're dealing with a lot of records, creating new tables for each date range might slowly build up the size of your database and become a bit loaded with a bunch of old dated tables. And if data changes, some of those tables might not be accurate anymore which might become a bear to keep track of. I suppose exporting the final made table to an excel sheet to maintain a copy of the returned data (with date fields of the range) is out of the question. Also keep in mind that if you have a lot of users using the same database, if one user has the table open, it locks it for other users trying to make that same table so you want to free that table up as soon as possible if multiple users are continuously running the procedures to make the table. I know some people will utilize linked source data MSAccess tables into separate front-ends but I'm not sold this would be the best solution (ie. you have multiple MSAccess front-ends with linked MSAccess tables in them all pointing to the same MSAccess source data tables.)

    Usually, when you utilize the make-table approach, you have the dates on the final report with the equation and thus have a hard copy when printed and don't worry about destroying and recreating the table (again, see the reporting example in the MSAccess code bank). Or an export routine is automated on the query/table (ie. docmd.transferspreadsheet.... command) - see also the import routine in the MSAccess db code bank which can easily be changed to an export routine. There are quite a few good examples in the code bank you may actually want to take a look at. I learned quite a bit simply from looking at other people's code.

    Some others might want to chime in here on a few suggestions regarding keeping the old data for the StDev but my first inclination is to do a report or export the data to excel.
    Last edited by pkstormy; 07-20-07 at 02:30.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •