How do I calculate a week number based on a field?
Calculating percentage (%)
I am wondering if it is realistic to create a query that will calculate the total % of a field based on four other field.
First field which will calculate the % of Q1
Second field which will calculate the % of Q2
the third field which will calculate the % of Q3
and the fourth field will calculate the % of Q4
AND the last field (the fifth one) should be the sum of Q1, Q2, Q3 and Q4 percentages and which should be equal to 100%
Converting dates into week numbers on graph
I need to make a graph based on sale records. Every sale record has hours done field, date field and company department field who did the sale. On graph Y-axis I would need to see how many hours were done each week and then on X-axis week numbers based on dates.
As an example for April I would have Week 1 and every sale done between 02/04/2012 and 06/04/2012 would go into Week 1 category and so on (counting Monday to Friday), then Week 2 would have all records between 09/04/2012 and 13/04/2012 - so forth for every week.
Graph is not static of course so it wouldneed to update accordingly for every new upcoming sale as well.
to sum number of hours for each company department (just 2 of them) each week based on the dates I have?
Count the number of Working Weeks
I am working on a new project now and I need some help. Here is the situation:
When we enter the serial number of a battery, we have to check if it will be rejected or not. For example if the serial number was SP044000056: the third digit is for the year this means that the batteries was manufactured in 2010 and on the 44th working week.
So the problem now is to calculate the number of week between the today's working week and the working week in which the battery was manufactured.
I am able to extract the working week of the battery which is 44 and the year which is 2010 with the function MID.
How can I continue
Report with Running Sum by week
I have a report is built off of a query that contains the following fields:
I would like to be able to keep a running total of how much the employee has contributed each payroll based on his deduction. The payroll is bi-weekly so there are 26 periods in the year.
the payroll dedution amount is a calculated amount based on the annual contrbutuion/26
I was hoping that I could have a field on my report that would allow me to key in the week number and it would calculate the Sum of his dedcutions but I can not find a way for this to work.
Will this work or is there a better way to go about getting this information onto my report.
Calcuating Values Pt/2
I have one last question which is my last real hurdle with this database. I am going to attach a demo of the database in question. RuralGuy has helped me get this far, now I would like to know if it would be possible to do multiple calculate using the code he provided.
I mean with his code we are calculating the total PcsProduced based on the legenths*PPL. fields
I need to be able to calculate hours earned based on the PcsProduced field
this is calculated by taking the PcsProduced / SpecSPM that will = Hours Earned
I also need to calculate the Standard Hours. To calculate this I would take the Number Machine Run / 480 / 60
Basically I need to calculate multiple values, based on other calculated values. Possible
Volume of work per week
I need a formula to calculate the sum of the total number sales per a given work week
Date and week fields in table
I am having problems with date and week fields in a table. Since I start my week number at 1 for the school year as of August going until week number 52 in July, I have put the week number along with week start and end date in a separate table from the date table. Since I use several years worth of weeks, I have run into a problem when I try to tie the date and week fields together to sort records. Do I need to include the week number, week start and end date in the date table? Does anyone have any suggestions as to best how to do this? I will be glad to give more details to find a solution
Financial/ Fiscal week number
Using the following in a query field, I can retrieve the week number from a field called 'StatusDate' where week 1 is always 1st April to 7th April regardless of the weekdays concerned
Week number wrong
I am extracting some information from my database in week format.
I have done this in the normal way of using the Format function and W.
The week number it gives me is one ahead of actual week number in my diary.
Has anyone else come across this, or know how to fix it
Changing Week Start Day
Access 2007, Crosstab query .Evening - We've just started opening our business on a Sunday and this little issue has just manifested itself!We record volumes of calls previously just Monday to Saturday and the call volumes are grouped by week number.
Which worked fine, now we open Sunday the Sunday values are pushed to the next week number. The customer wants all data for the week grouped Monday to Sunday, otherwise I'd just let the pushed week roll into the next week.
So for this week 16th to 22nd May, my week number would normally be 21. But the 22nd data is added to week 22
The current query field is:
This works. I've done some digging and found this possible suggestion
Val(Format([WorkingDate], "ww", vbMonday))
So my query field looked like this
But when I leave the field it adds  round the vbMonday, and the query fails.
So any pointers so that my week number group goes from Monday to Sunday?
Calculating the Difference between the same Sumof Field
I have a Pivot Table created from a query as follows.
Week No: Format([Sales Date],"ww")
New Week No: Right("0" & [Week No],2)
Having created a Pivot Table from the above query, it look like the image attached. Since the SumOfNet Sales is the same field, how do I calculate the difference between the 2 for each year. Ie. for say Week No 15, to calculate the difference between Net Sales of 2011 to Net Sales
Return a value on to my form depending on the date/week number
I have a form and I want it to show me stock levels for a particular week.
My table lists each item number and then tells me the stock that we have for each week by number.
On my form I want to return the quantity of the item that I am looking up, for each week. So I have a field on my form that I want to show week 38 and we have 20 in stock. Then in anotherfield, to show me week 39 and we have 50 in stock.
Yes / No switch with odd or event numbers
I am using an unbound form field to display week number based on a date field. I want to write vb code to run a query based on even or odd weeks.
VBA to select record in listbox based on textbox value
I've been reading up on selecting records in a list box using VBA but so far I can only find details of selecting a record based on counting how many there are.
Can anyone tell me how to select a record based on its value.
Eg I have txtbox1 which contains =Format(Now(),WW) to give me the week number.
I want to look that week number up in LstBox1 and select it using VBA.
How to calculate cost of good sold in access vba
- I need some basic help .I have a table with three field , Sales, Cgs and Average profit. I want to create a form based on that table .the problem is I dont know how to calculate the cost of good sold using access form .I need to calculate cgs but the situation is
I have to enter cost per item from purchase once a week and enter total sale every day.How do I multipy the quantity of sale with cost per item ?
if some someone help me I shall be grateful to you.
How to group by week
I have a list of rows in a table with a date field in every row. How can I pull out that data in a way that counts the number of rows in a certain week.
So basically, I want to report on entries grouped by week.
function for week number in month
is there a built in function for returning the week number for the month? I am aware of the the weeknumber function that returns the week number for the year, what I am looking for is the week number for the month. For example today is August 6, 2011 and this day is in the first week of the month of August (i.e. week 1 for August). If there is no built in function does any body know how to do it in VB
Date Comparison using vba
I have a table that contains a field (called "At") of type Date/Time in the format yyyy-mm-dd. I have another table that contains a field (called "EndDate") of exactly the same type and format. This tables data is a week ending/week number mapping table.
I've written a simple VBA module to iterate through the first table, matching the date with the date in the second table to find the relevant week number.
Here's my code
An example of the output I am getting is that for a date of 2010-09-06, it is returning week 10. This should be 22. Curiously, 2010-06-09 IS week 10!
Calculate Gender Formula in Excel
Someone created an database in access and they want me to calculate the total number of male and female they have entered on the database. The problem is I can't sort it based on gender because they never made a field and put was the person male or female.
So how can I calculate the total of female and male without having to go one by one to determine if the person is aman or a woman.
Looking for a good calendar with week numbers
I need to find a way to create a dynamic 3 column combo box (1 will be week number, 2 & 3 will be date range from/to)
It should be able to start on a specific date and end with current week. I have a week number combo built (See below) but I dont know if it will work after the new year. And I dont know how to put the date ranges into the mix.
Does anyone know of a calendar that will give me the week number and a data range that I can plug into this project? This is almost the last bit of code for this project and I am ready to put this puppy to bed