I want year in one table to be less or equal year in another table

Hi

I have some problems writing a query and I hope someone can help me. 

I have a database with serveal tables. In one table I have this information, 
Lake ID-number, treatment, year for treatment. In another table I have Lake 
ID-number, fish species (I am intrested in pike), year when pike is present. 
I want to find all lakes that have pike present before the treatment was 
done, I want the year in the second table to me less or equal the year in the 
first table. Is there a easy way to do this?

Thanks


0
Utf
4/21/2010 7:18:01 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
767 Views

Similar Articles

[PageSpeed] 34

Try something like this substituting your table and field names.

SELECT tblTreatments.DateTreated, Year([DatePresent]) AS YearSpeciesPresent, 
tblSpecies.Species
FROM tblTreatments INNER JOIN tblSpecies ON tblTreatments.LakeID = 
tblSpecies.LakeID
WHERE (((Year([DatePresent]))<=Year([DateTreated])) AND 
((tblSpecies.Species)="Pike"));

Regards

Kevin

"Cecilia" <Cecilia@discussions.microsoft.com> wrote in message 
news:A5C425F4-CD0E-4621-8A92-C000E4697C65@microsoft.com...
> Hi
>
> I have some problems writing a query and I hope someone can help me.
>
> I have a database with serveal tables. In one table I have this 
> information,
> Lake ID-number, treatment, year for treatment. In another table I have 
> Lake
> ID-number, fish species (I am intrested in pike), year when pike is 
> present.
> I want to find all lakes that have pike present before the treatment was
> done, I want the year in the second table to me less or equal the year in 
> the
> first table. Is there a easy way to do this?
>
> Thanks
>
> 


0
kc
4/21/2010 11:55:12 AM
Reply:

Similar Artilces:

