Queries :: List Of Dates And Records With No Matching Record OR Existing Record With Higher Date
Nov 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
I have four similar forms - they each have a list box and an "edit" button.
The user selects the item in the list box and clicks edit.
Another form pops up, open to the record that was selected in the list box.
This works in two forms, it doesn't work in the other two forms. Instead it pops up the first record in the table, regardless of what is selected in the list box.
The code is identical on all four "edit buttons." (with field names changed, of course.)
Code:Dim stDocName As StringDim stLinkCriteria As StringstDocName = "frmCaseTypeEdit"stLinkCriteria = "[Case_Type_ID]="&Me![listCaseTypes]DoCmd.OpenForm stDocName, , , stLinkCriteria The properties in the listboxes are also the same on all four forms - based on a query, 3 columns, and bound column is the first one (which is the ID field).
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database Dim FocusBln As Boolean Private Sub Identificeer() Me.[Datum Aangemaakt].Visible = True Me.[Datum Aangemaakt].SetFocus If Me.[Datum Aangemaakt].Text = "" Then
This is kind of a weird problem, and everything I've learned about access has been on my own through empirical experimentation so forgive my if my description doesn't make sense, or if I overlooked something really simple. (Apologies for that, I've looked over all the guides on queries and record counting I could find and got nothing on this.
I can't figure out a better way of explaining my situation so I'll do the best I can.
I need to make a query that shows the number of people that have registered for a class. In one table I have the primary key Schedule ID which is the unique class, which is linked in one-one relationship with another field of the same name. In the second table with that field I have the field schedule ID which shows up multiple times, for each student enrolled in the course, second is the field Participant. There is the following data: Schedule ID...Participant 1 ................ <name1> 1 ................ <name2> 1 ................ <name3> 4 ................ <name4> 4 ................ <name5>
What the result of the query should look like is this:
Schedule ID...Number of participants 1 ................ 3 4 ................ 2
But instead I get Schedule ID...Number of participants 1 ................ 5 1 ................ 5 1 ................ 5 4 ................ 5 4 ................ 5
I have tried various variations of the Count and Dcount functions but I always wind up with that result (if it works at all)
This is the expression I am using:
Expr1: DCount("[Schedule ID]","Class Roster")
where "Class Roster" is the name of the Table that Schedule ID is located. I have tried changing it out with the name of the first table, and I have tried using participant instead of Schedule ID, but neither works. So far I've been able to use a crosstab query to get something roughly what I want but I think the assignment calls for a regular query.
Again, sorry if this doesn't make any sense, I did my best to explain it, and I couldn't find an answer anywhere. Thanks in advance for any help!
Oh, by the way, it's access '03 that I am using.
UPDATE: I have a second problem I'm trying to solve, and this one seems more confusing than the first to me. I have a table with the following fields:
Project Name, Task name, employee ID, Production Week, and Hours Worked. The first two being text and the last 3 being numerical.
I need to create a query that totals up the hours worked for each employee (one employee ID can show up under multiple projects/tasks) and returns the total hours in a particular week (week 20) of each employee that has worked over 40 hours, and only the ones that have worked over 40 hours. I can not figure out how to create a field that will sum up the hours worked for a given employee, nor can I figure out how to show only rows where the total hours record exceeds a certain amount. It seems like I would want a conditional like an IIF statement but those seem to be only for returning values, not showing/hiding.
I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?
I have three tables: First, Second, and Third.The tables Second and Third each have a column with a default value of "X".I'm creating a query that selects fields from First table that JOIN with corresponding fields from Second and Third, so that all records from First are represented and only matching records from Second and Thirdare returned. I also select the "X" fields from Second and Third.
So I run the query: and the "X" column from Second only displays "X" for records that have a match in Second (since I mean for these fields to be quick indicators for if the record also appears in the other tables, this is my ideal result). However, the "X" column from Thirddisplays "X" for all rows, regardless of whether the record has a match in Third or not.
I can get more specific if need be, but I really can't tell why the one is displaying according to matching records but the other is always on. The JOINs are set correctly and the properties for the columns all check out.
The fields we want to focus on is "AccNo" and "TestCode"
Now i want to filter based on this condition:
If "AccNo" AND "TestCode" BOTH have duplicate data in a record. Show that record only once. Remember. . . "AccNo and TestCode, both these fields have to have duplicate data in a record.
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456.....CBCA CAPT.....M566679.....John Blue.....123456.....CBCA the example above should only display once because of the repeated AccNo and TestCode
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456..... CBCA CAPT.....M566679.....John Blue.....123456..... LIPID the example above is just fine because the TestCode is not matching
How do i create a query to accomplish this? someone please help, i've had troube with this for the passed couple weeks . .. .
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
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
I have the employee data record... I want copy of the same record to insert in table but with different employee id and Site through forms. StaffNo is the primary key.
I have a query that records a status update, and the date it was made by an employee etc. The table has a secondary key related to ... lets say a project table.
E.g. Status Id; EmployeeID;ProjectsID;Status;Date
I want to calculate the average amount of days it takes for a status change occur.
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
I have got this form, consisting of 1 form (that needs to add a new record to a table everytime) and 1 subform, that needs to show the last three records of the table.
My problem is that I cannot get it right, now both forms (form and subform) are showing the details on just one record. If I open my subform it shows me all the records of a table, but in as soon as I view the form as a subform I shows me only one record.
Any help is welcome, I attached my db, just in case....
I have a table where one of the fields is a list box, where it is possible to select more than one option. The options correspond to records in another table. I have a query that includes this list box field. The problem is that if someone has made two selections in the list box, the query creates two records, each featuring just one of selections from the list box. Can I get this query to hold the two selections in one record? This might be especially challenging because the query field is pulling attachments.
Long Version:
I have a table [Master List] (primary key is TagNumber), where one of the fields is a list box, [Standard Methods]. You can select multiple choices in this list box, which corresponds to a table also called [Standard Methods]. In the Standard Methods table, there are two fields for naming the method and attaching the PDF which describes the method.
I have a query called [Get Attachments] that is set to bring in different attachment fields from different tables, including [Standard Methods]. The problem is that the query creates a different record for each selection made in the list box. For instance, if you select two standard methods for a certain tag number in the list box, this query will have two records for that tag number, each with one of the method attachments. Is there a way so that the query just creates one record, with both attachments in that record???
It should also be noted that I am using Outer Joins, so that the query includes TagNumber records from the Master List even if there are no attachments for it.
Im not sure if this is the right place to put this but I was wondering if you could help me:
Say I have database with forms that display records or people ie name, address, postcode, etc. What I want to do is display a list of Names then from that list I can double, single click and open the record details.
Id call my self and amatuer at access and am probably guessing this is done via vscript or something else, but any help will do.
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
I created a search form that searches for Customer Number, Part Number, and Revision Number. The results are displayed in a List Box.
I want the user to be able to make a selection on the List Box and click an "Open Selected" button that launches that record in another form. How is this possible?
I have two forms in my database. One for equipment and one for the network information.I want to open the frmIP from the frmEquip and be on the coresponding record by matching the NIC Mac address field.Currently, I can do it but it is done by a filter...I don't want a filter...I just want to open to that record but be able to access the rest of the table without turning off the filter.Here is my code:Private Sub cmdOpenIPForm_Click()On Error GoTo Err_cmdOpenIPForm_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmIP" stLinkCriteria = "[NIC_MAC]=" & "'" & Me![NIC_MAC] & "'" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_cmdOpenIPForm_Click: Exit SubHow can I open the form and goto the record with the matching NIC_MAC (without a filter)?Thanks for the assistance, Jeff.
I have a bit of code that upon a button click will check to see how many records match (CustomerID); if there are no records returned it goes to another action (works fine), and if there is a record returned, it opens another form to that record (again, works fine).
However, should there be more than one record, how do I get it to open the first one?
Once the form is open, what code would I use for a command button to find/goto next matching record (as I haven't used .FindRecord in the first instance, .FindNext won't work - or will it?)
I have two tables. One is about 160 thousand records, which is a part number, a contract price and a day the price took effect. The items appear several times. Prices go up and down over the course of the data, and items are added and dropped.The second table is a list of dated sales and quotes of those items over the past 14 months.(About 10 thousand lines)
I need to match the items with the contract price that existed on the day the quote or the sale was created, so as to demonstrate we were always at or below the contract price.
Another problem bothering me. Wish someone can help me to solve it. I am doing the 'Edit' Option right now ... As a user friendly propuse, I had try to use a combo box to let user to choose my employee record with all the detail on but the problem is the program 'JUST' list the record they can not go to the current record!! When i try to edit my record, the program is not allow (for sure because they can't have a duplicate record on it). The reason is because the record just stay in the FIRST record but can not go to the current record. I do believe is the problem of i using the way of listing the record as below: Me.EmployeeName = Me.EmployeeName.Column(0) Me.IC = Me.EmployeeName.Column(1) Me.Nationality = Me.EmployeeName.Column(2) Me.Race = Me.EmployeeName.Column(3) Me.Sex = Me.EmployeeName.Column(4) Me.FMU = Me.EmployeeName.Column(5) Me.Position = Me.EmployeeName.Column(6) Me.SectionField = Me.EmployeeName.Column(7) Me.DOEmploment = Me.EmployeeName.Column(8) Me.DOResign = Me.EmployeeName.Column(9) Me.Remarks = Me.EmployeeName.Column(10)
So, Could someone help me to solve it like when i can use the combo box to select the Employee record with the details are on as the same time can go to current record... Thanks alot!!!
i do have the confident if i can go to the current record then my edit option can be working properly.
P/S: I had attach the picture... showing my program problem (with the red circle)
