Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1

    Unhappy Unanswered: Rounding And Exporting Issue In Query

    Hello All,

    I am trying to accomplish this task for two days now with no luck. Please understand my knowledge is limited when it comes to access.

    I have been using excel for sometime now and it does what I wanted to do but frankly I am tired of creating new tabs and editing fields every month as new tab gets created monthly and new sheet gets created yearly.

    so I have decided to move to access. In my humble opinion access handles data better in most ways and there is little modification required when it is completely setup.

    The end goal is to export all calculation to an excel format using access with the click of a button.

    so here it goes.....I have recreated my excel sheet in access format and it is not doing the following.

    Issues:
    1. Not sure how to make days field (in query) to round off days for the whole month?

    Rounding off formula in Query1 Days: Round([DailyTotal]/8,1)

    DailyTotal: [BusinessHours]+[NonBusinessHours]*1.5+[Travel]


    2. Once exported to an excel format using macro, the result is not based on my template specified in the macro. Output looks like a standard query output.
    button on the form is linked to macro which exports to an excel format

    I am uploading my excel template and my db.

    any help is greatly appreciated!!!

    Regards

    Ranjah
    Attached Files Attached Files
    Last edited by ranjah; 01-07-17 at 20:36.

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    I agree with you that Access is better at handling data. Excel is (usually) more user-friendly, though.

    Issue 1 - I would advise you not to use calculated columns in tables. If you want to perform calculations on table data, use queries and forms. There's nothing to stop you from basing a data entry form on a query. That being said, the UI in Access uses a lot of the same functions as Excel, so a certain amount of porting from one to the other is possible.
    From what you've put here, I can't see all of what you're trying to achieve, and you haven't stated what incorrect answers you're getting.

    Issue 2 - once I'd amended the the macro to deal with restrictions on my PC (can't work directly on the C drive), your macro correctly created a new workbook based on the template. However, your template includes a calculation in column H, and your output query has eight columns. This means that the macro will use the template to create a new workbook, and then the data from the query will over-write what is in the template for column H. Either move the calculation in the template to column I, or remove the final column from the output query.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1

    Unhappy

    Hello Weejas,

    After reading your posts I took my time and made some changed to my database.
    however even after making changes I am still not able to do the following.
    1. Cannot ROUND the monthly total entries from each user. for e.g: if Bob has 18 hours total for the month then you ROUND this to 2.3 days. (See database attached)
    2. Once I export the data to excel it not only deletes the formula for ROUNDING but also gets rid of my format as well. For e.g: changes font, deletes highlight of a user etc etc
    Please see the attached database file and an excel template before running the db so you can see the format.

    Please let me know what I need to get this fixed...I appreciate any and all help

    Regards
    Attached Files Attached Files
    Last edited by ranjah; 01-23-17 at 22:48.

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    1. If you want to calculate the number of days billed in a month, then you need to aggregate the hours billed by worker and month. Also, please note that Access's Round function takes two arguments - the decimal value with too many digits, and an integer that's the number of digits that you want. Further, while it generally rounds up (away from zero), if the final digit in the first argument is 5, it will round to an even number.
    Code:
    SELECT 
    Year([ActivityDate]) AS ActYear, 
    Month([ActivityDate]) AS ActMonth, 
    tblDelivery.DoneBy, 
    Sum([BusinessHours]+[Travel]+([NonBusinessHours]*1.5)) AS TotalHours, 
    Round([TotalHours]/8,1) AS TotalDays
    FROM 
    tblDelivery INNER JOIN tblhours 
    ON tblDelivery.DeliveryID = tblhours.DeliveryID
    GROUP BY 
    Year([ActivityDate]), 
    Month([ActivityDate]), 
    tblDelivery.DoneBy;
    This yields:
    Code:
    ActYear    ActMonth   DoneBy     TotalHours TotalDays
    2016       12         Bob        18         2.2
    2016       12         Jim        8.5        1.1
    2016       12         Mike       7.75       1
    As you can see, Access rounds 2.25 (18/8) to 2.2.

    2. When you use a macro to export data from a query to Excel, the process effectively copies the output from the query and pastes it into the worksheet. This replaces all formatting and formulae in the overwritten cells. If you want to output the results from the query but preserve the formatting in the worksheet, you will need to write a VBA subroutine that connects to Excel and controls how the data are used. This is not impossible, but it's beyond the scope of macros. The action "ExportWithFormatting" means that the data will be exported to the specified file format, not that font and formulae settings will be preserved.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    Have you Trying LINKING the Data into Excel That should stop the overwriting of the formating

    also with the Round problem Been there pulled my hair out also

    this mite help

    http://access.mvps.org/access/modules/mdl0054.htm
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  6. #6
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1

    Smile

    Hello Weejas,

    what a good teacher you are !! Thanks for the education I am starting to see the picture now.

    1. your query is working great but I am not seeing the result I see in my excel sheet, which is 2.3. So I tried changing the 1 to 2 Round([TotalHours]/8,2) and now it gives me 2.25hrs.

    How can I round that to 2.3 which is what excel sheet gives when you apply this formula. =ROUNDUP(F8/8,1)

    2. Also I am trying to see the full date for all records rather than month and year separately.
    Example:
    12/6/2016
    12/2/2016
    12/6/2016

    3. You have mentioned about writing a vba subroutine for formatting. I am at a biggenner level for access.

    Can I link my data to an existing spreadsheet until I fully grasp the sub routine concept?

    When you see the spreadsheet it will show each user name separated by yellow row.
    I am attaching my spreadsheet for reference.

    I know I am asking for a lot but trust me I am learning quite a bit.

    Thanks again!!
    Attached Files Attached Files

  7. #7
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1
    Hello Myle,

    Thanks for jumping in. I agree Rounding function is nothing but a nightmare for me.

    Its funny I just got done asking Weejas on how to link to the spreadsheet.

    do you have an example database that I can look at?

    or a code that I can try?

    Thanks again!

  8. #8
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1
    Data is usually pulled 3 to 5 days after the current month is over. so to pull the data for the month of January we need to do it on February 1st or beyond.
    For the month of February we need to pull it on March 1st or beyond.

    Original Working Query MINUS the Rounding function:
    SELECT tblDelivery.DeliveryID, tblDelivery.ActivityDate, tblDelivery.DoneBy, tblhours.BusinessHours, tblhours.NonBusinessHours, tblhours.Travel, tblhours.DailyTotal, Round([DailyTotal]/8,1) AS Days
    FROM tblDelivery INNER JOIN tblhours ON tblDelivery.DeliveryID = tblhours.DeliveryID
    WHERE (((tblDelivery.DoneBy)=[Name]) AND ((Year([ActivityDate])*12+DatePart("m",[ActivityDate]))=Year(Date())*12+DatePart("m",Date())-1));

    There are 3 main problems with the existing Query:
    1. As soon as I change the date on any record to 1-1-2017 or 2-03-2017 it messed up the days total. I should be able top pull any monthly data by simply specifying username and month name or date (1-01-2016)

    2. Rounding formula Round([TotalHours]/8,1) is still giving me 2.2hrs where it should be giving me 2.3hrs. for Bob 18hrs/8 gives me 2.25 and when you round it, it should be 2.3hrs.

    3. when I try to add the remaining fields into the current query, I start to see the incorrect results.
    I have added few (NOT ALL) fields to the existing query and now I am getting incorrect results.

    If I take out the fields I have just added then it works fine. I am guessing relationship needs to be fixed in the query?

    Working Query with just calculation fields:
    SELECT tblDelivery.DoneBy, Sum(tblhours.DailyTotal) AS TOTAL_For_Member, Format([ActivityDate],"mmm - yyyy") AS Month_Year, Round(Sum([DailyTotal])/8,1) AS Days
    FROM tblDelivery INNER JOIN tblhours ON tblDelivery.DeliveryID = tblhours.DeliveryID
    GROUP BY tblDelivery.DoneBy, Format([ActivityDate],"mmm - yyyy");

    Current Query:
    SELECT tblDelivery.DoneBy, Sum(tblhours.DailyTotal) AS TOTAL_For_Member, Format([ActivityDate],"mmm - yyyy") AS Month_Year, Round(Sum([DailyTotal])/8,1) AS Days, tblhours.BusinessHours, tblhours.NonBusinessHours, tblhours.Travel
    FROM tblDelivery INNER JOIN tblhours ON tblDelivery.DeliveryID = tblhours.DeliveryID
    GROUP BY tblDelivery.DoneBy, Format([ActivityDate],"mmm - yyyy"), tblhours.BusinessHours, tblhours.NonBusinessHours, tblhours.Travel;


    Query works @ 99% (Minus the rounding option) with just the calculation field but when I add few (NOT ALL) of the remaining fields it gives me incorrect results.

    Any ideas?
    Last edited by ranjah; 01-24-17 at 21:02.

  9. #9
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    You're welcome, ranjah! I've learned a lot from this forum, and I'm happy to be able to pay it forward.

    How can I round that to 2.3 which is what excel sheet gives when you apply this formula. =ROUNDUP(F8/8,1)
    Access uses a different standard for rounding. As I mentioned, when passed a decimal that ends in a 5 (e.g. 2.25), it rounds to the nearest even result. Therefore, 2.15 and 2.25 will be be rounded to 2.2. To get around this, you would need to write your own version of the Roundup function. The example that myle provided is a good one - I've used code written by Dev AShish before, and they've always been reliable.

    Can I link my data to an existing spreadsheet until I fully grasp the sub routine concept?
    I have used linked spreadsheets before, but not for exporting and definitely not that use Excel functions that are incompatible with Access. Instead I've used them for importing data, and even then I've generally moved the data into native Access tables before use. The trouble is that while Excel is laid out as a table, you can merge cells, change formatting and write formulae that relate to cells or ranges. You can't do this with Access. I haven't tried linking tables with any versions post 2007, so I can't say what Access will do if you try connecting a formatted spreadsheet to it.

    Rather than export your data to Excel, why not try designing a report to show the query results? Use the Wizard to generate a couple of examples, and then pull them apart in Design view to see how they work.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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