Query Record Set Reduces When Sorted
Jan 26, 2006
Hi,
Possibly related to my other problem, (see other post on queries page).
After running a query that looks at multiple tables I get a record set of, say, 560 hits.
To view the data in a useful way, I select one column or another to sort ascending or descending, no filter.
The record set returned after the sort is often smaller, say 350. As far as I have been able to tell, the records dropped and the records remaining are functionally the same and should all still be there.
Any ideas why the query might be doing one (or more!) of the following:
a) Returning too many records, the excess get dropped when sorted?
b) The sort is removing records that should be included
c) Access is applying a filter that I have not instructed it to do so
PS. I did try to analyze the difference between the results of this one time but got side tracked into creating a new query from scratch that seemed to work properly.
Regards,
Keith.
View Replies
ADVERTISEMENT
Jun 9, 2006
Hi guys, I need a little help on next record stuff...:eek:
Can someone please explain how I can make my database actually go to the "Next" record after I update the "LName" field on my form? My database is sorted on "LName". After I update the "LName" field and save the record, the sort order is messed up. If I requery the form in the sub routine, the database goes to the first record. I need it to go to the "Next" record (the one that would have actually come next before I changed the LName). For example, if my database contains these names:
Baker
Doe
Franklin
Goodwin
Johnson
Jones
Smith
Taylor
and I change the current record's LName from Franklin to Phranklin, I expect the database to go to Goodwin (the record that would have followed Franklin) after pressing my next record command button. Likewise, if I changed Phranklin to Franklin, I expect the database to go to Smith (the record that would have followed Phranklin) after pressing my next record command button.
I've tried different versions of FINDFIRST on this site, but can't get it to work. I would like to find the next record based on my key field (autonumber) named "rec_id".
Thanks, and luv ya in advance!
-carol
http://profiles.yahoo.com/c_coop2005;)
View 14 Replies
View Related
Apr 2, 2014
I have a main form[frmResearchNotes] with combo box controls that filters a query populating [subfrmNotelist] containing several records from the filtered query. From there, I double click on a field within one of the remaining records, [CompanyName] for example, and it opens the new form[frmNoteDetail]. The problem is that second form is not displaying that selected record. The second form's record source has been set to the same query so when it loads, it displays the same info but it's displaying the 1st record out of the entire filtered list, not the record I clicked on in that list.
I figured I could use the strWhere function to copy the record I selected in the event procedure and then open the new form with those details. Not sure how to actually do this with VBA or if it's even the correct approach.
View 14 Replies
View Related
Jun 30, 2005
Ok what I have is a List box with four rows. (Name, Assignment, Location, Description) These are all labeled with a frame at the top that when that frame is selected that row is put into alphabetical order.
The next thing I have is a combo box which also sorts the List box by "major location". The combo box has the following (All, Fort Mills, Corporate, Pequot Lakes, Savage, Retail) When one of those is selected the location row then shows only one of the following locations and the others are removed.
Here is where the problem comes into place. I want to be able to select a major location, then be able to sort with the frame. When I select a major location, and then click on the frame, lets say "Name" the list resets back to everything from that table and not just that certain "Major Location" that I have selected. I need to sort whatever is selected in that "Major Location" by whatever is selected in that frame. thanks for any help, I know its a hard one.
View 2 Replies
View Related
Jan 10, 2006
Hi All,
Just a quick enquiry if I sort a query by a date field in ascending order then select unique records will I get the latest records.
Thank You
View 1 Replies
View Related
Mar 4, 2013
I've just begun using microsoft access and would like to create a primary key on a table of data that has been sorted alphabetically. However, when I try to create this key (designview -> auto number-> increment) it autonumbers for the column the way it was before i sorted it. Is there a known way of doing this?
View 7 Replies
View Related
Jul 10, 2006
I have a client table with a field called location. On a reports form that I have, I want to make a combobox for all of the locations, so it could show all the clients from a particular location and also it would reduce the errors due to someone spelling a place name wrong. I could set the source to the location field in the table, but that would show them all in the order they come out and there would be duplicates.
Any help would be cool. Cheers
Bob
View 1 Replies
View Related
Sep 23, 2013
I have written a query where it displays sorted data for two columns, but problem is its sorting on one column but not on another.
When checking the query separately its giving proper output, but in form view its sorting only on one column not on another.
View 1 Replies
View Related
Feb 20, 2014
I have a form that contains a combobox. I want the default value for this combobox to be the Staff_ID of the first forename to appear in a table when sorted alphabetically.
The table I'm querying is called 'Staff'. Fields within are 'Staff_ID', 'Forename', 'Surname'. The table is sorted by 'Forename' in ascending order.
From the example tables below I would expect to return a value of '3' for example 1 and '9' for example 2.
Example 1
Staff_ID Forename Surname
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith
Example 2
Staff_ID Forename Surname
9 Alice Jones
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith
I'm using Access 2007
View 9 Replies
View Related
May 11, 2015
How do i create a report that is linked to my sub-form that i have just sorted in a different form? let's say i have a form named View Records and on that form i have added a subform that is linked to my Employee Table, and then on that View records form i have added a few combo box in order to sort the data on my subform on that form, now what i want to do is that, i want to link those data that i just sorted on my subform and transfer it to a report.
View 5 Replies
View Related
Sep 27, 2012
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?
View 6 Replies
View Related
Feb 28, 2014
I have a report that i can order through buttons on report or openargs. Is it possible to get a field that would count the number of time an item appears i.e., if I sort it by employee, it would count how many times each employee is on the report, if i sort it by TypeOfMalfunction, it would count how many times each appears.
View 4 Replies
View Related
Feb 6, 2014
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
View 1 Replies
View Related
May 20, 2014
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL:
SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes
ORDER BY 1 DESC;
View 5 Replies
View Related
May 25, 2015
I have a linked table(tblxyz) having property set as ORDER BY ID DESC, ID is autonumber, so my table view gives me latest record on top.
Now I have a subform , where i am calling this Table.....
[Forms]![MainForm]![Sub_DisplayFm].SourceObject = "Table.tblxyz"
This gives me datasheet view of the table inside subform but its not showing sorted data
Interesting thing is it give sorted data view when my table is not linked and is in same access file.
View 4 Replies
View Related
May 30, 2014
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
View 8 Replies
View Related
Jan 26, 2015
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
View 4 Replies
View Related
Aug 22, 2013
I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?
The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B
if it is possible, are there some requirements that have to be fullfilled?
View 2 Replies
View Related
Sep 15, 2005
In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e.
Name Value Previous
Record 1 1
Record 2 2 1
Record 3 7 2
Record 4 1 7
Is it possible to create some kind of simple expression to refer to data in another record?
Cheers,
Ben
View 6 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Mar 8, 2005
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
View 9 Replies
View Related
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies
View Related
Jun 29, 2005
The strSql string should find the record that I have already saved in my admin table and then compare it with combusername, where is my mistake.
Private Sub Command7_Click()
'Dim db As Database
Dim strSql As String
'Set db = CurrentDb()
strSql = "Select adminpass From admin"
If ((Nz(txtpassword, "") = "") Or (Nz(combusername, "") = "")) Then
MsgBox "Please Enter A Valid Access", vbOKOnly, "Error"
ElseIf (txtpassword = "admin" And combusername = strSql) Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
ElseIf (txtpassword = "user" And combusername = "user") Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
Else
MsgBox "Wrong Password, Please Try Again", vbOKOnly, "Error"
combusername = ""
txtpassword = ""
combusername.SetFocus
End If
End Sub
View 3 Replies
View Related
Oct 31, 2007
Hi,
Don't know if this is possible, or if im being just plain stoopid:
Is there a vb command that will update a record without having to use a query?
My problem is this - I have an HR database which has allows us to add employees that are going to start. We then set their [Activity Status] from "Starting" to "Active". At the moment this is done manually, but what i would like to do is have this automatically change when the [Start Date] = Date().
The code i tried to use (but is obviously wrong) is:
If [Activity Status] = "starting" And [Start Date] < Date Then
Set [Activity Status] = "Active"
End Sub
Any thoughts what i might change "Set" to, to make this work?
Thanks,
Ferg.
View 6 Replies
View Related
May 12, 2005
The novice that I am inadverdently deleted a record in a query. (I find it strange that it does not just automatically restore it the next time I run the query.) Is there any way I can get this record back without having to recreate the entire query?
View 2 Replies
View Related
Feb 23, 2006
Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?
View 10 Replies
View Related
