Sort And Count

Jan 27, 2005

I have a database that tracks Monthly repairs. One of the fields is set to short date 99/99/9999. I need to sort out monthly (like all the april ones) and then count how many there were for that month.

How do I do this??

View Replies


ADVERTISEMENT

Reports :: Sort By Count

Oct 1, 2013

I am trying to create a report that shows how many sales each sales person has in a 2 year period and sort it from the most # of sales to lowest. I want to show all sales people not just top 5 or 10.

View 8 Replies View Related

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 2 Replies View Related

Report Sort Different From Query Sort

Mar 3, 2006

i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..

now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..

i dont want to re-do the report it took ages.. how can i fix this?

View 3 Replies View Related

Can I Do A Sort Within A Sort On Combo Boxes?

Oct 16, 2006

Hi all,

I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.

It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.

Thanks in advance for any responses.

D

View 6 Replies View Related

Report Sort Is Not The Same As Query Sort...

Jan 9, 2005

I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??

View 3 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

Sort By A Value

Aug 8, 2005

I was just wondering if it was possible to run a query by a specific value first, then the remainder of the following values in ascending order.

For example, the data would appear in the table as

Smith
Bloggs
Jones
Taylor

What I would like to do is have all the Taylors at the top, followed by Bloggs,Jones then Taylor

I have looked at the OrderBy function, but believe that you can only sort Ascending and Descending

Checked search, cant find anything

Many Thanks

Andy

View 3 Replies View Related

Re-sort

Mar 25, 2007

Hi,

I try to do something but I don't know how.
I have 1 table with 3 rows (title, year, genre) and 1 Form.
Is it possible to create combobox or something with 3 criterium, when I selected
criterium to re-sort columns in Forms (by title, by year, by genre)?

thank you

View 2 Replies View Related

Sort Asc / Dec

Jan 13, 2006

Continuous form:
The standard menu / toolbar commands sort ASC and DEC work just fine.
However, if I define a button with the VBCode
"DoCmd.RunCommand acCmdSortAscending", I get an alarm message pop-up
saying "the command or action "SortAscending" isn't available now".
I made sure that the standard menu items "Sort asc / dec" are not grayed out(disabled) at the time I click the button in runtime. The mouse cursor points to the desired sort criteria field.

Any advice?

Regards from Belgium (pretty cold here)

View 2 Replies View Related

How To Sort By IP Address

Aug 29, 2005

I am completely stumped on this one. I have a database where we track IP Addresses and I just want to sort by IP...should be simple, I'd think.

With these 5 IP's, I'd want them in this order:
1.2.3.1
1.2.3.4
1.2.3.7
1.2.3.9
1.2.3.10
1.2.3.17
1.2.3.21

But it sorts in this order:
1.2.3.1
1.2.3.10
1.2.3.17
1.2.3.21
1.2.3.4
1.2.3.7
1.2.3.9

Is there anything I can do to fix this? I want it to see each number as a whole, for example "10" instead of "1" and "0"

Much thanks.

View 5 Replies View Related

Sort Order

Oct 10, 2005

How do you change a file's sort order from ascii to international?

View 3 Replies View Related

Sort Table

Dec 22, 2005

Hello all...
I have a table which includes month, day & date. I want to sort this table by day, and then by month & then by year... does anyone know how to do this?

Thank you...:)

View 4 Replies View Related

Help Newbie Sort Out His DB

Jan 16, 2006

Hello all

Been looking at this forum for a few weeks and think it's great!!

Now for my first post :rolleyes:

We have a DB at work (attached) and it was put together by a consultant would you believe. Anyway I have been given the task to fix this. It now work i.e. it records what I want. The only problem is that it's written with more macros than i've ever seen.

I want some advice on the DB to make it function better. It is used to record how long a member of staff spend on a piece of work, apologies for the interface.

1. The user must first input a date , start date and end date into a table. Now the start date will always be a Monday and the end date a Sunday. Is there anyway to automate that process? i.e if a user goes in on a Monday the prevous weeks date is there but if the user went in before it would not , get the idea.

Anyway, please rip it to shreads and give me some advice

Thanks

View 11 Replies View Related

Auto Sort Anyone

Jan 25, 2006

I have a table already set up in a Subform of a main form

The Table has values like
-Date
-Time
-Name
-SchBy

I want to have the subforms table to automatically re-sort ascending by Time, how would I go about doing this so the user doesn't have to click the field then sort ascending button...?:confused:

View 1 Replies View Related

Hello, Sort Anoob Here

Oct 17, 2006

hello ladies and gents!
I've been kind of lurking around.
I love this site! thanks for having me :)

View 1 Replies View Related

Sort Negative First

Jan 15, 2007

When i sort values, for some reason the negatives are at the top, followed by the highest positive values. Anyway to turn this off?
Tried google and searching these forums.

View 5 Replies View Related

MDB To TXT Looses Sort

Dec 9, 2007

Hi all.
I imported a text file into a new table.
I sorted the table on one field, then exported table to another text file.
But, the sort did not export. The new text file has the same sort as the original.
What am I doing wrong?
Thanks.

View 3 Replies View Related

Sort Date

Dec 13, 2007

Hey all, I looked throught a couple of threads for sorting, but could not find exactly what I need.

Basically, I have a list of dates:

10-Aug-07
20-Oct-07
13-Nov-07
etc...

and when I try and sort these dates from earliest to latest, it only reads the number and not the month, like:

10-Aug-07
13-Nov-07
20-Oct-07
etc...

How would I make it sort by date and month?

Thanks,
RR

View 4 Replies View Related

Sort By Month

Jun 21, 2005

Hello, I have the following query which gives me a count by day.

However, it sorts by day instead of day/month.
For example:

01/01/05
01/02/05
01/03/05
02/01/05
02/02/05 and so on...

Can I sort so it will show me all of January, then all of Febuary etc?

Thanks.

J.

View 5 Replies View Related

Sort By First 2 Digits Only

Jan 19, 2006

Open the database – then open the Report: rep_Billing

I have a query that is doing some sort and count functions. The column [TYPE] is sorted then counted. Instead of sorting by the full name can the query sort based off of the first 2 characters?

Example:
On the report the first company is: AMERICAN POSTCARDS

First it separates the colors, which is good.
Then separates the [TYPE] then counts the total amount that color was that type.
However I do not want the full type to be separated out, I want it based on the first 2 characters so that the “N-“ will all stay together as a group.

Final count should say: AMERICAN POSTCARDS
GREEN RA 3
RED N- 14
RED PR 16
RED RA 12
BLUE NC 1

I know I can get this info from the report, but it has to be done at the query level because it is going to be exported into another system at that point. Thanks.

Let me know if I need to make this clearer. Thank you all.

Travis

View 2 Replies View Related

Sort By Last Entry

Feb 3, 2006

Hello,

Is there any way to sort a query by last entry? Unfortunately, this would be the only solution to a design error. Thanks much.

Pat

View 1 Replies View Related

Sort By Last Entry

Feb 3, 2006

Is there any way to sort a query by last entry? Unfortunately, this would be the only solution to a design error.

View 5 Replies View Related

Sort Problem

Feb 10, 2006

I need to sort an address field by street name only. The address info is stored as: 123 Main Street, etc. Is there a way to omit the number portion of the field during a sort function in Design View?

View 1 Replies View Related







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