Queries :: Calculating Daily Change In Access Query

Jan 21, 2015

I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.

A. Date B. Price C.Ydayprice
01/01/2015 101.45
02/01/2015 104.70
03/01/2015 103.00

Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.

View Replies


ADVERTISEMENT

Queries :: Time Sheet / Payroll Database - Calculating Total Daily Hours

Feb 18, 2014

Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.

For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.

The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!

At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).

View 3 Replies View Related

Queries :: Calculating Change In A Set Of KPI

Aug 6, 2013

I have the following query, which gathers a set of kpi for an employee across two reporting periods.

Code:
PARAMETERS [Enter_ID] Long, [first_period] Long, [second_period] Long;
SELECT *
FROM kpi
WHERE PERIOD_ID in ([first_period], [second_period])
AND Manger_ID = [Enter_ID];

What I hope to accomplish is to calculate the change from one period to the next. So, say we had Jun 2013 and July 2013 as reporting periods I would want to subtract each KPI in June from the same KPI in July and report on change in that value, for each employee ID. Is this something that can be accomplished with SQL or would I have to use VBA to do this?

For simplicity we will say the only fields in the kpi table are EMP_ID, PERIOD_ID, Manager_ID and the 8 KPI: X1, X2...X8

View 7 Replies View Related

Calculating Daily Values For A Month

Jul 7, 2006

Hello all. I hope someone can lend a helping hand here. I am trying to create a query that will calculate a 24 hour value for every day of a month. For a month a go out a read meters. I need to get the difference of two readings by subtracting the first day from the second day and so on until I get to the end of the month. So, Day2-Day1=24 hour value, Day3-Day2, Day4- Day3... I have this in a spreadsheet but can't seem to grasp it for a query in a database. I would appreciate any help. Thanks.:confused: :D

View 1 Replies View Related

Queries :: Calculating Percentage Change From Previous Records

Oct 1, 2014

I have a table with the following fields; Row; AnimalID; Weight: Date_Weighed (see attached screenshot example).

I would like to make a query that gives me the percentage weight loss/gain since the last time a particular animal was weighed. For example, animal AAA gained 100% between 2014/01/01 and 2014/02/01. Animal CCC gained 0% between 2014/02/01 and 2014/03/01.

I plan to use this to create a pop-up that informs me if an animal had lost over 15% of its bodyweight since the last time it was weighed, indicating either a loss of body condition, or that a female has given birth.

View 8 Replies View Related

Queries :: Query With Daily Fields Based On Parameters?

Feb 11, 2014

I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:

Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.

This is not currently working.

View 8 Replies View Related

Queries :: Calculating Three Different Rolling Averages In One Query?

Jun 26, 2013

Is there a way to calculate three different rolling averages in one query?

I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.

Code:
SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num]
FROM LIST_WITH_TNC;

Is there a way to make this work?

View 2 Replies View Related

Queries :: Stock Database - Calculating Sum In Query?

Apr 17, 2015

I have been working on a stock database for my company. It needs to hold stock data, Any adjustments in the storesperson may do (QtyIn or Out). And allow Customer PO's to be raised (just to show prices and quantites sold to customers)

I have 4 Tables:

tblStockList
PartNumber (Primary Key)
Description
Location
StockCheck
StockListUnitPrice

[code]....

I then want to create a query to work out the total stock.

At the moment i have two queries, one to Sum the adjustments and one to sum the PO's. (The stock is not removed from the Customer PO until invidual line is shipped)

I then need to combine both these queries to work out the total stock value.

There will always be a value in the Sum of Adjustments but not in the Customer PO. So when i run the query to =SumofAdjustments - SumofCustomerPO's, if there is no record in the SumOfCustomerPo's, no value will appear.

View 7 Replies View Related

Queries :: Calculating Difference Between A Table And Query?

May 2, 2013

I'm trying to make a query where it can calculate the difference between a table field value and a query field value.

Ex. Table INVOICE - fields
invoice no, invoice value,
Query TOTAL - fields
invoice no total invoice value

I need to get the difference between invoice value and total invoice value.

How can I put this into a query (invoice value - total invoice value)?

View 4 Replies View Related

Queries :: Calculating With Null Fields In A Query

Oct 2, 2014

My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:

RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")

would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).

View 5 Replies View Related

Queries :: Calculating Fiscal Year Sales In A Query

Feb 18, 2015

I'm making a query that has the following Fields

Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)

Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:

Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013

how to link my criteria back to the "Date" field, or if I'm even going down the write path.

View 14 Replies View Related

Queries :: Calculating Bonus - Expression Using Sum Of Another Calculated Field In Query

Mar 28, 2015

I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:

Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)

100,000 1,000 Sum of total bonuses/salary*4%
90,000 900

How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?

View 13 Replies View Related

Queries :: Calculating Total Number Of Records From Union Query

Jun 16, 2014

I am using the following UNION QUERY to total up equipment tested for a report.

SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]

[code]...

I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?

View 2 Replies View Related

Queries :: Running Total Query Not Calculating First Fiscal Year

Aug 5, 2014

I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:

FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506

As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:

Code:
SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));

and the Aggregate query:

Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])

[code]....

I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.

View 8 Replies View Related

MS Access Query Help. Expression For Calculating Multiple Expiration Dates.

Feb 28, 2008

Hi everyone
I'm a very very new access user so many apologies in advance for when I have no idea what I"m talking about.

I'm working on creating a report that will display multiple expiration dates. Currently I have an employee database, not created by me, that has all of our employees professional licenses listed.

Prof license, auto license, liability, etc.

