Displaying Records And The Count
Jul 17, 2012
I have a continuous form (frmEmployeeVis) that displays the records in the form's Record Source (SELECT (whatever) FROM dbo_EmployeeData). One of these fields, NotActive, can be edited for each record and affects how that record displays elsewhere in my DB.
I have a query (qCurrentHC) that counts all of the records in dbo_Employee data where NotActive equals False (SELECT Count(*) FROM dbo_Employee Data WHERE (dbo_EmployeeData.NotActive="False")
Right now, I have a List Box that displays the result of the qCurrentHC query, where the row source simply selects the only value
It all works fine. The records display, the query runs, and the list box displays the number. However, if I change any of the NotActive values and hit update (Me.Requery on the frmEmployeeVis), the qCurrentHC query obviously doesn't update since it isn't the form's record source. If I open any of my other forms with qCurrentHC displayed in this manner, or reopen this form it will update since it'll rerun the query then.
How do I get the query to rerun when I update this field on that form?
View Replies
ADVERTISEMENT
Jul 31, 2006
Hi Folks
More questions from the newbie :eek:
I have a very simple table with a field that will have only the values open or closed
I created a query based on this table and used the count function (to count open or closed cases) and in the next querie field I used the group by open.
This gives me a count of all open cases
I can do the same for closed cases.
I want this count to be displayed on a Switchboard form but i cant get my nehad round how to get it there.
Can anyone help me out ?
Many thanks
Jimmy
View 6 Replies
View Related
Mar 10, 2014
I have a form, which is bound to a table, with an unbound textbox.
I am trying to get the number of months an employee has/had worked.
In the table are two dates, Company Start Date, and Resignation Date.
There are three scenarios in which I need to calculate the months:No Resignation Date
A Resignation Date in the future
A Resignation Date in the Past
See attached PDF
The Employees are under a one-year contract which ends the day before the one-year anniversary.
I originally tried using DateDiff in the Control Source, but the IIF seamed too limited for my needs.
Code:
If [Resignation Date] = "" Then
If Month([Nichii Gakkan Start]) = Month(Date) Then
If Day([Nichii Gakkan Start]) < Day(Date) Then
MIS = DateDiff("m", [Nichii Gakkan Start], Date) - 1
[Code] ....
MIS = The name of the textbox in question.
View 6 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
Feb 21, 2005
I have 500 Records in my Software Table, and I would like to display only those records that match my criteria that I place in my text box on my form called Software Name. So Once I have entered the software type in my text box, then I would like to display those records at the bottom of the screen as the selected records.... Any ideas??My table is called, Software, which contains Software Type, Software Name, Serial Id, Comments, Inventory Number, Version , Key and Company... I have a text box that is called txtSoftware, Which searches the databse and display the information in a list box called, lstResult, I would like to however, have the option, display in the list box but also display those records on the bottom of my form as it will look like in design view of my table. I have no idea how to ever start the idea ....
View 1 Replies
View Related
Nov 2, 2006
This is probably a very basic question, but I'm going to ask it anyway.
I have two tables linked together, in a one to many relationship (a supervisor table, and an employee table). Each supervisor can have many employees that report to them. I then set up a forum to enter new employees as they come into the corporation, and need to referance which supervisor they report to, I have a drop down box that I want to display SupervisorLastName, SupervisorFirstName (for example, Williams, George) but is only displaying the SupervisorID (for example 60).
How can I make the forum display Williams, George instead of 60?
Thanks in advance for your help.
View 2 Replies
View Related
May 14, 2006
I'm trying to get a query to display only the records that have a null value in a certain field.
I know this has something to do with the Nz function, but I can't figure it out.
View 2 Replies
View Related
Sep 5, 2006
Hi All
I have 28 records in my table - when I run the query (made by using the wizard) it only picks up 6 records.
I have no criteria in the query - just a straight query fulling all fields from the table.
Any suggestions?
View 7 Replies
View Related
Nov 7, 2007
I am attempting to run a query with fields from 6 tables. These tables have been related, but just a simple ID to ID throughout all of the tables. When I select the fields I would like to be in query using the disign wizard it will allow me to create the query, however it will not display and data.
Originally, the query wizard would not allow me to insert all of the different tables fields in the query because "they were not related", so I went back and did the ID to ID relationships. Like I said this now allows me to create the query, but unfortunately no data appears.
Anyone have any suggestions please??
View 9 Replies
View Related
Jan 14, 2008
Hi all, this is my first post here and im hoping one of you can help me! :).
Basically I have a report which pulls up a number of fields from different tables that basically show a work number and what the value of the materials used in the job cost. It works fine. Now when I go to add the labour table into the query, it instantly cuts out alot of the records. Now im 'assuming' this is due to the fact that not every job could have labour spent on it, that it is simply not showing records that do not have any labour hours associated with it. Therefore I want to be able to call up all the information as I did previously, but be able to show the hours spent on a job (if any). This is not my database and Ive spent a good day or so staring at it and trying multiple things but I cant seem to pull up the information correctly. There is a relationship between the tables, but I am puzzled why the information it pulls up when I add the labour table is so different.
Thanks for any help.
View 4 Replies
View Related
Dec 20, 2007
Hi,
This is not a very big issue, but a very annoying one. I'm making a weekly display of tasks preformed (One subform for each day). The functionality is almost like a timesheet. You register the hours worked on spesific tasks/projects.
The problem I got now is that the textbox containing the date (In the subform) is not displayed when there is no records in the subform. The date is set by the motherform - Me.Form!frmday1.Form.VarDate = Me.day1
View 7 Replies
View Related
Mar 23, 2006
I'm trying to get my query to display only the minimum value within my query results. I have the following fields, CustomerID, OrderID, ProductID, WarehouseID, Freight Cost.
My current query will return results that show me the locations and freight cost of each product to my customers. My intention is for the query to only return records that has the lowest freight cost as there may be several warehouses with identical products but with varying freight costs due to location to customer.
I've tried to apply the MIN function on Freight Cost but it will only sort the records in ascending order instead of only displaying the record with lowest frieght cost.
Thanks in advance for your help.
View 1 Replies
View Related
Mar 16, 2008
Hi guys
I'm a little stuck on three of my nine queries. Bascially, three of them display records for a given day, three for a given week and three for a given month. The criteria for days and months was pretty easy; I just got:
[Enter Date] for a single date
DatePart("m", [Date]) = [Month] for a given month
However, I'm really stumped on how to work out the criteria needed for working out records for a given week. All I managed to work out was:
<=[Week Ending] And >"Date[Week Ending]-7"
This works when there is no test data, but not when there is. I know it's something to do with the -7 bit, but I can't work out what.
Please help :(
Thanks
View 1 Replies
View Related
Aug 3, 2005
At the moment I have a field 'Author' which is displayed using a subform. The user can cycle through the many authors that are associated with each record in the main form. However, what I'd like to do is to display each record simultaneously in the same control, and with a little bit of playing around in VB get the form to work out how many Authors there are and display the names in a particular format. I.e.,
Bloggs, Jones & Smith
Hank & Mason
See what I mean? Does anyone know how I might refernce the records. So logically it would be:
lblAuthor = Record1 + "," + Record2 + " & " + Record3.
If that makes sense...Sorry if this is in the forums somewhere...
View 1 Replies
View Related
Jun 8, 2006
Hi
Any ideas on how to autofill the rest of a form when i select from a combo box?
ie when i choose a name from the list i want the rest of the form to populate with the rest of the data relevant to the selection.
Cheers
View 2 Replies
View Related
Nov 16, 2004
Dear All:
I am curious to see if threre is a way to display the number of records on a form in a text box. I know access has this number on the bottom of the form, but it's a vision issue for me.
Any ideas on how to get this done?
I am most appreciative of all the help.
Regards,
Dion
View 2 Replies
View Related
May 17, 2005
Anyone know how to display multiple records at the same time on a form?
View 1 Replies
View Related
Nov 29, 2013
If you have a one - many relationship between data, normally in the table it is captured, you would find multiple rows for every 'thing', each row detailing different 'functions' performed on it.
Is there a way that one can display these functions horizontally, instead of vertically, i.e, have one row per 'thing', instead of multiple rows.
View 3 Replies
View Related
Aug 20, 2013
I have a form where users can look through different records as well as add a new record through a control button. When a new record is added, all the relevant data should end up in the corresponding back end tables.
The problem I am having is that some of the new records I am adding work perfectly (sync with the tables, etc). Other records, however, are showing up in the tables but not in the actual form. For example, when I attempt to search in the form for the record name, nothing comes up.
The Datasheet View is set to No, so it's definitely not that. I'm not really sure where else to look - the new records appear in tables and the report I have linked to the form, they just don't all show up in the actual form!
View 3 Replies
View Related
Jan 17, 2015
I want to run a query to show all my outstanding orders between two dates. I've done all this correctly and it works fine. However I only want the query to show me the records where the checkbox on 'tblOrders' is unchecked. For obvious reasons, I don't need to see these records, just that records that have been checked, therefore are outstanding.
View 5 Replies
View Related
Dec 10, 2007
I have two tables, and I made a union query (tbl1 UNION ALL SELECT ...tbl2). Problem is, I have two entries that are identical in both tbl1 and tbl2, but I want to include BOTH of them in the union query. Even though I used the ALL operator, only one set shows up!! I'm pulling out my hair trying to figure this out. Please help, and thanks so much in advance!:(
View 2 Replies
View Related
Nov 21, 2013
I have a query that carries all the properties in our database, and data for when our company 'worked on' properties. Each property falls within a certain province and municipality (this is slightly irrelevant info) , we worked on properties in a period from 2009 - 2013/06/30 this period is called '2009', and we have again worked on properties during a period 2013/07/01 onward - this period is called '2013'.
Now I require this query to display all the properties, but only the entries in certain fields, that relate to '2013'.
I have about 7 fields where this date (either 2009 or 2013) can be displayed, I tried to filter them all simultaneously by using the criteria: Is Null Or "2013", in every one of these fields, but this criteria results in me losing entire records that contained 2009 data, instead of the data simply not being displayed. Even in cases where some of the 'date' fields contained 2013.
How I can keep all records but simply display info relating to work we did in the 2013 period?
View 8 Replies
View Related
Jun 25, 2013
I am on code want to display records on the form by order_number, but i am unable to do it.
Private Sub Combo63_AfterUpdate()
Dim rs As DAO.Recordset
Dim intcount As Integer
On Error GoTo ErrorHandler
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Master_Log WHERE Order_number = " & Combo63.Value & "", dbOpenSnapshot)
[Code] ....
View 6 Replies
View Related
Jan 22, 2015
I have a main form which displays data regarding stock takes for a number of locations. The form is bound to a query which is based on a table containing a STOCKTAKE_ID field which is an autonumber field. The STOCKTAKE_ID is present on the main form
I then have a subform which is linked to the mainform via STOCKTAKE_ID.
When I open the form for a particular location the subform displays data, no problem. When I do the same for other locations the form is blank. If I check the table that the subform is based on, there is a record with correct STOCKTAKE_ID but for some reason the form won't display it. There are also no duplicates.
View 2 Replies
View Related
May 6, 2013
I am currently creating a DB for a Customer Service team so management can keep track of who is skilled in what area, sick days, holidays etc. I have created three tables so far as follows:
EmployeeT with a primary key 'EmployeeID'
SickT with a primary key of 'SickID' and a foreign key of 'EmployeeID' linking to the EmployeeT (Reason behind this is that I may have multiple records for one Employee, meaning I would need a unique identifier)
SkillT with a primary key of 'EmployeeID' linking into the EmployeeT (Only possible to have one skill record linked to one employee)
Set up relationships as follows:
One EmployeeT to one SkillT
One EmployeeT to many SickT
I then created a blank form and used Tab Control.
On the first page I was able to populate the page with all the Employee info.
On the second page I was able to populate with all the skills relevant to the initial employee.
Then I tried populating sick records relating to the employee on a third page, but that's a no go. I was considering putting a subform on this page, but I wouldn't know where to even start with that.
View 1 Replies
View Related
Feb 23, 2005
Hello. The problem I am having is related to two tables. I have a table called Void and a table called Panel. A panel can have multiple voids and a void is assigned to only panel (one-to-many). In the Panel table, I set an autoincrementing number(SprayPanelId) for the pK. In the Void table, I set an autoincrementing number (VoidId) for the pk. I place the SprayPanelId into the Void table as a foreign key (same name in the void table). The problem I have is in my forms. I created a Panel form with a button to open a subform for data entry on the voids related to the panel in a new window. Data can be entered and is properly saved in the Void table. I open the Void table in a datasheet view under the table section and see it is properly there. I can run queries where Void.SprayPanelId = Panel.SprayPanelId and it returns the correct void with the correct Panel. The problem becomes when I open my void subform, it no longer displays that record associated with that panel. however, the record is there in my void table. Any thoughts? Do I need a filter perhaps, run any sql commands? Thanks in advance, Kevin
View 2 Replies
View Related