A bug in GUI of creating query with more outer joins between two tables
Hello, in GUI of Access 2007 (12.0.6423.1000) SP2 MSO (12.0.6521.5000) is generally very simple to make a select query between two tables joined with two or more outer joins (of the same kind normally). But you should be careful. If you build first join clicking on a field of the first table, drag then the connection to the field of the second table and choose the right outer join, then you have to make the next outer join between the same two tables on the same way. If you begin but with the second table first and although you choose the same type of outer join (the arrow...

credit card trans from one to another
My wife has her own copy of money 2003 on her own computer. She downloads monthly credit card transactions and then goes through each transaction and assigns a category/subcategory. She prints out a monthly transaction report and gives it to me. I then pay it via an online bank and use split transactions to enter the category/subcategory information manually. The transaction then shows up as being a bank rather than credit card transaction in the reports. As an attempt to avoid the manual transfer of information, she exported the transactions to a gif file. I then import them to my money. On...

Rename database Object from another Database
I need to Delete a table and rename other table with the name of the deleted table. But I wan to do all this from another database using VBA Hi, you can do something like this, air code to show the idea: dim dbs as database set dbs=dbengine.opnedatabase ("c:\my.mdb") dim tdf as tabledef set tdf=dbs.tabledefs("MyTable") tdf.name="NewTableName" dbs.close and you can use TableDefs.Delete to delete table -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com "Manuelauch" <Manuelauc...

Finding the currently selected cell in another worksheet
Hi, In Excel 2002 I want to display the current row of worksheet A in worksheet B (in another format). Does anyone know how to do this? Ideal would be if this updates automatically, but performance wise I guess a macro activated by a button in worksheet A would be the best solution. TBH, Jeroen Jeroen, I don't know of a way in a formula to determine the active cell of the worksheet. Worksheets other than the active one don't have an active cell. I think macros are the only way. Put this in a regular module: Public Roww As Long Put this in the sheet A module: Private Sub W...

referencing another sheet and using auto fill
Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 ...

Closed Year Finacel Statements
Hello, I have closed the year , but after that when trying to access any finacela statement report , I got this error : * The open year you've selected for a temporary column is invalid. * How can solve this error? With regards, On Apr 19, 10:52 am, Amdawi <amd...@gmail.com> wrote: > Hello, > I have closed the year , but after that when trying to access any > finacela statement report , I got this error : > * > The open year you've selected for a temporary column is invalid. > * > > How can solve this error? > > With regards, Repl...

Users accessing same table
I have an Access 2003 database on a small network (server + 4 pc's) with the database split and each PC having its own front end. Recently the users have reported not being able to access the same form at the same time, they have to wait until the other user has finished and closed it before accessing it. Any suggestions regarding this problem? What error message are they getting? They should have no issue opening the same form, but there could be a lock conflict if they try to access the same record at the same time. Please post some more information (the exact error) s...

Excel VBA
Hi. Is it possible to use VBA in one workbook to copy a worksheet fro another workbook into another different workbook? If so....how? Thank -- Message posted from http://www.ExcelForum.com Hi try recoding a macro while doing this manually. And yes it is possible :-) -- Regards Frank Kabel Frankfurt, Germany "cjh1984 >" <<cjh1984.16x63k@excelforum-nospam.com> schrieb im Newsbeitrag news:cjh1984.16x63k@excelforum-nospam.com... > Hi. > > Is it possible to use VBA in one workbook to copy a worksheet from > another workbook into another different workbook? ...

Anchor clipart in a powerpoint table
How do I anchor clip art or inserted pictures into a cell in a Powerpoint table. We have different team logos 'on' a table to show roles and responsibility, I need to be able to get the logos 'in' the cells so they move relative to the table as it is edited. Regards There's no simple way to do this. There's a possible work around here: http://www.pptalchemy.co.uk/PowerPoint_Tables_Picture.html -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "iwaddo&...

Best way to change style of a table
I have a table I added via VBA like this: set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTable(...) I then add some cells to it by: oTable.Cell(...).Shape.TextFrame.TextRange.Text = "some text" However, I cannot get a handle on these cells to update the font, fill, etc. What is the best way to do this? On Sun, 13 Dec 2009 21:01:01 -0500, Clifton Ivey <clifton ivey <"africom.mil>"> wrote: > I have a table I added via VBA like this: > > set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTa...

Pivot Table Sort 03-05-10
Hi I'm trying to sort a pivot table but it'll only let me sort on the first column, I need to sort it on the third, please can anyone help? Hi You will need to drag your 3rd Field to the 1st position in the row Area of the PT. -- Regards Roger Govier jen2018 wrote: > Hi > > I'm trying to sort a pivot table but it'll only let me sort on the first > column, I need to sort it on the third, please can anyone help? ...

Combining date and time into one cell
I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How can I merge these two in one cell with the format m/dd/yy hh:mm ? Thanks. Date in A1, time in B1, combined in C1: formula is =A1+B1 and format as you describe. On Sat, 22 Jan 2005 14:03:02 -0800, "Kelly C" <KellyC@discussions.microsoft.com> wrote: >I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How >can I merge these two in one cell with the format m/dd/yy hh:mm ? > >Thanks. =TEXT(A1,"m/dd/yy ")&TEXT(B1,"hh:mm") is one way -- HT...

Multple calendars in one user
I am trying to get multiple calendars set up under one user and some of the events would be shared between different ones. I can not find how to essentially invite myself to an event between the different calendars. You are allowed to create the different calendars but you are not allowed to share events between them. Anyone done this before? Create a meeting request in the non-default calendar and invite yourself. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by ...

Click and Add new order year to all customers?
I have a main form with customer info. A subform below has a yearly record of the progression of the order. I would like to click a button and instantly add a new year record for each customer. I currently have it set up that the user has to look up each customer individually and go down to the subform click the combo and select order year. How can I do this with one click instead? Thanks Dustin Execute an append query statement to add the records. This example adds a record to tblOrderInfo for each customer in your tblCustomer table, using the year of today's date: Dim strSql ...

Keystrokes and mouse clicks to another app
Hi all, I'm managing a project that will, as part of it, require me (so I'm told) to type in 800 NI numbers. I have the numbers in a file, but they can't (or won't) upload them. I was therefore considering writing an app that will enter these numbers via their interface, by sending keystrokes and mouse clicks to their window. I'm guessing that I need to get a handle to it and then send it messages, but that's as far as I've got. I believe that the app is web-based, running in IE6. It may be more effort to do it this way, of course, but it'll be a lot more int...

Copying one cell to muliple cells #2
Cell a1 is a date cell. Cells b1-b30 are date cells. What I am trying to do is every time that I enter a new date in Ai, I want it to go to a blank cell in b column and not over write the dates that are already there. A B 2/3/04 1/6/04 1/20/04 1/25/04 2/3/04 right click sheet tab>view code>copy\paste this>format col B as desired date >save. Now when you put a date in a1 such as 8/1, the last cell+1 in col b will get that date. Private Sub Worksheet_...

