Queries :: Modifying A Query To Return Only Instances Where There Is More Than 3 Records

Sep 26, 2014

I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.

Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.

I am thinking along the lines of DCount? but how it would be phrased in the query?

View Replies


ADVERTISEMENT

Queries :: Query To Return Records For A Given Year

Feb 3, 2014

I'm trying to create a query to show me records for a given year. The issue I'm having is that each record has (4) dates fields and each record can contain null values.Is it possible to do this in a query with data like the example below?

Field 1 ID (1), Field 2 Date (12/22/2012), Field 3 Date (2/06/2013), Field 3 Date (Null), Field 4 Date (Null)

In this example I would want 2013 data but would need to be able to search any year. I could also have dates in all (4) fields

View 3 Replies View Related

Queries :: Modifying Query Output

Feb 13, 2014

I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".

View 5 Replies View Related

Queries :: Query That Will Return Records From A Table That Have Related Records In Another Table

Mar 4, 2015

I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.

View 3 Replies View Related

Queries That Return No Records

Jan 31, 2006

Hi All,

I am wandering if it possible to check programatically whether a query returns a NULL result?

I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?

Thanks in advance:D

Cheers,

Karv

View 2 Replies View Related

Queries :: Return All Records With Last / Max Date

Apr 10, 2013

My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:

Field A Field B Field C
123456 AAAA 1/8/13
123456 BBBBI 1/8/13
123456 CCCC 1/8/13
123456 DDDD 1/8/13
123456 EEEEEE 3/10/13
123456 FFFFFF 3/10/13
123456 GGGG 3/10/13
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13

The desired results would be to return all records with the last/max date, so yield:

123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13

I have tried the max & last functions, yet no success.

View 1 Replies View Related

Queries :: Return Most Recently Created Records?

Mar 3, 2015

query to return the most recently created records in a composite key field, and only the most recent ones.

there is a screenshot of the composite key field attached

I want to get te records with the highest InvoiceID, there can be multiple records with the same InvoiceID and I want to get all of them.

Using the screenshot as an example I need to query to return the last two records as they both have the Same, highest InvoiceID.

View 6 Replies View Related

Queries :: Return Records With No Data In A Cell?

Apr 4, 2015

I've got a database of club members with names, addresses, contact info etc.

I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.

I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.

View 1 Replies View Related

Queries :: Return Records With Partial Match

Feb 14, 2014

I understand that a query can accept wildcards in the search criteria but this is different.

I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".

I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.

In other words, the record entries themselves need to be treated like wildcard entries.

View 7 Replies View Related

Queries :: Return Result When No Records Are Found?

May 2, 2013

Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?

Otherwise is there a way to make a master query ignore sub-queries if they return no records?

Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.

These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.

Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.

The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).

Here's an example of my sql statement for the modal queries.

Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate

HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))

ORDER BY Count([Toolbox Talks].TrendID) DESC;

- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.

View 6 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related

Queries :: Return All Records Shared Between Two Tables LESS THAN 3 TIMES

Nov 1, 2014

I am trying to write an access SQL query that returns all records shared between table1 and table2 LESS THAN 3 TIMES.

application has 4 tables: Patients, AnnualPhysical, Doctors, and PatientPhysicalDoctors.

each patient has an annual physical (once a year), and chooses one doctor per annual physical.

each doctor can only perform three or less annual physicals per year.

It is a small application with less than 1000 records per table.

the query will allow the patient to choose an annual physical doctor once a year from available doctors.

Patients is one-to-many to AnnualPhysical;
AnnualPhysical is one-to-many to PatientPhysicalDoctors;
Doctors is one-to-many to PatientPhysicalDoctors.

query for all records NOT shared between Doctors and PatientPhysicalDoctors is:

SELECT *
FROM Doctors
WHERE NOT EXISTS( SELECT * FROM PatientPhysicalDoctors
WHERE Doctors.DoctorID = PatientPhysicalDoctors.DoctorIDFK );

View 3 Replies View Related

Queries :: Return Records With Both Empty And Populated Field?

Apr 8, 2013

I am trying to create a query that returns records whether a field has data or not...

There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.

I would like to do this in the criteria box in the query.

The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...

View 2 Replies View Related

Queries :: Return Records If One Field Is True And Another False

Jul 23, 2013

I am trying to create a query pulling from several tables. I will use the example below to illustrate what I'd like to do.

I have a field called 'Acc_Num', one called 'Stat_Code', and a third called 'Cat_Code'.

Each 'Acc_Num' can have multiple records because there are multiple Stat_Code and Cat_Code values.

What I want to do is isolate just the Acc_Num records where Stat_Code = 1 and Cat_Code equals A1.

I also want to isolate the Acc_num records where the Stat_code = 1 and where Cat_code does not exist.

View 7 Replies View Related

Queries :: Possible To Have Multiple Instances Of Same Provider ID

Feb 9, 2015

I have a table called providers with the following 3 fields:

Provider ID
Cancellation Reason
Visit ID
Date

It is possible to have multiple instances of the same provider ID, cancellation reason and visit ID on the same date. I'm trying to count just 1 instance based on visit ID if there are multiple instances of the visit ID and reason on the same date. Select distinct doesn't seem to do the trick.

View 1 Replies View Related

Queries :: Table With Duplicate Records - Return Distinct Data For Each Client

Jan 17, 2014

I have a table with duplicate records (which is ok) and I want to return distinct data for each client.

