How To Query Data For Specific Criteria - Criteria Help

Aug 20, 2007

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%

View Replies


ADVERTISEMENT

Queries :: Using Criteria To Pull Data From Specific Table

Sep 16, 2013

Is it possible to have a query that uses criteria to pull data from a specific table?

For example: IIf([Result]'"Negative",(tblNegative goes here),IIf([Result]="Positive",(tblPositive goes here].

The tables are just text, but the query would be too long if it was used.

View 2 Replies View Related

Queries :: Mailing List For Specific Month - Date Criteria Query

Jun 11, 2013

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 Related

Setting Query Criteria To Be 'blank' Depending On The Criteria Of A Combo Box

Oct 21, 2006

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

View 5 Replies View Related

Getting Query Criteria To Select All Records Or Specific Records In Query Design Section?

Jun 16, 2014

How can I get a Query Criteria To Select All Records or specific records in query design section.

I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.

View 2 Replies View Related

Specific Criteria

Mar 21, 2006

I have a table & form which has a field called address. I need to create a query that lets me select all addresses which may contain say George Street, this includes lock ups and individual addresses for that address, i hope you will understand and be able to help

View 1 Replies View Related

Field Specific Criteria

Jun 14, 2006

I have a query drawing information from two tables. The first table has information about a product, and the second table holds all the faults found per product. So any one product can have anything from 1-14 faults. I have created a report from the query, but the Fault item that I put on it only ever shows the first fault for that product. Is there some way of fixing this at report level, or on the query?

The tables are connected through the ID field, but the primary key for the faults table is another autonumber. Ex:

Primary Key___ID___Fault
___1__________ 1____fault 1
___2__________ 1____fault 2

That's two faults for the same product. Maybe I could create new fields in the query like Fault1: [Fault] with criteria set to primary key = 1? And one for each fault? But when I do it, it sets that criteria to the entire query, so the results end up blank because the "primary key" field doesn't exist on the product table.

I guess what I'm asking is can you set a criteria for just one field of your query to follow? Or can I resolve this issue in the report design?

Many thanks,
Tom

View 1 Replies View Related

Search For Queries That Use Specific Criteria

Jun 6, 2005

Hi,

Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.

Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.

I hope my explanation is clear.
Thanks upfront for any suggestions!

BJS

View 4 Replies View Related

Fields Get Cleared Out When Specific Criteria Has Been Met

Oct 1, 2012

We have a PHP website that utilizes an Access 2003 SP3 database. This website is used as an authorization process for a business process in our company. On the website, 8 different people must "sign off" on the process. This sign off process simply consists of typing your name and date into two different text boxes, as well as a third drop-down menu for "APPROVED" or "DENIED". These, of course, correlate to their respective fields in the Access database.

Within the past year or so, the 8th approval section was added. Since then, there have been sporadic issues where all of the approval fields would simply be erased and everyone has to go back to the form and "re-approve" their fields. I believe we have narrowed the issue to when the 8th person signs off before the 7th person. However, the approvals are not cleared every single time this happens, but this condition exists every time data is cleared. So my question is, would this be an issue related to access, or with the coding of the website?

View 3 Replies View Related

Flag Specific Criteria In A Field

Feb 6, 2015

I'm using MS Access 2010, how to flag a criteria in an age field like, Pop-up if age is under 14??

View 3 Replies View Related

Using Query Data As Another Queries Criteria

Sep 14, 2007

Cannot seem to find an answer to this, but please point me in the correct way if you know of one!

Quite simple i think, but blank mind at moment!

How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.

Thanks in advance,

Emily

View 5 Replies View Related

Using Table Data In Criteria Of Query

Nov 5, 2007

Hi

I am trying to set up a number of queries to return completed jobs with a specified time frame, usually monthly. So rather than have to type the same dates in arround 20 times I tried to use the data from a table in the Criteria. This did not work any suggestion would be much appreciated.

Attached is a screen shot showing a fragment of the query and the dilog box I get when it runs.

Thanks Marcus

View 3 Replies View Related

[Query] Help With Criteria To Filter Certain Data

Feb 1, 2008

Hi there,

I was wondering when I am filtering out data from a table, is there a way for me to filter out specific lines (as in, if I have a 100 line datasheet, can i choose to filter out lines 5, 10 and 20 out of the query)?

For instance:

Line Name Address Phone Number
1 A1 fjlafs 453453454
3 A2 fsdfsd 343534534
4 A3 gsdgsdg 354543534
5 A4 gsdgsdgsd 345345345

I want to make it so that lines 2 and 4 are NOT included in the query when I click "RUN".

Thanks in advance!

View 4 Replies View Related

Criteria Based On Date Results In Specific Value

Dec 14, 2013

I don't know what it would be called or even how to start doing it in access other than it requires a criteria here is what I'm trying to make happen with access

name date yes/no criteria would be set to date > 180 days then it would equal value of No < 180 days then equal value Yes

If i set a today's date value on the database and then criteria is based of the value in the date box with the above information how would this be done.

View 4 Replies View Related

Data Type Mismatch In Query Criteria????

Nov 4, 2006

Query1:

Src: Table1 joined Table2

ID (Type Text)
Title (Type Text)
Remarks(Type Text)
Formatted: FormatTitle([title],[Remarks])
Expr1: InStrRev([Formatted], "~")


public functionFormatTitle(ByVal sTitle as String, ByVal sRemarks as String) as String
'do process code here very complicated an long, but works find in the end
'creates a Multi-String delimited by | (pipe)
end function

The above works, and Expr1 does give an accurate value for the position of a "~" (tilde) in the string Created by the FormatTitle() function.

However, If I put a Criteria >0 on Expr1 it asks for the value of the [Formatted] field as if it was a parameter. If I put a criteria for Formatted: Like "*~*" I get a Data Type Mismatch in Query Criteria

Query2:
Src: Query1
Title (Type Text)
Remarks (Type Text)
Formatted(Type Text)
Exr1 (Type Number) criteria >0

This Query Also produces the Data Type Mismatch in Query Criteria
pardon me, but WTF? If it isn't a STring, than InStrRev() should produce an error, not an accurate response, and if InStrRev() produces a number why can't i compare it to 0 (zero)? This is indubitably messed up that I'm getting this error. There is no data type mismatch, on either of these tests, one is a string and I criteria-limit it by a string operation, the other is a number and I criteria limit it by a number, WHAT IS GOING ON!!!

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner - The Frustratedly Confused

View 2 Replies View Related

Using Current Data On SubForm As Query Criteria

Feb 6, 2005

I have a Form> [Management]

I have a SubForm> [Management_History]

There is data in the [Year] field and the [Season] field.

I placed a button on the subform to create a report based on the data of the current "Sub Record" .

Basically, I want to use the data in the subform to create a small "Lookup" report.

OK:

I have the Management Form
I have the Management History SubForm
I Placed a Button Called PRICE LIST
The button kicks off a Macro that previews my PRICE LIST REPORT
A Query is the RECORD SOURCE for my PRICE LIST REPORT.

In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.

I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.

What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??

I tried the following:

Like [Forms]![Management]![Management_History].[Year]

Like [Forms]![Management]![Management_History].[Season]

View 2 Replies View Related

Queries :: Loosing Data When Put Criteria In Query

Nov 5, 2014

I have a data base with 6 tables

stock
stocklocation
location
orderdetail
orders
Customers

i have created a query to do the calculation of how much stock came in went out and is still on hand .the problem im having is that it shows me the 0 total for product with no stock in it when i put my criteria as >0 in my goods on hand field it show me no data in the query.

my calculation fields is as follows GoodsOnHand:[GoodsIn]-[GoodsOut]
Goods in gets calculated as follows GoodsIn:Nz([SumOfGoodsIn],0)
Goods out get calculated same way as goods in.

The Reason i have a sum of goods in is because i have to calculate the quantity in another query because it is spread over lots of locations.

View 1 Replies View Related

Query Criteria - Capturing Data In A Certain Format

Mar 19, 2012

in a query, I would like to capture data that is in the following format:

123456 (6 digits) AND
A12345 (1 letter and 5 digits) AND
AB1234 (2 letts and 5 digits)

The letter could only be at the beginning of the abbreviation.

View 1 Replies View Related

Modules & VBA :: Open Specific Publisher Document If Criteria Is Met

Jul 16, 2014

The code below allows me to open a publisher document that is already merged to the current record. What I need is to allow the user to open the document according to the group they are reviewing. How to accomplish this?

For example:

IF Group = A1 OR B1 THEN OPEN AB_1.pub
IF Group = A2 OR B2 THEN OPEN AB_2.pub
IF Group = A3 OR B3 THEN OPEN AB_3.pub

Dim pubApp As Publisher.Application
Dim pubDoc As Publisher.Document
Set pubApp = New Publisher.Application
Set pubDoc = pubApp.Documents.Add.Application.Open("c:Publishe rAB_1.pub)
pubDoc.ActiveWindow.Visible = True

View 3 Replies View Related

Queries :: Criteria Containing A Letter In A Specific Place In Sequence

Oct 11, 2013

How to sort a field of data in a table that contains the letter S that is always in a specific place in the sequence? For Example:

13123S0-BAX
13124E1-ARZ
13122X2-THX
12134S0-GAB

View 2 Replies View Related

Forms :: Pulling Data From A Query Based On Criteria

Apr 13, 2013

I am having a problem pulling some data from a query to populate text boxes in a form

Text27 = DLookup("'SumOfSumOfDocCount'", "SumTotalPerf", "DateReceived=" & Forms.Tracker.Text23.Value & "AND 'BookedInID'=" & Forms.Tracker.BookedInID.Value)

I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.

View 8 Replies View Related

Modules & VBA :: Query Data Using Multiple Criteria From A Form

May 29, 2014

I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?

I have tried setting VBA code as follows:

If Me.Combo1 = Null Then Me.Combo1 = "*"

However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.

View 13 Replies View Related

General :: Query Criteria - Data In Fields Are Numbers

Nov 7, 2014

I have a query criteria where the data in the query field are numbers.

The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.

Code:
Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null

Also tried

Code:
[FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null

Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox

Code:
Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2]
Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5]
Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]

[Code] .....

View 5 Replies View Related

Using Form Data In User-Entered Query Search Criteria

Dec 20, 2006

I am attempting my first access database which tracks blood components in our medical facility. When a component is issued to a patient there is a button to push which links to a report that runs a query. The user must enter the unique key for the component at a promt, which ensures the report generated will be for that component only. My question is if there is a way to cpture and use that key automatically when pressing the button. If I need to clarify something, please let me know--I may not have the Access developer lingo down yet...

View 2 Replies View Related

Queries :: Limit The Type Of Data That Can Be Entered In A Query That Has A Like Criteria?

Jan 18, 2014

Is it possible to limit the type of data that can be entered in a query that has a LIKE criteria?

( Like [Enter Data] & "*")

limit to two digits or any number of digits, or limit to numbers only or letters only. .

View 2 Replies View Related

Queries :: Date Formatting Criteria - Query Returns No Data

May 7, 2015

I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:

>=#06/04/2014# And <=#05/04/2015#

The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following

Format([Table].[Open Date],"yyyy-mm-dd")

The query then returns no data.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved