I was wondering whether it is possible (and if it is, the way) to perform the following task in Microsoft Access: there are two tables; first one consists of three columns
1) ID
2) tweet text and
3) date.
Second table is a single vector of words, like a lexicon. For every row of the first table, I want to measure occurrences of the words of the second table (lexicon) in the tweet text column (2). Following, I want to add a new column in the first table, in which I will keep a record of these occurrences. This is what I have done so far:
SELECT *
FROM Tweet_data
WHERE Tweet_text LIKE "*" & Positive_sentiment & "*";
However, I most probably have to make some changes in the part following the LIKE
I am having difficulty in solving a problem in Access 2010 which was a doddle in the Excel version of my database.
Table1 (MailList) has a field 'Member Name' and a new field 'NumberGroups'. Table 3 (GroupMembers) is a junction table and also has a field 'Member Name'. Records for 'Member Name' are repeated in several 'Groups' within Table 3.
I require to Count the occurrences of 'Member Name; in Table 3 and display them in Table1 'Numbergroups'.
I have written numerous Queries to try to achieve this, but without success. Does it have to be a Query, or can it be achieved entirely within the structure of Table1?
In addition, I need to interrogate the resulting numbers in Table1 'NumberGroup' to determine how many member attends 7,6, 5 etc different Groups. Again this was extremely simple in Excel but I can't crack it in Access.
I am trying to count the number of times a client has engaged with our company. I have a company table, a reservation table, a rapid prototyping table and a project table. There is a one to many from the company table to each of the other 3 tables as can be seen in the attached picture.
Is there an easy way, in a single query, to list unique company names that exist in 1 or all of the 3 tables?
I have a table with some prices that are charged by the pound and some that are charged by each.
tblPrices ID 1 - 1.00 per pound ID 2 - 1.50 per pound ID 3 - 2.00 per pound ID 4 - 5.00 each ID 5 - 7.00 each
My form is set up so that way I count how many containers I have and their weight, separately. I need ID 1 through 3 to do the math based on the pounds (essentially $1 per pound at 36 pounds) and ID 4 and 5 to do math based on how many of each are there ($5 each, and there are 3 of them).
Access 2007 query that has a specific date range between #6/1/12014# and #7/1/2014# returns the date I need. How do I now get it to pull just occurrences on Fri Sat and Sun of the month of June?
I have tried the masks 1/####;; , "1"/####;; 1/####;; , "1"/#### , '1'/#### but none work as if the first nubber entered is a 1 it replaces the 1 in 1/
If it is a 2 then it works fine.
I can set the 1 to and f (f/) and it works no problem.
I have a database that was built 5 years ago that has an auto field with an integer. There are relationships attached to this. I an rewriting it to simplify the database and I need to keep the relationships somehow. I want to make the auto field a text fields. How to work this out...
I have a table which is basically a calendar containing the columns (i) date, (ii) special info relating to that date (iii) morning volunteers and (iv) afternoon volunteers.
In the "Morning Volunteers" there are up to two names with sometimes a first name, sometimes an initial but always a family name.
The same is true for the "Afternoon Volunteers".
What I want to do is, using the Family name, to find out how many mornings/afternoons each of the volunteers has manned the Visitor Centre.
As I am new to Access I have no idea whether this is possible and, if so, how to do it.
I can do it by exporting the table to Excel but this is very clumsy and time consuming
I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?
In the Access Table, how does one Find and Replace part of the hyperlink if the Text to display is different?
Example of Hyperlink Editor:
Example of Find and Replace
In other words, I'd like to find FAKESERVER and replace it with C:Users in all 1000 records. Is there any possible way to do this if there is Text to display?
I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the code I found on an old thread and I'm now trying to accomplish everything with one step. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).
Code:
Public Sub ImportTextFile() ' to use the ADODB.Recordset, be sure you have a reference set to ADO Dim rst As ADODb.Recordset Dim rst2 As ADODb.Recordset Dim strFile As String Dim strInput As String Dim varSplit As Variant Dim intCount As Integer
I run a Query to determine how many employees attended a meeting, showing the number of years they have worked at the company. (Example output of query)
Employee Name Years with Company John Doe 3 Jane Smith 1 Bill Doe 3 Rick Mills 1
How do I count the number of people with each range or particular number of years with company? For example: 2 employees have 1 year with company 2 employees have 3 years with company
Thought I'd be able to find out easily, but.. here I am...
How can I query the number of occurrences of a string in a field, e.g.:
if a name field has the text: Martin Lacoste
how can I get it to tell me there are two "a"s in the field?
I can use InStr to find one, and make a few more queries to find a few subsequent, but the data I need to search could have 30-40 occurrences of the desired text in a field.
I have a table that tracks different events and the actual date they occurred. I am trying to write a query that counts the number of each event that occured by month. One issue I am having is if an event had no occurrence during a month I want to return 0 raher than no data for that event.
I know i could achieve this by creating a view first. For instance by saving this expression as qryCountOfColors
Code: SELECT tblColors.Color, Count(tblColors.Color) AS CountOfColor FROM tblColors GROUP BY tblColors.Color;
And then make the following query
Code: SELECT tblColors.Color, tblColors.Value, qryCountOfColors.CountOfColor FROM tblColors INNER JOIN qryCountOfColors ON tblColors.Color = qryCountOfColors.Color
However I'm woundering wheter or not I could achieve it without creating the qryCountOfColors.I'm myself into a mental meltdown . I've been playing with the idea of creating of creating a subquery to achieve this but im unable to get to work
Code: SELECT tblColors.Color As BANANA, tblColors.Value, (SELECT Count(tblColors.Color) AS CountOfColor FROM tblColors GROUP BY tblColors.Color HAVING (((tblColors.Color)=BANANA))) AS Expr1 FROM tblColors;
I have subform (datasheet) with a column for gender indicating "Male" or "Female" I wish to place the totals for each gender (and total overall) on the main form footer. I have tried objects of every type with calculated controls using conditional count formulas such as
I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:
PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime; TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode SELECT tblAttendance.EmployeeName FROM tblAttendance WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime])) GROUP BY tblAttendance.EmployeeName PIVOT Format([AttendanceDate],"Short Date");
This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.
i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.
I've always used an audit trail for tracking changes of records that already exist but I have had a situation come up where I now need to track records that have been deleted. For example, if I delete a record in the datasheet view.
I've seen several examples how to do this, with the most common seeming to store the data into a temporary table while waiting for the delete confirmation. If I am reading the notes correctly, it's done this way to capture occurrences when multiple records are selected to be deleted. Is this the best approach for this type of task?
What is the difference between "short text" and "long text"? Both seem to have 255 characters limit. I have Access 2013, and I found a solution, that you can use Memo instead of Short text. But the problem is, that there is no "memo" data type in Access 2013. So that won't work for me.
We have an Access 2000 backend database resides in a network server drive while users connect to the backend tables with a mde file on their computers. All users have Access 2010 and the mde file was converted from the 2000 frontend with Access 2010.
The database have been running for a few weeks but recently users have been complaining about record lost on the backend table.We have also experienced one incident of data corruption where the main table could not be opened. After Compact & Repair, the table could be opened but a few records were showing xxxxx on all the fields and we have to delete and re-enter these records.Would they relate to using 2010 mde converted from 2000 frontend when the backend is still in 2000? I am a little nervous about converting both the frontend and backend to 2010 since I have heard various issues on the new version.
We need to move rich text from an Access memo field to a Word text box. So far the best Ive been able to come up with is in the code below. In this code pprs!What is a record set field of a table memo field that is bound to a text box enabled for rich text. The rich text seems to be stored as html as so I can get word to convert it by enclosing it in html tags.
Dim What As Word.Shape Set What = doc.Shapes.AddTextbox(msoTextOrientationHorizontal , doc.PageSetup.LeftMargin, 225, 534, 0) Dim sPath As String sPath = "G:Temp.html" Open sPath For Output As 1 Print #1, "<HTML>" & pprs!What & " </HTML>" Close #1 What.TextFrame.TextRange.InsertFile (sPath)
I know this is an Access 101 question, but what is the best way to view text rather than numbers in my tables. In an example which I have attached, an Orders table shows the Customer field as a number. I would like to view the actual customer name when I am looking at the table.
I have a text source file and inpul layout i.e. field names start and end positions in excel file. I want create a table in access from the text data using excel file layout.
Can you please help me out in this. I am a mainframe programer and recieved an request to work in access.
