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 Replies


ADVERTISEMENT

Queries :: SQL Will Only Return Maximum Of One Result

Aug 29, 2013

Why this SQL will return only one query maximum?

DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)

A picture of the table is included.

View 1 Replies View Related

Forms :: Search Form Did Not Return All Data Records For That Particular Result Set

Jun 3, 2013

All using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.

Code:
Like [frm]![frmMyform]![MyField]

It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.

View 13 Replies View Related

Queries :: Return Value Greater Than The Result Of Another Column?

Jul 10, 2015

Basically in the query I need a Collumn to only output the greatest value that is given within the sub query. If that makes sense?

Eg. Tenancy start date must be the highest date against the date the property was added back to the system - for post development properties.

At present the it's returning previous tenancies from pre development.

View 3 Replies View Related

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

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.

View 1 Replies View Related

Queries :: Return Result When Enter Month As Search Criteria

Jun 15, 2015

I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.

I have attached two example for your reference.

View 9 Replies View Related

Queries :: Running Query In Background And Return Result In Control

Sep 11, 2013

I have several queries that perform an audit on data. I want to create a form that has two buttons, multiple labels and textboxes. When the first button is pressed, it runs all queries and returns a count of the records within each query into the corresponding textbox. Then I want a a button that will clear all textboxes. I don't want to see the queries, I just want them to run in the background and the count results to be populated in the textboxes.

So for example, it would return:

Check1: 3
Check2: 6
Check3: 2
Check4: 0

View 3 Replies View Related

Queries :: Calculated Field - Return Zero If Negative Result (Access 2007)

Oct 10, 2014

I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.

The only way I know how to do this is by an IIF statement :

Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...

(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)

Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.

View 2 Replies View Related

Queries :: Records Not Found

Aug 5, 2015

I've created a query with the following SQL:

Code:
SELECT Table1.[Material Key], Table1.[Material Name], Table1.[Info]
FROM Table1 INNER JOIN search_MaterialKeys ON [Material Key] = search_MaterialKeys.[MaterialKeySearch];

So at the moment, users paste material numbers in the search_materialKeys table and the query is created with the necessary information. If the number they paste into the search_materialKeys table is not found in Table1 then the query doesnt return anything. I need the query to list all the entered values in the temptable in the query so that users know which of their inputs were not found. Is there any way to do this?

View 1 Replies View Related

Queries :: Make Query Include All Records Even If No Data Found

Jul 10, 2014

I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.

Staff name etc is in tblStaff
Staff shift details are in tblShifts linked via staffID

tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.

The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.

View 7 Replies View Related

Modules & VBA :: Looping Through SQL And Skip If No Result Set Found?

Jan 15, 2015

When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.

What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.

For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.

Then, in the loop vba code:

after item 1 is run, do an export of the results.
after item 2, do an export.
etc.

Code:

select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME],
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)

[Code] .....

View 1 Replies View Related

Queries :: Sum Records And Put Result In Next Record

Mar 27, 2013

I have record like below:

id s q
1 11 11
2 14 15

I want to sum record 1 and record 2 and put them in next record.

View 2 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 :: 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 :: 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

Functions That Should Return Same Result But Do Not

May 8, 2015

I have two functions both should have the same results but they do not.

Public Function DefaultGreeting() As String
On Error Resume Next
DefaultGreeting = "Dear " & [Forms]![frm_contacts]![Dear] & ":"
End Function

Public Function DefaultBodyText() As String
On Error Resume Next

DefaultBodyText = [Forms]![frm_e_mailing]![mess_text] / this equals this just pulled from a table "Dear " & [Forms]![frm_contacts]![Dear] & ":"
End Function

View 5 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 :: 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 14 Replies View Related

Return Result Based On Percentage

Aug 8, 2012

I have a table that has 3 columns: Unique number, Date, and Results

I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?

View 1 Replies View Related

How To Return An Array From A Function ? And Handle The Result After ?

Jan 29, 2008

Hi there !

I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.

So I try this :

*here is my function

Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset

requetteSQL = "SELECT libelle " _
& "FROM operation;" _


rst.Open requetteSQL, cnn


get_nom_operation = rst.GetRows

End Function

* and here is the code that call the function

Dim res As Variant

Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1

For i = LBound(res) To UBound(res)

Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With

'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1


Next

But it doesn' work, and I don't know why...

The error doesn't show where the code is stoped, but only show the call to display this form.

someone could show me some way to find the solution please ?

Thanks a lot.

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







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