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.
View RepliesI have a table with 5 fields (F1, F2, F3, F4, F5).
F1 has 3 values (v1,v2,v3) and the other fields have different entries depending on F1.
-----------------------------------------------
F1 F2 F3 F4 F5
-----------------------------------------------
v1 f31
v2 f21 f41
v3 f41 f51
Is there any way to filter F1 to show only the fields which have a value?
(if I choose v2 to have shown only fields F2 (with f21) and F4 with f41
I have a subform query which includes EventName, Date, and Tags. Tags is a multivalued field.
What I need to do is allow people to filter this subform using the inbuilt filter, and then use the filtered data to populate a graph.
I am doing this by taking the Filter property from the form and then using VBA to build an SQL statement with the right data, and using that on the graph. It works fine when people filter on the EventName and Date fields.
However, when someone filters the Tags field, it fails. The filter property gets set to something along the lines of
([Lookup_Tags].[TagName]="whatevertagtheypick")
This syntax seems not to be supported when I put it into the SQL statement.
I am trying to provide the user a custom search feature. They want to enter a keyword or phrase and search 3 memo fields and filter the form base on the records found. they also want to be able to search the whole phrase or any part of the phrase.
I have a like expression for any part of the phrase but I when I set it up for whole phrase it doesn't work. Even if I run a simple query and use
For example: There's an acronym the user is looking for : ACA
If I set my query up like this: [field1] like "*ACA*" or [field2] like "*ACA*" or [field3] like "*ACA*"
it not only finds records with that acronym but it also finds records where that combination is found in a word, for instance vacate.
How can I set up my query to find the whole word?
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 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 have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
I have a database that tracks Construction projects. Each project can have multiple Managers, so I have a many to many relationship set up with tblProjects, tblProjects_Managers and tblManagers. I created frmProjects and sbfrmProjects_Managers. sbfrmProjects_Managers has a combo box control and is continuous. Its record source is tblProjects_Managers, its control source is ManagersID (the foreign key in tblProjects_Managers) and its row source is SELECT tblManagers.ManagersID, tblManagers.Managers FROM tblManagers ORDER BY tblManagers.Managers;. The bound column is 1, the column count is 2, and the widths are 0";4". When adding sbfrmProjects_Managers to frmProjects I made the child-master link with ProjectsID, the PK of tblProjects and the other foreign key in tblProjects_Managers.
I need the Managers to be able to do a search that will return only the projects that they manage. I've tried making a query composed of tblProjects, tblProjects_Managers, and tblManagers. I used the field ProjectsID from tblProjects and ManagerName (with the criteria Like [Manager's Name] & "*") from tblManager. The query worked when run by itself, but when I use it as a filter in a macro to open frmProjects, I get two prompts to enter a value, one that asks for tblManagers.ManagerName and one that asks for Manager's Name. No matter what I do I can't get only the second dialouge box to appear, and regardless I no matter what I enter into the two dialogue boxes my results aren't filtered at all.
I want a query that gives me something like this from 2 different tables:
table1 - AA, AC, DE
table2 - AA01, AA02, AA03, AC01, DE01, DE02
query -
column 1 - column 2
AA - AA01
------ AA02
------ AA03
AC - AC01
DE - DE01
------ DE02
Is that possible?
I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.
I have an annoying error which has always appeared but I don't understand the root cause of it.
When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):
"syntax error missing operator in expression 'name of field'".
This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:
Conditions:
<>"CINEMATIC" And <>"SFX"
Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*"
[Forms]![FRMscriptPrintReview]![selectLangCombo]
Calculated Field:
Audio Ref Guide: IIf(Left$(Right$([TBLdata]![Script Resource],2),1)<>"_","?",Right$([TBLdata]![Script Resource],2))
I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.
I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58
The SQL statement for the combo box is
Code:
SELECT SupplierID, SupplierName
FROM tblSupplier
ORDER BY SupplierName;
In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter". I have tried
Code:
In (Forms![frmSupplierReport]![intFilter]
but this does not work.
If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!
How would I achieve this?
I m trying to make form which filters my records and generates a report..here's where I am
Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Code:
Like "*" & [Forms]![Form1]![Text6] & "*"
the code is in report record source
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'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.
where each number corresponds to the primary key in another table, each number being a company name (not really relevant). What I want to be able to do is:
1. Produce a query table result with the Total Project Value (sum of all values) each company (305-9) is involved in. For example, in Project A and C, 308 are not involved, so the 308's Total Project value would be B+D+E+F Valuation.
2. Produce a query table result with the Average Project value. Similar to above, but taking the total and dividing it by the number of projects they were involved in. For example, 308 had a total of 259 over 4 projects, so an average of 64.75
3. Produce a query table result where it takes the most common role for each company ID. For example, Company 308 was Civil 3 times and Structural 1 time, giving 308 with Civil as their most common role.
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.
View 3 Replies View RelatedI have a table called tblAuthorizations. It appears as follows
AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits
1374006036.........356679..........20255102....... ...4/22/13......7/21/13.....0..............416
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520
504564357...........469432..........20254788...... ....4/22/13......7/21/13....0..............520
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520
I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.
What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.
I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.I've tried adding the following fields, but this isn't working.
PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])
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 am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses]
FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
I have the following source data:
Code:
Item Revision Start End
332 B 9/2/11 9/15/11
332 C 9/21/11 9/22/11
332 E 11/2/12 11/29/12
2A9 A 1/13/13 1/14/13
2A9 C 1/16/13 1/18/13
I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):
Code:
Item Revision Span
332 C 6
332 E 407
2A9 C 2
I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?
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.
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?
View 1 Replies View RelatedI want query to find duplicate records, i have two field in one table
Cusip and category
cusip and category are many or duplicates
but in one cusip category should be the same if not then provide the cusip which has different category used
like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A
result should be
CusipCategory
123R
123R
123A
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code:
SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType
FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID
WHERE (((tblMaterialSpecifications.ActiveInactive)=-1))
ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg