Queries :: Only Show Records Where Any One Of 6 Fields Is Not Null

Mar 17, 2014

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;

View Replies


ADVERTISEMENT

Queries :: Filter Using Date Range But Show All Records If Text Box Null

Jan 13, 2014

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

View 9 Replies View Related

Queries :: Show All Fields With No Matching Records - One Table

Aug 14, 2013

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.

View 5 Replies View Related

Reports :: Show If One Of Fields Are Null Or Not Check

May 1, 2013

how to run a report to show a Client if one or of the following fields are blank or unchecked(Yes/NO?

Fields
Client
Assessment Date(Null)
Placement Fee Paid(Check Box)(No)
TJ Completed(Check Box)(No)

So basically, if one of the above fields are null or unchecked, please show in the report. I'm sure it's easy but to do, but not up on coding.

View 3 Replies View Related

Null Combo Box; Show All Records

Feb 11, 2006

Hello All,

I’m using a combo box for the criteria for a saved query. Is there any way to show all the records if the combo box value is either “0” or null?

Thanks,

View 4 Replies View Related

Queries :: Want IF Statement To Show 0 When Null Value

Jun 8, 2014

After hours of playing around i finally have my IIF statement working the proplem is now is that when the Value is null it gives a blank value on my Report. How do i get it to show the value of 0 when it is Null. can i use an isNull withen my IIf statement maybe nested what is best practice?

View 6 Replies View Related

Queries :: Forcing Query To Show Null Results

Jan 16, 2015

I have a query that is search for fields in a table that are either

1 - High
2 - Medium
3 - Low

I have a query that counts these and then puts the results into pie charts on a report.

However, when there is no "1 - High" value in the table against a paricualr criteria, obviously the quiery has nothing to look for an does not show a 0 value against the criteria but simply omits it (correctly) form the results.

This does affect the pie charts though which I want to show red for High, yellow for Medium and green for Low.

Therefore I need the query to show all criteria search results include 0 values, or to understand how I can colour code the series rather than the segments on the report.

View 1 Replies View Related

Records With Null/blank Fields Only!??

Jun 8, 2005

I'm working on a db logging replies to a questionnaire. Judging by the responses I am receiving it appears that some sections are not completed at all. I need to be able to include these blank responses when it comes to analysis.
My design splits the questionnaire into sections, each section has a data entry form with its own underlying table. Each table has a primary key (autonumber field) which relates to each organisation that has replied.
If an organisation has failed to complete a section, I still need to create a new record in that section(table), triggering the autonumber field, hence referring back to the organisation.
I know I haven't explained this very well, but if anyone can make sense of what I'm saying and can give me any suggestions on how to make this happen, I would be most grateful.

In anticipation of your replies

Ride

View 4 Replies View Related

Queries :: Calculating With Null Fields In A Query

Oct 2, 2014

My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:

RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")

would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).

View 5 Replies View Related

Queries :: Give All Null Values 0 In Calculated Fields?

Jun 12, 2013

I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.

What I have tried:

1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.

2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.

3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.

The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.

View 14 Replies View Related

Queries :: Unable To Count The Number Of Null Fields

May 16, 2013

I am trying to create a clean database and code to generate a report.

I am trying to count the number of null fields in one of my queries:

However, because of this expression, I cannot carry other fields with it. So the end result looks like:

But I would really like it to spit out the following information:

Total Not Fixed: 241
Department: Sustaining Eng

is there a way to create an SQL query to simply add data: I have tried the following:

Code:

ALTER TABLE qrySustainingEngNotFixed2 ADD Dept TEXT(25)
Insert Into qrySustainingEngNotFixed2 (Dept) Values (Sustaining Eng)
SELECT TotalNotFixed, Dept
FROM qrySustainingEngNotFixed2;

The above isn't working. Keep in mind that I want this is just for display purposes. I pondered making a custom table and then making a Union Query, but I'm trying to do this all in one SQL statement.

View 1 Replies View Related

Queries :: Multi-field Query Not Working When Some Fields Are Null Value

May 22, 2013

Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.

Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.

The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"

It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.

View 14 Replies View Related

Queries :: Convert Boolean Fields To Integers / Blank For Null Values

