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.
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.
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.
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.
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.
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".
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];
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.
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
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..
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.
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.
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.
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.
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.
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.
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.
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".