I want to create a report that will tell me what has already expired or will expire in the next 30 days.

I did use this
Between DateAdd("d",-30,Date()) and Date() and it is bringing back info up to 2009. which isn't what I need. I'm sure I'm doing something wrong here.

I want anything that has expired regardless of the date from today, before today, and 30 days from today but I don't care about anything more than 30 days from today's date.

I noticed that when I looked at the existing database that the fields are set as text fields. I tried to change them to dates and it gave me a "deleting 106" records error message. Yikes!

Any help would be appreciated. I don't really understand expressions and I don't understand if I'm supposed to put the actual date in parentheses or what. Please pardon my ignorance.

Many many thanks.

View 14 Replies View Related

Queries :: Printing Daily Report For Existing Members

Aug 11, 2015

I have a kids club which do lots of functions for kids, to monitor the members and who is in and out I did a small access data base with details of Start date of the function and end date for each member.

I want to make a daily report showing only the active members in today date only for example:

Jack have swimming every Saturday, Monday and Wednesday Between 14 July 2015 till 14 August 2015
Jill have swimming every Sunday, Tuesday and Thursday Between 14 July 2015 till 14 August 2015

today is 11 August 2015 and I want to report to mention just who is active today only.

View 8 Replies View Related

Queries :: Make Only One Change In Mdb Access Database With Lot Of Rows

Nov 7, 2013

I need to make only one change in my mdb access database with lot of rows.For example, I have two columns: "num1" and "num2" with numbers and need to create new column (as a text) according the rule: num1 # num2....That means if the numbers in num1= 12 and num2= 1,3312, thenthe text in new column should be: 12 # 1,3312.

View 5 Replies View Related

Queries :: Daily Activities - Group As Weekly Total And Transpose For Reporting

Jun 5, 2013

Query of daily activities spent hours

1) to be group as weekly total
2) then need to transpose it for reporting.

My table fields are Date, SpentHrs and Code (activity code description).

View 11 Replies View Related

Importing Excel Spreadsheet To Access Database Daily

Jun 1, 2006

I need some help on this one. :confused: I have two Crystal Report generated Excel spreadsheets that are auto-updated on a daily basis. One spreadsheet contains computer assets, type, model, locations, etc. The second spreadsheet has users assigned to them, phone numbers, etc. I need to import these in to existing tables with numerous filters into my Access database weekly. They don’t change a whole lot but I need the changes to be reflected in my database.

The way I have it setup now is through a linked table, then I use a “Make table query” to filter the data.

My problem is the filtered table has relationships set up that I have to delete then recreate every time I need to run the “Make table query” because it has to delete the old table first.

Is there a better way to make this happen?

View 2 Replies View Related

Auto Email PDF Report Daily From Access 2007

Oct 17, 2011

I am working on Access2007. I designed a report that I need to email every night automatically to my client. The report resides in the Access database. I currently use a macro to auto email this report for me but it still requires me to double click the macro to make it run. I actually want the report to be email at night without having me running the macro manually. Is there any way to make this happen?

View 7 Replies View Related

Update Access Table With Daily Excel Spreadsheet

Apr 28, 2014

what I have in Access is a table which lists jobs via their HB Number (as well as a ID number access gives them). For each job there is a bunch of details and some Yes/No fields.Each day I will get a dump from another system that will list jobs like this, I then need to "Update" the Access table with any new information from the excel dump. The update would need to:

1. Insert new records (jobs) from Excel dump into Access Table
2. Update any of the records fields (except HB of course as its unique) in the access table from the Excel dump (the Excel dump obviously wont have access ID numbers, but will have the HB)For a real basic example:

Access Table Like So:

ID
HB
Finished

1
5A
Yes

2
5B
No

[code]...

the actually data has lots of different fields and many many many more records. But yeah basically need to update the table from an excel dump.I thought it was as simple as doing a Excel Import > Append Table thing in Access, but that just seems to add the new records and ignore the updated fields?

View 14 Replies View Related

Queries :: Importing Spreadsheet On Daily Basis - Detect / Remove Blank Columns

Sep 18, 2013

I am importing a spreadsheet on a daily basis that has the same columns in every time but depending on the company I am importing it for different columns will be blank and not needed each time.

What is the best way of detecting and removing blank columns.

View 3 Replies View Related

Linking Excel To Access Automatically - Publishing Daily Report?

Dec 6, 2012

Having experimented with Access for a few months now; I know you can link an Access Database to SharePoint, and you can export Excel files into Access for inclusion in tables.

My question is; If I have a daily report being produced, through script, with an excel output. Is there anyway to automatically publish this in Access, negating the need to manually go into Access to import?

View 5 Replies View Related

Daily Total Query

Sep 7, 2006

Hi,

I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.

Any help would be appreciated,

Ben

View 12 Replies View Related

Importing Daily Excel Spreadsheets Into Item Inventory Access Database

Feb 1, 2005

Hello,

I am not sure how complicated of a job this is, but we want to be able to import our daily recievals of inventory into our existing inventory database instead of entering it by hand. I try using the wizard, however when I get to "finish" it says there was an error and it was not imported.

I don't know much about access but I was hoping one of you could point me in the right direction.

View 3 Replies View Related

Query Date For A Daily Report

Sep 24, 2004

I need to generate a report with records generated on a daily basis and I am looking how to write the query expression. For example, my Access database generates record on a daily basis and I an using a query to generate the report. I only want to report the records created on that day, and then each subsequent day. So, in the query, I am looking for the correct expression to grab only those records generated that day. (Hope its not confusing, I'm new at this)

Thanks for any help in advance!

Keith

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved