Quick questions and I'm sure this is easy enough to warrant a quick response:
I have a query.
I have 12 different fields that may or may not have the value "Sabbatical".
I want to view only the records that have the value "Sabbatical" in AT LEAST one of those 12 fields.
Thanks in advance.
I am terrible with databases, but have designed a database that uses two text field types and 48 Yes/No check boxes. The two text fields describe documents (name and location), and all the check boxes indicate what types of documents they are.
What I need to do is to be able to run queries or reports where I can select CERTAIN of the Yes/No field types. For example, some of those check box fields are named 1A, 1B, 1C, 2A, 2B, 3A, 3B, 3C, 3D, 3E, 3F, 3G, (up through 8E) etc.
How can I get the query or report to bring up all of the records that have a check mark in either 1A, 1B, "or" 1C plus list the Field 1 and Field 2 that are text fields? I don't want it to list every record in the database, which is what is happening, because I want to then be able to sort the results.
I know it USED to be in my head how to do this, but I just can't seem to get it to work!
Thanks,
Toni
I have a report based on a query that I put inside a form. What I want to do is create 4 comboboxes from which users can choose options that will filter the report without leaving the form or opening the report in a separate window. Also, I want to make the filters dependent on each other, meaning if a select option A on combobox 1, the options i will select on combobox 2 will only be based on the earlier filter on combobox 1 ...
So far I'm able to do at least 2 of the comboboxes to filter the report, but they aren't based on which one was used to filter the report first. So if i filter the records by choosing option A on combobox 1, combobox 2 will still filter every record.
here's my 2 codes so far:
Private Sub cboLocation_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboLocation) Then
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
[code]....
the other 2 comboboxes, I can't apply the same code cause the enter parameter dialog keeps popping out.
I'm trying to implement a macro to filter through accidents to see if my company has already been hired on a particular case.
In the first form the user enters the date, location, and last name of one of the drivers. Then they click a button which opens a new form as a datasheet with accidents that match ANY of the three controls. Evidently, I need a filter that contains some form of a series of OR operators.
However, whenever I try any variation of a button that opens the second form and then applies the filter, I always get no matches.
Any way in report that I have 2 reference queries just 1 is to populate all details and 2nd query to filter details and will be the final reporting information???
View 5 Replies View RelatedHi Folks.
I have a select query that has 10 columns which are checkboxes (yes/no) from the original table. I can apply filters for individual columns but I want the query to show records with ANY of the boxes checked and only omit those with NO boxes checked.
I have spent the whole atfernoon researching the forum but I can't find what I need. Help much apreciated.
Chalkie.
I have a table with two numberfields (let's call it NumbersToFilter and FilteringField).
I want a query that shows me ALL NumbersToFilter data WHERE FilteringField is the highest.
Eg. if this is my table:
NumberstoFilter / FilteringField
999 / 50
947 / 44
920 / 50
941 / 48
I want my query to return 999 and 920 (filteringfield max = 50)
I'm currently using an aggregate query, using group by NumberstoFilter and max FilteringField, but it returns all the fields (the maximum is simply on top )
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies View RelatedHello All,
Thank you for taking time to view my access problem, I hope somebody can help / give advice on my problem.
I have a Form, within that form I have a subform. On the main Form I have several Combo Boxes.
ComboBoxes include; Year. Movie. Country.
What I want to do is when the user selects from the various values within each Combo Box to display the data in the subform accordingly.
I was able to use the Wizard to display the Movie Information correctly. The code is below for that. How can I go by doing this.
' Code to change the movie information, when they select the combo box
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & Str(Nz(Me![cmbMovieList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I am very sorry if my question seems broad, ill try to be more specific upon request. THANK YOU for your time and your help.
Basically i have a mortgage application system which im running a query on to see which applications have been completed in the last week or period up to now ie. last week, last month, last 3 months.
basically each application or CaseID has one or more applicants. The majority of them have two applicants. A few have one.
Problem is when i run this query which shows me: - CaseID, CLIENTNAME, LENDER, PRODUCT, MORTGAGE RATE, LOAN AMOUNT -
it brings up these fields but i only want one occurence of each CaseID????
CaseID Forenames Surnames PRODUCT RATEDateCompletedLoan required
2821NothandoDube71116.6920/01/2004£63k
2821Sipho Dube 711 16.69 20/01/2004 £63k
2909Hobbly Chise 726 15.69 31/01/2002 £164k
Get the picture? Both Sipho and Nothando are applicants on the same mortgage but i only want to show one name. Anybody know how to help me out???
Ive taken up this existing system, so i think there's possibly a normalisation problem thats causing this. But i need a quick fix for now rather than redesign the whole system.
I am working in MS access 2007.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
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.
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));
Hello,
My task is to compare our master membership table with a second table, removing matching names in the second table from the master table. (The second table are those individuals who chose not to receive a paper newsletter, so we want to save money and trees :) and not send them one.)
So to remove those names I tried the find unmatched query wizard but that only allows me to chose on one field it seems. There is no single unique identified field that would do this for both tables.
I thought if I built an unmatched query using the fields 'Last_Name' and 'Address1' and 'PostalCode' that should be sufficient for the task.
Here are portions the individual SQL statements that I need to get into one query.
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON tblAllMembers.Last_Name = qrySeekbyGroup.[Last Name]
WHERE (((qrySeekbyGroup.[Last Name]) Is Null));
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Address1] = [qrySeekbyGroup].[Mailing Address Line 1]
WHERE ([qrySeekbyGroup].[Mailing Address Line 1] Is Null);
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Postal_Code] = [qrySeekbyGroup].[Postal Code]
WHERE ([qrySeekbyGroup].[Postal Code] Is Null);
How is the SQL written so the find unmatched query will use all three of these sections in on query.
Thanks much for your assistance.
SAK
I've imported an Excel file into a table and now I've created a Query from it.
I now need to Add Fields (names are not in the table) and calculate totals for these renamed fields some of the answers are going to be the result of two or three fields.
Any help would be greatly appreciated.
thanks
Hello everyone!
I am new to MS Access. I am creating a small database for motor vehicle clearance. I have a SEARCH form where i want to put the fields for searching query table:
VEHICLE MAKE:
REF No.:
BEGIN DATE:
END DATE:
I need it in such a way that even when only one of the above fields is supplied with data it should still return correct data from the query table.
For dates (BEGIN DATE, END DATE) i tried: Between [forms]![search]![begin date] and [forms]![search]![end date]
This worked well when placed alone of the SEARCH form. But when i added more fields (VEHICLE MAKE, REF No.) the query returned nothing but a blank query table.
How can i possibly resolve this.
I will appreciated.
Hi! My question is similar to Hondasteed earlier today but a little different. I have a database that has 6 date fields. I would like to develop a query where the user enters a start and end date and the query checks all six of those fields for the appropriate dates and shows only those records who have dates in that range. The dates would change each month as we ran the query again. I would appreciate any help as this has really had me stuck and I'm guessing it's a simple solution. Thanks!
View 6 Replies View RelatedHi all,
Im trying to do the following query work.
I have a form, with combos , text fields which are filled in with parameters.
The query I want to make is complex.
For example we shall use field1, field2, combo1 , combo2 for explanation.
I want the field1 param AND the field2 AND combo1 AND combo2 to be evaluated for a result. At the same time, I want the user to fill in only field1 and combo1 or like that and the result to be right.
Is there any possibility to make them all work at the same time, individually, or in combinations ?
Sounds too Complicated ?
I ve managed to make it work only by filling in individually the fields or combos and have the right results. The other ways didnt.
Desperately need your helpppp. :rolleyes:
THNK U
Hi all,
Firstly, I'm an ex-fulltime access developer who has found himself doing access work again 6 years later; I can't actually believe how much I've forgotten :s
Anyway, I've done a search and havn't found anything that can help me so wondered if anyone could give some advice.
I have an access form with 3 combo boxes and a checkbox next to each of them. these are accessed using a query with 3 iif statements in it stating; (iif checkbox is null, "*", combo_box_value). the whole query looks like this
SELECT Customers.*
FROM Customers
WHERE (((Customers.Partner)=IIf(Forms!frm_rpt_main!check _partner Is Null,"*",Forms!frm_rpt_main!partner)) AND ((Customers.[Type Business])=IIf(Forms!frm_rpt_main!check_type Is Null,"*",Forms!frm_rpt_main!type)) AND ((Customers.[Year End Month])=IIf(Forms!frm_rpt_main!check_month Is Null,"*",Forms!frm_rpt_main!month)));
However, the query only seems to want to pull data from all 3 combo boxes
My issue is that if a checkbox is not ticked, I want the values to be ignored for all 3 combo's.
I've attached a copy of the mdb file as my description probably doesnt make any sense, the specific query is "qry_select_month_partner_type_wname_frm_rpt"
Thanking you all in advance for your help!
Younger
Hello all.
I'm having a problem running a query that compares one fields to 5 others. The code is below. I'm sure it's not correct because the results don't look like they should. I have these fields:
Account Number
Transaction Type Code
Bal unapplied code 1
Bal Unapplied code 2
Bal Unapplied code 3
Bal Unapplied code 4
Bal Unapplied code 5
field_result
I wrote the code to add "UF" before comparing any of the "Bal Unapplied code" fields to the "Transaction Type Code". I am trying to first see if the code in "Transaction Type Code" is located in "Bal Unapplied Code 1." If it finds it, then I want to put "Found" in the "field_result" field. If it doesn't find it, then move onto the next field and so forth until it goes through all 5 fields. If it can't find a match in any of the 5 bal fields, then I want to populate the "field result" with "Not Found"
If someone could help I'd be forever in your debt!
Here is my crazy code:
Iff("UF"+[unapplied balance_update].[bal Unapplied Code 1]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 2]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 3]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 4]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 5]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found","Not Found”)))))
Hey guys-
I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')-
xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this-
Field1 Field2 Field3
xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow...
Thanks!
Access2007- Building a query to handle future input...
I run an update query that does what it is supposed to do and gives the proper values.
THEN I add a one more field to the source with the builder using + that has no data in it yet, the query fails and gives blanks, even though one of the fields in the source has data in it.
I would think that the "no data" field would be considered 0.00 value and be added together to the field that has values giving at least the value of the field with values as a result of the query.
Is there a setting somewhere that I need to change so that the no value and the value would be combined? I am using the builder to combine the values in the query.
Thanks.
I have already made a query with all the information needed. What I am trying to do is create a form where a user can open the database and enter a unique number and have information from 10 fields associated with that number show.
View 5 Replies View RelatedI want to make a form which will allow me to get data from a query and search using 3 fields parallel.
Attached the database in 2010 format.
Password of the db is "nolimit".
The query PTM & Equipment should be bound with the form and
If I enter month, PTM and equipment need rest 2 fields displayed.
Like choosing the above 3 fields from the drop down , the next 2 text box should display the rest two fields.