Queries :: Count And Sort Criteria - Returning On Blank Values

Nov 18, 2013

I have a list of employees and sort criteria. for example

empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F

The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0

E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0

I can get it to return:
empID......Count
1234...........2
1236...........3

using Count and the criteria Where Criteria="T" but not returning zeros.

View Replies


ADVERTISEMENT

Forms :: ComboBox Doesn't Display Some Values - Returning Blank

Feb 10, 2014

In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.

The SQL used for the combobox is as follows..

Code:
SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact
FROM tbl_Contacts
WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]))
ORDER BY tbl_Contacts.ContactForename;

Why some contacts work and some don't ??

View 7 Replies View Related

Queries :: Returning A Blank Or Null Value

Jun 18, 2015

In a query, how do I return a blank or null.

IIf([Field1]=[Field2],NULL,[Field3]

They are all numerical values that I need to format into percentages however Field3 could contain zero so I can't replace Null with zero. I simply want to return nothing if Field1 is equal to Field2.

View 3 Replies View Related

Queries :: Query Returning Blank Columns

Jun 24, 2013

I've set up a simple query that returns 6 columns of data.This query then shows on a sub-form elsewhere in my DB.The problem is that the query always appears with a horizontal slider. Allowing the user to scroll across to see the other columns in the query...The problem here is that there ARE no other columns of data. They are just empty. I want to restrict the output of this query to ONLY show the 6 columns that i have specified.

I have tried deleting the columns in Query Designer, then save the Query. But every time i re-open it half a dozen or so blank columns are stuck on the end.

View 4 Replies View Related

Forms :: Returning Values From Tables Based On Criteria

Jun 27, 2013

I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.

Table 1
EmpID Name
1 EMP1
2 EMP2

Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B

Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18

View 2 Replies View Related

Queries :: Pulling Records Based On Blank / Non-blank Criteria

Jul 18, 2013

I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..

See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.

How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...

View 3 Replies View Related

Queries :: Search Not Returning Results With A Blank Entry

Oct 25, 2013

I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.

This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.

View 3 Replies View Related

Queries :: Using IIF For Sort Criteria

Jul 23, 2013

I am creating a query to select and sort events whose payments are overdue, prioritizing those that are most overdue at the top. The catch is that the due dates for payment varies based on several conditions and criteria.

Here's my end goal of the sort order (it's not in SQL, I'm just describing it):

1st step- If [Program_Code]="BD" Or "GT" Or "SG" Or "SC" Or "PR", then the sort criteria is [Date_of_Event] < Date()

2nd step- If [Program_Code]="WE" Or "KD", then the sort criteria is [Date_of_Invoice] < Date()

3rd step- If [Program_Code]="ZM" And [Cost_Category]="Full Price" Or "Discount", then the sort criteria is [Invoice_Date]+30 Days is < Date()

4th step- If [Program_Code]="BD" Or "PR" And [Deposit_Paid] Is Null, then the sort criteria is [Invoice_Date] < Date()

I'm not sure how to go about this but I have a feeling it involves an iif statement in the SQL and/or breaking it into smaller queries that are then consolidated into a "master" query.

View 14 Replies View Related

Queries :: Listbox Filter Not Returning Empty Values

Jun 14, 2013

I have a form in which users can enter data in several textboxes to filter the listbox below it, this works great except for the fact that when a record lacks certain data it doesn't show up

Basically there are 4 filters, one for the name/id which works great, as the entries without id's show up just fine but this filter needs to be checked against the other 3 filters, for their group, education and type.

Lets use group as an example.

When a student has no group it should only show up when the group filter is an empty string. AFAIK the wildcards should see to that.

Currently however, when a student has no group, it doesnt show up at all unless I remove the | Like '*' & [groepFilter] & '*' | part from the query.

I've never used complicated WHERE's like this so it might be something very simple. I think I could get it to work using VBA and modifying the rowsource of the listbox from there but this would be a lot easier and I'm curious as to why this doesn't work.

The SQL:

SELECT Student.Studentindex, Student.studentid, Student.studentvn, Student.studenttv,
Student.studentan, Student.Groepcode, Opleiding.opleidingsnaam, Opleiding.type
FROM Student LEFT JOIN Opleiding ON Student.opleidingid = Opleiding.opleidingid
WHERE (((Student.studentid) Like '*' & [naamFilter] & '*') AND ((Student.Groepcode) Like '*' & [groepFilter] & '*')

[Code] ....

View 2 Replies View Related

Queries :: Calculating And Returning Values From External Database?

Jan 2, 2014

I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.

I have written a query that pulls to fields of data together but cannot get the two to calculate.

Within SQL server management studio I use the following query to give me a numerical response.

SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE

Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?

View 9 Replies View Related

Queries :: Blank Values When Duplicated?

Mar 21, 2013

I'm trying to execute a query that will delete values in a certain column, but I'm getting no positive result.

Quote:

--- Table1 ---

Original:
col1 col2
-----------
text1 Stext1

[Code]...

View 7 Replies View Related

Queries :: Ignore Wildcard If Criteria Is Blank

Dec 16, 2014

In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.

The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.

I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".

I tried using the criteria:

Code:
Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"

Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.

Code:
IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")

The full sql of my query is:

Code:
SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact,

[Code] ....

View 12 Replies View Related

Queries :: Blank Query After Setting Criteria

Feb 4, 2014

I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.

In my query I have a name field where I put the criteria: Forms!Formname.Textboxname

By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.

Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.

View 7 Replies View Related

Queries :: Query Criteria - All Results If Blank

Aug 7, 2013

I use this on most queries where I need to return all results if the form field is left blank. Works like a charm every time...

Like [Forms]![frm_main_menu]![Week] & "*" Or Is Null

except for this time..I need to filter by week number (52 weeks in a year)...problem is if I enter week "1" I also get weeks "10, 11, 12, 13, 14, 15, 16, 17, 18 and 19".

View 4 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 :: Omit Records With Blank Field - Criteria With IFF Statement And Checkbox

Apr 18, 2013

I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.

What I'm trying to do is:

I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.

Here is what I have:

IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)

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 :: Group And Count On Multiple Criteria

Jun 4, 2015

I have a table similar to the following

area q30 q40 q60
A 2 1 3
B 1 1 3
A 2 1 3
A 1 1 3
C 1 1 3
C 1 1 3
A 1 1 3
A 1 1 3
B 1 1 3
C 1 2 3
A 1 1 3
B 1 2 3

Grouping by the area and using the criteria

(Q30 = 1 AND Q40 = 1 AND (Q60 = 1 OR 2 OR 3))

I would like to see a table similar to the one below counting those which fulfils the criteria mentioned above

Area COUNT
A 4
B 2
C 2

View 2 Replies View Related

Queries :: Count Duplicates Based On Set Of Criteria

Aug 27, 2013

want my query to count only records that are not duplicated based on a set of criteria.I have a table with customer IDs and Call dates and the employee ID. I want a count of the call dates for each customer ID for each employee. However I only want to count as one occurrence if multiple calls happened on the same day. For example

Cust_ID Call_Dt
12315 8/1/2013
12315 8/1/2013
12315 8/1/2013
14253 9/1/2013

Customer ID 12315 would only count a 1 call entry instead of 3 for the date of 8/1/2013

View 3 Replies View Related

Blank Field Returning Error

Dec 8, 2011

I have some code that is below!

Code:
Public Function DegreeChecker(AGShrs As Integer, ASBAhrs As Integer, ASCJhrs As Integer, AAhrs As Integer) As String
Dim Degree As String
If ((AGShrs = 0) And (ASBAhrs = 0)) Then
Degree = "ASBA"
ElseIf ((AGShrs = 0) And (ASCJhrs = 0)) Then
Degree = "ASCJ"

[code]...

The problem is, the information that is populating the query which is running this code may not be there. As in some of the fields, AA, AGS, ASBA, and ASCJ might be left blank. Unless these four fields are populated by something, it returns #Error instead of the specified number.Is there a way to say, If variable is blank, ignore and continue on to check if the next thing is 0, or if none are 0, state Potential.The information in this query is to be exported out to excel to be used as a Mail Merge file.

View 8 Replies View Related

Query Criteria With Blank And Non-blank Text Records

Mar 13, 2007

OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.

I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).

