Totals From Instances (not Of Instances)
Jul 21, 2006
I've come across an interesting problem a colleague has been trying to solve and even though I can 'see' what needs to be done and could probably program it on our mainframe, I'm really stuck on how to achieve the same results in MS Access
I'll do my best to explain the problem by showing an example of how the data exists in the DB and also how it 'should' be output on a report.
DATE OPERATOR COUNT
29/06/06 - Oper A - 32
29/06/06 - Oper B - 12
29/06/06 - Oper A - 10
29/06/06 - Oper C - 13
29/06/06 - Oper B - 10
The output needed however should read as follows
29/06/06 - Oper A - 42
29/06/06 - Oper B - 22
29/06/06 - Oper C - 13
Setting up a date selection shouldn't be a problem, its just adding the totals on each instance of each operator into one figure without ending up with a total of the number of times an operator appears on a date.
Any help and guidance about how best to approach this problem (macro/query/script) would be appreciated.
View Replies
ADVERTISEMENT
Jan 18, 2007
I am fixing a database that was previously built. It is use to show listings of items for sale. There is a form that displays all of the information about a particular listing, each with its own id code.
If I want to email a particular item and info to an email address i can, but i can only send one at a time. I click a button which then asks what format to save the report, i choose html, then it opens a new email message from Outlook and sets the file as an attatchment.
The problem is, i want to be able to send more than one listing at a time in the same email. But once the first listing is created and attatched i have to send that one before i can send another listing.
Any help would be greatly appreciated.
Jared
View 2 Replies
View Related
May 12, 2005
Hi, I would like to recreate a record locking problem (error # 3218) our users are experiencing on an application we have developed. I currently do not have access to a network, so I would like to run multiple instances of Access on one machine. How do I do that?
Thanks,
Bill
View 1 Replies
View Related
Feb 22, 2007
I have a very elaborate Access 2000 application that uses three different SQL databases as the back end. Mostly the data is modified or selected via ADO connections.
The problem I am having is that recently users on Terminal Server are not able to open the application because for some reason the application is not allowing more than one copy of it to be opened at once. I checked everywhere and couldn't find anything that would not allow multiple instances but couldn't find anything.
Any ideas on what could be causing this? Any help is greatly appreciated
View 11 Replies
View Related
Jun 11, 2005
Hi Guys
Sorry to come to you with this one but my sql is terrible......
I have a recordset named Leave reason. Its numerical and can cover some 20 differing numbers. These are not always sequential.
Ive tried a crosstab - not good enough, Ive tried a form with =Count(IIf([Leave reason]=9,0)) in it, this works, but, it requires a text box for every possible number that could occur!
To labour intensive that.
So the bottom line is is there a small piece of sql that I can add in to the queries sql view to handle this issue, if there is can someone just lay down the code (yes I know Its naughty to ask) so Ill learn and be able to do it in some 30 other
queries of the same nature.
thanks guys - sorry its so drawn out
gareth.
View 2 Replies
View Related
Aug 4, 2005
Is there a way to allow Access to open a second (or thrid...) copy of a form? We need this so we can visually compare the contents for record one with the contents of record two.
Thanks,
David
View 11 Replies
View Related
Feb 9, 2015
I have a table called providers with the following 3 fields:
Provider ID
Cancellation Reason
Visit ID
Date
It is possible to have multiple instances of the same provider ID, cancellation reason and visit ID on the same date. I'm trying to count just 1 instance based on visit ID if there are multiple instances of the visit ID and reason on the same date. Select distinct doesn't seem to do the trick.
View 1 Replies
View Related
Jul 31, 2014
I have a database for storing part numbers of power supplies, very basic database. I also have a multifield search form and search query. All very basic stuff, however I am new to access and cannot seem to find out what the is causing this issue.
There are 174 entries in the database table, they all start with "0" for the part number, I have wild cards in my Query so that for instance if I searched 0 for the part number field, it should return all 174 entries, but for some reason it only returns 49 entries.
View 1 Replies
View Related
Mar 17, 2015
I have a text file that cannot be imported and turned into a table because it has no line feed characters and is therefore well over 255 columns. The fields are delimited by commas, however.
How can I search this file for all instances of "{id" and then grab the next 10 characters to the right and store them somewhere?
View 14 Replies
View Related
Dec 8, 2014
I have a subform that formats the data in a record in a particular 2D way. I want to use it a variable number of times on a single form. Is there any way to do that? I've tried using CreateControl, but access just says it can't add the controls I've requested -- with no reason given, of course.
View 2 Replies
View Related
Jun 14, 2013
I have an Access 2007 mydb.accdb file. If I click on it, it starts up fine. If I click on it again, then focus goes to the already open db.
If I do the same but with the .accde of the same db, for each click a new instance opens. This is not good, in that a single user then can have multiple instances running without noticing it, and that might mess things up. What Windows setting to use, to convince Win XP that only one instance of mydb.accde should run, similar to the .accdb?
View 2 Replies
View Related
Jul 21, 2013
I have written a large database for the school I used to work for. In the main table there is a field named "Active" which is text with yes or no choices.Is there a way to find in what objects this field is being used in the database?
For the life of me I can't remember why I put this field in or if its even used now.This database has had many versions over the years.
I have searched many of the queries but can not find where this field is used.If there isn't a way I plan to backup, remove field from table and test all the features of the program to see which crashes. However I'm hoping there might be a quicker way.This was one of the first DB I wrote and didn't pay much attention to mapping or keeping notes back then.
View 5 Replies
View Related
Mar 25, 2013
The thing I've been trying to do is make it so that my form filters my records, and I'm trying to make a between function for it. My form is shown below in the attachment.
What I need to do is make it so that my form filters my records Between the two year boxes AND between the two Length boxes. But I need it so that if nothing is in the boxes, it shows all records, and if something is in only the Year boxes, it only filters the years and not the lengths.
Code:
Field: Length
Criteria: Between [Forms]![SearchForm]![Length1] And [Forms]![SearchForm]![Length2]
Then in a separate column I had
Code:
Field: [Forms]![SearchForm]![Length1]
Or: Is Null
This works fine if it's only for Length, but if I try to do the same for the MovieYear boxes, it screws up and just shows me no records...?
View 9 Replies
View Related
Mar 28, 2014
After creating multiple instances of a form how should they be closed so as to ensure that all of their assets are disposed of and there are no memory leaks? DoCmd.Close seems not to work because it expects a name string which would be the same for all instances of the form.
Since a form's name is a read only property it cannot be modified when the form is created so it doesn't uniquely identify a particular form instance. I've tried setting the form reference to Nothing which appears to work, but I'm not sure whether this is the correct technique and whether it disposes of everything cleanly.
View 1 Replies
View Related
Nov 7, 2013
I have a database where several entries may have the same address, but someone has been inputting some of the zip codes incorrectly. I want to come up with a way to get a query or the like to spit out a report with just the entries that have identical addresses and conflicting zip codes, and have hit a brick wall.
View 5 Replies
View Related
Dec 20, 2005
Not sure if this is the correct forum but its worth a shot.
When I try to open any database, MS Access will hang if there is already another instance msaccess.exe running. I even tried opening two newly created blank dbs with the same result. Has anyone ever come accross this behavior?
I have tried repairinging/reinstalling/checking and unchecking "Windows in Taskbar option"
Thanks for any help!
View 1 Replies
View Related
Nov 4, 2013
I have some code that opens and populates an Excel spreadsheets then does a save as to a specific directory and also outputs a PDF. This code is working with one small problem, I am leaving the first instance of Excel open and can't figure out where I should put the code for it to quit. Here is my code:
Code:
' This code creates a recordset based on the current reservation in order to gather data on
' a specific reservation, to output to a payment schedule excel spreadsheet which calculates
' Payment dates and amounts for specific reservations
' Declare variables for recordset
Dim rst As DAO.Recordset
Dim db As DAO.Database
[Code] ....
The instance that I need to close is shown in red.
View 1 Replies
View Related
Sep 26, 2014
I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
View 14 Replies
View Related
Jun 24, 2014
I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.
This query works, on anything that starts with the letter D or later... A-C will not work.
Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.
This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.
Code:
SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;
The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.
Running Access 2013.
View 2 Replies
View Related
Mar 22, 2013
We have a form that will have one combo box for Associates (like Employee in the OPs DB) which I've got set to show only active Associates for new entry per the tips in the above thread, but we'll also have a combo box for Developer which I need to function the same way. I don't know how to add that part to the On Current event?
Code:
Private Sub Form_Current()
Dim strComboRowSource As String
If Me.NewRecord Then
strComboRowSource = "SELECT T_Associates.AssociateID, T_Associates.Associate, T_Associates.Active " & _
"FROM T_Associates " & _
"WHERE (((T_Associates.Active)=True));"
[Code] .....
View 4 Replies
View Related
Sep 30, 2014
I've got a single table with multiple fields, three of which are a date field ('DDate'), a time field ('TimeET') and a unique identifying field ('Unique Call Key'). I'm attempting to write a query in the QBE that will allow me to count the number of instances of 'UniqueCallKey' for each hourly time interval (7:00:00 AM - 7:59:59 AM, 8:00:00 AM - 8:59:59 AM, 9:00:00 - 9:59:59 AM, etc) for any selected date range (BETWEEN 'DDate'(1) AND 'DDate'(2)). When I try to simply use the Count function on 'UniqueCallKey' as an Expression and 'TimeET' with 'Like '7:*AM' as a Where criteria then do the same with another instance of 'UniqueCallKey' and 'Like 8:*AM' as the criteria for a second Where criteria for 'TimeET' the query returns an empty set. What I'm trying to accomplish would be a column of dates, a second column that counts the number of instances of 'UniqueCallKey' at the 7AM interval for each date in column 1, a third column that counts the number of instances of 'UniqueCallKey' at the 8AM interval for each date in column 1, etc to a final column for counts at 7PM.
View 3 Replies
View Related
Sep 4, 2007
Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!
View 14 Replies
View Related
Jul 27, 2007
Hi there, I am fairly new to access and am having trouble doing what I need to do. I have a table that contains 2 number fields, 14 yes/no fields, and one text field. I want to be able to display the total number of yes's per field, and the total count of each individual value for the number and text fields.
I was able to make individual queries that count all of these, however I'm unsure how I can make a query that will do it all together, or how to put them all together.
I may be doing this wrong, or there may be a better way of doing this.
Any help would be greatly appreciated!
Thanks very much!
View 10 Replies
View Related
Nov 10, 2006
is there a way that i can get a total from a number on a form and a number on a sub-form (I want to add the numbers from the form and sub-form and have the total appear in a text box on the form)
View 3 Replies
View Related
Mar 7, 2007
i want a field to total up the amount of times a person apears in another table. does it involve using the greek version of E?
View 2 Replies
View Related
Jun 28, 2005
I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.
Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.
Below are the 12 scenarios:
1.) Reduces 75% at Age 70
2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000
3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.
4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.
5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%
6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.
7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.
8.) Terminates on the first of the month following the 65th birthday.
9.) Terminates Age 70
10.) Terminates on date of retirement or 1st or month following 70th birthday
11.) Terminates Age 65
12.) Never Reduces
Thanks for your time, Brian
View 2 Replies
View Related
