Trying To Figure How To Count How Many Times A Word Has Been Used And Return 0 If Not
Sep 23, 2005
Trying to figure this out, but I’m not getting anywhere.
I have 2 tables one is just a list of options that can be used in a drop down box in the other table.
What I am trying to do is create a query that will count how many times the words from the first have been used in the second query. I have been able to get it to count how many times the word has been used but I also need it to return a 0 if the word has not been used.
In my query I used both tables to try to do this but it only returns the ones that have been used.
I will attach the DB if someone is willing to take a quick look at it to see if they can get it to work.
Any help will be greatly appreciated.
Thanks in advance
Jon
View Replies
ADVERTISEMENT
Jul 12, 2006
I just can't get my head round this one and can't even think of a keyword phrase to search on!
SELECT CompID, CaseRef, ServicesID, Summary
FROM qryAllStats;
In my query I need to return all records where there are more than 3 instances of field 'ServicesID' .
For instance in the recordset ServicesID 47 occurs 5 times while ServicesID 36 occurs 4 times and all other ServicesIDs occur 3 times or fewer.
I would need to return all of the records with ID 47 and 36. Is this do-able?
View 3 Replies
View Related
Nov 1, 2014
I am trying to write an access SQL query that returns all records shared between table1 and table2 LESS THAN 3 TIMES.
application has 4 tables: Patients, AnnualPhysical, Doctors, and PatientPhysicalDoctors.
each patient has an annual physical (once a year), and chooses one doctor per annual physical.
each doctor can only perform three or less annual physicals per year.
It is a small application with less than 1000 records per table.
the query will allow the patient to choose an annual physical doctor once a year from available doctors.
Patients is one-to-many to AnnualPhysical;
AnnualPhysical is one-to-many to PatientPhysicalDoctors;
Doctors is one-to-many to PatientPhysicalDoctors.
query for all records NOT shared between Doctors and PatientPhysicalDoctors is:
SELECT *
FROM Doctors
WHERE NOT EXISTS( SELECT * FROM PatientPhysicalDoctors
WHERE Doctors.DoctorID = PatientPhysicalDoctors.DoctorIDFK );
View 3 Replies
View Related
Jun 16, 2006
What I would like to do is have a field in a given record increment itself every time that record is selected. For example, say you have a table that stores items in the inventory: each item has a times_search_for_field.
Now, lets say a user runs a search which results in the following SQL:
SELECT * FROM Inventory WHERE experation_date=Today();
Now I want to have every record returned by that SQL statement increment it's times_search_for_field incremented by one. I'm just looking for the most efficiant way to do this.
I know I chould just have my asp .net code loop through the results and call an SQL UPDATE statement for each record, but it seems there should be a more efficient way of doing this.
Mike.
View 1 Replies
View Related
Oct 24, 2014
I have a query that gives a value in a column either "A","V","L","H","P","S" or blank.
I want to be able to count the amount of occurrences of each letter and hold that number in a column. Is it easy to do?
View 5 Replies
View Related
May 31, 2013
I have a log in table that counts every time a user logs onto the database. Each time a user logs in it adds another entry to a table.
I have built a query to display the user name and date of log in so I could display this information in a report. The report is now getting rather long, and I am looking for a way to display each users name and have the total number of times they have logged in, not display each time they have logged in separately.
The Query has two fields "Agent Name" and "Logger Date"
The report displays the data as follows
Agent Name Logger Date
User 1 26/5/13
User 1 26/5/13
User 1 27/5/13
User 2 28/5/13
What I would like to do is have a report listing each users name, with a column showing how many times they have logged on. e.g.
User Name Login Count
User 1 3
User 2 1
View 10 Replies
View Related
Apr 2, 2013
I'm compiling a monthly report based on data from my table "JobSheet"
I want to count how many times last month a specific field was entered, it's a drop down list which is formed from it's own table "Problem"
Is there a simple way of doing this?
View 3 Replies
View Related
Oct 9, 2014
I would like to count the number of times a value appears in a column using Ms Access
E.g.
Name Count
Mike 2
Paul 1
Mike 2
Peter 1
View 4 Replies
View Related
Jul 18, 2013
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
View 4 Replies
View Related
Sep 23, 2013
How to count the number of times that the data appears in a certain field (which is [Ema!l]). I have a list of ema!l addresses and I want to find out whether that ema!l address has appeared once, twice or more. I want to add this as a field in a query. I don't want to delete it or anything because it's perfectly fine for the ema!l address to appear more than once, I just want to be able to identify when the ema!l address has already appeared.
If I was using Excel I would use a Countif function to count how many times the data in the specified cell appears in the whole column, and that would give me the number. I'm not use to the language of Access so I can't figure out how to achieve this.
I tried adding a Total row to the grid in the query and then changing the total to 'count' but this just returned 1 for every row.
View 3 Replies
View Related
May 9, 2014
I have 2 tables...a lookup table with possible responses (i.e. yes, no, don't know) and the second with 8 fields that contain the values selected from the first table. Tables are related one-to-many. I need to count the number of times each of the responses shows from the lookup table shows in each of the fields in the main table. Have tried to query with the lookup field set to "group by" and each field set to "count".
View 2 Replies
View Related
Jun 14, 2013
I would like to update a txtbox with a simple int count of the number of times a mouse is left-clicked while on an active form. How do I do this?
View 5 Replies
View Related
Aug 20, 2013
I have lookup table I use to return names for various "Sales Class" codes.It all works good but if there is a code that isn't in the lookup table it leaves that field empty.I want it to return the word "Unknown" for any code that doesn't have a match.Here is the SQL:
Code:
SELECT [Data1].OrderNum, SalesClasses.[Name]
FROM [Data1]
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];
Table examples:
Data1:
OrderNum - Sales Class
111 - class1
222 - class2
333 - classX
[code]...
View 3 Replies
View Related
Jul 20, 2006
Is there a way while using the count query to make it return zero when using the criteria (not like "N") instead of a blank datasheet.
View 1 Replies
View Related
Apr 10, 2008
I have three queries that count non-compliant records in each of three catagories per month. One catagory has records for both Jan and Feb and returns the rersults as follows:
Jan 3
Feb 4
The other two vatagories however have no non-compliant records for January so they return as follows:
Feb 2
Feb 1
I wanted to tie the three queries together in the form for reporting:
Cat 1 Cat 2 Cat 3
Jan 3 0 0
Feb 4 2 1
Is there any way for the queries to return a zero for any month with no records rather than not returning the month at all? Thanks for any help.
View 2 Replies
View Related
Jul 26, 2005
Hi I need to count the number of words in each cell/field in my access database. Is there some standard function that does this thing or can i write some code to do it for me? thanks
View 1 Replies
View Related
Sep 20, 2005
Not sure if I worded my subject correctly. Here's my problem.. I've used query to combine 2 tables of data..
Here are my fields..
I've assignment_num field, project_num field, project_name field, emplyee_name field.
here are my data..
assignment_num field: 1001, 1002, 1003, 1004, 1005
project_num field: 20,20,15,16,15
employee_name field: tom peter, tom peter, tom peter, julie, sally
What I want is to display the employee name with 2 projects or more...
What i have done?
I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..
pls help.
View 13 Replies
View Related
Apr 11, 2013
How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.
I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below
Table A
Table B
Tag No
Tag No
2009
ZZZ-2030-DC
2010
ZZZ-2010-M9P
[code]....
They're both in MS Access.I am trying to match 2 tables - columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.What query is suitable to compare two tables.
View 8 Replies
View Related
Sep 16, 2014
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
View 3 Replies
View Related
Nov 26, 2007
Hi,
I'm creating a new database for an investment brokerage company. The company invests clients' funds for a specific length of time and pays out a return on their investment on a weekly basis for the duration of the investment.
This is my first real database so I'm gonna need all the help I can get. The company has some pretty huge requirements but right now I'm focusing on phase 1 which will be very simple.
Right now I'm working on the database design but there's been one thing which has been bugging me for a while so I figured I'd ask you guys to help me get my head around this.
Let's say we have a table called Investments. This table will include data on the amount invested, the number of weeks invested for, and the ROI percentage. For example...
Investment Record Example
Amount: £10,000
Weeks: 10 weeks
Percentage: 10%
One of the requirements of the database is to produce a report that will list each week of the cycle and the amount paid to the client. This will require calculations in order to produce this data. Here's an example...
Payment Report Example
Week # | Payment Date | Payment Amount | Notes
1 | 1/1/01 | £100 | Enjoy your first payment! :-)
I can see how this can be easily done by producing a query and then a printing a report based on it. However, here's the tricky bit... for various reasons, the payment date or the payment amount for a certain week may need to be modified, and a note value may need to be added for particular weeks.
Of course, I could just create another table called Payments to contain this information, but I don't want to have to enter the data manually. It would be a lot more productive to have the database calculate these values, but somehow let the user modify these values, if necessary. That's what I need to figure out!
Can someone advise me how this can be accomplished? I've been reading up on the Make Table feature, will that do the job? Note that I do NOT want to create a payments table for every client so I'm quite sure.
I look forward to reading your replies.
Thanks in advance.
Warmest,
Zahid
View 5 Replies
View Related
Feb 11, 2008
Can someone explian to me how the max function works!
View 5 Replies
View Related
Jun 30, 2006
This is probably a long shot, but I figured I'd post it anyways. We have a form that is used to input customer data. It was written about a year and a half ago and uses some programming in it. It's worked fine on the people's computers it was originally installed on, but recently we've tried to put it on some of the newer computers and when we run the form, it just freezes Access up instead of going to the form log-in screen. The weird thing is, it works on some of the new computers. They all have the same version of windows and the same version of Access, and the code in the form itself hasn't changed at all. I was thinking maybe the form was looking for something that wasn't there, and freezes because of that. Anyways, any ideas would be appreciated.
View 1 Replies
View Related
Aug 9, 2006
Situation: I have to seperate tables(table A and B), both have a part numbers field, manufacture field, and manufacture p/n fied. But only one of the tables(table B) has manufacture and manufacture p/n filled in. Also, some part numbers are missing from table A that need to be in B and some are missing from B that need to be in A.
Goal: My question is how do I make a query or whatever i need to tell access to find all the manufacture and manufacture p/n info(related to its designated part) in table B and put it into the fields in table A respectively. I guess i'm basically trying to join two fields in two different tables, i mean i need to the manufacture and manufacture p/n copied over to its related records in table A.
The reason for this is because we are migrating from Quickbooks to a program called Shoptech E2(access based) to do all our supply chain management. Things were correctly imported from QB except for the fact that the manufacture and manufacture p/n was not imported for some reason. Now E2 only looks at table A to pull its info and does not see the relationship, thats why i need manufacture and manufacture p/n copied over to table A to its respective parts.
View 9 Replies
View Related
Aug 14, 2014
My end goal is to populate a pre-existing table in an MS Word document with records from a query. The easiest way I've found (through scouring the internet) is to start with the code below (ran during OnClick() even in Access) to get the table the same size as the recordset:
Code:
Dim wDoc As Word.Document
Dim wTable As Word.Table
Dim wCell As Word.Cell
Set wDoc = appWord.Documents.Add(strDocLoc)
wDoc.Visible = True
[Code] ....
The code will shrink the table down just fine if the table has more rows than the recordset +1 (for header column). My hangup with this is the last line ("Selection.InsertRowsBelow 5") isn't executing; rows are not being added to the table. I get no errors -- it just does nothing. I set it as "Selection.InsertRowsBelow 5" arbitrarily just to see if it would even add rows, and sure enough it's not.
View 9 Replies
View Related
May 20, 2005
Hi
This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.
View 1 Replies
View Related
Oct 21, 2005
I have a query in which I need to display just the data that was entered from 9:00 p.m. the previous day until now. There is a date/time field called "QtyTime".
Thanks.
View 2 Replies
View Related
