Can Count Return A Zero?

Apr 10, 2008

I have three queries that count non-compliant records in each of three catagories per month. One catagory has records for both Jan and Feb and returns the rersults as follows:
Jan 3
Feb 4
The other two vatagories however have no non-compliant records for January so they return as follows:
Feb 2
Feb 1
I wanted to tie the three queries together in the form for reporting:

Cat 1 Cat 2 Cat 3
Jan 3 0 0
Feb 4 2 1

Is there any way for the queries to return a zero for any month with no records rather than not returning the month at all? Thanks for any help.

View Replies


ADVERTISEMENT

Getting Count To Return Zero

Jul 20, 2006

Is there a way while using the count query to make it return zero when using the criteria (not like "N") instead of a blank datasheet.

View 1 Replies View Related

Count Function To Return Unique Records

Sep 20, 2005

Not sure if I worded my subject correctly. Here's my problem.. I've used query to combine 2 tables of data..

Here are my fields..

I've assignment_num field, project_num field, project_name field, emplyee_name field.

here are my data..

assignment_num field: 1001, 1002, 1003, 1004, 1005
project_num field: 20,20,15,16,15
employee_name field: tom peter, tom peter, tom peter, julie, sally

What I want is to display the employee name with 2 projects or more...

What i have done?
I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..

pls help.

View 13 Replies View Related

Trying To Figure How To Count How Many Times A Word Has Been Used And Return 0 If Not

Sep 23, 2005

Trying to figure this out, but I’m not getting anywhere.

I have 2 tables one is just a list of options that can be used in a drop down box in the other table.

What I am trying to do is create a query that will count how many times the words from the first have been used in the second query. I have been able to get it to count how many times the word has been used but I also need it to return a 0 if the word has not been used.

In my query I used both tables to try to do this but it only returns the ones that have been used.

I will attach the DB if someone is willing to take a quick look at it to see if they can get it to work.

Any help will be greatly appreciated.

Thanks in advance

Jon

View 2 Replies View Related

Modules & VBA :: Return Rows In Query From Variant Array Return From UDF

Sep 16, 2014

I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.

So if my input table looks like this

[strField]
"kick the ball"
"return the pass"

my query result should looks like this

[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"

Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.

With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.

View 3 Replies View Related

Return Criteria Or Return All

May 10, 2007

Hi:

Using MS Access 2000.

Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.

criteria: [Enter date - mm/dd] Or >#1/1/2001#

When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.

I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.

Any help is appreciated.
:confused:

View 5 Replies View Related

Count Records Problem. Display Field Even When Count Is Zero.

Apr 13, 2006

I have a table tblBookings.

In this table it has a bookingID, CustomerID and some other none relevant details.

The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings

A customer can exist in tblCustomer without existing in the booking table.

I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).

I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.

SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;


How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)

I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.

The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2

For every hotel.

That kind of thing.

If you need more information please shout.

View 3 Replies View Related

Queries :: Using Count And MIN Together To Retrieve Only MIN Record With Count

Aug 16, 2015

I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.

BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797

The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.

Right now this is what i get

BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014

This is the output i would like

BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014

Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?

current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));

View 9 Replies View Related

Count Of A Count Or Sum Of A Count? Newbie Needs Some Help.

Sep 20, 2005

Hi,

Just spent the past hour in here trying to nut this one out, but not sure I've found something quite the same...though I know the answer will be painfully simple.

I have a customer table and a product table, and a query that groups customer first and last names along with a count of products per customer e.g. 1,1,3,2,3,4,2,1 indicates customer A buys qty 1 of product z, customer B buys qty 1 of product x, cust C buys qty 3 of product y and so on.

All I need to do now is do something to also output the total number of products. ie as per example above, 1+1+3+2+3+4+2+1 to get 17.

Can I do a count of the count or do I do some sort of sum of the count results?

I've tried everything I'm capable of as a newbie, and I'm not having any progress.

Any help appreciated.

View 2 Replies View Related

Return Zero Value

Mar 28, 2007

Hi,

In a query, I am using the following code to return the average length of each tenancy. This works fine unless the length is 0. If so, it only brings back 'blank'. How can I change this code to return a 0 value?

Tenure Length AVG: IIf([Average Tenure Length]>0,Format([Average Tenure Length],"Fixed",Format(0,"Fixed")))

Thanks in advance

Lee

View 2 Replies View Related

How To Do A Carriage Return

Oct 4, 2005

Hi guys

Im trying to set my label to a string.

Something like

myLabel.caption = StringOne & chr(13) & StringTwo

The problem is I just get funny symbols instead of the carriage return.

Please help

Thank you in advance

View 2 Replies View Related

Carrage Return

Mar 22, 2008

Hi There,

Can anyone tell me how to send a Carrage Return in Access? When I try to use the 'SendKeys with{Enter}', Access starts to run in an endless loop that seems to be some Wizard. All I want to do is go to a new line within the same text field.

Can any one help me? I'm an amature at Access and VBA.

View 7 Replies View Related

Return To Null

Apr 24, 2006

Hi all

i would like to know if there is a way to return a field to null

thanks in advance

View 2 Replies View Related

Return To Null

Apr 24, 2006

Hi all

i would like to know if there is a way to return a field to null

thanks in advance

View 1 Replies View Related

Serach - Return

Oct 17, 2006

I am creating a database for a hyperthetical car hire company. I have created three tables, customer table, car table and bookng table. The customer table is linked to the booking table using a Customer ID (autonumber). The car table is linked to the booking table using NumberPlate. The primary key for the booking table is Booking ID.

