Queries :: Rolling 12 Month Query - Keeping Track Of Orders Placed For Given Part Number By Month

May 5, 2014

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.

View Replies


ADVERTISEMENT

Queries :: Dates In A Query - Show Orders By Month For Last 12 Months

Apr 22, 2013

I want to create a simple query from a list of orders dating back over 12 months.

The fields I have is Ord_Date, Qty

I need to show the orders by month for the last 12 months.

The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.

I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.

View 6 Replies View Related

Rolling 12 Or 6 Month Query

Sep 22, 2011

I have a date field "yyyy/mm/dd"

The data is back up til 2009, but i only want to bring a rolling 12 months of data.

What should my query look like in Access

I did

Month()-12

This gives me an error Message ...

View 2 Replies View Related

Rolling 12-month Total

Feb 25, 2008

Hi all,

I have a problem creating a query calculating a rolling total based on the last 12 months grouped by product.

The table:

Product no. / Period / Gross Rev / Clients
810 / 01-01-2006 / 1.000 / 10
810 / 01-02-2006 / 1.000 / 10
...
810 / 01-01-2007 / 500 / 10
990 / 01-01-2006 / 1.000 / 10
990 / 01-02-2006 / 1.000 / 10

From this table I want to create a rolling total so that the rolling gross rev of product 810 MAR07 is based on APR06-MAR07 data. The same applies to the number of clients. Is this possible?

E.g.
Rolling sum of clients in OCT07 = No. of Clients (NOV06+DEC06+JAN07+ ... +SEP07+OCT07)
Rolling sum of clients in JUN07 = No. of Clients (JUL06+AUG06+ ... +MAY07+JUN07)

The problem is that the output has to be a table/query (a complete list) with following data:
- Product
- Period
- Gross Rev (prenst)
- Clients (present)
- Rolling Gross Rev (last 11 month + the present)
- Rolling Clients (last 11 month + the present)

The table is to be exportet to Excell and used in a pivot.

I have tried using the Dsum function but I have only managed to calculate a running total (not based on the last 12 months). Can somebody help me here?

View 1 Replies View Related

Modules & VBA :: Loop To Do Rolling 12 Month Average

Aug 13, 2013

May be VBA is a better solution for this:

Say for Material A: I need Access to see that the 1st date is 05/01/2013 and say 365 days out or 12 months (05/01/2013-04/01/2014) the average is 158.6 then 06/2013-05/2014 the average is 146.667 and so on.

Every month is not going to be listed in the results, if there is no month then assume 0.

Sample data attached.

View 7 Replies View Related

Subtract Number Range While Keeping Track In A Table / Form

Mar 31, 2014

I have two tables (one is a query)

Table 1 (query based)

EMP_ID
Prev_Emp_ID
EMP_ID_DDSK

332-123
1
500

332-133
1
501

332-144
0

332-156
1
502

332-654
1
503

332-456
1
504

332-967
0

Table 2

Res_Numbers
Num_Of_Employees

500
10

505 - after 5 numbers are placed
Will be added for next hire

My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)

Example: If field "Prev_Emp_ID" = false

Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".

Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".

View 4 Replies View Related

General :: Create Invoice Per Month Of Multiple Orders Per Customer?

Mar 19, 2013

My current database creates an invoice per order. Until now that worked wonderful. Now i want to create an invoice per month per customer with multiple orders on the same database. So i will have to create a new table/query on the existing order table.

View 1 Replies View Related

One Query To Return Either Records Within "Date Range" Or "12-Month Rolling"

May 25, 2005

I have a query that selects usage records from a table where a data field falls within a rolling 12-month period that ends on a month and year selected by the user on a form.

PARAMETERS [Forms]![frmReport]![cboMonth] Short, [Forms]![frmReport]![txtYear] Long;
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((DateDiff("m",[dtUsage],DateSerial([Forms]![frmReport]![txtYear],[Forms]![frmReport]![cboMonth],1))) Between 0 And 11));

I also have a query that pulls data between two dates that the user specifies using two textboxes on a form.

SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]));

On the form I have an Option Group control that sets the visible properties of the 4 controls;
12-Month Rolling
cboMonth - Month Combobox
txtYear - User entered year
Date Range
txtStartDate - User entered Start Date
txtEndDate - User entered End Date

What I was planning on doing was adding two new textboxes (txtQryStart & txtQryEnd) and depending on what option is selected, change the dates accordingly

blnDateRange = (Me.optDate = 1)
blnMonth = (Me.optDate = 2)

