Basically I want to create a query that shows a table like so:
Wages:
Contractor [Contractors.Contractor]
Jobs_done [counts the number of rows from Job that has the Contractor_ID equal to the Contractor_ID specific to Contractor (above)]
Eg.
Job:
Contractor_ID
1
2
3
2
1
4
Contractors:
Contractor_ID Contractor
1 Bob
2 Bill
3 Jim
4 Jill
Wages:
Contractor Jobs_done
Bob 2
Bill 2
Jim 1
Jill 1
I have a form that records followup activities for protocols. The form allows the user to enter up to six f/u actions from a dropdown menu in six separate fields (called fu1_action, fu2_action, etc).
I am trying to write a query that retrieves only protocols that only have one f/u action selected as "telephone correspondence" and am having trouble. It seems I could write a function in VBA that would somehow loop through the 6 f/u action fields and give a value of 1 to those that have 'telephone correspondence' as a response, and then add them up to give a total of calls for that specific protocol, and then in a separate query only retrieve those that have a total number of calls = 1, but how to get started in writing this.
I am trying to create a clean database and code to generate a report.
I am trying to count the number of null fields in one of my queries:
However, because of this expression, I cannot carry other fields with it. So the end result looks like:
But I would really like it to spit out the following information:
Total Not Fixed: 241 Department: Sustaining Eng
is there a way to create an SQL query to simply add data: I have tried the following:
Code:
ALTER TABLE qrySustainingEngNotFixed2 ADD Dept TEXT(25) Insert Into qrySustainingEngNotFixed2 (Dept) Values (Sustaining Eng) SELECT TotalNotFixed, Dept FROM qrySustainingEngNotFixed2;
The above isn't working. Keep in mind that I want this is just for display purposes. I pondered making a custom table and then making a Union Query, but I'm trying to do this all in one SQL statement.
I would like to know, how do i reset the count of my auto number fields after testing? is it also possible to specify which number the auto number should start counting from?
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
I have a db where a planner inputs a commit date. The problem lately, is that the commit changes day to day before the part if filled. Management is looking for a way to count the number of times a commit field has been updated? Is there a better way to do this? I have minimal VB knowledge. Please help!
how to get a specific task done. My table has automatic ID in one column which is unique in each data entry row. There is another column which has "Batch Id" which might be repeated in some of the rows but most of the time it is unique value. I would like to get get an updated count of this "Batch id" from start to the point of entry of new entry to understand how many total batch ids entered to that point. I am open to do this in table if possible of by creating new query.
I have created a database which allows people to enter in when they are off sick. They have to enter in the FIRST DAY of sickness and the LAST DAY of sickness. All of this information is stored in one big table - SICKNESS TABLE. There are no other date fields in that table.
I want to know how many people have been off for 5 TIMES OR MORE in a 365 day period (which may be say, from Oct 2005 to Nov 2006 - so not always in the same year).
I have managed to get a query that will count the number of times someone has been off; but that could be over, for example, 2 years.
How can I get it to tell me if someone has been off for 5 TIMES OR MORE in a 365 day period???
I need to do a count of the total number of rows in a table or query. Say I have a table with 7 records, I want to be able to get total number of rows instead of the sum of the row and save it to some other table.
I just can't seem to get this one to work right. I've got the following query. I need to count the number of Null dates or show zero if there are no Null Dates.
Code: SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, Count(qryNoticeResponseNew.[fldResponseSeen]) AS fldCount FROM qryNoticeResponseNew GROUP BY qryNoticeResponseNew.fldNoticeID;
Which is just counting the number of dates so far. It got me to thinking I need to do something like this.
Code: SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0) AS fldCount FROM qryNoticeResponseNew GROUP BY qryNoticeResponseNew.fldNoticeID;
Which pops a "cannot have aggregate function in expression" error.
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 stock record database which I have 'inherited' from someone far cleverer than me! I'm fairly used to basic SQL, but I'm teating by hair out over this particular problem.
I'm writing an Update Query to count the number of [NewModelCode] records WHERE Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes and update a field called CountNew on a table called InstStockReference.. So far I have the following SQL:
UPDATE InstrumentStock INNER JOIN InstStockReference ON InstrumentStock.NewModelCode = InstStockReference.NewModelCode SET InstStockReference.CountNew = DCount("NewModelCode","InstrumentStock","Status='Available'") WHERE (((InstrumentStock.Status)="Available") AND ((InstrumentStock.StockType)="New") AND ((InstStockReference.OnlineShop)=Yes));
It partly works, but the result I get is a column count of 939 in every field where the Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes.
I want to do a count the number of [NewModelCode] records.
I am wanting to display in a text box or on my report the total number or records in my database. Also I have some buttons that filter the results a little, I'm wanting to display the number after I hit the button(s). Would I just add the query to the end of each button I have?
I need to write a code that checks if every 6 lines in a table contains a string. If it contains the string, then carry on looping, but if it does not, it will delete the current line and the previous 6 lines before it. The program should then continue looping through the entire table.
For example,
Code: Apple a b c d e Apple f g h i j Apple l m n o p q Apple . . .
I need to find the string "apple" in every 6 lines. So, that means that the first 2 Apples are fine, but for the third one needs to be deleted as it contains 7 lines instead of 6.
I have a main form [frmZone] and a sub form (single form) [fromZoneSub] linked master/child by [ZoneID]
If there are, say, 5 related sub form records I'm trying to get a label [LabelCount] on the sub form to say "1 of 5" and as you click through the sub form for the label to change "2 of 5", then "3 of 5" etc....basically letting the user know how many records there are and as they click to the next record know which record they're on.
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
I have loads of projects in a table, they all have a status (eg Red, Green, Amber).
I have a query for each so if you run qry_RagRed, it will show in a sheet all Red projects and obviously have a total in there.
So I have a form which has buttons on it for navigation, what I want is to display various things on there so for example, a field that simply has the number of red projects, or green etc.
All I want it to do is tell me a number based upon number of items in a query.
I have a memo field that feeds to a textbox control on one of my forms. I would like to re-format the textbox using the .Height value based on how many lines appear in it (i.e. it will fit only 3 lines if there are 3, but 5 lines if there are 5, etc.).
Is there any way to count the number of lines that appear in the textbox?
I have a form that could have duplicate material descriptions , is it possible to have on that form a field that counts the number of line items for that material and when the material changes the new number will appear .
