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.
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.
I have a csv file created from e-mail gateway app for incoming and outgoing mails. I am trying to get e-mail stats from this file. One column contains "To" data. In the "to" field I may have more than one recipient per line (one mail cc to many) eg. user1@domain.com, user2@domain.com, user3@domain.com etc. in the next line I might get user2@domain.com. So for this my stats would say I recieved 2 mails (the line counts) but 3 recipients. Here's what I need. Need the data to be sorted to show only the first recipients name and do a count of how many mails that recipient received. In the outgoing it works nice, because I only have one sender in the "From" field there I just run the query wizard and presto. This doesn't work to well in the incoming since the data differs as the "To" recipients are'nt all the same.
I have a questionnaire table that has three columns, each column containing a textural answer to the column header question. Each answer is one of six textural choices that are specific to each column.
What I want to do is summarise the information so that I can read off how many of each answer there are e.g how many answered 'x' in quesiton 1, how many answered 'y' in quesion 2. If it helps, I don't require a summary that might detail how many answered ''x' to question 1 AND 'y' to questions 2. I just need column summaries.
I have qrys that show current info and also repeated info. A request has been brought to me to now count how many times the repeated data has been repeated.
The data is updated daily, so the repeated record would need to increase in the number of it's occurances by 1 each day.
Any guidance on how to accomplish this is GREATLY appreciated! :)
How do I count only filled out data in a query? I am using the count function and it counts blank and filled out records, I just want the filled out ones.
I'm using Access 2010. I have a report that summarises students and the number of courses they are attending after a set date. My query lists name, course date, course description, the count being on the course description. I thought it was working until I noticed that students are listed twice if they attended courses on two separate days.
For example Liz 4 Liz 3 instead of Liz 7
I have moved the count to other fields but it then doesn't show any students at all when I run the query.
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.
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
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?
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
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.
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.
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.
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?
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.
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?
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.
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...?
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.
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.
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
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"
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.
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?
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.