Condensing text values from one wksht to another
I have text data in column A of sheet one that is too lengthy. I wish to condense the data by copying JUST that data that leads up to the first space (break in the string)and copy to column A of sheet two. Example: blah,blah,blah blahhhh becomes blah,blah,blah. Is there a formula for the destination cell in sheet two that will accomplish this task, or does it require vba? If so, could someone walk me through it?? I am nearing desperation. Please help!!!! Jayceejay, In cell A1 of sheet2 fill in this formula and copy it down to A?? =LEFT(Sheet1!A1,FIND(" ",Sheet1!A1,1)-1) I ass...

I want to print my own money !!!!
What do I need ? On 23 Jan 2005 00:06:36 -0800, pain_wheels@hotmail.com (Dan Simper) wrote: >What do I need ? A number of years to spend in jail. ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- ...

How to set up two accounts with one brokerage?
I am using MS Money 2003. I have two accounts with one online brokerage. One is my 401k and one is just some stocks. In order to log in, I have to use a different user name and password depending which account I want to access. The problem is, MS Money only allows me to enter one user name and password for each brokerage. I change the online properties of each MS Money account one at a time, but the changes do not stick. Both accounts wind up with the user name and password of the account I changed last? Am I doing something wrong? Any help would be appreciated. In microsoft...

money 2002 only reports one discrentary account.
I want to have a cash flow report that I can rely on. Money 2002 reports my dining as trended , which is fine because its defined as discretionary. However there are other discretionay catagories that are on my credit card and checking account that are ignored ! Why is this. Are there any updates for Microsoft 2002 OEM version 10. Is there any q & a I can refer to? You have not provided enough information to understand your question. I use Money 2002 and its reports are excellent. Don't know what cash flow report you are talking about. The Cash Flow Review in Accounts...

Invalid partition table
I have a Toshiba Laptop which came pre-installed with Vista home. Lately, I have received an Invalid Partition Table on bootup. I tried using the recovery disk with the OS on it as a boot disk but it doesn't seem to be working even when I tell the bios to boot from CD/DVD. Any suggestions on how I can create a boot disk and have the computer "see" it upon startup? Thanks so much!! Sandy -- regnells Posted via http://www.vistaheads.com If it is still under warrantee, take it back to the store for a refund or another that works. "regnells&quo...

Open another program sing VBA
Hi all, I would like to open another program, (MYOB), by clicking its picture that I've pasted to a worksheet. I know how to assign a macro to the picture but I'm having difficulties in writing the macro to execute the MYOB program. Rob Hi Rob, Right-click the picture and assign a hyperlink to it. Ed Ferrero "Rob" <NA> wrote in message news:ONCsTTYMFHA.3796@TK2MSFTNGP10.phx.gbl... > Hi all, > > I would like to open another program, (MYOB), by clicking its picture that > I've pasted to a worksheet. I know how to assign a macro to the picture...

Office 08 works only for one user
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I have installed Office 08 home and student edition. While logged under the admin user everything works fine but when I log into the other user ID Word will not work properly. It open with some weird layout and everytime you move the window it drags and copies itself...very odd. The other programs, excel, ppt, etc seem to work fine on both user log ins but not word. I have tried reinstalling it 3 times to reach the same result. Please help. First & Foremost: Reinstalling complex apps such as Office is *never* the first step to co...

Can one have an extra margin gutter, for binding a document print.
I need to print Excel documents (landscape A4) on both sides of the paper, and bind them along the long edge. Thus I need an extra margin gutter along the top of odd pages, and along the bottom of even pages. (It's wasteful to put the extra margin on both top and bottom of every page...) Using Excel 2002 SP1. Hi Dave, AFAIK, Excel provides no options for true duplex printing. Headers/footers, margins, etc. are only geared for single-sided printing. You might want to consider using Word for that purpose. You have more page layout options that may give you what you want. Use Word...

Requery one subform from another
Hi guys, I have a combobox on a subform within a subform on a tabbed control on a mainform. The content of this combo box is "Username" and determined by a username field in a users table. That users table can be appended using another subform on the mainform. Does anyone know the requery code so that if I add a record to the table, the combobox will automatically be updated so that the new record is available to select in the combo box? Tony Hiya I figured it out. I was going about it in totally the wrong way. I just stuck a Requery in the OnGotFocus event on the combobox. I ca...