i have a table with 20,000 records. one column includes names. another column includes multiple dates the person visited a doctor, and another column includes dates the person visited a hospital. i am only interested in the last date the person visited a doctor and the last date the person visited a hospital (if they had any visits to the doctor or hospital). I am also interested in members who have had no visits to the doctor or hospital. any suggestions on how i can filter the data to only return these records? thanks
Name Date Visited DoctorDate Visited Hospital
Nigel01/01/1980012/31/1995
05/20/199906/06/2005
03//02/200405/06/2004
For example, I only want to return
Nigel, 03/02/2004 (Date Visited Doctor), and 06/06/2005 (Date Visited Hospital). thank you. i really appreciate any help you can offer.
In my database I want to create a report that identifies if some is available or not every day. I have a general table(Tbl_General) with names and contact info, in a second table(Tbl_Recall) I have 3 fields Location StartDate EndDate. I want to show every person from the general table and only records from the Recall table where the start date is before today and the end date is after today.
The queries I write exclude some records if I limit the dates and the reports I build show all records in the recall table.
I am trying to build an expression in a query that will filter specific numbers when the value is entered. I would like the value box to read something like "Please enter the file number" and when entered, see example of file # in brackets (001-200-001), it will show those records with that file number only. In addition, because I will be printing it from a report, the filtered file numbers should also show up in the form.
I have a few queries which are used to create reminder email on training which is due for renewal.Some training required reminder 6 months before 2 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-18,1) And DateSerial(Year(Date()),Month(Date())-17,0)
Some training required reminder 3 months before 1 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-9,1) And DateSerial(Year(Date()),Month(Date())-8,0)
My problem is with training requiring reminder 6 months before 3 year expiry. Using this criteria:
Between DateSerial(Year(Date()),Month(Date())-30,1) And DateSerial(Year(Date()),Month(Date())-29,0)
Doesn't show any results (although there is training which was done 30months ago, expiring in 6 months time).
Changing the -30 (months) and -29 (months) in above down to -22 & -21 shows records as expected, but anything below -22/-21 doesn't show any records.
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I need the code for a filter or query which will show the past x (12, 24 etc) months worth of data. Each record has a date tag so i need a filter which will just show records for say the past 3 months.
I have created a form for a table which contains ~600 movies and their name, genre, rating, director, year it was made, and length (min).I need to be able to enter numbers into the two Year boxes, and then it filters the movies in my database and only shows me records from between those two years. The years in my database are just in one column in the format of: XXXX e.g. 1996 etc..I've tried the code:
Code:
Private Sub Year2_AfterUpdate() Me.Filter = "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2 Me.Filteron = True Debug.Print "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2 End Sub
Year1 and Year2 are the boxes Year: and To: respectively. [Year] just being the column name which contains all my years.When I try to run my query it shows me my records, but it shows me all of them! It doesn't filter it at all!
I am having trouble getting a query or report to show only the most recent data.
We have salesmen that use a handheld data collector scanners to count inventory in stores. The scanner data is imported to a Access table. Each record line is one scanned item. I have a query with totals that counts the records and gives me a total count of each item at the store on that date.
I then need to filter the data to only show the most recent date. Using Max Date I get the most recent date but the count fields are showing totals for all dates. I am also getting the unique item from the earlier date in this query which I do not want.
Here is my data table: Inventory Scans from stores.
Scan Date Item Scanned location
1/1/2014 item123 Store ABC
1/1/2014 item123 Store ABC
......
Here is my Query with Totals that counts the item records:
Scan Date Item Scanned location (Item Scanned) count
1/1/2014 item123 Store ABC 2
1/1/2014 item 456 Store ABC 3
1/1/2014 item 789 Store ABC 1
2/1/2014 item123 Store ABC 2
2/1/2014 item 456 Store ABC 1
This is what I am trying to get - only the most recent date of counted items:
Scan Date Item Scanned location (Item Scanned) count
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code: TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
Hi, I have this table with job numbers stored by days of the week e.g. mon job, tue job, wed job etc. You can have the same job number everyday of the week or different ones. I have to get a report that shows JOB # ??? and all of the fields associated with it. I'm trying with a query, but can't get it! How can I accomplish this?
I have set a query to carry out a list of companies from a table based on two criteria. However, the same company could appear two times in the results .
Is there a way to requery results to filter records that appears two times and leave them only one time in the final list? Thanks in advance
setting up a database for my department at work and have a few questions
a.) i need to set my form to have a search or filter based on user criteria
1.) users need to be able to select themselves from a drop down list that's based on a table in order to filter their cases from everyone elses
2.) instead of using the drop down menu i'd like them to be able to enter an ID number that pulls up a specific record rather than first selecting all their cases and then navigating those until they come across the case they're looking for
I want to write a code that upon opening the report it looks at the sumofamount field and only shows me the records that the sum is greater than $50. It currently shows me all the records and it they are less than $50, it comes up as a negative. I only want to see the one which have amounts over $50. Would I write this in code and have an even procedure upon opening of the report? I should also add that I have the amounts listed out the bottom of the page, so I can't just go into the query and put >50.
Thanks. ="" & [first name] & ", for the month of " & Format([date],"mmmm") & " your DHL bill came to " & Format([sumofamount],"$#,##0.00") & ", the company allows you $50.00 per month, this leaves you with an overage of " & Format([overage],"$#,##0.00") & ". This amount will be deducted from future commissions. Below is an itemized list of your charges. If you have any questions, please contact me at extension 4491."
In access, I have the following: 2 sub forms located in an overall form. I would like when I select a record in the first subform, that the second subform would automatically filter for all records that contain the same ID Value.
I need to navigate through all records for a table where a <field> = a certain "string". Ideally I would have a next record button that would know to ignore records that do not meet my criteria?
Simply put, Next available record where <field> = "string"?
I use Access 2003 and saw Ken Higg's sample database to filter records in a form using a combobox. I managed to replicate his settings and wondered about an extension to the principle, but I don't know if it's possible.
In the sample, a value is selected using a combobox and all matching records are filtered so it's possible to scroll through them. Is it possible to have a second (or more) combobox on the form so it is possible to filter on one or another criterion? This would mean one combobox filling the other one and I realise that the one that Ken provided is unbound.
The reason that I'd like to do this is, for instance, if there are records with Surname, 1st, 2nd and 3rd lines of the Address and Postcode, I may wish to filter records by entering the Surname or Postcode. Each of these fields would have to be displayed in a combobox. As the sample is set up at present, only one of the Surname or Postcode has to be selected via the combobox and the other is a textbox.
It may be that what I'd like to do isn't possible - or someone may have come up with some other idea. I'd prefer to have the search/filtering on the form, rather than using the buttons on the Form View toolbar.
A form I created contains buttons for the user to restrict the display- showing records that are tasked to one specific employee. So their choices are Employee a, employee b or all employees. This is a process I use a lot :) This last form opens with NO records displayed. When a button is clicked the records show correctly. Any idea what to check? Thanks,
I am importing about 1.5 million records from a .txt file into an Access database. I have the code that splits the records and adds them to the database. However, the database has a 'specialty' table that lists certain specialty codes. The incoming record has a field that is a specialty code. Any way to search the 'specialty' table for the code that is coming in on the record to be added, and then, if the record 'code' is not found, skip it, and if it is found, add it to the database. There are about 70 codes in the 'specialty' table.
Made some tables with data, then wanted to filter it by form with comboboxes approx 10 of them, so started to write a simple query but it doesn't really work. Then I found this forum and when started to review some threads, noticed that some (most) of you write a query in vba under a button with on_click function.
The question is, what is the correct way of writing a query? Is is a vba code under a button, or is it a code in the actual query?
It have several fields, and the fields that I want to filter is ItemNo and SellingPrice.The problem is ItemNo has many different SellingPrice. I put a parameter in SellingPrice field as >=75 only for ItemNo "49565", but still i want to show other record in ItemNo and do not have filter in SellingPrice field.
vba code for filtering the records of the form made out of a table. The form contains combos that contain the lists of unique records in several colomns of the source tale. So i need to filter the records of the form using the combination of the values of that combos. But can't even apply a single filter using this code:
"Business" is the name of the column in the source table, and the "list59" is the name of the combo which contains the unique records of the "Business"
the code takes place as a button "apply" is pressed
I can create a dropdown list to filter my form (text) however im struggling to get back to showing all the data prior to my drop down selection.
ive tried refreshing and requerying with no success.
I would either like to get a drop down with the addition of "all records" in it or a refresh button.
the form that this is on is a sub form.
I could reference the drop down to a number if required.
Additionally: When I have selected my choice from the drop down box it blacks out "selects all". Is there away of making the list just select and lose focus so to speak.
