Lookup IIF query???

[camollie]

Hi All,

I am just a beginner and I have a problem. I am building a db in 2007 and in the source data there is a date field which has dates into the future but I only want the query to run with dates from the current and previous months. I do not want the user to have to stipulate the date range so I have created a make table Month(Date)) this I have put into a macro which the db admin can can monthly to update all source data and run all reference queries. The Month(Date)) query returns a numerical value Sept = 9, therefore my logical was to create a lookup table rows 1-12 and in the next column the required month end date eg: Sept may close off on the 25th of the month, my idea was to the make this amake table query which would only generate the create month end date and then I intended to query against this date for all other queries ie: <= 25/09/2013.

The problem I is how to write the lookup query ie: Lookup Current Month, if = 9, lookup referenceMonthtbl where column 1 = 1-12 and therefore row 9 = {date} in column 2

any ideas and sorry if its a bit wordy

Alex

 

[plog]

Yeah, you lost me. Generally Make Tables are band-aids used by people who just can't understand relationships or have set them up incorrectly. Can you post some sample data from your tables (include field and table names) and then what you would like to be returned for a specific query/search. Use this format to post data:


TableNameHere
Field1Name, Field2Name, Field3Name
David, 12/13/2010, 194
Sally, 4/4/2007, 86
Jim, 2/3/2012, 17

 

[camollie]

Hi Plog - Many thanks for your prompt reply and help (even though you may feel you haven't done anything - you have )

Think I was over complicating matters and have a brain f*rt. it was just a relationship link between two of my tables.

The next stage of development will be to remove some of my sticking plasters (tables) as its starting to look like a mummy.

thanks again