Date Range query

[It_thug]

First, I want to thank all the people who have helped me in the past.

I have a bit of a dilemma for this current query.

Okay, As an Example, I have a Company Code, a Variable, a Date Range and a Value.

I know how to do the Date Range to pull between any two dates. (in this case, years). What I am trying to do, though, is pull only those Company's that have a Value for all years in the date range.

SELECT DISTINCT Data.COMPANY
FROM Data
WHERE (((Data.VARIABLE)=[Enter Variable]) AND ((Data.YEAR) Between [Enter Start Year] And [Enter End Year]) AND ((Data.VALUE)<>"" And (Data.VALUE)<>"0"));

But this isn't pulling just those that have value for all years. (So if I do a date range of 2000 to 2010 it will pull all the Company's that have a value in any of those years. But I want only Company's that have a value in all of those years.)

Number of years and the dates can change.

Any ideas?

Much obliged. And Happy New Year.

 

[jdraw]

First, I want to thank all the people who have helped me in the past.

I have a bit of a dilemma for this current query.

Okay, As an Example, I have a Company Code, a Variable, a Date Range and a Value.

I know how to do the Date Range to pull between any two dates. (in this case, years). What I am trying to do, though, is pull only those Company's that have a Value for all years in the date range.

SELECT DISTINCT Data.COMPANY
FROM Data
WHERE (((Data.VARIABLE)=[Enter Variable]) AND ((Data.YEAR) Between [Enter Start Year] And [Enter End Year]) AND ((Data.VALUE)<>"" And (Data.VALUE)<>"0"));

But this isn't pulling just those that have value for all years. (So if I do a date range of 2000 to 2010 it will pull all the Company's that have a value in any of those years. But I want only Company's that have a value in all of those years.)

Number of years and the dates can change.

Any ideas?

Much obliged. And Happy New Year.

You may have some issues with your field "YEAR" which has specific meaning in Access. When you say , "But I want only Company's that have a value in all of those years", a Value in what field?
I am adjusting your query based on a value in the Year field.

I believe your query should be:

SELECT DISTINCT Data.COMPANY
FROM Data
WHERE Data.VARIABLE=[Enter Variable] AND 
Data.YEAR Between [Enter Start Year] And [Enter End Year] AND 
Data.YEAR IS NOT NULL;

If you have a field Data.Value that you mean should be populated, then
replace Data.YEAR IS NOT NULL with Data.Value IS NOT NULL

 

[It_thug]

Thanks. Yes. Year is actually just what I threw out here. all of the info in there is bogus, as i didn't want to give away the DB structure. So thank you.

Sorry, I wasn't clear.

The only data that i want to retrieve is where VALUE has a value for all years in the Range entered, for the variable used.

I tried the Not null before, and was still getting the same thing. But that doesn't mean I did it right. I'll try again Monday, and let you know.

Thanks for the tip.

 

[Brianwarnock]

Having run that query to select all of the data between the dates you will need to run another query
roughly
q2 select company, count(*) as years
from yourquery
group by company
having count(*)= (endyear-startyear)+1

This will give only the companies having data for all of the years.

As the parameters are required twice I would use a form to enter them and drive the query.

Brian

 

[It_thug]

Thanks to both of you. I was able to get it working with a combination of the two.

Now on to the second part of the problem. I may open a new question for this one. I think I may only be able to do this with a VBA script, in which case I'm in for a lot of learning on the fly

Is there a way, in the query to have the results for the number of years show up in their own unique columns?

I can do that with hard coding the query, and using joins. But the number of Years could vary from request to request, so I need it to be done on the fly.

I'm not real strong with VBA, but I think that may be where I have to go with this. Not asking for anyone to do the work for me, but maybe nudge me in the right direction? I've tried some searchs on the interwebs, but no luck so far as to what I may need to do.

 

[Brianwarnock]

I'm more than a little confused here. At the moment you would appear to have a list of the companies that have the chosen data in each of the chosen years.

Now what is it you want to do with this list?

Brian

 

[It_thug]

I'm more than a little confused here. At the moment you would appear to have a list of the companies that have the chosen data in each of the chosen years.

Now what is it you want to do with this list?

Brian

No worries on the confusion. I'm a bit scattered on this as well.

I needed the list of companies, to make it unique to those that had a Value (based on the variable) for all years chosen. (so if it was 2000-2010, I'd get only those Companies that had a value for all 10 years.).

now, I need to present all 10 years for each company, and the value. Preferably in a format like so

Company Variable Year1 year2 year3 etc
Company1 1501 Value Value value

But since I don't know the number of years that will be chosen, I need that to be something that can be done on the fly, for any date range entered.

This got way more complicated than I expected it to for this project, and got a little bit out of my depth.

I'm thinking this may need to be done in VBA or something like that, but I'm no programmer...

 

[It_thug]

I'm thinking this may need to be done in VBA or something like that, but I'm no programmer...

and I, by no means, want anyone to do the work for me. But def need a nudge in the right direction....

and very much appreciate all help previously provided so far.

 

[Brianwarnock]

Ok take alook at this thread and see if it helps, but you may needto start a new thread to get what you want.
I think that this type of question has been answered by people like Pbaldy fairly recently. That is not an invite to PM him just an indication that a fresh thread with a fresh title might elicit more responses.

http://www.access-programmers.co.uk/forums/showthread.php?t=106426&highlight=wayne+recordset


Brian

 

[It_thug]

Ok take alook at this thread and see if it helps, but you may needto start a new thread to get what you want.
I think that this type of question has been answered by people like Pbaldy fairly recently. That is not an invite to PM him just an indication that a fresh thread with a fresh title might elicit more responses.

http://www.access-programmers.co.uk/forums/showthread.php?t=106426&highlight=wayne+recordset


Brian

Thanks. I'll check it out. And I appreciate your help.

 

[It_thug]

Ok take alook at this thread and see if it helps, but you may needto start a new thread to get what you want.
I think that this type of question has been answered by people like Pbaldy fairly recently. That is not an invite to PM him just an indication that a fresh thread with a fresh title might elicit more responses.

http://www.access-programmers.co.uk/forums/showthread.php?t=106426&highlight=wayne+recordset


Brian

Brian, that is perfect. I think it will do exactly what I want it to do, with minimal changes. Much obliged.

I just wasn't searching the site with the right keywords to get to where I needed to be.

Thank you again.

 

[Brianwarnock]

Yes searching can be a problem, anyway I hope it works for you.

HTH

Brian

 

[It_thug]

Brian

Thanks again. I was able to finally do this using a Pivot table... the reason it wasn't working before was that the Value field(pulled from the DB) was a Memo, and not a number.

Chose to add a step to make a temp table with the column as a number.. then used a query to pull the data, format it to a Pivot table and export to Excel....

Anyway, wanted to thank you again for your help.

 

[Brianwarnock]

Glad you got there.

Brian