Automating Queries To Include New Month Of Data

Aug 10, 2005

Hi

Each month I use Access to import text files and export cleansed data to Excel.

The information contains (i.e. forecast data) that has 6 months of data. Each month, the data is rolled over to include the new month and delete the oldest month of data.

Is there a query(s) or module that can automate this process?

Would greatly appreciate any tips on this one!!

Lucas

View Replies


ADVERTISEMENT

Queries :: Include Date Criteria In User Defined Function That Calculate End Of Current Month

Jul 22, 2014

I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().

The field on which this function is to enter as a criteria is another calculated date function called Due.

When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.

View 3 Replies View Related

Queries :: Make Query Include All Records Even If No Data Found

Jul 10, 2014

I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.

Staff name etc is in tblStaff
Staff shift details are in tblShifts linked via staffID

tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.

The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.

View 7 Replies View Related

Queries :: Return 0 Where Data Does Not Exist For A Particular Month

Mar 13, 2015

I'm trying to make a query to return payroll data, but I'm not sure how to get Access to return 0 where data doesn't exist for a particular month.

For example, imagine this table:

Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160

How would I get this ouput:

Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
Matt 2 0

View 10 Replies View Related

Queries :: Exclude Current Month Data

Mar 10, 2014

I have a query that shows data from January to March (which is the current month). is there a possibility to exclude the current month

The query has the following fields

SegmentDate
PCC
AgencyName
SegmentData

View 12 Replies View Related

Automating Report Construction From Queries

Sep 14, 2011

I have a very large query that includes order data for about 100 clients. There are two smaller queries that take data from this one (to group by a "job code" and by a "job category"). I'm looking for a way to automate the process of running the series of three queries and producing an output (table or report) that gives the relevant data I need. I want to run this process for all ~100 clients, so I don't want to manually edit each query and copy and paste the results every time, but I'm having trouble creating a macro that will accomplish this.

View 10 Replies View Related

Modules & VBA :: Automating Data Entry For Weekly / Biweekly Dates

Oct 24, 2013

I am trying to write code for a form. When the user enters the "Number of Payments" and the "1st payment date" it will fill the amount of the payment into a specific month.

For example: 1st payment date would be 10/24 the payments would be bi-weekly and the amount would be $50.00. The number of payments would be 4. I would need it to put $50.00 into a field called "October" $100.00 into a field called "November" and $50.00 into a field called "December". In excel I would do this simply by using a (date)+14 formula. I am sure that I need to do this with loops but where to even begin.

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

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

Queries :: Access 2010 - Query To Show Data On Every Monday In Current Month

Nov 8, 2013

is their a way to have a query to only show data on every monday in current month.

Month([datefield])=Month(Now()) And DateAdd("d",7,[datefield])

View 11 Replies View Related

Queries :: Yes / No Query To Include Some Or All Results?

Jul 4, 2013

I'm having a mental block on this one.

I have a table with a yes/no field. It's linked to a form with a yes/no box, but the outcome I want is that if the form box is unchecked, the query returns all the "yes" records - which is trivial; however I want to return ALL the records if the form box is checked.

View 1 Replies View Related

Extract Data From Table On URL (to Include PNG Files)

Oct 25, 2013

I am a new user to access. I would like to extract a table from a URL, it is 4 fields and has around 150 records. One of the fields is made up of png files, relatively small ones. Is it possible to extract all the text and png files straight into access without individually saving each picture and attaching it to a record?

View 2 Replies View Related

Is There A Way To Include Wildcards In MS Access Parameter Queries?

May 4, 2008

I have a table with a string field for a street. It might have data such as:

Bleeker Street
or Bleeker St.

I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?

Thanks.

View 2 Replies View Related

Queries :: Don't Include Certain Records At Press Of A Button

May 7, 2015

if its possible to have a button that after clicking it, it doesn't show certain records. Reason being I have a "shows" for each month on our personal site and amazon, and we've been selling on those for about 2 years, and I have a "show" for each month. But, that quickly clutters up the list box. Any way to create a button the (after typing in the name) it doesnt show only those record(s)?

View 2 Replies View Related

Queries :: Include A Field Not Related Into A Query

Jul 10, 2015

How can I include a field from a table in to a query - that has no relationship?

For example...

The 'Despatch' field is in a seperate table called 'Product', because we despatch multiple products for any ONE order at different times, so it needs to be against the Product and not the full job itself.However, when I include the 'Product' table into the query and insert the 'Despatch' field into my query, and run it comes up with an error...

"The SQL Statement could not be executed because it contains ambiguouse outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then iclude that query in your SQL statement". Here's the current SQL statement

