I've been looking for an easy way to have 12 columns in my query which are each of the 12 months and are all using the same date field "dateSent". So i'd want to just use the month part. within the actual columns there will be a disbursal field which will tell a user how much money has been sent (if any) on that month. then If done correclty, it could be analysed in Excel.
I've attached an example of what is required of me. I'm expected to have the same format but i'm unsure how I'm going to go about this.
I've attached an example. Maybe someone might know a good way to do this and perhaps point me in the right direction
I 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 was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
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..
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...
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?
Anyone know if i can use DateAdd or something like in a query to show current month I want the user to be able to see records for the month we`re in Thanks all
Hi Everyone, I come to you guys for help once again,
Here is my query code: Total Records: Count(IIf([Record #]=1 And [Date]=Month([3]),0))
I think I am formatting this wrong, I would like to query data based on the record number equaling 1 and the month in the [Date] field equaling March. Could anyone help me with the proper formatting of this? Thanks in advance!
I was wondering is there a way to only QUERY day and month?
dd/MM ?
The issue is i need to build a report that has all the accounts opened within a certain month/time period. BUT its not year specific so i would need for instance.
01/02 to 28/02 dd/mm dd/mm
Note: The date stored in the DB is in this format dd/mm/yy < Standard format.
Is there a way to do this? Your help is much appreciated... THANK YOU..
Would someone be so kind as to tell me why I get Err in the Runtot field from this query ? It seems to run fine except no totals.
SELECT DatePart("yyyy",[Recdate]) AS AYear, DatePart("m",[Recdate]) AS AMonth, DSum("Creditamt","DatePart('m', [Recdate])<=" & [AMonth] & " And DatePart('yyyy', [Recdate])<=" & [AYear] & "") AS RunTot, Format([Recdate],"mmm") AS FDate FROM TblPayments GROUP BY DatePart("yyyy",[Recdate]), DatePart("m",[Recdate]), Format([Recdate],"mmm") ORDER BY DatePart("yyyy",[Recdate]), DatePart("m",[Recdate]), Format([Recdate],"mmm");
I am trying to set up a query where the user inputs the month they want in "MMM" format (i.e. Jan or Dec). Additionally is there a way to run a query where the user is promted to select a value from a drop down menu? Thank you for your help in advance
I am trying to design a query to pull all the records which have a birthday in a specific month (ex., all birthdays that fall in May). The table is imported from Excel. Currently, the format of the field is MM/DD/YYYY.
I'm not sure how to either format the birthday field so that I can run a query on it or how to design the query parameters to pull a specific month. Within the query, I need to pull all birth years and days in the month.
I'm a complete newbie to Access, so any help is appreciated! Thank you in advance.
I'm using a query to look up report criteria by type, response etc using a query to search value in a field ex [Reply]=[Forms]![Form1]![Cmbreply] Or [Forms]![Form1]![Cmbreply] Is Null
I'm trying to work a month look up the problem is the date is set July 25,2005 and my field is searching for the month July not the specific date. I'm stuck on how to look for the month only and ignore the day and year - any ideas
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.
HiI 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
I am trying to create a cross tab query which will output the data for only three months starting from the recent month. I would want these months to be heading. However, I don't want to create reports over and over again. I want something, that will resolve the issue through parameters kinda thing. I don't know, if this can be done or not. Right now I have the cross tab query for all the months, but I have to manually choose the months to fit in the page. I hope I am making sense here. I didn't know where to post this, in query or in report. Therefore, I am posting it here.
Below is the Field data I have in a query. This allowed me to get a count of forms for the current month. I think it is giving me a count of forms for both October 2004 & October 2005. Up until this month I did not have any data covering the same month of both years. Could this be what is happening and if it is how do I change my field data to ensure it is current month and current year only?
FORMS: DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now)")
From this table I want to create a running total so that the running gross rev of product 810 MAR07 is based on APR06-MAR07 data. The same applies to the number of clients. Is this possible?
I have tried using the Dsum function but I have only managed to calculate a total running (not based on the last 12 months). Can somebody help me here?
I am looking at creating a query which would report whether a specific field is yes/no. However, I would like it to based on that days specific month. For example, I have 12 check box fields, one for each month, and I would like to create a report where it would look up whether or not the current month has a check box in it. Additionally, I would like to create another where the criteria would require me to put in a month to search for. For example, to search to see whether or not december has been checked.
I want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.
I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?
