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.
I have made a query where the user have to enter from date and to date, and the result will be between these dates. If I write, lets say, from 01.01.2008 to 29.02.2008 It brings the results for all entrys between these dates, But I wonder if it is possible to make two results for that one, one for january and one for february. (All in one query, just sorted after month) Or maybe in the report perhaps. But I have no idea if this is possible, or how to do this. I know how I can sort it after date with the same query, but I want the results per month. Would be very nice if I can get it listed in a report, so that I can print out one page with results for january, february, march etc.
View 1 Replies View RelatedHow do I setup a query so I can sort a report by calendar month?
View 2 Replies View RelatedIn a report I need to sort by the month. the table field is "Appointment_Date" this is the code I am trying to use in the query behind the report . Appointment_SortMonth: Format([Appointment_Date],"mmmm" & " " & "yy")
but it doesnt sort. also the SORTING & GROUPING MENU does not work either. what do I try Now ???. your assistance will be appreciated.
Jabez
I've created a query based on 2 other queries.
I then filter the results of the third query based on 2 dates.
This worked great for 2013.
As a test I started making some records for 2014 and I've now found that my third query is not filtering the dates properly.
The filtering is happening based on date fields that contain only month and year eg: "09-2013".
My problem is that when I try to filter for records in 2014 it brings up results for 2013 as well.
If for example I had a record in August 2013, September 2013 and August 2014.
The sort would be:
08-2013
08-2014
09-2013
NOT:
08-2013
09-2013
08-2014
How do I make the sort apply to month then year to get the correct results returned?
I'm trying to sort my query results by the month value "Data (IMPORT).KP_MM_DS" but I get alphabetical results.
SELECT [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, Round((Count([Data (IMPORT)].KP_ISSU_ID_NB)/20)) AS CountOfKP_ISSU_ID_NB, [Data (IMPORT)].KP_MM_DS
FROM ([Data (IMPORT)] LEFT JOIN [Facility Lookup] ON [Data (IMPORT)].KP_FAC_NM = [Facility Lookup].[FAC NAME]) LEFT JOIN DEPTLOOKUP ON [Data (IMPORT)].KP_DEPT_ORG_NM = DEPTLOOKUP.[EPM DEPARTMENT NAME]
WHERE ((([Facility Lookup].[REPORTING MCA])<>"XXX") AND (([Data (IMPORT)].KP_SB_RGN_CD)="N"))
GROUP BY [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, [Data (IMPORT)].KP_MM_DS;
I have a table with product numbers and their cost. That table has 3 fields : SKU, Cost, Month. (The cost can change every month, therefore the list is built to have a cost / month)
I want the user to be able to input the data in the table using the form but I would like the form to be sorted Alphanumerically for the SKU numbers and then per month (in their normal order not alphanumerically).
I have a combobox (named month for testing purposes) to force the user to select one of the 12 months and the field type is Text.
Also I am unsure how to "autosort" (or apply a permanent sort) the form whenever it's opened. Will it be applied if I just set the sort and then restrict the user from seeing let's see design mode?
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?
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
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 RelatedI am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
I have a table full of dates of meetings through out the year...
Example
tMeetingDates
16th August 2005
18th September 2005
19th October 2005
23rd November
-----
i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.
I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.
i then want this date to be displayed in the Report.
So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...
Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...
we only ever have one meeting a month!!!!
Please help
Andy
Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?
View 6 Replies View RelatedHiI have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.kind regards in advance.peter
View 5 Replies View Relatedplease help me
i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..
any ideas please
Hello,
I've been looking through the archives and learnt a fair amount about date manipulation, but am still a bit stuck. I'm sure there is a way to do this and would appreciate any ideas:
Basically I am monitoring changes in Fuel level over months. For each month, I have a variety of readings, each on a different table, including sales from that tank, delivery in to it, and most importantly a closing dip reading taken at the end of the month.
In order to figure out the change in Fuel level over each month, you need an "opening" figure : this is taken to be the closing dip value of the previous month. And this is where I'm getting stuck!
I am writing a query to generate new table which combines all the relevent values. I have managed to link them through the date field, even though the specific date is not identical what matters is that it is the same month. Effectively I am trying to add a new 'column' showing the opening value, where opening value = previous months closing value for this tank.
I noticed there is a post talking about previous month data <a href="http://www.access-programmers.co.uk/forums/showthread.php?t=39708&highlight=previous+month">here</a>, which gives me some ideas but I'm not sure how to go about putting this into the SQL query. I can't use Date() because I'm interested in more than just previous to THIS month - the data spans back over a year!
I'm still learning Access so this might be a simple question - if so please point me to the relevent parts of the help file!
many thanks
Catherine
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
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
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)
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.
How do you change a file's sort order from ascii to international?
View 3 Replies View RelatedHello 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...:)
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
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:
hello ladies and gents!
I've been kind of lurking around.
I love this site! thanks for having me :)