It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.

I.E

SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient

Works fine with only the Distinct records for each client returned

However

SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient

Now returns Duplicates!

Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?

View 5 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

I have a table which includes a start date field and completion date field for housebuilding.

I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.

It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.

View 7 Replies View Related

Count Instances Of Records At Each Hourly Interval Within A Selected Date Range

Sep 30, 2014

I've got a single table with multiple fields, three of which are a date field ('DDate'), a time field ('TimeET') and a unique identifying field ('Unique Call Key').  I'm attempting to write a query in the QBE that will allow me to count the number of instances of 'UniqueCallKey' for each hourly time interval (7:00:00 AM - 7:59:59 AM, 8:00:00 AM - 8:59:59 AM, 9:00:00 - 9:59:59 AM, etc) for any selected date range (BETWEEN 'DDate'(1) AND 'DDate'(2)).  When I try to simply use the Count function on 'UniqueCallKey' as an Expression and 'TimeET' with 'Like '7:*AM' as a Where criteria then do the same with another instance of 'UniqueCallKey' and 'Like 8:*AM' as the criteria for a second Where criteria for 'TimeET' the query returns an empty set.  What I'm trying to accomplish would be a column of dates, a second column that counts the number of instances of 'UniqueCallKey' at the 7AM interval for each date in column 1, a third column that counts the number of instances of 'UniqueCallKey' at the 8AM interval for each date in column 1, etc to a final column for counts at 7PM.

View 3 Replies View Related

Query That Return No. Of Records In Run Time

Sep 20, 2005

Hi all,

May I know some to construct any functions or query string that can track the no. of records that mean certain criteria in the run time.

Basically I want to track the records in a subform (in datasheet view). Should the no. of records is 0, then I can disallow user from saving it onto the table for better record keeping.

Thanks !!

View 1 Replies View Related

General :: Calculated Fields - Reading And Modifying Records

Aug 2, 2012

I am new to Access and this is likely a very elementary question. I recently created a database to track status of projects including progress and funding. The table contains several fields that record various aspects of funding. These fields are added (very simply) and create a calculated field that is a summation of those fields. The table currently contains approx 60 records. The calculated field functions for 2 or 3 records and will not calculate further. I have tried this in a form as well as a query. The form will change the same number, 2 or 3 records. When the query is run the result shows only the 2 or 3 records. What I am doing that is only reading and modifying those records?

View 3 Replies View Related

Query To Return Records Containing 2 Characters In A Field

Sep 5, 2013

I have a field in Access 2010 called Section Number which generally holds two characters. The characters can be numbers, letters, or a combination. Examples are:

01
X1
KA
40

Sometimes this field holds three characters, such as:

01a
02b

I want to write a query that returns only the Section Numbers that contain two characters. The instances where the field holds three characters are too numerous and changeable to use a parameter that says, for example: not "01a" and not "02b"

Is there a way to write a parameter that means "not Section Numbers of three characters" or "not Section Numbers of more than 2 characters"?

View 2 Replies View Related

Queries :: DCount To Add Sequential Numbers By Group Only Works In Some Instances

Jun 24, 2014

I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.

This query works, on anything that starts with the letter D or later... A-C will not work.

Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.

This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.

Code:
SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;

The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.

Running Access 2013.

View 2 Replies View Related

Totals From Instances (not Of Instances)

Jul 21, 2006

I've come across an interesting problem a colleague has been trying to solve and even though I can 'see' what needs to be done and could probably program it on our mainframe, I'm really stuck on how to achieve the same results in MS Access

I'll do my best to explain the problem by showing an example of how the data exists in the DB and also how it 'should' be output on a report.

DATE OPERATOR COUNT

29/06/06 - Oper A - 32
29/06/06 - Oper B - 12
29/06/06 - Oper A - 10
29/06/06 - Oper C - 13
29/06/06 - Oper B - 10

The output needed however should read as follows

29/06/06 - Oper A - 42
29/06/06 - Oper B - 22
29/06/06 - Oper C - 13

Setting up a date selection shouldn't be a problem, its just adding the totals on each instance of each operator into one figure without ending up with a total of the number of times an operator appears on a date.

Any help and guidance about how best to approach this problem (macro/query/script) would be appreciated.

View 2 Replies View Related

Form And Query To Return Records Between Certain Date Range

Oct 15, 2013

First, I am trying to get a query to return records between a certain date range. In the form I have DateFrom and DateTo unbound text boxes from which faculty select the date range. I know I have done this before, but I cant figure out how to create a field name and write the criteria for the source query in design mode!

I tried [Form]![FormName]![DateFrom]<[Form]![FormName]![DateTo] in the criteria but I can't seem to write a valid field name that doesn't alter the criteria in some way...

Second, in the same form, faculty enter the StudentID for the student they wish to get records for. How do I write the code to show a msgbox when there is no such ID in the event they enter an StudentID incorrectly.

View 1 Replies View Related

Write A Query That Will Return Records From Multiple Tables

Dec 19, 2012

I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.

The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.

View 14 Replies View Related

Queries :: Max Query To Return One Row Only

Mar 20, 2013

I have a query which is a max sum of sales for several sales reps. (If that makes sense?)

Sales Rep, Total Sales
1, 100
2, 200
3, 500
4, 50
5, 150

I need to create a report which delivers the sales rep with the most sales and only that sales rep

e.g.

3,500

View 7 Replies View Related







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