I am using a form to query the table...no problem. The form has text boxes the user filter down the data

The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!

Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)

what else can I do?


Thanks

View 5 Replies View Related

Queries :: Select Count Duplicate Values By ID

May 23, 2013

I am using Access 2010 on a Windows 7 laptop. I need a query to provide a list of ID's that have more than one occurrence of IDandDate combined but haven't been successful getting past syntax errors.

Using this table structure as an example:
ID - defined as text field
Date - defined as date/time field
TestValue

This legacy table contains a record for each test. The table should be unique based on the ID and Date combination but was never restricted to that rule. I am converting to a new table but need to identify the duplicate entries so they can be addressed by business folks.

Access 2010 query.

View 1 Replies View Related

Queries :: Count Unique Values Without GROUP BY

Sep 15, 2013

I am currently using the below code to query values that have a unique "OverrideDescription" and where by the "Upload Date" is always the latest.

SELECT t1.*
FROM [DaisyServiceRates-Amended] AS t1 LEFT JOIN [DaisyServiceRates-Amended] AS t2 ON (t1.OverrideDescription = t2.OverrideDescription) AND (t1.[Upload Date] < t2.[Upload Date])
WHERE t2.OverrideDescription IS NULL;

However I also need to be able to do a unique count of the [OverrideDescription] field.

I have found methods whereby I can do use, but they all use a GROUP BY function, the issue being that when I use this method it prevents be from updating the values saying "recordset not updateable".

Any method that does not use the GROUP function. I did also try preforming the COUNT on a separate table and doing as JOIN, but this also prevented updates to the date.

View 1 Replies View Related

Queries :: Count Records Across Multiple Columns With Certain Criteria

Jun 10, 2013

I've got a table with 23 columns. Column 1 is the ID row which has a unique client ID in it. Then we've got a column called 1st_Reason and one that goes with it called 1st_Transfer_Date. This pair repeats for 2 through 10. Each of the Reason columns can have a set value, for example "First Processor".

What I need to do is create a date with months on the Y axis and the 5 different reasons on the X axis. I need to count the number of "First Processor" across all 10 Reason columns for each month. I would need to repeat that for each other reason type, but if I can get one to work I can simply change the reason type.

I'm pretty new at Access queries so I don't know how to use a count statement to pull from all those queries AND get the corresponding date to be in range.

View 3 Replies View Related

Queries :: Count Number Of Records With Values Within Specified Ranges?

Oct 1, 2014

How do I count the number of records with values within specified ranges?

My database contains a field with values ranging from say 1 to 1000. I need to write a query to show count the number of records with values in ranges I specify in the query.

I'm looking for output such as

<100 - 50 records
101 - 500 - 30 records
501 -1000 - 50 records

View 2 Replies View Related

Queries :: Applying Multiple Count Functions With WHERE Criteria Without Affecting Others

Dec 11, 2013

Any way to use multiple count functions in a query with their own individual filters without affect the others?

For example:

SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2]
FROM [E&I Table]
WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV"))
GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;

I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...

View 6 Replies View Related







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