I am trying to count records in a running sum fashion in a QUERY and for the records to restart when the field changes. I tried DCount without success.
ie. if a field contained the following data
Code Running Count
C101 1
C101 2
C103 1
C103 2
C103 3
C104 1
C104 2
Thanks
:eek: I'm running a Make table query that links two tables in a one to many relationship. What I need to do is add a column that is a running or sequencial counter. The counter is used to preserve the order of the child records thus I don't want it sorted. It would be nice if the counter would reset when the Parent-ID changes. This use to be quite simple in Access 98...so much for better ideas. I have reviewed the other forums, but most deal with totals, I need running count. The others are unclear, so I asking one more time. Thanks
Sample of what I need:
Parent-Id_ Child-Id_ Count
___ 123___ 043____ 1
___ 123___ 121____ 2
___ 123___ 321____ 3
___ 123___ 065____ 4
___ 243___ 032____ 1
___ 243___ 043____ 2
___ 243___ 012____ 3
___ 187___ 321____ 1
___ 187___ 022____ 2
I have a query with 40 fields and 16,000 records. Field 1 is Location, of which I have 197 unique values. What I would like to do is have a running count for each unique location and when the location changes start the count over again.
Example
Count Location Function
1 100 1111
1 105 1111
2 105 1121
3 105 1131
1 110 1151
2 110 1151
I am using Order By to get the proper sort. In some other instances I may want the count to be dependent on the combination of Location and Function codes. Can someone provide some SQL tip(s) that would allow me to accomplish this.
Thanks
Loki
I have data like so:
Code:
SubjectID VisitID
1 5
1 5
2 7
2 7
2 9
2 9
etc
And need to return a running count for the number of visits per subject, so:
Code:
SubjectID VisitID VisitCount
1 5 1
1 5 1
2 7 1
2 7 1
2 9 2
2 9 2
etc.
I'd like to use DCount and have tried all variations of the following, but none of them returns the correct number:
VisitNumber: DCount("*","mytable","VisitID >= " & [VisitID] & " AND SubjectID =" & [SubjectID])
I have an access query named "leaveapp" and I want a running count as below:
EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1
and i need a column in the right with running count like below
EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1
I want to do something within Access 2010, but unfortunately I have only partly succeeded so far..
So far this thread has been very useful: [URL] ....
It (almost) completely explains my problem. I have a database with suppliers that participated in tenders. Now I would like to, on a chronological basis, make a "running count" on how often a supplier participated in these tenders (on the basis of supplierID).
In the attachment you can see an example of how the data in my table looks like. With the following function, which I got out of the thread above, I have already been able to make a running count on how often a supplier participated (its "experience").
The function:
DCount("*";"[Table1]";"Supplier_ID = " & [Supplier_ID] & " AND ID <= " & [ID])
So far, so good. But I would like to go for an extra addition, since now the period on which this running count is based is the entire data set, which runs from 2006 till 2013. In other words, Access starts counting from the first record and ends at record 300.000. What I would like to do is to base this running count on the two years before (and including) the tender's Decision_Date, which is also in the dataset.
Example: a specific supplier participated in a tender that took place on 10 december 2010. Know I would like to know its "experience" with tenders, by means of a running count, based on the period from 10 december 2008 till 10 december 2010.
Query is based on 1 table" tblTimeCnv_AgeGroups
Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4
See attachment
My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])
In the Ranking column the result is: #error in the first 3 rows then zeroes
I have made a form on access which will be used by other employees within the company, however they have never used access before and they are wanting to access only just the form as I feel with Access and all the tools in the background will confuse them, plus I don't want them editing the data base its self.
Is there a way to only bring up the form, unless I obviously need to edit the date base.
I've done some research on Google and it seems I need to use:
SW_SHOWMINNOACTIVE
However I can not seem to find how I would use this or where?
and also if i did find a way to only bring up the form, how would I be able to switch it from that veiw to the veiw I edit in?
Having a problem here after installing 2007. All my previously created databases work fine in 2007 (after clicking the enable, which is a pain,but don't know any other way) When I return to 2002 and try to open the databases I get different errors all something like this on....... "The expression On Open you entered as the event property setting produced the following error: Object or class does not support the set of events. Whats up here?????I have found a site here...http://allenbrowne.com/bug-17.htmlMentions this problem,but refers to Vista..... Does the same apply to XP? And which is the best fix?
View 7 Replies View RelatedI run several databases daily - after running two of them (at different times), the database itself closes but the Access program stays open. In order to close that program, I have to close it out using the Task Manager.
Any suggested solutions? Thanks (as always) for your assistance.
Bob
:confused:
Hello, I have volunteered to do a database for the school of nursing to use at a small Christian University using Microsoft Access 2003. I have Students, and each of these students is going to take each of these 12 nursing courses. Each nursing course has a specific number of credit hours (2,3,6, etc.) and they are to receive a grade for each class (on a 4 point scale, so their grades are going to be a number 0 for F through 4 for an A). I need to have access calculate a running GPA which only takes into consideration the classes they have completed thus far. Any help would be appreciated. I can do it in excel by just leaving the row for untaken courses blank, then autosumming the credit hours column for total hours taken. I can use a formula for each row that just multiplies the grade (like 4.0) by the credit hours (like 3) and get a total grade points of 12 for that course. Then I can add those numbers up(autosum the total grade points column) and devide by the calculated autosum number I would call total hours. I hope it is clear what I am trying to do and why I don't know how to do it in Access. ***************Extra Info*********
I have a student table and each student has a unique studentID, I have a second table called Nursing grades which I have a Student ID field, and 2 fields for each class a grade recieved and a credit hours field (ex. I have PharmacologyGR and PharmacologyCH, NursingResearchGR and NursingResearchCH). If I need to redesign how I have my tables I can do that, I have a unique Course ID for each course I could put in if needed. Basically, I'll do whatever it takes to get this to work.
Thank you so much for even reading this far, and any help would be appreciated more than you know.
Hello,
I have an Access application which was written on Access 2.0. I need to run it (as is). I saw that it was possible to run it using some kind of Access 2.0 runtime environment - my client uses the following command to run the application:
Msarn200.exe application.mdb /ini application.ini
Where can I get this MSARN200? Do I have to find the Access 2.0 installation itself?
Is there anyway to use Access to run reminders?
I am trying to figure out the easiest way to have a reminder pop-up to run a letter for specific reps that would happen every 3 months.
Thanks for any help on this.
Anyone know how to run a bat file from Access.
This is what I am trying....I see the DOS window pop up and then its gone...the .bat does not appear to be running.
Call Shell(Environ$("COMSPEC") & " /c F:BAT_File_Storagedir_list.bat", vbNormalFocus)
ADDITIONALLY I want to replace "F:BAT_File_Storagedir_list.bat" with variable. What woudl be the correct syntax for that?
.
does anyone ever have problems with constant crashing. it just crashes for no reason, i'll just start typing some code then CRASH. i'm all up to date according to mircosoft.
i'm running vista/office07/accessfile00-03
I have just installed the windows version of Access 2010 on OSX Mountain Lion using a program called Crossover which works very similarly to WINE. I have found that Access will install fine except for VBA form modules and BAS modules, although there seems to be no indication that they did not install as the entire install progresses fine.
I've put together an invoicing program that was created in Access 2010 in Windows but have transferred it over to Access 2010 installed on OSX. It contains a number of forms with code but when I go to VBE, form code modules are not visible. When I try to view the code for a form I get the following error message: "Microsoft Access failed to create the Visual Basic module. If your database is on a network drive, check your network connection, then try again". I was able to insert a BAS module and create a rudimentary VBA subroutine which ran without error so it does seem able to interpret VBA as per normal.
I have access 2003 installed on two different machines. One machine was built 6 months ago with the best hardware available. The other was an older slow IBM.
For some reason, I am experiencing what appears to be some serious time delay (talking in the range of seconds, sometimes tens of seconds) when building a report that has a subreport in it on the fast machine. Yet when I load the exact same database on the slow machine, the report opens instantly.
Any ideas? I'm lost.
Access 2003
I've inherited an Access Application which has
(1) a number of linked tables that are used to seed the underlying SQL Server database.
This system has been set up and is working in Development. My task is to move it to UAT but with regards to point (1) above I can't see any alternative but to copy the Access Database, create a new System DSN and re-import the External Data from the new DSN. What I'd like to do is use the same Access Database to point towards DEV or UAT or PRD. Is this possible to do this or am I stuck with maintaining 3 different Access Databases ?
I am a developer with many years of experience but my Access knowledge is limited so any pointers would be helpful.
Not sure what happens.
occassionally i will run queries that have run before,
the hourglass will turn on, then turn off, and access does nothing
visible, but tack manager says its running 90% cpu. . .
any ideas on what is happening or i am doing wrong?
thanks
sportsguy
Am running a query in Excel on an access table that uses lookup columns. The query is returning in excel with the right data but is returning the lookup value number rather than the data stored inside. How can I return the actual data value rather than the lookup value number.
i.e I have a list of areas
NY
CT
WA
in a lookup column.
User selects CT so is lookup value 2. But I want excel query to return CT.
How can I do this?
thanks
CAn i change the name of a field in a a folder from access. or better yet can i automate a file being copyed from one folder to another.
For example, lets say i have a folder named photo. i want access to find all the items that finish with a "f" and copy them into a new folder named "f_Photos"
Any Help
Hi all,
I was hoping that someone could tell me if it is possible to run an Excel macro from an Access database. I am importing 3 Excel worksheets into Access and this macro needs to be run on all 3 evertime that this file is imported by the user (twice a week). If this can be done could someone please tell me how ??
I have an MS Access application for about 40 users. The problem is that the company does not want to have to buy all these licenses and put it on each machine. Also because each person had to bring in their own set of data multiple people cannot be hitting this at once. So they want to know if there is a way to allow the people to use it without having the application on their machine; and thereby saving the money of buying 40 licenses.
View 2 Replies View RelatedCan anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
Many thanks in advance.
Peter
Hi all..
I have access 2003 and I package my application with Access runtime. Users do not have access installed in their computers.
The problem the users have is, they cannot Copy/Cut/Paste from the textbox controls in the application..The same application when I run using my full version of Access 2003, I can cut/paste...
Is there a way to fix it without having to get full version of access ? or I have to live with it because I use access runtime.
Thanks
Hi there - hopefully someone can help me out, as we've got clients waiting on my response to this problem!!! :eek:
Anyway, my company produces an Access 2000 MDE which we ship with the Access 2000 runtime (just in case our clients don't have full Access installed)
We've had a number of calls saying that our app doesn't run on Vista, and the error provided points towards a reference problem. However, when I load the MDB on Vista using Access 2007, there are no reference issues.
Is this problem related to Access 2007 reference ("Microsoft Access 12.0 Object Library") somehow being used instead of the Access 2000 one? ("Microsoft Access 9.0 Object Library") - I am about to try uninstalling Access 2007 and running the app again to see if that helps...?
Any help greatly appreciated!
Baz