Results 1 to 6 of 6
  1. #1
    Pluven is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4

    Practical problem variables in query

    Hello,

    First off i'd like to say that i'm not that good in access so it is possible that there's a simple solution to my problem but i just can't find it. I'll try to explain it as well as i can.

    Where i work the technicians use an access database to report problems. Here they fill in if certain production lines have had downtime and the reason why.
    I also added which line they mean depending on the machine they have selected. So basically i have a table looking like this:

    Downtime: 2 5 3 4 8 2 1
    Line: Line1 Line2 Line1 Line4 Line1 Line3 Line2

    Now i want to be able to show in a Pivotchart how the UPtime was in % per week. simple calculation would be: ('total hours week' - 'Downtime')/('total hours week') *100
    The problem is that we never have the same total hours in a week so i need to put it in manually every week.

    Now how can i, using this data (downtime, line & manual input of total hours every week) make a query that calculates the Uptime % every week?
    Please note that the downtime hours are put in per day and i only know the total hours per week.


    Thanks in Advance,
    Pluven


    Note: This has to be done in access 2003

  2. #2
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Interesting. I actually have an application that I'm working on now for exactly that purpose - downtime hours by line. You can accomplish this by using several queries. I'm assuming your Downtime table also has WeekNo and RunDate fields? I'm also assuming you are storing your weekly hours in a table that also has Line, WeekNo, ScheduleHours. If so you can create the following queries to get you where you need to go.
    Code:
    SELECT Sum(tblLineDownTime.ActualDT) AS SumOfActualDT, tblLineDownTime.WeekNo, tblLineDownTime.Line
    FROM tblLineDownTime 
    GROUP BY tblLineDownTime.WeekNo, tblLineDownTime.Line
    Save this query - we will call it Query1

    Create a new query and add your TotalHoursPerWeek (table you are storing weekly hours in). In my project it's called tblLineSchedule
    Code:
    SELECT tblLineSchedule.Line, tblLineSchedule.Hours, tblLineSchedule.WeekNo
    FROM tblLineSchedule
    Save this query - we will call it Query2

    NOTE: Your tblLineSchedule should only have one record per line per week.

    Create a new query and add Query1 and Query2 in it - join on weekNo and Line.
    You probably don't really need Query2 as you can just join Query1 directly to your schedule table but I added it here as a visual.

    you can start creating your calculated fields in this query.

    Let me know if you need further help with this application or plan on making further enhancements. The application I am working on now has dynamic charts that provide downtime% by line and week with weekly average and ytd average trend lines - we have about 5 charts both line and bar charts that get drawn dynamically when a user selects the week or date range they want to view for.

  3. #3
    Pluven is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4
    Thank you very much for the reply, i'll defenitly try this out and get back to you!

    Thanks.

  4. #4
    Pluven is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4
    Hello again,

    I tried out your solution (for which i thank you) and have come across an annoying problem.
    Everything works except the weekno. As i want to use the Date/Time value from the table with the downtime i tried putting it in format ww. But Access keeps seeing this as a day instead of a week. It sais '15' but if you click on it, it changes to the day which is very annoying. Now i'm sure i'm just missing something simple here but i can't quite figure it out.

    Thanks

    Update: to avoid making things complicated i am now using the Year & Month functions to show the data per month instead of week.

  5. #5
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Okay. So you have your queries set up with WeekNo= Format(DateEntered,"ww")? And then are you trying to display from the query field on a form or in a datasheet?? What is supposed to happen when you click on it? Can you post some screenshots of when you are clicking and it changes to day?

  6. #6
    Pluven is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4
    Well we have decided to work with months, this is alot simpler as we can just use the Month() function, so for me this is solved but i'll upload some screens of problem that annoyed me endlessly

    Attachment 7285
    Attachment 7286

    Thank you for your help and the fast responses.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-29-2011, 03:12 PM
  2. Using date variables from table in query
    By Skybeau in forum Access
    Replies: 1
    Last Post: 12-22-2011, 08:46 PM
  3. Using user inputs as variables in a query/report?
    By cheese9799 in forum Reports
    Replies: 3
    Last Post: 03-01-2011, 11:30 AM
  4. Replies: 9
    Last Post: 02-17-2011, 03:33 PM
  5. Append Query using variables
    By hawg1 in forum Queries
    Replies: 2
    Last Post: 06-09-2010, 08:59 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 - Senior Forums