Query that changes each month

My dept has a table with colums like this

TaskNo,Owner,Desc,DueDate etc.....then  Jan,Feb,Mar,Apr,May,Jun,July.....

we each have our own query filtered on owner and each month we bring in the 
correct month and mark our task as completed when were are done under the 
month column with they day it was completed.

Is there a way to create a query that always pulls the current month? (or 
the prior month which is really what I need)

Is there a better way to desing this?  Do we need two tables?

thanks,
Billy
-- 
Billy Rogers

Dallas,TX

Currently Using  SQL Server 2000, Office 2000  and Office 2003
0
Utf
7/25/2007 9:02:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
760 Views

Similar Articles

[PageSpeed] 19

You certainly need some sort of design change. Any time that you have 
something like Jan,Feb,Mar,Apr,May,Jun,July for column headings, there's a 
problem.

If you just need the completion date for a particular Task and Owner, you 
really only need one date/time column called something like CompleteDate. 
Then there are a number of ways to extract what month/year the job was 
complete.

If it's more complicated than that, you may need another table instead.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"BillyRogers" wrote:

> 
> My dept has a table with colums like this
> 
> TaskNo,Owner,Desc,DueDate etc.....then  Jan,Feb,Mar,Apr,May,Jun,July.....
> 
> we each have our own query filtered on owner and each month we bring in the 
> correct month and mark our task as completed when were are done under the 
> month column with they day it was completed.
> 
> Is there a way to create a query that always pulls the current month? (or 
> the prior month which is really what I need)
> 
> Is there a better way to desing this?  Do we need two tables?
> 
> thanks,
> Billy
> -- 
> Billy Rogers
> 
> Dallas,TX
> 
> Currently Using  SQL Server 2000, Office 2000  and Office 2003
0
Utf
7/25/2007 9:14:01 PM
"You certainly need some sort of design change." ...that's what I'm trying to 
figure out....exactly what I need to change.

This task list is for a set of reports that have to be done each month by a 
specific date.  There is column that list the day of the month (10th, 20th, 
25th etc).
People use the table to see what they have to do each month and what they 
have already done for the month.


-- 
Billy Rogers

Dallas,TX

Currently Using  SQL Server 2000, Office 2000  and Office 2003


"Jerry Whittle" wrote:

> You certainly need some sort of design change. Any time that you have 
> something like Jan,Feb,Mar,Apr,May,Jun,July for column headings, there's a 
> problem.
> 
> If you just need the completion date for a particular Task and Owner, you 
> really only need one date/time column called something like CompleteDate. 
> Then there are a number of ways to extract what month/year the job was 
> complete.
> 
> If it's more complicated than that, you may need another table instead.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "BillyRogers" wrote:
> 
> > 
> > My dept has a table with colums like this
> > 
> > TaskNo,Owner,Desc,DueDate etc.....then  Jan,Feb,Mar,Apr,May,Jun,July.....
> > 
> > we each have our own query filtered on owner and each month we bring in the 
> > correct month and mark our task as completed when were are done under the 
> > month column with they day it was completed.
> > 
> > Is there a way to create a query that always pulls the current month? (or 
> > the prior month which is really what I need)
> > 
> > Is there a better way to desing this?  Do we need two tables?
> > 
> > thanks,
> > Billy
> > -- 
> > Billy Rogers
> > 
> > Dallas,TX
> > 
> > Currently Using  SQL Server 2000, Office 2000  and Office 2003
0
Utf
7/26/2007 12:14:00 PM
Reply:

Similar Artilces:

Can I change the calendar year beginning date?
Our fiscal year runs July 1 thru June 30 of each year. I need to track attendance, gas cards, etc for monthly reports. I have a HUGE table with a gazillion queries and reports for each one. I need one report to reflect quarterly output for our fiscal year as stated above. Can I make this report do that? Thank you! Create a query to use as the source for your report. In query design, type this into the Field row: FinYear: DateAdd("m", -6, [InvoiceDate]) replacing InvoiceDate with the name of your date field. This yields 2007 for all dates in the 2007/2008 financial year...

change horizontal header position in excel
Anyone know how to change the horizontal position of the HEADER or FOOTER in EXCEL???? I would greatly appreciate any help. Thank you for your time. pico Wrote: > Anyone know how to change the horizontal position of the HEADER o > FOOTER in > EXCEL???? I would greatly appreciate any help. Thank you for your time. Pico, Can't you go into View/Header-Footer and use the "Left-Center-Right" catagories? You could use spaces in front of your data to move back an forth. Dav -- Piranh ----------------------------------------------------------------------- Piranha's Profi...

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Why is Excel changing the last 2 digits of a 17 digit num to 00.
When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to 00 when I leave the cell. Format - Cell does not have a setting to stop this 'feature'. How do I make Excel recongize the large number? On Thu, 28 Jul 2005 19:09:01 -0700, "Allie" <Allie@discussions.microsoft.com> wrote: >When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to >00 when I leave the cell. Format - Cell does not have a setting to stop this >'feature'. Excel Specifications and Limits: Calculation specifications Feature Maximu...

