I have a database with 2 tables. Table1:drawing number and drawing title and Table2: date, action, status. Table2 gives the complete history of our internal review process.
I need a report which lists just the 'not approved' drawings. I've been able to add a query which lists the drawings but I loose the complete history, i.e. drawing recieved date.
Is there anyway I can display all the fields but only for those drawings which are 'not approved'
Dear Experts, I have created a database with queries that is working fine, however there are many records that have the same information that can not be removed easily, is there a method anyone knows of that can filter the records produced from a query to remove duplicates? i.e. 'select distinct' but for the whole query after the search parameters have been entered.
e.g. the search query is producing, after the refined search values are entered: 414353p - Fiat - 1.6 - MLS 414353p - Fiat - 1.6 - MLS How can i then apply a filter to automatically reduce these duplicates to just one entry for the whole form?
1 table contains the records I needs to be queried. The result Im looking for is to view all the clients that have a different advisor assigned to a duplicated ssn. - not same advisors assigned to a duplicated ssn. Example of desired outcome. ssn name advisor plan 111223333 joe smith john doe b 111223333 joe smith jane sony c 111223333 joe smith unknown a
I am getting all data. ssn name advisor plan 111223330 Jack Jack john doe b 111223330 Jack Jack john doe c 111223330 Jack Jack john doe a
Ive tried query with using 'first' for the ssn and the advisor still give me all data. Please help me.... Thanks
I wonder if anyone can help. I attach a table that is a material list (27000 records). When I add a new material I want to be able to see the last part code I created for a particular supplier.
For example C001, C002 and C003 are for a supplier company called Chapters so I would scroll through the combo box search list in the form for the last one and create a new record called C004. The problem is that E00001, E00002 for a company called Edwards supplies 20,000 items so it's a long way to scroll through to create a new part code called E20001. How can I show in the form what the last part code is for each Supplier?
I attach the form and table as I'm not very good at explaining this, although I have had to delete approx 20000 records as the database was too big to be posted. Any advice/help would be greatly appreciated, many thanks
I have a database to record drawings received. On the form there is a combo box to select discipline (Civil, Electrical, Mechanical) and a combol box to select the volume the drawings are in (Volume 1 - foundations, Volume 2 etc). The disciplines are stored in tblDisciplines with a relationship between this and the main tbldocument. I also have a table , tblLocation which has the volume ref and description. However when I click on the location field it shows me all volumes for all disciplines. What I want is if the first field selected is Civil only the civil volumes are displayed. Can any one point me in the reight direction
I have a rather simple problem that I do not have the time to solve. I have a time series taken at 30 second intervals over 5 months and the records total over 250,000. I only want data from every 15 minutes, so only one out of 30 records. How do I filter the table to extract only the records I need?
how I can modify this code to make it filter results in a combo box by what the user types in to search for.
Private Sub txtSearch_AfterUpdate() With Me.RecordsetClone .FindFirst "[FirstName]=""" & Me.txtSearch & """ OR [Lastname]=""" & Me.txtSearch & """" If .NoMatch Then Beep
I have a database that I have secured by using Access 2003 user security. I need for the database to allow the user to login and once they have logged, to open a form and filter out only their open "tickets" - records.
Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected.
I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself.
First Approach: I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria. This is the code I used: __________________________________________________ _______________ Private Sub Preview_Click()
End Sub __________________________________________________ _______________ But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already?
Second Approach: On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database.
This is the code I followed: __________________________________________________ _______________ Private Sub btnClear_Click() Dim intIndex As Integer Me.cmbCountry = 0
End Sub
Private Sub btnsearch_Click() Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter
Me.frmqrybyCountry1.Requery
End Sub
Private Function BuildFilter() As Variant Dim varWhere As Variant
varWhere = Null ' Main filter
'Check if there is a filter to return... If IsNull(varWhere) Then varWhere = "" Else varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter If Right(varWhere, 5) = " AND " Then varWhere = Left(varWhere, Len(varWhere) - 5) End If End If
BuildFilter = varWhere
End Function __________________________________________________ _______________ After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function.
Please help me, I would really want to figure this problem out. Thank you so much.
I have a query that shows member check-ins to a fitness facility. Customer scans their membership card and it logs their customer id, scan date/time (date and time are one field), name, etc. Sometimes the customer will scan their card multiple times if they've left and come back, but I only want to count their first scan. I have a second query that runs off of this one and counts visits by hour for a date range.
So my question is, how do I filter out subsequent scans on each day in a query and how do a I do that when running the query for multiple days? So in the multi-day query I want to count each day they came, but only the first scan of each day? How to do this in the query design grid...
I've run into a little snag and I'm hoping to reach out for some assistance.
I've created a form that has a drop down box. This dropdown box is populated with a list of values taken from a database.
I've created a query and built an expression that filters the query records by the value of said drop down box.
My issue here is if no value is selected in the drop down box, the query produces no records. I would like the query to display all records if the user did not select a value from the drop down.
Although I'm a beginner by any stretch of the imagination, I did manage to make a conditional in VBA to apply a wildcard (*) if nothing was chosen in the drop down, but that didnt work.
I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.
Anyone help I have 2 combo boxes cd group and cd name when I select CD group the records only associated yo that cd name display with that group.
Anyway, also the name of songs form works too,. So then now when I go from record to record the records display right with the songs and not with the cd group and name when I select Mormon Tabernacle Chior and name God Bless Ammerica it disaplys for all the records any help.
I am making a Customer Contacts database. The people in this database are organized into 30 groups.
I have one main form, and 30 queries to filter the records displayed on the form
I made buttons on a form representing the different groups of contacts to view, and when you click on one of those buttons, the main form opens with the record source as the query of the group you clicked on.
Also on the main form, I have a button to open a report that displays address labels of all the people in the database
How do i code that button, so that the records displayed in that report will be the same as those records displayed on the form? How do i filter the records in the report so they will be the same as in the form?
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?
I'm using a datasheet that can be filtered for certain fields. I have a macro that opens a form to the record that was selected (aka "current") in the datasheet. It filters this opened form to "1 of 1" which makes sense. How would I go about filtering that form for all of the records left (after filtering) in the datasheet?
I hate macros, but I'm doing client-specific work. The primary key is a field (autonumber) called "ListingNumber".
I am using Access 2013, I have a recipe project with multiple one-to-many relationships. The main table in all of them is RECIPE. Child tables are HOLIDAY, SPEC_NEED, COURSE... each one of these child tables are comprised of multiple checkbox columns (yes or no)...
I need to filter the RECIPE records based on the selected checkboxes in these child tables..so for example if COURSE.Appetizer is checked and HOLIDAY.Christmas is checked RECIPE will produce the appropriate records. How to pull this one off...I am currently using the Options Group design tool and have used a separate Options Group design for each of the child tables...
I have an appointment database with, of course a Date field.I would like to be able to filter the records to show appointments for a particular month.I want to see what appointments I have for September, for example.Ideally I would like to populate a combo box with month names and filter the form based on the month selected.
trying to enable database users to filter records based on column names which i have in a combobox. They enter the required value in a textbox and click the "Find" button.The code (linked to button click event) is not throwing any errors but the records are not being filtered.
I have a header/detail form working well. I use a Dsum to update a TOTAL field in the header - based on all the detail records for that Header.
However, I want to 'filter' or condition the records which the form will initially display from the Header file. I have a conditioning field (Invoice date) in the header which should 'block' it from display on the form. I may also desire to SORT the selected Headers record into a different sequence before display...
Looks like any "filtering" I try on the header table makes it difficult to go back and update the TOTAL in the Header?
i have about 14 fields that relate to subjects, basically i want to be able to run a query that searches for all the people who have choosen the module and report bak who they are..
now i know i can do this 14 times over but is there a way that the user can choose the subject first and the query will then run hence meaning only one query/report.. i thought of using a list/combo box but have absolutely no idea...
im very much a novice but any help would be most apreciated!
I have a query filtering question. Please note attach pic for further details. I am trying to put together a search in a form to seach for a certain color or color series from a Qery from two two different sources (from a Combo Box and from a Text Box). I am not sure how to set this up in the query.
Can someone please point me in the right direction?
OK, new problem to solve. I don't know if I am going about this the right way, so please feel free to make suggestions on better ways to acheive an outcome!
I have a query "qryBuildsPrinted" which searches a specific table for appropriate records, feeds these selected records into a report "rptBuildSlips" and prints them.
I am now trying to allow the user to print these build slips selectively, instead of printing out the whole lot pulled in by the query every time. My approach is to use the existing query, but filter the query based upon some selections made by the user in one of my forms. I am having trouble getting the query to filter successfully.
I realise that you can filter both the query and the report independently, so I probably only need to filter the report, but that is not working for me either!
Here is my code:
Private Sub cmdPrint_Build_Slip_Click() Dim intBikeID As Integer, intBuildID As Integer Dim varItm As Variant Dim ctl As Control Dim intCounter As Integer Dim intRecords As Integer Dim varArray() As Long Dim strFilter As String Dim blnPrinted As Boolean Dim msgMessage As Variant
'Print Build Slip for selected bike
'Set Control Set ctl = Me.lstResults intRecords = 0 intCounter = 0
'check if a selection has been made (kinda dodgy) For Each varItm In ctl.ItemsSelected GoTo Selection_Made Next
GoTo CleanUp
Selection_Made: 'Count how many records have been selected For Each varItm In ctl.ItemsSelected intRecords = intRecords + 1 Next
'Clear Array and Re-allocate Upper Bound of array (Array starts at 0) ReDim varArray(intRecords - 1)
For Each varItm In ctl.ItemsSelected
'determine BuildID of selected record intBikeID = ctl.ItemData(varItm) intBuildID = DLookup("[BuildID]", "tblBuilds", "[BikeID] = " & intBikeID) blnPrinted = DLookup("[PrintedSlip]", "tblBuilds", "[BikeID] = " & intBikeID) If (blnPrinted = True) Then msgMessage = MsgBox("One of the bikes selected has already had a Build Slip printed. Please adjust your selection", vbOKOnly, "Build Slip Already Printed") GoTo CleanUp End If
'Fill array with values, given how many values are present varArray(intCounter) = intBuildID
'Adjust counter to go to next array value intCounter = intCounter + 1
Next
'Select Filter String Select Case intRecords Case 1 strFilter = "[BuildID] = " & varArray(0) Case 2 strFilter = "[BuildID] = " & varArray(0) & " Or " & varArray(1) End Select
'Open query which displays the bike selected DoCmd.OpenQuery ("qryBuildsPrinted") 'DoCmd.ApplyFilter , strFilter
'Print report of query with filter applied DoCmd.OpenReport "rptBuildSlips", , , strFilter
A further question that is probably on the same topic is that once this has been completed, I also need to modify my existing Update Query which makes a modification to the data in the table. Is it possible to apply a filter to an Update query also?
I realise this may not be the most effective use of Access, so as I said, please feel free to suggest alternate methods I can research.
