Rounding And Exporting Issue In Query, Access 2013

[ranjah]

Hello All,

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.

Things I am trying to accomplish but unable to do so:
1. Rounding dailytotal field for each user to days. This addition is done using monthly total of dailytotal field and rounding that total to days. so if Bob has 9.75hrs for the month of January then it should round to 1.3 days.
My excel formula that works: =ROUNDUP(G8/8,1)

2. Export query in excel format that will show all users for any given month by just specifying month/year name. so for e.g if we are in the month of march 2017 and I want to see data for any or all users for the month of January2017 then I just type 1-1-2017 and it should output that into excel format.
3. Also if I say cancel to the query after clicking a button it gives me error. I have tried several code for ignoring the error but it is not working for me.

I have tried this formula but doesn't give the result I am looking for.
Days_2: Round((([BusinessHours]+([NonBusinessHours]*1.5))+[Travel])/8,1)

Please see my attached db and excel sheet that shows data output.
Days field is not showing the correct data,. The data that I am looking for in this field is in correct days field.

any help is greatly appreciated!!!

:banghead:

Regards,

Ranjah

 

[CJ_London]

its because you are not comparing like with like - in excel you are applying the roundup function and in access the round function. There is no equivalent to the roundup function in Access - and if there was it still wouldn't work as you expect because in excel you are applying it to the total and in access to the individual rows.

This is the sort of thing you would use a group by query to get your total then a specific calculation to get your roundup

 

[sxschech]

I've used this function in a query where I needed to replicate a rounded number to the way things looked when the data were formerly in excel:
http://access.mvps.org/access/modules/mdl0054.htm

 

[ranjah]

Hello CJ_London,

I have made change by adding a coding to the current form but it is just rounding the main form and I still don't know how to export that in my query.

Do you have any example I can use? can you please take a look at my example and guide me. I would really appreciate it!!!

My database and excel file attached.

Regards,

 

[ranjah]

I've used this function in a query where I needed to replicate a rounded number to the way things looked when the data were formerly in excel:
http://access.mvps.org/access/modules/mdl0054.htm

Hello Sxschech,
Thanks for the link. I am guessing this code needs to be copy pasted into a module which shouldnt be hard.
how to do I call this module and also what fields I need to change on this code to match with my database?

sorry I am at a beginner level when it comes to access.

Thanks,

 

[sxschech]

I am guessing this code needs to be copy pasted into a module

-yes.

Something like this:

Days_2: XLRound((([BusinessHours]+([NonBusinessHours]*1.5))+[Travel])/8,1)

 

[ranjah]

Hello CJ_London & Sxschech,

I have modified my query a bit which seems to be working at 95% right now.
Let me explain:
My query is given me 2.25hrs which is not what I see in excel. Excel sheet gives me 2.3hrs.

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. Since I am trying to keep the format which goes away when query executes Can I link my data to an existing spreadsheet?

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

here is my query at the moment.
SELECT Year([ActivityDate]) AS ActYear, Month([ActivityDate]) AS ActMonth, tblDelivery.DoneBy, Sum([BusinessHours]+[Travel]+([NonBusinessHours]*1.5)) AS TotalHours, Round([TotalHours]/8,2) AS TotalDays
FROM tblDelivery INNER JOIN tblhours ON tblDelivery.DeliveryID = tblhours.DeliveryID
GROUP BY Year([ActivityDate]), Month([ActivityDate]), tblDelivery.DoneBy;

Please let me know.

 

[CJ_London]

perhaps if your rounded access to 1 dp as you have in excel you would get the same result

 

[ranjah]

1 or 2 db still doesn't get me correct result.

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?

 

[sxschech]

I tried the xlround function on the data you provided, does seem to round up with 2 decimal places, but doesn't with only one. Sorry about that.

Days    Daysxlround
0.12    0.13
0.94    0.94
0.44    0.44
0.31    0.31
0.12    0.13
0.31    0.31
2.24    2.26

Perhaps it has to do with the underlying data and the actual amount of decimal it contains, because in the immediate window, if I calculate as a hard coded number, it does round up.

? xlround(2.26,1)
2.3

Maybe you may have to save the rounded data in order to do your final rounding, or do the rounding as the very last step instead of in the middle?