The cars are catagorised in different groups e.g. hatchback, sports car etc. The customer will choose a car type which is entered into the booking table. They will also enter the dates for which they want to hire their car.

I want my database to take these dates and the type of car, search through the car table and return a list of cars of that type which are availiable on these dates.

Can this be done???

View 1 Replies View Related

Expression? Return 1 Of Many

Aug 19, 2005

When running a query, I want to hide duplicates records for a given field. PO# will have many duplicates, depending on how many part numbers were order on the same PO#. Other unique fields are not displayed. Can an expression help with this? If not, how do I go about hiding them in a query?

Current Query results:

DATE PO#

08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD

Desired Query results:

08/18/2005 ABCD


Thanks in advance with any help!

View 2 Replies View Related

Null Value To Return 0

Dec 12, 2005

When the SSN field is null I would like this query to return : 000000000, is this possible and if so could you show me how? Thanks

SELECT
[CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID AS [IVA #],
[CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN AS SSN,
Sum([CASEHEADS RECEIVING NON WELFARE MONEY].AMT_DISBURSE) AS AMT,
[CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE AS [DATE] INTO [CASEHEADS RECEIVING NON WELFARE MONEY TBL2]

FROM [CASEHEADS RECEIVING NON WELFARE MONEY]

GROUP BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID, [CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN, [CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE
ORDER BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID;

View 3 Replies View Related

Return All Rows

Aug 16, 2006

I hope this is a simple query becuase my brain has drawn a blank on it.

I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.

Any help appreciated!

Dave

View 7 Replies View Related

Return Greatest Value

Oct 31, 2006

I have a table that has multiple Account Numbers and their Balances, I need to only pull the records that have the largest value.

Example: Acct# Balance
2 $1.00
2 $500.00
2 $300.00
3 $25.00
3 $400.00

I need my query to pull only these records Acct # Balance
2 $500.00
3 $400.00

Please help me figure out how to only pull those records

View 1 Replies View Related

Return Different Values......

Jan 29, 2007

Hi.
I have 2 queries, one that returns all the project numbers in the db (say 50 projects), and one that returns some of the project numbers (say 30 projects)

I can make a query that combines the 2 queries, and it returns project numbers that are in both queries (30 projects), but I would like the query to return the 20 projects that are in first query, but NOT in the second query.......does this make sense?

I am sure this is fairly easy.......it has just got me stumped!!

Many Thanks.

Frank.

View 2 Replies View Related

Return Lowest Value Only?

Jan 16, 2008

hi,

I have a table that contains itemcodes and prices. An example of the records would be:

<Item Code> <Price>
Product001 £34.56
Product001 £49.23
Product001 £23.22
Product001 £98.43
Product002 £12.45
Product002 £54.34
Product002 £25.51
Product002 £76.84
Product002 £14.97

I would like to run a query that returns the lowest value price for each Item code. So in this example it would return:

<Item Code> <Price>
Product001 £23.22
Product002 £12.45

Does anyone know the criteria code in the query design view for this problem?

I know it sounds silly, but there are unique codes in another field, i just need to query against these fields.

Thank you for your help.

Dean

View 3 Replies View Related

Return YES If Data Available

Jan 31, 2005

This is probably the first of many questions. I have an Access2000 DB with two tables. Each has a field called ParID. I want to have a form with Table1 and Table2 listed. I want to query on a parcel number I keyin. If Table1 has the number in it, I want the form to note such. The same goes for Table2.

Example: Keyin: 123

If Table1 has 123 in it and Table2 does not, my form would note:

Table1= YES
Table2= NO

If they both had 123 in it:
Table1= YES
Table2= YES

etc.

Thanks,
SKK

View 3 Replies View Related

Get A Return Value With An INSERT

Oct 2, 2007

Is there a good way to return an id that is created when I insert values into a database?
So I have this: INSERT INTO assignedReview ( reviewUser, reviewedEmpuser) VALUES (@reviewUser, @reviewedEmpuser);
And from that I want to get the reviewID that is generated (setup as a random autonumber) on the INSERT to pass back the user.

View 3 Replies View Related

Queries :: Return One Value Or Another

Nov 20, 2013

I am looking up a product based on a unique code in tblProducts...I need to return one of two values

If the value of [Product type] = "multi" then return "multi"
else
If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields

for example

tblProducts
PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE
C13T0714010 | single | <empty>| yellow | <empty>
C13T0754010 | multi | red | yellow | blue

query
C13T0714010 returns "yellow'
C13T0754010 returns "multi'

View 13 Replies View Related

Return Age With Decimals?

Oct 18, 2012

Is it possible to return an age as: 21.45 or 64.93?

I have done some searching and can't find any formatting or code to make it work.

I have changed the field format to Fixed and then it gives: 21.00 or 64.00

I like the 2 decimal places for quick at-a-glance-knowing of the age.

Of course I have a formula that returns an age with decimals in EXCEL, that's why I think it should somehow be possible in Access.

View 1 Replies View Related

Dmax - To Return Not Quite The Highest

Jan 18, 2006

Hello,

I am using DMax() to return the highest date in a table, but really need it to return the second-highest...so, if I had:

14th Feb
10th Dec
1st Jan

I don't want it to return 10th Dec, I want it to return 14th Feb

Any help much appreciated. I am sure there was something like dmax()-1, but that just returns (in my example above) 9th Dec!

Thanks!

View 4 Replies View Related







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