Can't Modify/add Records Because Of Record Source With COUNT Function!!!!
Jun 19, 2006
I modified a form and now it seems it's impossible to modify/add records. It seems that all comes from when I modified my record source query to add a a field in which it COUNTs all related records in another table. Now the form can't modify anything else but I need to change other fields or add new records!
In other words: how can I add a simple box which, for every record of my table/form, counts the number of related records in another table (relationship is 1-to-many, and of course is based on a field in table2) letting me modify and add records?
Thank you
View Replies
ADVERTISEMENT
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
Aug 10, 2015
I have the following code
SELECT Count(T_STUDENTS.studentsID) AS res1yes, T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup
FROM T_STUDENTS, T_COACHING
WHERE (((T_STUDENTS.res_vrijstelling)=False) And ((T_STUDENTS.stud_year)="1") And ((T_STUDENTS.stud_coach)=forms!F_cboCOACHING!cboCOACHING))
GROUP BY T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup;
It runs fine, untill the moment the count is 0. At that point it breaks the form. How can i tell access to deal with this? Somehow i need to replace the count result with 0 if there are no records that meet the criteria.
View 8 Replies
View Related
Oct 12, 2006
Hi,
I am trying to create a textbox that will show a document number of the last record so when a user clicks add new document he/she knows what document number to enter in next. It must not involve the user looking a subform with the document numbers on it.
Thanks for your time,
Leon
View 2 Replies
View Related
Mar 29, 2014
In my database I have a form who's record source is a query. That query is based on a table, and have set a criteria on one of the fields.
When I use the form to add a new record I want the value of the field in question to automatically be what I have specified in the query.
Instead, the record is added but without having that value in the field, and when I go back to the form it doesn't display the record because it doesn't meet the criteria specified in the query that the form is based on.
View 2 Replies
View Related
Jun 21, 2007
Hello,
I have a form (linked to a table). I have 'txtBoxA' and 'txtBoxB'. Now, each record will have different values in 'txtBoxA' and based on that value 'txtBoxB' will change for that specific record. In the 'OnLoad' event for the form I type the code, but when I re-open the form it only works for the 1st record, and it doesn't change all the rest (records).
Do I need a loop to go through all the records?? I thought that this was done automatically by ACCESS. If I have to use a loop, can anyone please give me the exact syntax?
Please, this is really urgent so I would really appreciate an answer asap
Thank you very much.
View 2 Replies
View Related
Jul 18, 2013
I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database.
Code:
Private Sub Form_AfterUpdate()
Dim dbs As DAO.Database
Dim rstTest As DAO.Recordse
Dim strQuery As Strin
strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining
[code]....
View 12 Replies
View Related
Feb 5, 2008
Hello All,
I have three tables: Employees, Gender, Diversity. Both the Gender and Diversity tables are one to many relationships with the Employees Table. I am trying to run a query that will output a count of all diversities and genders. For example:
Diversity-----Gender-----Employee Count
White----------Male---------------1
White----------Female------------5
Asian----------Male---------------3
Asian----------Female----------<Blank>
Hispanic-------Male-------------<Blank>
Hispanic-------Female----------<Blank>
I am trying to get all counts, even if the combination of diversity/gender is not in the employees table. I am going to use that information in a Crosstab query.
What I thought would work was do a Left Join For Diversity and Employee such as:
SELECT Diversity.[Diversity Description], Count(Employee.ID) AS CountOfID FROM Diversity LEFT JOIN Employee ON Diversity.ID = Employee.Diversity GROUP BY Diversity.[Diversity Description];
Then, do a Left Join for Gender and Employee such as:
SELECT Gender.[Gender Description], Count(Employee.ID) AS CountOfID
FROM Gender LEFT JOIN Employee ON Gender.[Gender ID] = Employee.Gender GROUP BY Gender.[Gender Description];
And then do a Union. But that doesn't work.
Any thoughts or comments would be much appreciated!
Thanks,
Jon
View 1 Replies
View Related
Sep 19, 2005
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
View 14 Replies
View Related
Feb 21, 2006
Is it possible to run a query/filter in order to hide/show a field in a record instead of it affecting the entire record.
What I'm trying to do is, in a form the user can enter information about a service call and they can enter a recommendation for the next visit. When they enter a recommendation they are able to select which month the recommendatin should pop up. This Service report get's printed on a monthly basis as a report. Now the recommendation field should only pop up if that particular month which was selected earlier is true. I've tried different methods, but it would affect the entire record as opposed to just one field.
Thanks.
View 1 Replies
View Related
Jul 2, 2014
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
View 8 Replies
View Related
Apr 13, 2006
I have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
View 3 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Jul 6, 2006
Hi there,
I have a field called Unit in my table. That field references another table which has about 5 entries. The user can select one on the form.
What I want to do is count how many times each one has been selected so that i can create a chart. The unit names are: North, West, East, South and Main.
Any help would be appreciated.
Thanks
View 6 Replies
View Related
Aug 15, 2006
i have a table which contains the attendance records for employees. It shows their supervisor their name aemp number and which kind of hours they worked basic, overtime, Been late and absent is all recorded here.
SupervisorEmp NoSurnameForenameCodeValue
JENNIFER BUCKLEY200McCormackBridgetOT14
JENNIFER BUCKLEY200McCormackBridgetOT21
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS5
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS5
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetLTAM0.05
JENNIFER BUCKLEY200McCormackBridgetOT14
JENNIFER BUCKLEY200McCormackBridgetOT21
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS4
this bben a sample from the table. I want to be able to have the employee name, NUMBER AND SUPERVISOR there just once with the appropiate heading for what type of CODe they have BAS, OT1 as a heading with a count of the number of times they done that particular code
Where do i begin any help
View 1 Replies
View Related
Aug 29, 2006
Hi
I have a table where is listed all happened faults from a month period. Every fault have start time and end time field and ip field which tells what device has been "broken". Now I'am trying to calculate how many faults has happened in every day. I have make a query from the table which looks like this:
"SELECT (DateValue(Table.[start time])) AS date, Count(Table.[IP adress]) AS [faults happened]
FROM Table
GROUP BY Table.[start time], Table.[IP adress];"
And I get this:
date faults happened
12.8 2
12.8 1
12.8 1
13.8 3
..
But how I can make my query count together how many faults has happened in every day? So it should look like this
date faults happened
12.8 4
13.8 3
..
View 1 Replies
View Related
Jun 13, 2007
Hi guys
I'm trying to create a query that will count records from 2 different tables that are both related to a 3rd master table. e.g. table 1 is the master table and contains records of employees, this then has 2 child tables: customers and products. I want to count the number of customers and the number of products that belong to that employee in the same query so that i can then produce a report without the hassle of sub reports etc. any advice on this would be great
Cheers
View 5 Replies
View Related
Jun 15, 2007
Hello!
i am trying to sum the total of ID's by corporate titles in query. In the totals row i have selected 'count' in the ID column and 'sum' in the title column but this doesn't work. I tried the reverse and it also was no good :confused:
Can anyone help - i'm sure its something simple
thanks
View 7 Replies
View Related
Jul 5, 2005
I wonder if someone could help me with the count function,being as I've never used it. I have two forms, one with a number box called:
Form1![CR2]
The other is called:
Form2![[TotalCR2]
I want to add all the records from Form1![CR2] and put them into
Form2![TotalCR2]
I know its going to be simple and I'm pretty sure I should be using DCount or DSum but I am unable to access the help files on my PC at work for some reason. Thanks!
View 5 Replies
View Related
Jun 15, 2006
Can anyone help? I'm running a report that shows conference delegates names and the various workshop options they are choosing for an event. What I am trying to do is work out if there is a way to count the number of people who will be in each workshop. I have set up the database so that when someone registers for a workshop, I show this through a Yes/No box (ie, put a tick in the box). So my end report looks something like this:
Name Workshop 1 Workshop 2 Workshop 3 Workshop 4
Person 1 [tick] [ ] [ ] [tick]
Person 2 [ ] [tick] [ ] [tick] ….and so on
I have managed to count each column by the following control source: =count([workshop 1]) ; =count([workshop 2]) etc. The problem is the number it gives me is the TOTAL number, not just the number of ‘yes’s’ or ticks.
Is there something I can add to my statement to get the formula to just count the ticks? I tried: =count([workshop 2]=YES (and TRUE), but it still counted the total boxes/lines. Basically I don’t want to include the ‘no' boxes in my count.
ANY HELP anyone can give would be HUGELY appreciated. Thanks :)
View 1 Replies
View Related
Aug 16, 2015
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
View 9 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
Mar 17, 2008
HELP PLEASE!!! There are two fields in my form that need the following relationship:
1. When I enter 1000 in my "spec no" field in my form, I need my "Number Field" to enter a "1"
2. When I enter 1000 in my "spec no" field in the next record, I need the "Number Field" for this record to enter "2" but "1" to remain on the first record.
3. If I enter 1001 in my "spec no" field in the next record, I need the "Number Field" to enter "1"
4. Continue this logic for all records without modifying the previous "Number Field" record.
I was told there was a way to accomplish this using DCount or Count function. Maybe there's an easier way??? Any help is greatly appreciated!! TX!
View 1 Replies
View Related
Aug 31, 2005
I have designed an attendance database, with fields for personal details and fields for the days of each month.
I need to have a query that will ask for a certain date(i.e. find the specific field) and then search on specific criteria (i.e. ON SITE) to see which staff are available.
What is the best way of doing this? Any help would be greatly appreciated
View 4 Replies
View Related
May 26, 2006
Greetings!
I'm trying to make an Attendance Report for my students. I want the report to show each student and how many times they've been "Present", "Late" or "Tardy" in a month and in one year.
I've set up my db with two tables.
Table 1 - contains Student ID and Student Names
Table 2 - contains Attendance ID, course id, student id and the Status ("Present", "Late" or "Absent")
I used the sum queries and it doesn't do the count function properly. This is a copy of my query.
Thanks :o
Surfette
View 4 Replies
View Related
Mar 13, 2007
Hi
I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.
The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty
FROM [tbl_Audited Classroom for Week 02]
HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));
So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.
Can you please help?
thanks
View 5 Replies
View Related