May 22, 2015

I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)

So something like this :

Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]

However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.

So I tried this :

Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]

However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)

How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?

Only thing I can think of is to use (untested) :

Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))

But there's a number of boolean fields in there, all requiring the same treatment.

View 14 Replies View Related

Queries :: Filter To Show Only The Fields Which Have A Value?

Sep 9, 2014

I 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

View 14 Replies View Related

Queries :: Field Criteria - Cannot Get Blank (Not Null) Records

May 15, 2013

All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?

View 2 Replies View Related

Queries :: Date Range Query And Null Records

Mar 5, 2014

I have two questions, both the same query.

I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?

This is what I have currently in my query

CO_resp_rcvd (date field)

Criteria: Between [Start Date] And [End Date]

(I need null values as well because there will be some if the CO has not responded yet but needs to)

Formula:

This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?

Not sure how to combine it to work - the wrapper is a bus day function

This is what I have so far in the query

CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())

View 6 Replies View Related

Queries :: IIF Condition - Displaying All The Records Including Null

Aug 26, 2013

I want to list all the records which contains data as per my filter form field company.

I have tried this

Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*")

problem is this i want to display all the records including null, but this code only show all records except null

Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*" here we have to add codes for null values also but i don't know how ,"*" & [Forms]![Filter]![COMPANY] & "*")

View 10 Replies View Related

Queries :: Form To Select Which Fields Show In Query

Oct 8, 2013

I want to create form with 10 checkbox, each checkbox is linked to a field "name, Address, TelephoneNumber, BuissinessName" I would like to know if it is possible for the user to check the box and then the field will show in query. if only name is check then only name will show in the query.

I have tried using [Forms]![Formname]![CheckboxName] as criteria but this didn't work it when I had more then one checkbox and when the one check box was not checked nothing showed in the forms.

View 5 Replies View Related

Queries :: Show Only Percentage Of Records

Nov 7, 2013

Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?

View 3 Replies View Related

Queries :: Duplicate Records Do Not Show

Feb 20, 2014

How can I have this query to show no duplicate records.

I would like to only see the first record only of each stock code.

View 2 Replies View Related

Queries :: Only Show Records That Do Not Appear In Another Query

Jul 30, 2013

Currently I have a query, QRY_Test which contains a load of records under the fields "Gift_ID" and "Person_ID". I have another query, QRY_PersonList in which I would like to show all of the "Person_ID"s except the ones returned by QRY_Test.

View 3 Replies View Related

Queries :: Duplicate Records Show Only The Last One

Jun 24, 2015

I have a table and there are so called duplicate records (not entirely duplicate because there is field with the date of creation and other differences in a few fields, but from my point of view they are duplicates.). I d like to make a query which shows me only the newest record in case of duplicity.

View 3 Replies View Related

Queries :: Show TOP N Records In Table

Jun 15, 2014

I have a table that includes a date/time field. I want to query that table and show all but the TOP N records. Since the number of records will always be changing I cant do it using the BOTTOM N records.

View 2 Replies View Related

Queries :: Show All Blank Records?

Nov 19, 2014

how to have our query show ONLY the records with NO DATA in the company field?

View 12 Replies View Related

Queries :: How To Get Multiple Records To Show As One

May 4, 2013

I have a query which is supposed to show the revenue from each advertising source.

PLease see attached in zip file (cant post pics b/c I have less than 10 posts)

As you can see it has revenue amounts for the same source of advertising:

Radio has two amounts, INternet has two etc..

What can I do to only have each form of advertising show up once?

and the revenue amounts summed up for each advertising medium.

View 2 Replies View Related

Queries :: Joining Two Tables - Show All Records

May 31, 2013

I have a query joining two tables. TableInvoice in the query is Product number and Date purchased. TablePrices contains the same fields and contains prices (quarterly) over the past five years.

Joined by PONumber, I want the query to find the price charged for that date. Right now, if the Product number (TableInvoice) is not listed in TablePrices, it won't show. So, 100 Records might return only 80 if TablePrices does not have all of the Product numbers.

Is there a better way to return all 100 records in TableInvoice and show (Blank) price data if the item is not in TablePrices?

View 1 Replies View Related







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