I am new to Access and would be very grateful for some adivce on setting up a query.
The data in the table below comes from a photon counter. Both fields are numbers although TIME actually represents seconds.
I need to be able to group the DATA field by arbitrary TIME periods. e.g. to divide the table up into bins of, say, 8.7 seconds length, showing the total accumulated in the DATA field for each successive period of 8.7 seconds.
The tables are about 300,000 records long.
Any help would be much appreciated.
I'm having a hard time trying to figure out how I should go about designing the table structure for the hours tracking for the database I'm working on. Basically, I have a table with projects (tblProj) that I need to track the number of labor hours worked per month. The problem lies in the fact that each project could have varying time periods, anywhere from less than a year to more than a year and can start and end at any time of the year.
I was thinking of creating a second table (tblProjHours) to store the hours for the project but I'm not sure what the most efficient way of doing this would be. My idea was to create a table as follows:
I would create a large number of fields/columns, something like Month1 to Month36 just to make sure I have enough months to enter in the hours. This is of course inefficient since some projects would be way shorter than the maximum allowed months set by the table structure and there is always the possibility of surpassing the maximum allowed months based on the table structure. I was wondering if someone had any better ideas on how I should pursue the design. Thanks in advance!
I have a MS Access database that tracks when packages are received, returned, etc from different agencies. I need to create a query or report that shows how many packages were received, returned, etc PER MONTH, PER AGENCY. I was able to create a query that listed all the "Date Pkg Rec'd" dates for October (Between #10/1/2004# and #10/31/2004#), but I don't want a listing of all of the dates, rather a total of the records within those timeframes PER AGENCY. For example, APHIS 10, FAS 3, etc.
The other problem I have is I need this done across many columns without excluding those records without an October date (for example) in them. For example, I could have a "Date Pkg Rec'd" date in October, but not an October date listed in the "Date Pkg Returned" column.
Basically, I need the simpliest way to generate monthly "record totals" for several fields.
Suppose that you want to make a formula which will calculate time periods based on a given tariff. To be more precise.
Suppose you can "get in" somewhere, entrance is $2. You are free to stay there, without additional charge, for 5 days. Then, for the next 5 days, the charge is $1 per day The next 7 days, the charge is $1.5 per day Thereafter, $3 per day.
In this scenario, we can make a query, we use datediff function to see the number of days between "gate in" and "gate out".
The problem is that the tariff is not very simple and the query gets to big and difficult to control with lot's of "iif" and may be I need to make more than 4 queries and union select and so on.
I tried to find in the docmd if there is any suitable function that I could use.
I'm trying to calculate some running sum queries in preparation for some charts on a report.
I have: tbl: Spencerdaily fld1:datein fld2:milesdriven fld3:vehiclenumberdaily
I am trying to calculate the running sum of milesdriven for each year per vehicle. If I use the following I get the same set of sums repeated for each vehicle:
SELECT spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year]) AS Mileage FROM spencerdaily GROUP BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]) HAVING (((DatePart("yyyy",[datein]))>2001)) ORDER BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
I tried this but it didn't work:
SELECT spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily AS vehiclealias, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year] & " AND [vehiclenumberdaily]<=" & [vehiclealias] & " ") AS Mileage FROM spencerdaily GROUP BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]) HAVING (((DatePart("yyyy",[datein]))>2001)) ORDER BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
I am attempting to set up a query which will perform aggregate functions on records from two fields (Speed, Volume) and group them by a time field (SensorTime). This should essentially result in several months of data being combined into a succint 24-hr time period, divided into 288 five-minute intervals.
The problem is that Access will group some of the time values together, but not others. After some detective work I discovered that many of the times that APPEAR to be equal are actually different values when carried out to the 15th decimal point. I learned that this is a result of rounding error within Excel, from whence I originally imported the data.
My question is: can I apply a ROUND function to times in the original table, thereby solving the problem in all the resulting queries (and if so, how)? Or will I need to round the time values in each individual query (this would take some time)? Or is there a better way to equalize these time values that I'm not aware of? I know Excel has a "Precision as displayed" option but I couldn't find anything similar in Access.
I have a table called tblTransactions with columns called (among others) [Date Time], Ticker, Quantity, Price and Commission. Column [Date Time] contains both a date and a time. I want to determine the commission paid per day. If I use the following code I still get all single trades on a day as the times at which the trades were exucuted are all different. Is it possible to group by full days only without having to change my database in order to determine the daily commission paid?
SELECT T.[Date Time], sum(Commission) AS TotalCommission tblTransactions AS T WHERE T.[Date Time]<=#04/17/07# GROUP BY T.[Date Time];
I am collecting data every hour on the hour and I would like to group this data according to Shift (A, B and C) by Date. The only problem I am having is- how can I keep the overnight shift together as one date? 10pm - 6am
I was able to do a simple query where I was able to assign an A, B, or C depending on the field with the Short Time format using nested IIF statements. From there I was able to group by shift (A, B, or C) per date. However the C Shift is split up.
Example. Can Sunday October 5th 10pm to Monday October 6th 6am be grouped as one date.
I 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?
I have a question that I just cant seem to get my head around....so I thought I'd pick your brains!
I have a table that has DriverID, Date, Work, Sleep, Rest. As you can imagine, this records their hours of work, sleep & rest.
Now with new fatigue management laws in Oz, we need to find out if they're working for more than 168 hours in a 14 day period.....
My thoughts are, create a query that runs through every date of the year, then counts forward 2 weeks, sums the WORK hours and only displays anything over 168 hours??? Is this correct? Would that not simply die ? Considering we have about 30 drivers to run through?
I have read more post than i care to think about but I can't stil get my head round the following problem. I have created a database (with this forums help) but I am stuck on the following query/form/vba etc....
Our staff holidays run from 01/05/05 until 30/04/06 and i have a query that calculates what holiday entitlement staff are due from 01/05/05 on a daily basis up to 23 days worth of hols ending on 30/04/06, but I can't work out how to set next years holiday period 01/05/06 - 30/04/07 without changing the form manually, is there a way for it to auto update??
We have a db of salmon survey data where catch data is recorded by date, site, etc. Historically, we have used ‘sampling periods’ to group our results into blocks of time that can be compared from year to year, and within year.
Now, having frequented this forum I’ve read that we shouldn’t store information in tables that can be calculated from fields and I want to try to follow that advice in this instance but am having trouble working this out.
Here’s how we define a sampling period.
1.The start date of the first sampling period in a sampling year is the last Monday in the most recent November. 2.Each sampling period is 14 days long.
What I want to do is have a query use the date of a set to calculate what sampling period the set belongs to. I have an idea that I could use a datediff function to calculate this if I can figure out the ‘last Monday in the previous November’ part. I’ve searched this forum and googled but am having trouble finding anything that I can adapt to this as a query expression.
Anyone care to help a fish bio out? Much obliged :)
If my fiscal year ends 12/31 but the pay period doesn't end until 1/10, how do I get a query to tell me there are 25.3 pay periods remaining (counting from the last pay period 1/11)?
I'm having trouble finding an entry anywhere about this, although I'm sure I'm not the first person to come across it...
When I am typing my entry into a combobox it begins to autosearch, highlighting the part of the entry that it finds that matches the beginning that I typed in (as a normal combobox does in access).
However, when the entry it finds has multiple periods in it (i.e.: S.T.A.R) it stops searching and does not highlight the remainder of the entry it moved to. I'm not sure that searching is the correct term, which may be why I can't find any information on this, but is there a way to correct this problem?
these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)...I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values
Hi, in the qry attached I would like to group the records by yyyy mm int the field "Year month" I need then to count the number of records in HOTAC which have value "yes" the sum of "Total Flight Time" and finally the count of records that contain the letter F or P in the "Duty" field. Example:
Year Month Hotac Flight Time Duty 2007 05 0 2.18 2 2007 06 8 XX 2
I developed a finance tracker database for tracking project revenues and costs (forecast and actual amounts). I have a form where we enter a new Work Order in the DB. This Work Order form has a continous subform where we create all the billing periods needed for the life of the Work Order. Once the billing periods are created (opened) we can then add our revenue and cost forecasts for each period. We have a Billing Period lookup table that has our billing periods with their respective start and end dates (which usually begin around the last week of a month and end about 3 weeks into the next month).
For the purpose of this question, lets say we only enter the required WO_Number and WBS_Code (Composite key) and the Work Order Start_Date and End_Date. I want to click a button to runs some code to automatically create all the billing periods for which their start and end dates fall into the Work Order start and end dates.
For example, say a Work Order starts on 5/1/2013 and ends on 7/31/2013
The billing period dates in the Billing Periods lookup table are as follows: May-13 ---> 4/22/2013 - 5/19/2013 Jun-13 ---> 5/20/2013 - 6/23/2013 Jul-13 ---> 6/24/2013 - 7/21/2013 Aug-13 ---> 7/22/2013 - 8/18/2013
Then we would need the following billing periods created in the Work Order subform: May-13 Jun-13 Jul-13 Aug-13
I don't even know where to start on this. Is it possible to automate this process with the setup I have? If so, how would I structure the VBA code/logic to use the billing period lookup table and create entries in the subform for all the billing periods that fall into the duration of the Work Order.
The attached DB is a stripped down version with only the tables and forms needed for this problem.
I have 1 table with multi Text columns (location, coaches,ssn etc), and multiple Num columns (pts and product counts)
I need to sum all the unit counts & points for each person, but only bring back the location for each person that had the 'max' sales (pts).
ex. ssn xxxxxx has 3 sales codes : code 1&2 had less sales aquired then sales code 3. Sales code 1 was location Vt, slsCd 2 was location CA and slscd 3 was location FL. I need to bring back the sum of all three codes but only the location FL as it had the most sales pointing to it. I have deadline of Monday to get this done.PLEASE help
I’m creating a report on which I want to group data base on a field that has two different data one is KEY and OTHER. Now I need to be able to show on the detail section all records base on KEY, and group the rest of the records by OTHER.
I need to group data (cases) in range like this (0-5, 6-10, 11-15 etc).
I can do this with the iif statement but this is a bit tedious.
I have two simple table:
1. Salestbl with two fields: a. customerid, b. Cases 2. Rangetbl with two fields: a. Range b. Category
The rangetbl is where i would define my range and it look like this the range field have records 0,6,11 with corresponding category which look like this '0-5', '6-10', '11-15'
How can I use this to group my data. that is customer with x cases have group '0-5' or '6-10' etc....
