New to MS-Access and I'm trying to help our HR with creating a production tracking table.
This is just a small simple table with all 12 months of the year and a total production column for summing up everything that has been produced for the year. But being "very" new to access I have zero clue on how to do this. I would appreciate any Access for this dummy help I can get . Thanks in advance.
Running Access 2010. I have a large data set here is sample:
Type Capture Date Volume
1 1/5/2014 7
1 1/15/2014 5
1 1/8/2014 3
1 1/10/2014 4
Is there any way to sum the total values for each change in date while keeping chronological order? To get access to produce something like this:
Type Capture Date Volume
1 1/5/2014 7
1 1/8/2014 10
1 1/10/2014 14
1 1/15/2014 19
Hello! I'm new here, and I'm back into Access after a few years of not using it.
What I'm trying to do seems simple, but I can't seem to get the sytax down.
I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?
Thanks!! :D
I'm adding to a DB which has been working well for a while.
I have a continuous form which has a query behind it. The query takes two values: (For example)
[LineCost] and [LineQTY]
Inside the query, we use the expression TLC: [LineCost] * [LineQTY]
I need to sum this so a Sale with several lines gives me the total sell and cost value.
If you look at the sfrmEditQuote - you'll see the exact same method - working. sfrmEditSale is direct copy of this form, with some changes here and there. But can I get past the dreaded #error? Nope.
I've tried renaming the fields and text boxes, checking the query, but I keep getting #error on the sfrmEditSale form footer.
I have attached the DBs in old and new format.
I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:
=IIf([FTE_Percentage]="76-100%",1,IIf([FTE_Percentage]="51-75%",0.75,IIf([FTE_Percentage]
="26-50%",0.5,IIf([FTE_Percentage]="11-25%",0.25,IIf([FTE_Percentage]="1-10%",0.1,0))))).
This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.
I have a linked table. The table looks like:
Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3
I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:
Course name | Course Grade
Student 1
Student 1
Student 2
Student 2
What is the best way to do this?
I have 4 revised ship date columns, an est ship date, and an actual ship date. I have a column summing days late for est ship date and one summing revised days late. The expression I am having problems with is to calculate the total days late for the revised. I want it to check the 4th revised and if it has a date calculate from that date. If not I want it to go to the 3rd and calculate from that, and so on. I have pasted the code I was trying to use. It just goes all the way to the bottom of the code and calulates from the 1st revised ship date. I have also attached a jpg of the layout of the columns. I also want to do this in the query using expression builder.
Days Late Revised: IIf( [4th Revised Ship Date] <>null,(DateDiff("d", [4th Revised Ship Date] ,
[Actual Ship Date] )-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,1)-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,7)),IIf( [3rd Revised Ship Date]<>null ,(DateDiff("d", [3rd Revised Ship Date] ,
[Code] ......
Im working in Access 2007.
So i have query based on 1 table that populates a Form. The primary key for that table is Entity ID. Therefore once the query has been run I have multiple records that i can scroll through in my form distiguished by their Entity ID.
I have a second table that has a Entity ID column, AFE Available column, and many others. The primary key for this table is called Match ID. This table contains records that have the same Entity ID.
My goal is to display on the form the Sum of the "AFE Availible" for each Entity ID. so as you scroll through the records the Entity ID is changing and you are able to see a the Specific "AFE Availible" Sum related to the current Record showing on the form.
I couldn't figure out a way to have a query based off both tables where the records are only uniquely defined by the Entity ID in Budget Info. What was happening is the query was displaying all the records that had the same Entity ID because of the AFE Spent table. That way when you scroll through the records the form shoes records with the same entity id.
Maybe im doing it all wrong and you dont need the tables attached to the same query. That would make it easier i think. So you would have two queries populating different text boxes on a form. Is that even possible?
Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.
Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add
Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By
Field: Pending
Table: qryStatusRptA
Total: Sum
Field: Overdue
Table: queryStatusRptA
Total: Sum
Field: TotalRecords: Count(*)
Table:
Total: Expression
Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)
Many thanks,
Christine
I have two columns in my report that at the bottom it gives me the following total, as I put below. I am trying to take the two totals that is gives and get the percentage. 1058/3024 = 35%.
=Sum([TotScrwBlank]) =Sum([TotalAmount]) .349 or 35%
1058 3024
I had did the equation as =Sum([TotScrwBlank])/([TotalAMount]), but this is giving me the answer of 46% which is not correct.
I have a personal expenditure data base indicating daily expenditure on various heads ( File Attached). I want table to reflect expenditure on various heads on daily basis in COLTOT column. Any subsequent change in any of the field to be dynamically updated.Presently I am exporting file to excel and calculating the total there and then manually updating the COLTOT field. Other alternative I tried was to create a form and make a text field and sum the fields using formula like =nz([milk])+nz([vegetables]). I feel it may be unwieldy for such large number of expenditure heads.
View 14 Replies View RelatedI am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.
I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this
Place 1 Place 2 Place 3 Place 4
13............36.........3........77
So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?
I am using Access 2003 and trying to create a report that has a running balance of payments into an account. There are also payments out of the account which reduce the balance.
When I add the other columns into the expression for the running total the result shows in the report as "0".
The expression I am using is: =([Escrow Pmt Amount]-[Ins Pmt Amount])
I have this expression in the "Detail" section of the report with the "Running Total" toggled to "Accross Group".
How can I subtract the values in the other columns (Outgoing Payments) from the balance and continue the running total.
The list of payments within the report are date driven and grouped by an account number.
I currently have a query that pulls selected data from a table. There can be multiple rows of data, and two columns include dollar amounts and quantities. I have a total line going at the bottom so I can see the Grant Total of all the rows (for dollar amounts and quantity).
Is it possible to add a column to this query that will calculate the expression:..?
=Grand Total of Dollar Amts for selected data/Grand Total of Quantity for selected data
I tried to use a query in a query, but must have done something wrong because it just said circular reference.
I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;
1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings
These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).
I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).
I have a database which I use for scheduling production. I would like to know if there is a way to "push" the schedule dates. I have multiple products scheduled for production but at times I need to adjust the production dates by pushing all the schedule dates ahead by one day. I don't want to re-enter all the dates to do this adjustments.
View 14 Replies View RelatedHi
I keep the production version of my database on a server, and a copy of it for development work on my desktop. Today I've made several changes to the development version, which didn't work. I then opened the production version held on the server in a separate MS Access window to compare the queries/results to see where the problem was and have found that the changes have automatically updated in the production version. Can anyone explain this? They shouldnt be linked at all. The 2 versions both link to a datawarehouse through an ODBC link, but shouldn't be linking to each other (I'm using Access 2003).
Cheers :)
:eek:
My boss threw a curve at me this morning and I need some guidence. I created a commission tracking db for our industry (automotive). Each technician will have a base commission that they'll get regardless of their production. Should they exceed their production by X, we would like to give them a bonus of 2% of all of their work. Should they exceed their production by X+Y, we'd like to increased the bonus to a total of 4% for their work during this pay period.
I need help with the table stucture, to start. My db currently contains a variety of tables including tblTech. tblTech contains information specific to each tech including their annual PERSONAL income goal (not to be confused with Bonus scale). Depending on a number of factors (ie. how long they've worked here, how much other responsibility, etc.), I'd like to assign a bonus tier to each technicial. ie. Technicial A = Joe. Joe is promoted to manager and therefore needs a greater bonus for the same amount of production. I'd like to assign him to Tier 2 where he makes more then a Tier 1 one employee.
I'm having a hard time getting focused. All suggestions are appreciated.
old Access database that's been upgraded through to Access 2000 or 2003 (probably 2000), but not beyond. Was rebuilt in Access 2003 format around 2006/2007 (by someone else who no longer works here). Being used now in Access 2007 and 2010. Uses DSN to connect to SQL Server backend. Last week it was SQL Server 2005, moved the back end on Saturday to SQL Server 2012. Changed the DSN when moving to the new server. But I don't think this has to do with the DSN (it's getting data just fine).
There's a data entry form. User enters a Generator ID, event triggers it to look for the details for the generator and load them into a generator subform. After it loads the information into the subform, and before the user does anything else, it throws "This connection cannot be used to perform this operation. It is either closed or invalid in this context."It triggers post update of the Generator ID:
Code:
Private Sub txtGeneratorID_AfterUpdate()
On Error GoTo Handle_err
Me.txtGeneratorID = UCase(Me.txtGeneratorID)
Call FillHandlerSubform(Me.subGeneratorInfo, Me.txtGeneratorID.Value)
If GetGenStat(Me.txtGeneratorID.Value) = "N" Or GetGenStat(Me.txtGeneratorID.Value) = "OB" Then
MsgBox "Warning Generator Status! " & UCase(Me.txtGeneratorID.Value) & vbCrLf & _
"This Generator has a status of N or OB!", vbCritical, "Bad Generator Status!"
Cancel = True
End If
[code]....
It doesn't throw this error in the development copy of the database, dev copy works just fine. It only throws it from the production version. They are located on the same network, just in different folders. They are pointing to the same database on the same SQL Server using a DSN file located inside the folder where the .mdb file is located (this is a change in the front end, prior to this it was using a DSN on each individual machine, but I changed it to stop that).
I currently have a continuous form in my database that has 24 records in it. every record is blank except for a time (I will post a screenshot)
Our employees are instructed to enter various information every hour in this form. coding a button that, when clicked, will create 24 new records in the table that this form is based on, insert times in those 24 records, and then display those 24 records in the form. It would have the illusion that the end user it "resetting" the form at the end of his shift and preparing a clean form for the next person.
In the spreadsheet that we currently use, there is a button that, when pressed, saves the workbook with the date in its file name and then clears all of the end-user's data so that the next shift has a clean spreadsheet to use. I would like to mimic this button as closely as possible in MS Access
I understand that this database design is not exactly ideal in the world of database engineering; however, I was told to make this a perfectly seamless transition from the excel spreadsheet we currently use (there is a folder with 900+ workbooks from over the years. This obviously makes the information useless - we need all of the information in one file - this database)
I want to release an Access program to the production environment for users. I remember there being a file extention that can be used so the users cannot access the DB or Code.
View 2 Replies View RelatedI have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as
date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |
I want this on a single query so that i can make a report out of this .
I need to make a report that shows a work schedule for 10 employees. I need it to print in a monthyly calendar format with horizontal lines to show start and end date and time. I need it to show that there is or is not any overlap for that machine."
It has been a while since I have created a DB and I get some of this okay. So far I think I need three tables. One is the machines. Two is the product schedule (how long it takes to produce X number of products). The last would be work orders and that pretty much covers the data.
I think most of the data can be maniputated by queries and that is even done on the report. The report as we all know is the reason for a database. If it was just about the data it would be simpler to do a spreadsheet. It could get fancy and make a pivot table. That output can be a calendar of sorts.
I've been asked to edit a current report that lists our production in week number order. I need to look at way we can 'flag up' orders that are within a 4 week period from the current date.
For example,
10 Orders in total in our database. 5 of which are due the drawings back within 4 weeks from today (26/02/15). I'm looking for a way for the report to show the 5 orders as priority, either by formatting the orders in bold, a different colour or under their own heading/group.
I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year
So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.
I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!
Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..