If blnDateRange Then
Me.txtQryStart = Me.txtStartDate
Me.txtQryEnd = Me.txtEndDate
End If

If blnMonth Then
If Me.cboMonth = 12 Then
EndMonth = 1
EndYear = Me.txtYear + 1
StartMonth = EndMonth
StartYear = Me.txtYear
Else
EndMonth = Me.cboMonth + 1
EndYear = Me.txtYear
StartMonth = EndMonth + 1
StartYear = Me.txtYear - 1
End If
Me.txtQryStart = DateSerial(StartYear, StartMonth, 1) - 1
Me.txtQryEnd = DateSerial(EndYear, EndMonth, 1)
End If

Now I can use one query to accomplish both types of query;
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtQryStart] And [Forms]![frmReport]![txtQryEnd]));

This approach works. It saves me from having to duplicate the queries and some future headache if anything needs to be changed in the queries.

What I was wondering is there an better/simpler way to do this?
:confused:

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

Calculate Variance Of Payment Month On Month - Values Comparison

Mar 19, 2012

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.

View 10 Replies View Related

Matching Current Month With Month In A Table

Oct 18, 2005

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

View 11 Replies View Related

Forms :: Calendar Scrolling Month By Month

Aug 9, 2013

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 Related

Queries :: How To Query Any Date Before Current Month

Jul 11, 2013

I am trying to query any date before current month. My data is employee start dates, thus my end aim is to pick up all employees at end of last month.

View 1 Replies View Related

Queries :: Group Query By Day Of Selected Month

Jul 21, 2015

I am trying to make a query that counts the orders on a day of an specific month selected from a form.

The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.

Any way of making a query that adds the days with no records with a count value of 0?

View 3 Replies View Related

Month By Month Query

May 17, 2005

please 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

View 2 Replies View Related

Auto Number, Year, Month Number Combination

Apr 20, 2008

I need to create an auto number for service calls that show the following:

year as 08, 09 etc, month as a letter, Jan = A, Feb = B etc then an incremental number starting at 300 for each month/year combination.

So for example: 08A300

Can anyone help me as I am stuck?

View 10 Replies View Related

Queries :: Formatting Month Name In Running Total Query

Dec 18, 2013

I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.

Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);

I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.

Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);

Do any of you know a way I can make this work?

View 2 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 :: Create A Query With Parameter - Searching For A Specific Month

Nov 22, 2014

I am trying to create a query with a parameter on it, but on a date box, but only searching for a specific month, without regards to the year. So what I want is for me to run the query, a pop up box asks "which month?", I then put in Jan for example, and the results will show all records with the month Jan regardless of the day or year.

Is this possible? If so - whats the code I would use?

View 2 Replies View Related

Convert Month Name To Number

Apr 9, 2008

Hi All - I would like the user to be able to import a month name (April for example) and then have access convert that name to a number (4) so that I can search for records that were opened before the month entered. I have been using:

MonthName(Month([CompletionDate])) = [Enter Month]

but

MonthName(Month([CompletionDate])) <= [Enter Month]

does not work.

Thanks!

View 1 Replies View Related

Convert Number To Month Name

Feb 4, 2008

Hi
I am struggling to get a number to change to a month name.
When the user chooses a month from a drop-down:

Value List
1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"

I use the month number to run the query, ie: 1, 2, 3 etc.,
But I want the Month Name to use in the subsequent report.

I have no other data in the query other than a month number to use so I thought about an invisible text box that get's the month number and I could reference it from the report, but I just can't get the text box to get the month name from the drop-down.

=Month([Month4Query].[Column](2))

Maybe there is a better, easier way?

Martin

View 3 Replies View Related

Queries :: Access 2007 - Query To Pull Fri / Sat / Sun Occurrences In A Specific Month?

Jul 7, 2014

Access 2007 query that has a specific date range between #6/1/12014# and #7/1/2014# returns the date I need. How do I now get it to pull just occurrences on Fri Sat and Sun of the month of June?

View 5 Replies View Related

Queries :: Mailing List For Specific Month - Date Criteria Query

Jun 11, 2013

I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.

View 2 Replies View Related

Queries :: Creating Field Names In A Query That Will Automatically Update Each Month

May 10, 2013

I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.

Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?

View 4 Replies View Related

Queries :: MS Access 2010 - Put Month On A Form Based On Data In Query?

Mar 26, 2014

How do you put the Month on a form based on the data in a query?

View 1 Replies View Related







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