Code:
SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch
FROM Product, Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];

View 1 Replies View Related

Queries :: Like Operator - Include Fractions In Result

Apr 24, 2013

I have the following query, which I simplied to show only the WHERE clouse.

Code:
WHERE ((([Using our own appt date].PrblDiagCode) Like "519" Or ([Using our own appt date].PrblDiagCode)="414" Or ([Using our own appt date].PrblDiagCode)="428" Or ([Using our own appt date].PrblDiagCode)="250" Or ([Using our own appt date].PrblDiagCode)="430" Or ([Using our own appt date].PrblDiagCode)="585" Or ([Using our own appt date].PrblDiagCode)="573"));

Is there any way to also include the ones with fractions? For example, a record can be 519.3. I find that the current code doesn't give me the ones with fractions. What needs to change in the query?

View 1 Replies View Related

Queries :: Way To Include Firstdate And Lastdate In Range Of Dates?

Jun 7, 2013

I am using Between Me.Firstdate and Me.Lastdate in a query. I thought that it would include both Firstdate and Lastdate in the range. But it appears that it does not. Is there a way to include Firstdate and Lastdate in the range of dates?

View 13 Replies View Related

General :: Automatically Change Data Paste To Include First Eight Characters

Dec 8, 2012

I have a form with one control field that I paste a 17 digit alpha numeric value into. I have a command button that then runs a query based on the pasted value.

Is there a way to automatically change the data I paste to include the first eight characters, replace the 10th and 12th position with wild cards, and delete the remaining 6 characters ?

Example:

Copy: 1GTEM14M0WZ526688

View 3 Replies View Related

Tables :: Create A Table Which Would Include Financial Data By Year / Client

Nov 15, 2012

I am trying to create a table which would include financial data, by year, by client. For example:

Client #1

2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500

Client #2

2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Etc.

Should I have headers as such:

Client #, 2009Revenue, 2009Expenses, 2009NetIncome, 2010Revenue, 2010Expenses, 2010NetIncome, 2011Revenue, 2011Expenses, 2011NetIncome

View 6 Replies View Related

Modules & VBA :: Selecting Fields - Change Text To Include Data From A Table

Nov 5, 2014

I have an on click event to mail a report which works. I want to change the text to include data from a table.

I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.

Code is :
Private Sub cmd_mailreport_Click()
Dim office As Object
Set office = Checks.office
DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _
"info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _
True, ""
End Sub

View 1 Replies View Related

Queries :: Execute Query That Doesn't Include Specified Expression As Aggregated Function

Mar 21, 2013

I am receiving this error when i try to run a query:

"You tried to execute a query that doesn't include specified expression as an aggregated function"

There are two images attached which show the SQL view, the error and the design view.

View 11 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

12 Month's Of Data

Aug 22, 2006

I'm looking for a starting point for this query.

I currently have a table "tblMonth" with the fields: product, year and each month (Jan, Feb, Mar, Apr, etc.)

I enter month volumes into this table which is used to calculate savings amounts. Currently I create queries that show the month data for each month for 2006. Each month is a column header and I just have 2006 in the "Year" column at this point.

I now need to create another one that will show data for the current month plus the next 11 months, August 2006 through July 2007. In my tblMonth table I just add the product again for the year 2007 and enter the data so I will have two rows of data for each product.
How can I get my query to show 2007 data as well with the column headings beginning at Aug and ending at July 2007?

I can create the data but Jan 2006 and Jan 2007 are in the same column.

I'm sure it's easy, just can't wrap my brain around the concept yet.

Thanks

View 14 Replies View Related

Queries :: Last Row Only By Month

Apr 9, 2014

I have a table set up like:

CUSTOMER ID / CUSTOMER NAME / PAYMENT ID / PAYMENT DATE

Only want to show the last of each payment per month in form based on query.

So if they have a payment in April of $900 on 4/15 and then a payment in May 5/2 $500 and May 15 $1000

It will show two rows...one for April $900 payment and one for May $1000 payment.

I know the logic is something like

If more than one payment row for month, show last, otherwise only one payment for that month show the one payment.

I tired just setting hte "last" paramater but it only shows the very last payment, not taking month into account. So it only shows the $1000 may payment. thx

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 :: Records From Now Until End Of Next Month

Sep 27, 2013

I am using the following code to get results from next month,

Code:

Month([ptdate])=Month(Now())+1

which works great to get next months results. I try to get what is left of this month by adding the code:

Code:
Between Now() And Month([ptdate])=Month(Now())+1

This actually gives me results from July and August, eventhough it is Sept.Basically I want the results from the remainder of this month until the end of next month.

View 2 Replies View Related







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