Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 49
  1. #16
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    OK on the dates, so specifically, what do you want to see on the subform?

  2. #17
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    I need to see Total Usage (what's already there as field "Usage"), Usage Date (also already there), Weekly Usage, Monthly Usage, and Monthly Iron Days.

    Weekly Usage = Usage - Previous Usage

    Monthly Usage = Usage - 4 Weeks(entries) Ago Usage

    Monthly Iron Days = Usage - 4 Weeks(entries) Ago Usage /(Divided by) 8

    So you're saying I can get all of this to display on the current Usage subform, are you talking now about creating another subform to hold this data?

    The kicker is I want this subform to be in the Usage History tab next to the current usage subform and only display the usage pertaining to the forms currently displayed Asset.

    Wow I just thought about something, probably something you may have already mentioned... Couldn't I just add some new unbound controls to the current subform with the calculations in them???

  3. #18
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Is the attached what you are after? I only did the previous week value since the other queries have to be set up for your other calculations.

  4. #19
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by jzwp11 View Post
    Is the attached what you are after? I only did the previous week value since the other queries have to be set up for your other calculations.
    That is EXACTLY what I was after... Dang dude, thank you! I am going to examine what you did and see if I can't tackle the other ones myself so I can learn something.


  5. #20
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Where should I be modifying to change it to be searching 3 records before current? I am looking in Query 1 and I'm thinking it's in either MonthEndUsage or MonthEnding (I have copied the query and made it MonthEndUsage / MonthEnding). The select strings are above my skill level though, I am uncertain what some of the references do such as "top 1".

    Also what does the # signify in the text box?

  6. #21
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You need to create a new query that uses one of the queries I created. If you modify the ones I provided, it will mess other things up.

    Previous 3 records? You mean you want to get the previous month's data correct? You will need to use some of the date manipulation functions built into Access. You might need dateserial(), dateadd() to name a few.


    The # signs are used for dates. The dates have to be enclosed between the # signs. Text values have to be enclosed within single or double quotes depending on what you are doing. Number fields do not need to be enclosed in anything.

  7. #22
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by jzwp11 View Post
    You need to create a new query that uses one of the queries I created. If you modify the ones I provided, it will mess other things up.

    Previous 3 records? You mean you want to get the previous month's data correct? You will need to use some of the date manipulation functions built into Access. You might need dateserial(), dateadd() to name a few.


    The # signs are used for dates. The dates have to be enclosed between the # signs. Text values have to be enclosed within single or double quotes depending on what you are doing. Number fields do not need to be enclosed in anything.
    Yeah I copied one of your queries yesterday to try and begin modifying it to work for monthly usage. I will do some research on the functions you listed to see how I can get them to work for me. Thanks for the explanation on the # signs, it makes more sense now looking at the control source for the week usage box on the subform.

    I'll report back with what happens when I try to get the monthly usage to work.



    EDIT: dateserial() looks like it should work for this, but I have a question about it that you probably know the answer to. Is there a way to control only one part of the date? What if I don't want to specify a year or day (otherwise I will have to update it yearly). Is there an operator that I can use for "any"? Something like dateserial(#,#-3,#) or something to that effect?

  8. #23
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You can use variables within the function. For example, in Query3 I provided you have the PeriodMonth and PeriodYear, you can use those variables in the dateserial() function to construct a date.

    dateserial(PeriodYear,PeriodMonth,1) would yield the first of the (variable) month/year

  9. #24
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    I don't know why this is so confusing to me. These queries are just hard for me to make sense of.

  10. #25
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I show query1 below with the 2 subqueries in red and blue

    Code:
    SELECT AssetUsage.AssetID, AssetUsage.Usage AS WeekStartUsage, (Select top 1 Q1.usage  from AssetUsage as Q1 WHERE Q1.AssetID=AssetUsage.AssetID and Q1.UsageDate>AssetUsage.UsageDate Order by Q1.AssetID, Q1.Usagedate) AS WeekEndUsage, (Select top 1 Q2.usagedate  from AssetUsage as Q2 WHERE Q2.AssetID=AssetUsage.AssetID and Q2.UsageDate>AssetUsage.UsageDate Order by Q2.AssetID, Q2.Usagedate) AS WeekEnding, WeekEndUsage-WeekStartUsage AS UsageAmount
    FROM AssetUsage
    ORDER BY assetusage.AssetID, assetusage.usagedate;
    The query shown in black is your base query; I've used an alias for the Usage field so that I can distinguish starting usage from the next usage entry.

    The query shown in red finds the usage for the asset that has a date greater than the StartUsageDate and has the same asset number as the base query (shown in black). The TOP 1 predicate shown in the red subquery just takes the first record that meets the criteria. Note that all the queries are ordered (sorted) by assetID and usage date. Given that the default order is always ascending, you want to find the usage that is next in line for the asset that is also greater than the current record's usage date. A subquery can only return 1 field from 1 record so the Top 1 limits it to the first record that meets our criteria. Since the subquery can only return 1 record from 1 field, I had to add the second subquery (in blue) to also return the date that corresponds to the usage from the red subquery. Basically, the blue subquery is the same as the red but I just brought the date in rather than the usage value. A subquery must not have the same name as the base query therefore I named them Q1 and Q2 (red and blue respectively).

  11. #26
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by jzwp11 View Post
    You can use variables within the function. For example, in Query3 I provided you have the PeriodMonth and PeriodYear, you can use those variables in the dateserial() function to construct a date.

    dateserial(PeriodYear,PeriodMonth,1) would yield the first of the (variable) month/year
    When I use dateserial() how do I reference the PeriodYear/Month as they are in a different query. I tried using QueryX.PeriodYear and Month (X=whatever the number of the query it is that it needs to point to) and it's giving me the enter parameter value box.

    Edit: Nevermind, needed to add the query to the main FROM statement.

    Should it look something like this? (i have renamed all of the queries that you originally made so that they were more descriptive and knew what was what)
    Code:
    SELECT AssetUsage.AssetID, AssetUsage.Usage AS MonthStartUsage, (Select top 1 Q1.usage  from AssetUsage as Q1 WHERE Q1.AssetID=AssetUsage.AssetID and Q1.UsageDate>dateserial(qryMonthlyUsageCalc2.PeriodYear,qryMonthlyUsageCalc2.PeriodMonth,1-30) Order by Q1.AssetID, Q1.Usagedate) AS MonthEndUsage, (Select top 1 Q2.usagedate  from AssetUsage as Q2 WHERE Q2.AssetID=AssetUsage.AssetID and Q2.UsageDate>AssetUsage.UsageDate Order by Q2.AssetID, Q2.Usagedate) AS MonthEnding, MonthEndUsage-MonthStartUsage AS UsageAmount, AssetUsage.UsageDate AS UsageStartDate
    FROM AssetUsage, qryMonthlyUsageCalc2
    ORDER BY AssetUsage.AssetID, AssetUsage.UsageDate;
    Edit: Ok I think I have something backwards, I think I added the dateserial() part on the wrong side of the equation.

  12. #27
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Should it look something like this? (i have renamed all of the queries that you originally made so that they were more descriptive and knew what was what)
    I'm not sure. If the query returns the correct data, then it is correct. In other words, you have do some manual calcs to verify that the query is returning the values you expect.

  13. #28
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202

    Question

    Ok so it looks like I have it looking back 30 days now, but to me at least it seems as if the monthstart/end dates are reversed? Also, some of the query results show negative, some show positive, and some of the source information looks likes it randomly coming up in reverse order? ie. all of my usage entries are increasing in value, but in the query some show up in reverse, others do not?

    It's qryMonthlyUsageCalc1 that I'm trying to work with. Thx.

  14. #29
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The FROM clause in your query is a problem

    FROM AssetUsage, qryMonthlyUsageCalc2

    This will return a Cartesian Product of the assetusage table and qryMonthlyUsageCalc2 which is not what you want. I missed that earlier, sorry.

    On your form you have the control labeled as follows: Previous Months Usage

    Is it just the sum of the usages from all previous months prior to the current month/year?

  15. #30
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Yes from what I think you are thinking. The equation should pretty much be exactly the same as it is for getting previous weeks usage, except it should span a month long period opposed to a week long period.

    I will have to research a Cartesian Product, you lost me on that one!

    Edit: Forgot to mention, it CAN be a rolling month, shown at every week. It doesn't have to be only on the weeks that make a new month.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 09-02-2010, 01:59 PM
  2. Replies: 2
    Last Post: 07-26-2010, 11:28 AM
  3. Repeated rows in query-form
    By astraxan in forum Forms
    Replies: 2
    Last Post: 05-23-2010, 10:25 PM
  4. Subtract Rows in Query Results
    By Sengenbe in forum Queries
    Replies: 5
    Last Post: 02-08-2010, 06:05 PM
  5. Adding rows on cross-tab query report
    By KahluaFawn in forum Reports
    Replies: 2
    Last Post: 02-18-2009, 10:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums