Queries :: Sort By Year Is Sorting By Month

Sep 16, 2013

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?

View Replies


ADVERTISEMENT

Queries :: Sorting Month / Year Within A Crosstab?

Jul 7, 2014

I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy.

This works well as Access only wants one variable in the column header. My problem is that it is not sorted nicely and with my data being between Aug 2013 and Jun 2014 the headers look like this.

1/2014,10/2013,11/2013,12/2013,2/2014, not a very nice order.

Is there a way I can reformat MTHYR or a completely different solution?

My current code is

Code:
TRANSFORM Count(VOR_CALD_REGION.[Case Number]) AS [CountOfCase Number]
SELECT VOR_CALD_REGION.[Dealer Name], Count(VOR_CALD_REGION.Case Number]) AS [Total Of Case Number]
FROM VOR_CALD_REGION
GROUP BY VOR_CALD_REGION.[Dealer Name]
PIVOT VOR_CALD_REGION.MTHYR;

View 3 Replies View Related

Month / Year Queries

Apr 5, 2006

I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:

Format([date],"mmm - yy")

And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.

How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???

:confused: :confused:

View 1 Replies View Related

Queries :: Total By Each Month Of The Year

Feb 6, 2014

I have a query that I need to total by each month of the year. Field names and types are as follows

ID (PK)
Month (Date/Time)
PoliceResponse (1 or 0)
TerminalCheckTrue (1 or 0)
RampCheckTrue (1 or 0)
AOACheckTrue (1 or 0)
TotalTerminalRampAOA (TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
TotalChecks (PoliceResponse+TerminalCheckTrue + RampCheckTrue + AOACheckTrue)

Now, I need to just get the total of each of these fields per month for the entire year. So it would look like

Jan, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Feb, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Mar, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...

Below is a sample from the query. I guess Im just not sure where to start since there are multiple months the same value, but i need them to total up..

Code:
SELECT DISTINCTROW tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks
FROM tblBlotter
GROUP BY tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

View 14 Replies View Related

Queries :: Group By Month And Year?

May 9, 2013

I try this code

Code:
SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot,
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg,
Round(Avg(ReportTbl.avg_len),2) AS LenAvg,
Round(Avg(ReportTbl.avg_in),2) AS InAvg,
Round(Avg(ReportTbl.avg_out),2) AS OutAvg,
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg,
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY ReportTbl.lot_date;

But it not working and have warning box with "you tried to execute a query that does not include the specified expression 'ReportTbl.lot_date' as part of an aggregate function"

and when I try
GROUP BY Month(ReportTbl.lot_date),Year(ReportTbl.lot_date) or
GROUP BY datepart("m",ReportTbl.lot_date),datepart("yyyy",R eportTbl.lot_date)
or other code that I can search in google

I get the warning message that resemble with above message

and when I try to GROUP BY ReportTbl.lot_date it work but the result does not meet the requirements.

View 3 Replies View Related

Queries :: Matching Dates By Month And Year

Aug 27, 2013

Looking to match to date fields within a query by using the month and year only, is it possible and how.

I'm using Access 2003

View 13 Replies View Related

Queries :: Calculates Number Of Days / Month / Year

Sep 15, 2013

I am building a report and need to know the coding in the query (or in the report) to achieve the following.

Currently, in my query - 3 fields that work fine:

AppDate - dates of set appointments (Appt)

MonthNo: DatePart("m",[App_Date]) - get Month value

YearNo: DatePart("yyyy",[App_Date]) - get Year value

What I need is : EXACT coding to get these counts to show in my report:

January 2013
12 appointment (there were a count of 12 appts in January)
over 23 days (there were 23 days where appts were booked in January)
of 31 days (total number of days in the month of January)

February 2013
18 appts (there were a count of 12 appts in February )
over 19 days (there were 23 days where appts were booked in January)
of 28 days (total number of days in the month of February )

etc.

View 3 Replies View Related

Queries :: Calculate Fiscal Year And Month From A Date

Jul 13, 2015

I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:

The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.

In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by SQL statement into a string. But this also didn't work, the types were different.

Code:
UPDATE tblSAPOD
SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]<
DateSerial(Year([sapOD_OrderDate]),6,16),1,0)
WHERE sapOD_OrderDate Like "*/*/94";

.. but I guess it's wrong, it gives me a syntax error.

View 11 Replies View Related

Queries :: How To Format Crosstab Query By Month And Year

Jul 1, 2014

This is my expression to format crosstab query by month

Expr1: Format([Recharge_Date];Month([Recharge_Date]))

but i can't make it with month and year....

View 2 Replies View Related

Queries :: Query Using Date From Form In Month And Year?

Apr 3, 2013

My question is that I wish to have a query that looks up on a name in a form (No problem got this bit) and also the Date field from the same form so that from the date :-

example 24/03/2013 only cares about the Month and Year so would look up only March in 2013

The end result would be to supply me with a number (using count) of how many times this persons name has occurred within the calendar month and year of the date supplied in the form

View 1 Replies View Related

Queries :: Convert 3-char Month And Year To Date

Aug 17, 2015

I have 2 columns that are listed as such:

AssumptionMo AssumptionYr
MAY 2014
JUN 2015
JUL 2015
OCT 2016

I need to create a field called AssumpDate that converts the month into a date field on the 1st day of the month. ex May 2014 needs to read 5/1/2014. When I use the expression AssumpDate: DateValue("1-" & [Assumption_Month] & "-" & Year(Date())) of course the year changes to the current one--2015. How can I I change the expression so that the year is based on the AssumptionYr column?

View 3 Replies View Related

Queries :: Set Month Or Year Default As Per System Date

Oct 16, 2013

How can I set Month or Year default as per the system date. ie Only select records by Month (October) or Year (2013) as per system date?

View 3 Replies View Related

Queries :: Sort Out Invoices By Year - Query Based On Combo Box To Show All Records

Mar 4, 2015

I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.

I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.

On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.

My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :

IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])

If I select the year from the combo, it works, if I select "ALL" nothing is shown.

here is the full SQL

SELECT DISTINCTROW Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]) AS DueYear, [Invoice]![month] & " - " & [Invoice]![Year] AS InvoiceMonth

[Code] .....

View 5 Replies View Related

Queries :: Calculate Profit Made By Type / Month And Year

Apr 22, 2013

I want to make a query to figure out profit made by month year and room type.

View 1 Replies View Related

Generating Year Values And Sorting

Jan 25, 2005

:confused: :confused: :confused:
Please bear with me. I'm new to using access and plodding along little by little and I had little luck finding relavent past posts. I have a table with the column headings and a couple of records listed below. Each of those records has a subdatasheet pertaining to data for the commands. For every command, my dept does a survey or inspection every so many years. How often we do these surveys depends on the HAZ CAT. If the command has a HAZ CAT of I, they have a survey done every year. (HAZ CAT= II, every 2 years; HAZ CAT = III, every 4 years). On a form, I want to be able to generate the next serveral survey years (let's say for the next 10 years) based on the command's most recent survey year and its HAZ CAT. So for each command, there should be a listing of all its survey years. Then I want to be able to sort by year so I'll know what commands will need to be surveyed each year. I'll eventually have a form which will be my control panel. On the control panel will be command buttons labeled with years for the next 10 years. The button should open up a form with the records for the respective command information.

[CommandID] [Command] [HAZ CAT] [Last Recent Survey]
1 AIMD I 2004
16 DODDS III 2002

View 2 Replies View Related

Sorting By Month

Mar 1, 2006

I have looked and tried a lot of things that I have seen in this forum, but I am not able to make this work. I have a query that I am trying to sort a table field, "Date" by month. The field has a "General" time/date format in the table. This is the last code I tried, but as you can see I am not much with VB. Please see if you can help me. The table name is "Batch Data" and the field name is "Date".
Thanks,
Robert
SELECT Format([Batch Data].[Date],'General Date') AS formDate
FROM [Batch Data]
ORDER BY [Date];

View 5 Replies View Related

Sort Records Based On Fiscal Year

Oct 14, 2005

I would like to sort records based on fiscal year for a chart. The fiscal year would be 7/01/2003 to 06/30/2004. I would like to sort this based on oldest date to newest date. This query could span several fiscal years. Any help would be greatly appreciated.

View 7 Replies View Related

Sort Date Formatted As Year And Week

Dec 7, 2004

I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.

How can I get Access to sort in ascending order correctly on the formatted date?

Thanks

View 2 Replies View Related

Need User Friendly Sorting Of A Report Using A Sort By List...

Jun 29, 2006

Ok, I am a noob at reports and did a search on this. My searching DID NOT HELP ME! I thought I would make that clear :D Maybe someone can tell me what to look at for this issue.

I need to take one report, allow the users to easily use a dropdown or listbox on a form to Sort and Group many fields of data. To recap: The user will click on name in the list and it will sort report by name. Also I need them to be able to select up to three sort criterias.

EX: Sort by Name then LastName then Company

Please guide me to the answer. Thanks.

View 3 Replies View Related

Sorting By Chronological Month

Nov 1, 2005

I have my query set up, but I want it to sort by chronolological month and not alphabetically. Below is the SQL statement.

GROUP BY [Implementation Pipeline].[Parent Company], PUR_OWN_ASC_11.CUST_ID, MonthName(PUR_OWN_ASC_11!PERIOD,False), [Implementation Pipeline].[Transition to FSCR/AM]
HAVING (((MonthName([PUR_OWN_ASC_11]![PERIOD],False))<"October"))
ORDER BY MonthName([PUR_OWN_ASC_11]![PERIOD],False) DESC;

View 2 Replies View Related

Date - Month And Year Only

Dec 18, 2005

can i change date format that contain day, month, and year to month and year only..
i try change at fromat at porperties, but it change back into dd/mm/yy at combo box..
this is bcoz i want to filter up my subform that contain parts that purchased by customer by month..

thanks..

View 3 Replies View Related

Month End And Year End Reports

May 1, 2006

For the record, I have indeed searched the forum for help with this, but no luck so far, so here goes. (C++ programmer, little background in access).

I have a table that stores a person's name, gross amount spent, gst spent, pst spent, and total amount spent.

I need to be able to create a query or something that asks me to enter a year, and it will sum up the amounts, gst, and pst over the course of that year, and print it neatly on screen.

Also, I need to be able to create a similar query that asks for a year and a month (does access possess Java's ability to easily create a pop-up calendar applet-like instance that provides a GUI for selecting months/years, or does the data need to be entered through a command prompt?) which will create a month-end report in the same manner, but only spans a 1 month period?

Any information that could be provided to me, even a means of getting started on this problem, would be great. Thank you and cheers everyone!

View 2 Replies View Related

Distinct Month AND Year

Aug 28, 2006

if i have a table like this:

4/3/2006
4/3/2007
4/12/2006
4/12/2008
5/2/2003
5/5/2003

i would like to extract only the distinct month and year date, so my query should return

4/12/2006
4/3/2007
4/12/2008
5/5/2003


can anyone plz help me with the sql query? thanks

View 2 Replies View Related

Grouping By Month & Year

Jul 22, 2007

I have another...

How do I group the dates on my query to show only a certain month and year?

View 2 Replies View Related

Return Month And Year

Aug 14, 2007

Hey all, I have a form that has two textboxes, one box is for month and the other is for the year. After I enter the month and year I want, I click on a command button that previews a report. Well, the report is not giving me the information for that particular month and year, actually it gives me nothing.

The report gets its information from a query. In that queries criteria field is the below expression that I have.

Like [forms]![frmPEL]![cboMonth] & "/*/" & Right([forms]![frmPEL]![txtYear],2)

Can anyone see anything wrong with this? I should be able to enter the month and year and click on the report button I want and preview that report, but no joy.

Your help is greatly appreciated.

RR

View 5 Replies View Related

Calculate First Day Of Next Month In Following Year?

Jul 30, 2012

I have tried to do this using DateAdd but I cant get it to add both the month and year.

I have a date field [HireDate] and I need to add one year then get then get the first day of the next month.

For example: HireDate = 1/15/2012 - I need access to return 2/1/2013.

how to build that expression?

View 4 Replies View Related







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