I have a pre-established database with a form that calculates total sales and tax for a time period i specify. I want to add a box that will display the number of sale records. Ex. if i had sales of 50,000.00, and that came from 200 sales i want the box to show the number 200.
View RepliesI am trying to get a count from a table, but only for a certain time period. Here is a little bit of my table:
STRMIDtrDateDT trDT
1617 3/25/2015 7:30:00 PM0
1530 1/8/2015 4:40:00 AM10
1532 1/12/2015 9:20:00 AM60
1533 1/14/2015 4:50:00 AM1
What I am currently doing is querying a table for the Count(*) for the day before. This works all well and good, but now I am wanting to break it down by a time range from the trDateDT field.
We sell products out of vending machines and once in every few days (time span between readings may be different every time) read data from the machines using flash drive. Each reading from all machines produces an Excel file:
Reading_Date | Machine_ID | Product_ID | Amount_sold
2008-04-08 | 1 | 1 | 26
The only date that we have is the day on which the data has been read and this tells how many sausages were sold from the date of last reading till the date of current reading (after each reading counters are set to zero).
The problem is to get average amount of product X sold from machine Y on every calendar day. For a total rookie like me it seems like creating another column that would divide the Amount_sold by the number of days between two consecutive readings and put this average amount for every calendar day between these two readings. May it be done this way? How? Another way? How? I would be very grateful if anyone could help me and explain the solution in an accessible way.
I'm in the process of creating a database in Access 2010 that has two front ends, basically. What I want to be able to do is lock out one of those front ends for a specific period of time every day.
View 2 Replies View RelatedI have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday
EXIT FUNCTION
ELSE
IF [PunchOut] ISNOT Between Midnight and 0559 hours
EXIT FUNCTION
ELSE
DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I have a report that i can order through buttons on report or openargs. Is it possible to get a field that would count the number of time an item appears i.e., if I sort it by employee, it would count how many times each employee is on the report, if i sort it by TypeOfMalfunction, it would count how many times each appears.
View 4 Replies View RelatedI want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.
I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].
Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?
Hello all,
I need your help please, i have a Form that also has a Subform in it. I need to create a Text box on the form that will display a number which will reflect the total enties made against a particular ID within a rolling six month period using the system date as a perameter. The number shown will only reflect previous entries made that fall within the six months from the current system date, and will need to update once new entry is made. Incase i have confussed the hell out of you, let me give you an example:
Example One
Entry made today = 24/02/08
Previous entry Date = 20/10/07
Textbox on Form = 2
Example Two
Entry made today = 24/02/08
Previous entry Date = 20/08/07
Textbox on Form = 1
I have a table that contains a person ID, a start date for that person, and a GroupType. I want a way to count the number of people who started within ANY 40 day period. Then depending on that number , assign a value to the GroupType field (ie. if the no. of people is 1-15 then groupType = groupA, if its 16-30 then groupType = groupB, if its over 31+ then grouptype = GroupC. This needs to be re-counted every time a new record is added.
As well as having the groupType visible on the form for that person, I also want to be able to run a report showing all people, their start date and group type. Note that when start dates are entered these may be either future dates as well as dates in the past.
If possible, I would also like to track /store when a person's group type changed in another or the same table perhaps.
How do I enter and store a period of time that may be of any length: years (.. unlikely but ...), days, hours, minutes, seconds (or even micro seconds) that I can later add to other dates or times stored in my database? How do I create a sensible data entry mask for yyyy/ddd/hh:nn:ss.999 and then convert this to a field compatible with date/time functions? And what format should this data take (be converted to ..?) and be stored in?
View 2 Replies View RelatedCan you help we have a data with a list of jobs that scores points, I need to build a query or scirpt that will add 5 points every 30 day fro the date the job was submitted this will help old jobs come the top of the list.
View 1 Replies View RelatedHi,
Over the past three months I have been trying to develop an application which can repeat appointment entries.
I would like to have the option to repeat an appointment entry for a given number of weeks.
For example if an appointment entry was placed for today (Wednesday), there would be a command to create additional duplicate enties for the same day of the week for a given period of weeks ie say every Wednesday for the next 52 weeks.
Any help on how I could approach this would be very much appreciated.
Thanks
Hi there
I have created a database in Access XP (2002). In a nutshell, the database records numbers of people attending a seminar; which can take place any number of times per week, and so hence can take place any number of times per month.
I have set up the query so that it can run immediately after a seminar to show the attendants who attended the seminar on that current date "Date()" in the criteria box. However, how would I go about setting it up so that it shows who attended every seminar in the current week or month?
I have designed an access application that logs emails that come into a department. However, the team leader has asked me to create a report to work out if emails were answered within a 24 hour period.
Is there a method to do this. It is not something I have attempted to do within access before.
I am using access 2013 and have an issue creating a query with employee hours for a two week period.
For starters I have a table with the following:
Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)
My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.
At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.
In my query (to Start) I need to have the following with it set up per pay period.
Employee Number Rate Code Regular Hours Overtime Hours
How to set it up so that it calculates the hours based per rate code with the given pay period.
I've been struggling with getting a report to simply count records (I don't need any detail other than the counts). Here is what I have:
Table CompressorRoundsT contains these fields (many more actually but these are the ones I care about for this purpose):
Date, Shift, Round
Possible records in the shift field are 1st, 2nd, 3rd and the round field has Rnd 1 and Rnd 2.
For each date, I need to count the number of records per shift and per round. For example, the report should show that on June 7th, 1st shift has 14 records for round 1 and 14 records for round 2. Same for 2nd and 3rd shift. I don't need any other details of what the records contain, just the counts.
I've had some success but the layout ends up too spread out because it is including the individual records instead of just showing the counts and it isn't grouping the way I want.
It's a report for management to see how many readings (rounds) were missed in a period of time.
Hi,
I've got a combo box that uses
DatePart("ww",[Date],2) AS WkNum
to obtain the week number.
How would I obtain the start date and the end date from just the week number?
Is it also possible to change WkNum to the week number of the selected month?
E.g. if December is selected, instead of week 53, show week 5.
Any help would be much appreciated.
HI all,
I am still new in Access databases :o
I have a table with 'General Date' column. So I have to create query wich extracts records between Last record (via Date field) and 'for example' 10 days before. But Last record in Date column may differ from Now().
When this is done I have create calculations with extracted records using agregate functions.
I'm trying to use Last function for Criteria putting it into Date field and substracting with -10 (for 10 days) but it doesn't work.
Please help! :confused:
Could I change my unbound textbox (txtperiod) on the Report to display the time period.i.e.
Weekly, Monthly, Quarterly
Criteria is BeginDate and EndDate on main form.
Calculating the rates over a period of time. I think the attachment will give you a clear picture of what I am looking for.
To get the cost for a particular period I want the rates to be updated as per the given period. How I could get the calculation in the mdb format...
stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.
Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]
[URL]
As you can see, the PRODUCT_ID column is not combining together according to their IDs.
[URL]
This how my Design View looks.
The SQL for my current query is:
SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));
I have a login form that closes automatically after a specified time if you do not login with user name and password. It also keeps track of how many times you login and whatever value you set for a password reset it forces you to change when that value is met.
Problem: If you do not login within the allotted time even though a password change has been scheduled, my code resets the login count back to 0.
This is what i want to get around if I can. Either I have to pause the timer or stop the reset.
After you enter the password is when the login count is fired.
And also when it checks if a new password is required.
I have a main form [frmZone] and a sub form (single form) [fromZoneSub] linked master/child by [ZoneID]
If there are, say, 5 related sub form records I'm trying to get a label [LabelCount] on the sub form to say "1 of 5" and as you click through the sub form for the label to change "2 of 5", then "3 of 5" etc....basically letting the user know how many records there are and as they click to the next record know which record they're on.
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 RelatedI'm creating an employee audit database, and, in the audit form, the user (ie. supervisor) can select a number of items from a listbox. Each item selected corresponds to an error that the employee has made, and, as such, the employee's Audit Score has two points deducted for each item that is selected.
Incidentally, there are other, solitary elements to the form, but this particular listbox houses a collection of items that are related under a single category.
The score is displayed at the bottom of the form, and it needs to update in real-time.
The problems that I am encountering are that I am unable to count the number of items selected and then I am unable to multiply that count by 2 (the point-value of each item on the list.)
Table name :Imports
Field1 : ContainerNumber
Field2 : Bkg_number
Field3 : Size
Field4 : Weight
I have created a form which is having the above table as "control source". When data entering, if select a booking number from a Combo box, should checking how many records already having with same booking number and reflect the number of existing records in the form. If null records, it should show as "ZERO" records.