Hi!
I cannot stop Access 2002 from automatic creation of Total column for every column. Is there any method to compute totals only for some columns?
I would appreciate any help!
With kind regards
Maxim Ivashkov
I have a report that shows 3 pivot tables on the same page. What we would like to do is total (or perhaps subtotal) the three tables into a summary table.
each table has number code, name, data 1, data 2.
The three tables are identical with the exception of I filter on only specific number codes.
Currently I simply have a 4th pivot table with all the number codes and just hide all the rows except top and totals.
I would love to be able to use something like subtotal to simply display the totals (from my 3 tables)
I have made a database where i plan the company's resources in form of hours. The idea is that every employee will put in their own data for the upcoming 4 weeks. I do this in a table where i store employeeID, weeknumber, year, PlanneHoursUsed, ActualHoursUsed.
This again i take into a pivot chart to see a forecast for the next month.
I have somehow achieved to get up a graph where i for every month can see the sum of all planned hours and all used hours (See attachment). BUT; my boss wants to see a running total instead of a "weekly image". On the graph attached one can see the numbers for every week. But i would want to see instead a running total.. For instance week1 then week2 would be the sum of 1 and 2, week 3 would be 1 ad 2 and 3 and so on. So the graph would be inclining throughout the year.
Is this possible with functions with my current data? Or do i need to have another field in my table where i store a running total? (This would be tricky when we get a lot of data and when somebody changes an old value...)
Hi there,
I've developed over the past 3-4 years a database holding data relating to workload figures for my place of work. It has grown to a large size (eg. one table holds 1-2 million records). I've been trying to run a particular pivot table based on the sql query below:
SELECT Tests.Test, Year([DAUTH])+(Month([DAUTH])<4) AS FYear
FROM Sets INNER JOIN Tests ON Sets.SET = Tests.[Set Code]
WHERE (((Sets.DAUTH) Between #4/1/2003# And #3/31/2007#));
When I try producing a pivot table using the above query it takes forever - has it running for over 8 hours last night - then access closed down with a runtime error. Does MS access have a problem with large databases? Is there any way I can improve the efficiency / speed of access with a download add-on? Or, will I have to resort to using something like MySQL on a LINUX OS which has fewer demands on system resources when using large databases.
Any help would be much appreciated. Thanks for reading.
Hey all. I posted this over at Mr. Excel too on the Access forums, but I thought maybe someone from this site might know the answer to my question.
I know pivot tables and charts are more up Excel's ally, but my question has more to do with Access I think. I've made a pivot table form from a query and everything is working fine except one thing. I want the table to refresh automatically. I don't want the user to have to go in and refresh the data manually.
I'm thinking I could put something on the forms "On Open" event but I don't know the specific coding. If anyone has any idea on this, I'd greatly appreciate some feedback.
I have a table with 4 field which describe clips.
ClipName (txt)
ClipSize ( Long Integer)
ClipDuration (HH:MM:SS)
Date created (dd/mm/yy).
I would like to get the total of ClipSize and also total of Clip Duration either in table down or in query.
When I start my database normally, I'm not able to add fields, change chart type, etc, by right clicking in a pivot chart or table. It works when I open the database while holding down the shift key though, so I suppose there is an option in my database somewhere that I have changed, but which one.
View 1 Replies View RelatedHow to incorporate access pivot tables in an access form? Also how to subtotal on changes withinin a field in a form?
View 4 Replies View RelatedI have an Access database with millions of records.I am only interested in a subset of records (250,000+) that I would like to analyze in an Excel pivot-table. My issue is; how do I best export this information to Excel.I've tried filtering and exporting, but all records are exported. I've tried copy and paste but only 65K are allowed. Is there a better way to do this, perhaps linking the Access database?
View 1 Replies View RelatedI am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have built a database and within it my queries I use for reporting make tables in a separate database. This allows me to work in my data base and update info as needed. My problem is that my make table queries wont run if anyone has one of the report pivot tables open. I have added macros to all the Excel files that pull from the reporting DB but I still have several times when I cannot update because someone is using the pivot table, Is there a way to allow me to update the report DB's while the pivot tables are open? I have tried using manual refresh methods and disabling auto update on open but the result is the same.
View 2 Replies View RelatedI have a larget transaction data set in access with Datetime column/filed.
I have been running pivot queries to excel to do analysis of the data but the datetime field is returning too many unique values for the pivot table to run.
What is the best way to reduce the datatime field to date only and where should this be done?
i.e. should I have a calculated field that trims datetime or should I set someohting up in Powerpivot?
I work on a pre-created Access database, and the other day I was working on it, and was trying to export something to Excel to sort it and do some Pivot analysis.
Anyway, I must have pressed something, because now every time I open the database, rather than saying "record 1 of 20463" and showing the data from record 1, it shows "record 1 of 1" and all the data fields are blank. If I go to "Records" and "Show All Records" they'll all come up, but I don't want to have to do that every time, and as I import and export all the time, I'm worried that the next time I try it it'll mess up the years of data I have.
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 would like to sum up 5 fields and save (or just show it in the form view is enough really) in a seperate field, i have managed to sum the 5 fields, but it summed up all 5 fields in all records, i'd like to have my form show the sum for the record, not the table.
it would be ideal that it would refresh when one of the 5 fields data changes, i.e from 1000 to 2000. again updating in the form view is what is needed.
I have a simple table named [Groups] containing a Primary key ID, a Group name, a Group Leaders name and a date field. The date field merely lists the date of a particular Group related occurrence.
I need to know how many records have dates entered and assumed a simple total would achieve this. The Total is correctly shown in the appropriate row but most, but not all, of the date records are converted to show the same 'total' number.eg the date 28/01/2014 is changed to !4. Quite a weird occurrence.
Is it the case that date records cannot be totaled, in which case, why not all?
Access 2010 ... I have 2 tables. One with base information second one is linked with multiple results each having a price. On table one i see the + sign when i click i can see the linked second table. Can i get a total amount of the price on table 2 on table one?
IE:
Table 1:
Trans ID - Seller - Quantity - Lot Cost - Parts Cost<-- the one i need total for.
123 - joe - 3 - $100 - $20 <-- the total of the 2 linked parts (Keyboard, Mouse)
Table 2:
ID - Trans ID - Part - Cost
1 - 123 - Keyboard - $10
2 - 123 - Mouse - $10
I want to create a table having. These fields
(1) amount paid
(2) total fund(calculated field that is =total fund-amount paid)
Up to here every thing is ok but U want to update the total fund field updated in the next row as remaining fund
as
amount paid--------total fund
5000------------20000
1000------------15000
0-------------14000
Any way of setting up a table containing the following date/time fields.
StartDate
StartTime
EndDate
EndTime
Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.
In either scenerio, how do I subtract the two in a query for a report?
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..
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
Hi,
Have Access XP on Desk top and lap top. Have about 10 various databases for different projects.
On the desk top, I cannot get a Pivot Table to open in any of the databases, but on the lap top, the same databases all have Pivot tables.
Is it possible that I have turned it off on the desk top?
Thanks.
Mike
Hi everybody.
I have a pivottable query.
This works great.
I have a column with averages.
This works great to.
Now here is my problem:
At the bottom of my Averages column there is a total (sum)
I want an average in stead of a sum.
Please help me guys....
Thank you very very much in advance...
Hello,
I am a student on a apprenticeship about MS access. My assignment is to build a MS Access application. But i am stuck. I will put my problem as simple as possible.
I have 2 tables. 1 with employees:
Department(string) | EmployeeNr(AutoNr) | EmployeeName(String)
And one with a couple of times:
Time(string)
The table i need is as follows:
Department(String) | EmployeeName(String) | EmployeeNr(AutoNr) | Time1(Boolean) | Time2 (Boolean) | time3(Boolean) | ......
Unfortunatly i have absolutely no idea how to do this and it is killing me :S
Can anybody help me? Thanks in advance.
The CitiZen
I work on an enterprise which we send people to do field work, & i am doing a database in Access 2013, there i have a table with all the data of our employees defined by an ID, & i have also a table with all the drivers that take em defined by another ID for them, the problem is when i am trying to make a query..I made a table with the ID of the drivers, the ID of our employees that are on that trip with him, the departure date & the arrival date; and when i try to make my query i want it to display: the both dates, & Name of the driver, his ID, & the truck he is using (all this info is on the driver table), and also to diplay the name, the ID & the rank of the employees going with him (all this info is on the employee table), the problem comes that since i am using the ID to search for every employee info, when i put that more than 1 employee is going in that trip i get this error on the top of table: Expr1000, Expr1001; instead of ID, Name, etc... & no data is displayed.
View 2 Replies View Related