Any MS Access guru's out there?

Discussion in 'OT Technology' started by Argovian, May 12, 2004.

  1. Argovian

    Argovian New Member

    Joined:
    Dec 29, 2003
    Messages:
    107
    Likes Received:
    0
    Location:
    Dallas,TX
    I'm trying to write a query to group my data based on the days of the week, (M,T,W,Th,F). Currently the date is in a Short Date format. I dont have a specific column to sort them manually, was hoping to avoid that. If anyone has any suggestions please let me know.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You have to create an expression for the column (you don't need to add another column in your table though - just in your query)

    weekday([sometable]![datefield])

    and then do a group by on that expression
     
  3. Argovian

    Argovian New Member

    Joined:
    Dec 29, 2003
    Messages:
    107
    Likes Received:
    0
    Location:
    Dallas,TX
    That worked great! Thanks for your help.

    Do you know if its possible to also sort them into weeks?

    Like:
    Sum of Field1, for May 2nd through May 8th. (Sun - Sat) So that you can break a month into 4 or 5 weeks?
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Gets a bit dicey, but you can do it. If you create one column where you break it down by month:

    month([sometable]![datefield])

    and then another column by week of the year:

    datepart("ww",[sometable]![datefield])

    That should work. You will get duplication of the week of the year since they get split between months but I think that's what you are looking for.
     
  5. Argovian

    Argovian New Member

    Joined:
    Dec 29, 2003
    Messages:
    107
    Likes Received:
    0
    Location:
    Dallas,TX
    5Gen_Prelude, thx for your help. I hate to be a pest, but I got myself stuck again. Those other commands worked, and I have my data sorted by week.

    I"m trying to recreate an Excel Table into Access.

    This is kind of what it looks like in Excel:

    [​IMG]


    I can create the query to Group by location (Local & Branch) and then I made a new query for the totals, but obviously it doesnt want to make 1 chart with them. So it wants 1 query for the whole thing. Is it possible to have it total those? As well how the heck do you turn something into a percentage?

    I tried using:

    Sum([April_04_VDN]![ACD Calls]) And [April_04_VDN]![Location]="LO" Or [April_04_VDN]![Location]="BR"

    But of course it didnt work, it just shows me what I already have.

    Thx again for your help.

    :bowdown: :bowdown:
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I'm having problem understanding what you are trying to do now.

    As far as your percentage problem is concerned (like your Aband), choose Expression as Total Type (ie Sum, Group By, Expression):

    Sum(TotalDrops)/Sum(TotalCalls)

    Then just change the format of that column into percentages.
     
  7. Argovian

    Argovian New Member

    Joined:
    Dec 29, 2003
    Messages:
    107
    Likes Received:
    0
    Location:
    Dallas,TX
    Here is what my Table Fields look like.
    [​IMG]

    In the Query, I"ve got the following:

    [​IMG]

    But I need to get it to Sum up each week. So that it would show the Total for April week 14. Adding the Local (LO) and Branch (BR) ACD Calls together. So that I can put it all on 1 pivot chart.

    Hope that helps...
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Why not just create another query based on this query then?

    I mean to add the BR and LO together, just don't group that field, if you need to qualify the records so that you only show BR and LO, then instead of grouping them, just change it to WHERE, and that will limit the records to BR and LO, but not group on that field.
     
    Last edited: May 12, 2004
  9. Argovian

    Argovian New Member

    Joined:
    Dec 29, 2003
    Messages:
    107
    Likes Received:
    0
    Location:
    Dallas,TX
    I have two individual queries one that seperates them out by Location & one that sums up the whole week. However, when I try to put all that information on a Pivot Table, it says I cant link them together. So I figured it all had to be on 1 query.
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That is correct, you can't link them because there is nothing similar about the values. What exactly would you put in the location field for the totals then? Has to be something right? How useful is this graph going to be if you're comparing a partial, a partial and the sum of those partials?

    Regardless, what you'd need to do is combine the data into one table. You can create another query to make a table based on your first query, and another query to append the data from the second query, thus giving you what you want - one table with all of the data. In the Location field you could append it with the simple word of All or Total.
     

Share This Page