Difference Between Weekly Sales
Sep 11, 2011
In a table I have the sale of a department:
week SP Value UNIT
N John 100 8
N-1 John 80 6
N-2 John 120 10
N-3 John 90 9
(just the last 4 weeks and N sale professional)
I would like to have a form with
SP Value N-1 N-2 N-3 Units N-1 N-2 N-3
JOHN 100 20 -40 30 8 2 -4 1
And this go on for each SP
View Replies
ADVERTISEMENT
Sep 6, 2007
I'm new to Access, and I've been able to figure out a way around most of the problems I've run up against, but this one has me completely stumped. I would really appreciate any help!
I need to create a query that will produce a report that will follow the sales performance data of new agents for one year. I need to have two pieces of data for each week: number of policies sold and total value of policies.
The format looks like this:
Jan1 Jan2 Jan3 Jan4 Feb1
Number 12 4 6 3 6
Value 1554 320 229 221 1824
I have been pulling weekly sales data from an external database weekly and importing it into individual tables in my Access database. I had no problem for the first 3 months, but now I am getting the error message "Too Many Fields". If I understand it right, you can have up to 255 fields in a query. I don't have anywhere near that many. What could be wrong?
Any suggestions would be greatly appreciated!
View 2 Replies
View Related
Oct 11, 2007
Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.
View 1 Replies
View Related
Jul 22, 2005
I'm sure this must have been the subject of a thread before, but after an hour seaching my head aches.
I'm running (Access 2000 on XP Prof) a sort of planning board. The week commencing dates change automatically, as per previous postings on here.
I have a job run from a command button to then change a field representing the week the job is to be run in ie week 2 jobs to week 1, week 3 to week 2 and so on. So far so good.
I would like this job kicked off automatically once the first time the db in opened in the new week.
Any advice or pointers to appropriate thread welcome.
Ta
View 4 Replies
View Related
Mar 14, 2008
I'm not really sure what to do on this one, I'd like to hear some suggestions on what everyone thinks..
I have a database where users enter information Bi-Weekly into a form. I have 2 date fields set up, one is called the RecordDate, it is the date at which the user has entered the actual data into the database. Ignore this date.
I have another date, called MainDate, which is the date that all departments need to have submitted the reports (Each department fills out these bi-weekly reports [approx 100 departments]), meaning that one complete set of 100 reports is entered every 2 weeks. Even though they are all entered at various dates within those 2 weeks, usually almost at the end of the 2 weeks, all of the reports will contain this MainDate and it will be the same for all departments every 2 weeks.
I'm looking for the easiest method to accomplish letting a user select these bi-weekly dates and making sure that the dates are correct, validation?.
Some thoughts I had
1) For instance, if typing in the bi-weekly dates, I want it so that they can only type in the dates of every 2 weeks, no other dates will be allowed.
** Best option in my opinion 2) Have a small calendar that expands when clicked right next to the MainDate field so that on the calendar you can only click on dates every 2 weeks, or have every 14 days a different color on the small box for the date. [the users seem to like the ease of using calendars, one of the reaons is is a good option in my opinion]
3) Have a dropdown? That shows the current date that is allowed to be selected based on some sort of 14 day interval cycle. (i.e. after 14 days of one cycle are up, the only option in the dropdown is to click the next MainDate, which will be exactly 14 days from that just passed.
Anyway to have this date automatically filled in after 14 since the last MainDate entry? Bi-Weekly reports are due every second Thursday.
Thanks in advance to any input you can offer
Dave
View 1 Replies
View Related
Mar 18, 2006
hi,
I am creating a booking system and I need to create a query for a report that will show how many bookings are made per week and the total of the bookings. I have managed to make a query that will get the number of bookings and the total, but i have no idea on how to do this for each of the weeks in a month. From this query i hope to then make a report showing 4 weeks at a time.
Any help would be great. :)
View 3 Replies
View Related
Aug 15, 2007
On the attached jpg(s) you will see the query I'm working with. It's fairly simple except for a couple of things and those are the items I'm stuck on.
What I'm trying to do is get a representation of the amount of traffic that has come through our salescenters in the last week. Some centers had no traffic and others had multiple prospects. So when I look at the query for week 33 (this week) I would like to see all the centers even if they had zero traffic - the query should show zero.
If you look at Traffic_Query.jpg you'll see how the table is laid out. With the query I'm trying to show all of our centers for the current week even if no traffic was entered.
I may not be explaining it clearly so if you have any questions let me know.
Thanks,
Chester Campbell
Joseph Freed and Associates
View 5 Replies
View Related
Sep 17, 2007
I am just starting to use Access and have set up many simple forms, datasheets, reports, summaries, etc. I have not worked with macros, etc. and need help for this project. I have set up a task table
F1 - DateOfCall - date - now()
F2 - Client - text
F3 - DateCompleted - date
F4 - DueDate - date
F5 - problemDescription - memo
F6 - Resolution - memo
I need to run a query and/or report every week summarizing what has been done the past week. Would also like to view previous weeks. I need help. How do I go about doing this.
View 1 Replies
View Related
Nov 26, 2011
My next challenge/task is to create a way to record weekly attendance. I have looked at several databases available online but not that really reflect what I need. Also I am trying to do it the simpliest way possible (for now) with my limited knowledge of VBA code.
Here is what I am hoping to do. I have a form linked to a query that will select the students assigned to a faculty member in all the courses that faculty teaches. I would then like to be able to use the same form, and a second combo box, to create records in an AttendanceTable that stores the attendance for that student in that course for that week. I have created a key using StudentID-CourseID to identify each student with each of the course they are enrolled in. It is also used to relate all the involved tables.
View 2 Replies
View Related
Nov 18, 2013
We import data to a table on a weekly basis. We delete the table and replace the data. Most of the data is the same. It's date's, group names, ...
Now we would like to save the weekly table and add a saved date. Then we would like a query that puts all the saved tables together and that we could filter.
For example. A team is planned to do a work on 01/01/13. The next week it has changed to 07/01/13. So the data in the table it updated but we can't track the changed date. If we had the different tables we could filter that team and so that the work was originaly planned on date x to be done on the 01/01/13, on date y it was 07/01/13....
Of course if we would save the complete table every time it would mean a lot of useless data that hasn't changed. So a better solution would be that a query could see which data has changed and it only saved those lines on that date.
View 11 Replies
View Related
Jan 16, 2007
Below is the code that I have for a query. Currently the
GrandTotal Column appears to the left of all the Weekly Columns.
Is there a way I can have the GrandTotal column appear to
the right of all the Weekly Columns? The weekly columns
will expand or contract depending on the dates selected.
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
View 1 Replies
View Related
May 16, 2014
I have made a query that was supposed to update my reports every week with new inquiries that we get.
I thought i had solved this but when i opened the report this morning, it's just showing last week's.
I have this as the criteria:Between (Date()-7-Weekday(Date(),2)+1) And (Date()-Weekday(Date(),2)+1)
It's probably wrong.
View 7 Replies
View Related
Mar 13, 2013
I have an excel report which I would like to run through Access to drive trend analysis and compare with other similar reports. The excel report has a cumulative spend figure each week and not the actual weekly spend numbers, the budget figure also can change depending on the actuals.
Excel report:
Week 1
Product ID
Customer
Yearly Budget
Spend
1122
Sam
100
3
1123
John
200
4
[code]...
Will I need to create a new table each week or can I link the file and it updates automatically?Can Access store the weekly data and just update it one week at a time?
View 3 Replies
View Related
Oct 26, 2006
Hello my beautiful worldwide friends :D
I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.
I have attached my report.
Can you help me figure out why my daily total shows but the weekly total is not showing?
View 9 Replies
View Related
Oct 4, 2013
I have a staff rota system that works on a rolling 4 weekly basis. I am using a table to store the shifts of a person dependent on week. I want to be able to tell access that Monday on week 1 corresponds to a certain date and then get access to figure out the rolling system based on that date.
eg Monday 21/7/13 is week 1 (7 days later it knows it is linked to week 2)
This is so if a staff member is off sick I can say they were off sick on the 24th and it will populate their timesheet with the corresponding shift without me having to input it manually. Doable?
View 1 Replies
View Related
Mar 10, 2015
how to create the report monthly,year and weekly using access 2007?
View 3 Replies
View Related
Nov 3, 2014
I need a calendar that can go forever that shows weeks and not months and each record i add (or is added by admin form) will show up. I have a calendar attached here to show what i mean but its monthly not weekly.
Basically it needs 7 spots for employee vacation leave, 2 spots for supervisor leave and 2 spots for other leave. My current calendar will only fit the 7 employees and no1 else thus the reason i want to go weekly and not monthly, so it has more room.
View 1 Replies
View Related
Jun 26, 2014
I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.
I have no clue how to do this or if Access is even made to do something like this.
This is an example of what I have so far
Quote:
P/N QTYWork DateWW (Work Week)
25COMP16/11/201428
25COMP26/20/201425
25COMP27/9/201428
25COMP36/20/201425
....And so on
My code is
Code:
SELECT dbo_JBKLG.JKPRT AS P/N, dbo_JOB.JBQOR AS QTY, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS [Work Date], DatePart("ww",[Work Date]) AS [WW]
FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO
GROUP BY dbo_JBKLG.JKPRT, dbo_JOB.JBQOR, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))
There are more part numbers and the data from the other parts must be in the same format in the same query.
Quote:
P/N QTY WK WW
25COMP 15506/09-06/15WW24
25COMP 22706/16-06/22WW25
25COMP 16006/23-06/29WW26
25COMP 20006/30-07/06WW27
.....And so on
View 14 Replies
View Related
Dec 10, 2013
I have been tasked at my work to create a database for tracking time off from work. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date 2 – Date7
I now have a form. This form has the 7, fields Date1, Date2 – Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,
Date3 = [Date1]+2 and so on until I have all 7 fields showing date from Sunday – Saturday.
These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2….. fields to show their respective Work Week codes by writing very simple “If Then” statements . So here is where I AM STUCK…When I am showing this week view how do I get D1 –D7 to identify what date they are supposed to be in reference to Date1 – Date7 then compare themselves to the table “Time Requests” to see if they have a match and then set the value of D1, D2 …. to the “Time code” in that table. D1 – D7 need to auto populate and do this for about 50 employees.
Here is a screen shot of what I have so far and where I am stuck: [URL] ....
View 2 Replies
View Related
May 21, 2012
I am building an attendance database. The attendance data will be inputted at a daily level. That is working fine and the hours are calculating correctly. The next thing I need to do is create a Table (maybe) that will show the total hours worked in a week. The purpose of this, and the reason I don't want a report, is that the employee will accumulate vacation based upon the numbers of hours worked per week. I will want to be able to show a running total on the vacation, and will eventually create something to allow them to deduct from those vacation hours when they are used.
View 2 Replies
View Related
Apr 13, 2006
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
View 3 Replies
View Related
Mar 4, 2015
I have a table with staff in.
I have a table with the start and end of their shifts.
We have four possible locations and four weeks.
So I have four tables for each location.
I have a module that can work out hours and deductions based on time in and out. I built that into a seperate databse working on a one time in and one time out setup.
I want to incorporate the two so I can get the hours worked over four weeks at all locations and divide that by 4 to get the average weekly hours for a staff member.
View 8 Replies
View Related
Jun 11, 2013
how to build a weekly report to count the number of computers that have entered a defined process. I have four processes and I need the report to show counts for each process even if it's zero. The report is supposed to show the history of each process and not just the current week. Is this possible to do in a single query? Or do I have to make multiple queries?
I have a table that holds the history of each workstation including the workstationName, date of the record, the phase of the workstation, and the status of the phase. Another table holds the phase codes and phase statuses for lookup purposes.
View 2 Replies
View Related
May 13, 2014
I have a user that uploads a spreadsheet weekly. I want to assign a unique id to each record that is the current date plus the start and end date for the data they are loading which would be the week prior plus a counter.
For example: Data from the week of 5/4-5/10 is loaded on 5/12.
Record one would be 050414-051014-1
Record two would be 050414-051014-2
Record three would be 050414-051014-3
and so on. How do I accomplish this in my table design? They will be deleting the prior week data and pasting the new data so the table design will not change.
View 2 Replies
View Related
Oct 24, 2013
I am trying to write code for a form. When the user enters the "Number of Payments" and the "1st payment date" it will fill the amount of the payment into a specific month.
For example: 1st payment date would be 10/24 the payments would be bi-weekly and the amount would be $50.00. The number of payments would be 4. I would need it to put $50.00 into a field called "October" $100.00 into a field called "November" and $50.00 into a field called "December". In excel I would do this simply by using a (date)+14 formula. I am sure that I need to do this with loops but where to even begin.
View 1 Replies
View Related
Jan 15, 2015
I have created a table for a Gym that shows which classes / activities are on which days. You will notice in the image below that i have assigned Boolean to associate a class with a day of the week.
I now am trying to create a Calendar STyle report that GROUPS all activities by the day they are on. So, for example, see below:
As you can see, all of Monday's activities will appear first (in a group) followed by Tuesdays.
One way I think I could achieve this is by doing a QueryMonday, QueryTuesday...etc so I have dataset or resultsset for each day. Then I could bring into a report, each query as a sub-report. I believe this is overly complex for what i'm trying to achieve. Also, using 5 grouping levels in one report doesn't achieve it either.
View 2 Replies
View Related
