hi
i am trying to show the total number of records that are in a table.
there are currently 8 entries in the table 'applications'
now i would like to show this on a tabcontrol in a form. I have been told to use Dcount. I have read about this and tried this on my form but unfortunately its not working.
Can anyone show me an example of how i would do this.
Hey guys.
Seems simple enough, I just can't figure out how to do it. How can I display the total number of table entries in a textbox on a form? thanks!
I have 3 tables;
tblEmployees - Stores employee data
tblProcess - Stores process data
tblTraining - Stores data on which employees have trained on which process
tblEmployees has a 1 to many relationship to tblTraining through empID
tblProcess has a 1 to many relationship to tblTraining through pcsID
When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.
In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!
So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.
I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).
I have table with different companies and related info. Another table with contacts for each of the companies, each company might have different number of contacts. I want the form to show all the contacts for each of the company, if there are two contacts in the company then show two, if five then show five.
Just to make sure I am doing it the right way, what is the best way to build a form that is based on multiple tables.
Thanks in advance,
John
Access 2010 ... I have 2 tables. One with base information second one is linked with multiple results each having a price. On table one i see the + sign when i click i can see the linked second table. Can i get a total amount of the price on table 2 on table one?
IE:
Table 1:
Trans ID - Seller - Quantity - Lot Cost - Parts Cost<-- the one i need total for.
123 - joe - 3 - $100 - $20 <-- the total of the 2 linked parts (Keyboard, Mouse)
Table 2:
ID - Trans ID - Part - Cost
1 - 123 - Keyboard - $10
2 - 123 - Mouse - $10
I created a form for data entry only where records could be continually added until you were done and closed the form. Into that form I dropped a query that kept a running list in datasheet form of all the new records entered while the form was open, and then cleared when the form was closed (so the query was empty when the form was opened again).
My Problem: I accidentally deleted that query.
What I need:
-Query to be empty when form is opened.
-Records added while form is open get displayed in the query after record is saved.
Hi ALl,
I have a subform that accounts and adds all the expenses for a certain project (for example, total is $27,000). I would like to know how to duplicate the final cost in the subform and show it on an unbound textbox of that linked main form (the unbound textbox in the main form shows $27,000 also).
Thanks in advance, guys...
Caliboi
I have a form containing 4 text Boxes txt1, txt2, txt3 and txt4
I want
txt1 show records from table empDetail having age between 18 to 25
txt2 show records from table empDetail having age between 26 to 35
txt3 show records from table empDetail having age between 36 to 45
txt4 show records from table empDetail having age between 46 to 55
I am trying to build a form that can show the total from the amount that the report generate.
View 3 Replies View RelatedIs it possible to convert fields listed down to show across.
IDNo Code Position
AAA B01 1
AAA C01 2
AAA D01 3
AAA E01 4
I want to show it as
IDNo Code Code Code Code
AAA B01 C01 D01 E01
Is this possible using Access 2003?
How can I sum the amounts in the list box column and show the total on the main form?
View 2 Replies View RelatedI have a simple table named [Groups] containing a Primary key ID, a Group name, a Group Leaders name and a date field. The date field merely lists the date of a particular Group related occurrence.
I need to know how many records have dates entered and assumed a simple total would achieve this. The Total is correctly shown in the appropriate row but most, but not all, of the date records are converted to show the same 'total' number.eg the date 28/01/2014 is changed to !4. Quite a weird occurrence.
Is it the case that date records cannot be totaled, in which case, why not all?
Hi all
I'd be very thankful if someone can please help me with this problem.
I have 1 Table. This Table is imported and contains data from Excel. It also contains fields I added manually. There is about 20 imported fields (full of data) and about 100 manually added fields.
Then I made a Query of this table. I made some calculations in this Query.
I then created a Form based on the Query. I then made a Combo Box. This Combo Box selects a customer, and then updates the other fields (the ones I imported from Excel) depending on the customer I selected.
So now what I want to do is this: I want the fields (updated from the query) and newly entered data to save to another newly-created table. Can someone please help me with this? Any ideas?
Thanks a lot. All input greatly appreciated.
kruger101
I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.
e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.
Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?
Below is the my VBA code so far, which produces the above result.
SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date
FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn
WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date]))
ORDER BY tblEmpSalaryHistory.next_review_date;
My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.
I am trying to create a query which show me which of my customers in my database does not also contact a contact to go with it.
I have two tables; Customers and Contacts.
When I create a query I add the fields Company Name from the Customers Table and First Name from the Contacts Table. There are obviously other fields in both tables.
However when I run the query it does not not show me all the results. It only shows me customers if something has been entered into any of the fields in the contacts table for that Customer. I suppose this triggers a contact ID entry for that customer even if no Name has been entered.
However, how do I run a query to show me customer entries which have no contacts to go with them because no contact entry has been made for that customer?
I'll start by saying that I'm not sure which sub-forum to choose, as my question(s) covers several areas, so I have tried with this one first.
I'm currently making a database, which among other things contains a 'Port of Call list' table. I have a form, where I can add data to this table, meaning, that whenever a vessel has left its port, user can update following: Port name, security level, arrival date and departure date.
What I need is two things (it might be more or less the same):
1. When user click a "Generate" button on a form, the 10 latest port calls from the table, should be shown. I'm not sure what kind of control is the best to should the list on the form. Code is also needed.
2. I would also need to be able to print the list. It should be part of a report. Again which controls/which code to be used is needed.
I hope that someone are able to assist, as I have no clue what to do.
Thanks.
Hi,
In the current db there are some tables and queries, forms designed around them
With one table Test
http://members.optusnet.com.au/~lukechang/access/table_d.JPG
That has 3 records in them
http://members.optusnet.com.au/~lukechang/access/table.JPG
Just won't show in form anymore, at this stage
http://members.optusnet.com.au/~lukechang/access/form.JPG
The Form was working previously. This is the second time this problem has came up, I thought it would go away if I made a new form and pasted all the controls and codes. It indeed went away for a while until this problem hit me again...
This db is in Access 2000 format and designed using Access 2003
I would appreciate if anyone is willing to point me to the right directions, many many thanks in advance
I have a login form. 2 spots for users to input and ID and their name. I want to do error checking to see the user inputs a wrong name or wrong id with their corresponding name or ID. For example, I don't want User A to be able to login with User B's name. If User A inputs their ID and inputs User B's name, I want to show an error message stating the their is a mismatch of credentials. Here is my code
Private Sub Command12_Click()
Dim rs As DAO.Recordset
Dim txtID As Variant
txtID = Forms![LoginForm2]![txtEmployeeID]
txtName = Forms![LoginForm2]![Text13]
Set rs = CurrentDb.OpenRecordset("SELECT * FROM CoachTable WHERE EmployeeID = '" & txtID & "'", dbOpenDynaset)
[Code] .....
I have a form (called User_Input)with 2 text boxes, 5 combo boxes and a Submit button, which is bound to a table (called Submssion table) i.e the form submits its entries into the table called Submission.
I want the submit button on the form to work based on this condition:
1. Check current submission of four fields (corresponding to entries from 4 out of the 5 combo boxes on the form) in the table and confirm if the current selection on the form already occurs in the table up to two times. If this is the case, the Message Box should pop up on the form informing user to make another selection as maximum number of entries for that particular selection is already in the Submission table.
2. The checks for the four fields will be done simultaneously as the selection will be done together on the same form.
3. However, if the entries on the Submission table is not up to 2, the entries should be saved to the table and Messagebox should display "Thank you for your Submission"
I need to use Macros for this being a web database which will be published using Sharepoint and VB is incompatible with the web database.
Hi All.
I created form with unbound listboxs based on query which included Credit field that I want to calculate total and show result in caption of window. If is it possible how to do it?
Thanks.
I am trying to build a query to show the total sum of hours a staff member has done in a certain department and the amount of sleep ins they have done. Each staff member has a unique ID. In the query I can get all the information but it shows on two lines dependent on department. I want this to show on a single line. Each employee will never have more than two departments. The query is called "QryForForum".
View 2 Replies View RelatedI have a query that just shows all the records in a table. It is used by the end user for filtering primarily. Now the user would like to see a total for the amount filtered.
For example; the table is for repairs. The query just shows ALL the repairs. The user filters the client field to find all repairs for one client. He then wants to see what the total charges are for that query.
I cant create a new field and sum the records because it is not a totals query. Is there any way to embed the query in a form and use the form portion to sum the filtered results?
I have quite an extensive form linked to a table. When I add new columns to the table I seem to have a problem getting the form to read the data.I have just added a numerical column to the table and added a text box on the form that is bound to it. When I try to pull up the value using VBA it is blank, even though an entry is visible on the form. I have set the text box to general number and the entry shows in the actual table. When typing in the entry into VBA it capitalises where required so it must be registering the table entry. However the value it pulls remains blank.
View 2 Replies View RelatedI'm fairly new to access and have been tasked with fixing a database that to my knowledge is built incorrectly but due to budget constraints and the time it would take to build a new one, we have to stick with this one. Here's my problem, in the database is a form that we use to create new entries in a table but when I try to create a new entry through the form, it creates multiple entries in the table with the rest of the data split between all the new table entries. Is this something that is caused by us moving over to the most current version of Access? Is there a way to fix this problem given the software we are using? At one point I had recreated this table because we had been having issues with other aspects of the database (security deposits were not being updated when entered through this form).
View 14 Replies View RelatedI have setup a simple access database consisting of 3 tables
Volunteer
Id (PK)
Name
Job
Id (PK)
JobName
Link
Id (PK)
VolunteerId (FK)
JobId (FK)
There is a one to many relationship between Volunteer and Link and a many to one between Link and Job
I want to create a form from which I'll select a Volunteer Name and then choose a JobName from a list box of options and have it create an entry in link with the id of the volunteer selected and the id of the job selected
So let's say there are 5 entries currently in the Job Table.. let's call the JobNames A,B,C,D and E.
In my form I select my name from the list (sourced from volunteer.Name) and then select B from the dropdown. My desired result would be a single entry gets created in Link with VolunteerId = 1 (representing me) and JobId = 2 representing the selection of B. Problem that I am having is that after I select B in the dropdown it creates a new record for B in Job and the Link table gets updated with that JobId as opposed to simply using the one that was already in the table.
I have a continuous subform. Because the records are often many, the user needs to scroll down the page.
How do I have a text box in each row that shows Record x of Total Records?
I tried setting a text box control source = [currentrecord] in the Detail area of the subform but it didn't work . It did weird things because I suspect the control is trying to show the record that has the focus or something