I'm very new to access - perhaps this is an easy one...
I have a master table 2 columns wide. I want to extract data from the master table for values that appear in a second table.
That is...
Master Table:
1 a
1 b
3 c
4 d
4 e
6 f
6 g
5 a
My second table
1
6
5
I want to return:
1 a
1 b
6 f
6 g
5 a
I basically want to enter a list of values in the criteria section of the query design view.
Any suggestions?
I have a query which is based off all fields of the main table. I want to run the query based on one or more choices made from a list box on one field: category.
tbl_ClientMain
qry_ClientCategory - all fields of tbl_ClientMain with criteria under Category as =[Forms]![frm_CategoryList]![lstCategory]));
frm_CategoryList is a listbox form based on qry_Category
qry_Category is a group by query from tbl_ClientMain of the Category(s) collected
When I run the query, I get Enter Parameter Value: Forms![frm_CategoryList]![lstCategory]));
Based on what I've read in various postings and other sources - I must be close, but I'm missing something. Any ideas?
Hello Daily Post, I suppose,Here is what I've got:On one form resides a list box that get's it's results based on a customers sales history.The form contains all the customers prudent information, otherwise. What I'm attempting is such:Onclick for the listbox is DoCmd.OpenReport "RptIndividualSale" the source for the report is a query whose criteria is the unique SaleID [Forms]![Main]![LstSales].[SaleID] for the records in the list box.It also has a few other fields such as the item name, sale amount, etc. My hitch is that it is using every SaleID as a result in the report. What I wish to attain is a report that only generates the information for that single sale, instead of the entire history.So if the list had SaleIDs of 12345and a user clicked 5 a report would open showing ONLY the details of the Sale with ID 5, as stated it currently shows details for all the SaleIDs, so I have a report with 1, 2, 3, 4, 5. Any way to fix this?
View 1 Replies View RelatedHello everyone, i am trying to get 2 listboxs (the first is for name, the second is for state). I need to be able to select a name from one listbox and a state from the other and have a query display after a button is clicked. At the moment i have found a section of code which runs under the OnClick Event Procedure of the button to display the query (one listbox only though). It is as follows:
Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tblCompanies"
'Build the IN string by looping through the listbox
For i = 0 To name_listbox.ListCount - 1
If name_listbox.Selected(i) Then
If name_listbox.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & name_listbox.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strCompanyCounty] in (" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
'Clear listbox selection after running query
For Each varItem In Me.name_listbox.ItemsSelected
Me.name_listbox.Selected(varItem) = False
Next varItem
Exit_cmdOpenQuery_Click:
Exit Sub
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If
End Sub
Is anyone familiar with the code to tell me how i go about adding code to this so to have a second listbox working?
The working example with one list box can be found here: http://www.databasedev.co.uk/downloads/multi_select_listbox_2000.zip
Cheers
Pete
I have a parameter query where user enter the department number to get their info. I want instead of entering the department number, a list box pops up and ask to select department as parameter and run the same query.
View 2 Replies View RelatedIs there someway within Access itself or the documentation to see a complete list of:
Functions such as sum, median, average
Operators such as "and" "or"
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View RelatedI have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
I have a table where the columns are team1, team2 and winner. Team1 and team2 are comboboxes that show all the teams in Team table. After I pick the team1 and team2 in a row I want to make a listbox that shows only those two teams as options for the winner.
View 3 Replies View RelatedGood afternoon,
I need to use a selection from a drop down list to change the criteria in a query as opposed to typing text in a parameter box. This is to allow the user to choose from a list and then click on a command button to move to the next query. There are 20,000 records in the table. The series of queries will take the user to 1 or 2 records in 3 or 4 mouse clicks.
Thanks
I am trying to create a form that allows the user to select a detective and multiple case statuses (such active, inactive, dna, filing, etc.). The form is intended to allow the user to determine the number of assigned cases. The form is tied to a query and the criteria that I've set up in the query is:
[Forms]![DET_CaseManagementFRM]![Combo30] which is under the investigator field.
The case status field has similar criteria [Forms]![DET_CaseManagementFRM]![List43].
My problem is that I cant figure out how to allow the user to select multiple criteria in case status field in my form. The research I've done online says you need to specify simple or extended in the Multi Select field under the other tab in the property sheet. However, when I do specify simple it doesnt return any records which I know it not true. Is there VB code that I need for this?
I have an AfterUpdate event where I want a list box to be populated based on three different criteria based on a table in my database
1. Complete = False AND
2. Supplier on form = supplier in table AND
3. Status in table = "SUPPLIER_RFQ FOLLOW-UP" OR "SUPPLIER_RE-RFQ TO OTHER SUPPLIER"
I am having trouble with the last OR statement criteria, i cannot get this to return values correctly. Here is my code:
Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool].[RFQ Contact] " & _
"FROM Consolidated_Master_Req_Pool " & _
"WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool].[RFQ Supplier] = '" & Nz(Me.cboStatusRFQ) & "' And [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RFQ FOLLOW-UP" & "' OR [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RE-RFQ OTHER SUPPLIER" & "'" & _
"ORDER BY [Consolidated_Master_Req_Pool].[RFQ Contact];"
I am trying to add up a list of dates that match a criteria... a search box result.
I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)
But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.
Code:
Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant
[Code] .....
I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?
Details:
I have a table of data for each month with supplier codes in each table, but no date field.
I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.
The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.
Please help
Thanks for reading
HI all,
i have a really annoying proble where the solution is probably staring me straight in the face. Basically i want to produce a report where the user is prompted to enter months to be looked at within a certain period and also the years, in my query i am using:
Between [First month] And [Last month]
and
Between [First year] And [to end year]
when i do this the query just asks for the months and doesnt go on to ask for the years...help?! :D
Hey Guys,
What i want to be able to do, is that when a user clicks on a report, they are asked to enter a month and only the records with that month will be displayed!! What expression can i enter in the citeria of the query, i did try =like"month" but that means creating a query for every month of the year!!!
Thanks in advance guys!!
Is there a way to have a Criteria set to promt a user to select a item from a dropdown list.
So when the prompt is made to ask for the Item Name it will have a dropdown list that the user can choose from
Hi,
I hope someone will be able to help me with this one, I have a simple access query set-up which references to a table containing information about processed orders (From a ficticious company). I am trying to set up a query that will display all order placed within the last week. I have edited the criteria on the date filed to
Between Now() And Now()-"7"
Now this sort of works in that it filters the data, but it doesn't filter it by the day but by the year. i.e. Any order played within the last 7 years rather than the last 7 days. Any help much appreciated.
Sorry for being such a n00b,
Thanks for looking!
cheers
xRes
I'm developing a report whose query is linked to a resources table containing background info about each of the resources in my department. Currently, the query pulls data for all resources regardless of whether or not they're still employed by my organization. In the resources table there is a field called "end date." If a resource is no longer employed with the organization, we input their date of departure. I only want the report to show info for those employees currently employed with the organization. Hence, I must make note of this in the query. I've tried the following in the criteria box for the end field,however I continue to receive a data type mismatch error. Help would be greatly appreciated. Thanks in advance.
"Is Null"
<> "True"
hey people, im new to the forum and so far its been v.benficial, iv got a simple database running but can't get a query going; can some one please show me how to do the following on a query:
What do i put in the criteria so that it does not show records with null value?
Thanks
Mos
Hi all,
In my query i have 5 different fields showing different type of fuel types, these are checkboxes on a form.
I have another field which shows the 'dry time' (this is calculated on two other fields, the time of runout and time of delivery)
What i want to to is have a selection criteria whereby if 'Diesel' (one of the fuel tyeps) dry time is over 06:00 hours, it is displayed.
Im not sure whether this should be in the Diesel field, or the dry time field.
I have tried both using:
'Diesel' > "06:00" and also 'Dry Time' > "06:00"
but niether work...any help would be appreciated. Thanx