Detect page change in PropertySheet?
Hi, I have a property sheet that is the parent of several property pages. If the user clicks a tab to change to another page, is there a way I can detect this (and possibly prevent the change) in the PropertySheet dialog? Does windows/MFC send any kind of message to the PropertySheet dialog? I looked at all of the over-rides and WM_ message, but cannot see anything. Thanks! Not directly. You can catch that in the CPropertyPage and relay the message to the CPropertySheet. (I know you are asking, why doesn't the CPropertySheet get the notificatio first? who knows!) AliR. "...

Changing e-mail address
dI´m trying to change mail-address in AD-Users and computer. From "nnnn@xxx.xx" to "nnnn@yyy.xx", but it won't work. On the tab "e-mail Addresses" i try to set "nnnn@yyy.xx" as Primary, but it don't stay that way. If I open directly and check it seems right, but close and do one more client, the first has change back to "nnnn@xxx.xx". What am I doing wrong ???????? You will need to uncheck the checkbox "Automatically update e-mail addresses based on recipient policy" for the change to stay. Also make sure that you ...

... I want to change a chart type ...
I have a "Line - Column" chart in my spreadsheet. My Bars represent the month which is in a Linked Cell. The Value of the Bars is a Dollar amount from a Linked Cell. The Line represents a Target Dollar Value. I would like to add a second line that would represent a count from a linked range of cells. Can I edit my existing chart, or do I have to create a new one? Could you give me a few pointers? Darrell Select and drag the new column onto the chart. In the resulting dialog box, ensure you specify 'new series'. Once XL finishes adding the series, if it is not the...

Changing fonts in msgbox
If i create a msgbox with a prompt, can i change or modify the font of the text in the msgbox ?? Ditto Inputbox.... thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You can't change the font used in a MsgBox. This limitation is one of the prices you pay for the simplicity of a MsgBox. If you need to change the font, you need to create a UserForm and display that form instead of using a MsgBox. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpears...

Change of field separator
When I open CSV files in Excel all data is put in one column. Can anyone tell me, where I change my set up, so I get another field separator? Please be specific, because my Excel is a Danish version, and sometimes I have a hard time following the English instructions. Thanks! Jane Hi you specify this in the Windows regional settings. Another idea: - rename your *.csv file to a *.txt file - now open it with excel. The Import wizard should appear and should allow you to specify a different delimiter "Janepige" wrote: > When I open CSV files in Excel all data is put in one co...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Paper settings change for specific printer
I just installed access 2007 and my application uses reports to print labels. Specifically a Dymo turbo 400 and 30252 address labels. when i was using acess 2003 i specified to use a specific printer and everything was great. and when i copied updated FrontEnds to my networked computers every thing transfered and the labels printed fine however with 07 the "Specifc printer" has a different page size.. i fixed all the reports on my master FE but the changes didnt stay when i copied FE to other computers and im not sure where to go. i installed all the printers from the s...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

How to Change Outgoing port in Exchange 2003
I am running Exchange 2003 on a SBS 2003 server and using the POP3 connector to get the e-mail from my account and also send the email out throught the SMTP server. Starting last Friday the outgoing mail is not being sent. Earthlink tells me that they are now using port 587 for the outgoing mail. How do I change the outgoing mail SMTP port? Thanks -- Don Holden -- Don Holden On Mon, 26 Jun 2006 09:07:02 -0700, Don Holden <DonHolden@discussions.microsoft.com> wrote: >I am running Exchange 2003 on a SBS 2003 server and using the POP3 connector >to get the e-mail from my...

grayscale autoshape changes to color when converting to PDF
When converting from Microsoft Publisher 2007 to Adobe Acrobat 8.0 PDF, the autoshape picture that we changed to grayscale changes back to color when reviewing the created PDF. Are you setting Acrobat to print in black and white? Select the PDF printer, properties, paper/quality tab. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "CDJ" <CDJ@discussions.microsoft.com> wrote in message news:6BE9EE01-97DB-4AB6-BD9C-25E27E0BB68D@microsoft.com... > When converting from Microsoft Publisher 2007 to Adobe Acrobat 8.0 P...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

Outlook changing PDF extention to PDF=
I have a client that uses Outlook 2000 for e-mail. About a week ago, she started complaining that everyone that she sent PDFs (Ex. Document.pdf) that they were receiving PDF= (Ex. Docuement.pdf=). This is only happening with PDF files, not word documents or anything else. I have tried to change the outgoing mail server, but that made no difference. Is there something in Outlook that can be causing this? is she using RTF formatted messages? Antivirus or antispam scanners on outgoing email? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2...

Preventing changes in the Options
Hello, Is there a way in Excel to prevent users from making any changes in the Options (ie protect or disable the Options Dialog box). Thanks in advance, brgds, Chantal ...

How to change the FROM field in email campaign activity?
How to change the FROM field in email campaign activity? I would like to create an email campaign activity and change the TO field of the email to be abother name instead of mine I would also like to add attachments I can't find a way to do this Any help is very much appreciated Thank you Stella Hi Stella, There are many ways to send marketing emails from CRM: Quick Campaigns Quick Campaigns with mail merge using Microsoft Word E-mail Campaign Activities E-mail Campaign Activities with mail merge using Microsoft Word Direct e-mail using templates Send a workflow e-mail Integrate...

Difficult query
Hi, I have a table called WT,contains the fields "Type of call","DateW" and "ID", this table is used to by users to add rows that determine type of calls received in a call center,I want to create a query with the following criteria: 1- To view number of calls received in each type per day. 2- To show the field "Type of call" in this query,even the type that wa not used,and to view number 0 in the count field. 3-Prcentage of each type of call . On Dec 11, 3:52 pm, Pietro <Pie...@discussions.microsoft.com> wrote: > Hi, > I have a tabl...