complicated query... help needed

[Norma]

I am trying to make a query pull data from between the dates I enter
in the parameter but also look back 'in time' to see where 2 other
fields have null values, and only pull data into the query if those 2
fields are null prior to the beginning date of my parameter.
The reason for this (to help make this a little clearer)is to pull
production into a query if it is a 'new' for the month. That is, never
run before the dates entered. I have 2 fields that I am looking at to
see if it is a 'new' product, those fields are hours and cases.
Any help would be greatly appreciated!

Norma

[deko]

Re: complicated query... help needed

"Norma" <njhildebrand@s uscom.net> wrote in message
news:207ecba8.0 406231351.3614a 883@posting.goo gle.com...[color=blue]
> I am trying to make a query pull data from between the dates I enter
> in the parameter but also look back 'in time' to see where 2 other
> fields have null values, and only pull data into the query if those 2
> fields are null prior to the beginning date of my parameter.
> The reason for this (to help make this a little clearer)is to pull
> production into a query if it is a 'new' for the month. That is, never
> run before the dates entered. I have 2 fields that I am looking at to
> see if it is a 'new' product, those fields are hours and cases.
> Any help would be greatly appreciated!
>
> Norma[/color]

Off the cuff, I'd suggest using two (or even three) queries: one that
narrows the set to your first criteria, then another that has the secondary
criteria, and joins to the first. If you come up with a first draft and
post it here I'm sure someone, if not myself, can provide better guidance.

[Phil]

Re: complicated query... help needed

select x.job from (select t.job from t where t.fldDate between param1
and param2) as x
left join (select t.job, sum(t.cases) as sc, sum(t.hours) as sh,
t.fldDate from
t group by t.job, t.fldDate having t.flddate<param 1) as y on x.job =
y.job where y.job is null;

Norma <njhildebrand@s uscom.net> posted in
news:207ecba8.0 406231351.3614a 883@posting.goo gle.com
[color=blue]
> I am trying to make a query pull data from between the dates I enter
> in the parameter but also look back 'in time' to see where 2 other
> fields have null values, and only pull data into the query if those 2
> fields are null prior to the beginning date of my parameter.
> The reason for this (to help make this a little clearer)is to pull
> production into a query if it is a 'new' for the month. That is, never
> run before the dates entered. I have 2 fields that I am looking at to
> see if it is a 'new' product, those fields are hours and cases.
> Any help would be greatly appreciated!
>
> Norma[/color]

--
Phil

[Norma]

Re: complicated query... help needed

Phil,
I am trying to decipher what you wrote and put it into language that I
would understand. I am a novice in access SQL. Is there anyway you can
restate all that in laymans terms?

Thanks,
Norma

"Phil" <stuff@basketba ll.net> wrote in message news:<WdpCc.806 0$6k3.8018@news svr22.news.prod igy.com>...[color=blue]
> select x.job from (select t.job from t where t.fldDate between param1
> and param2) as x
> left join (select t.job, sum(t.cases) as sc, sum(t.hours) as sh,
> t.fldDate from
> t group by t.job, t.fldDate having t.flddate<param 1) as y on x.job =
> y.job where y.job is null;
>
> Norma <njhildebrand@s uscom.net> posted in
> news:207ecba8.0 406231351.3614a 883@posting.goo gle.com
>[color=green]
> > I am trying to make a query pull data from between the dates I enter
> > in the parameter but also look back 'in time' to see where 2 other
> > fields have null values, and only pull data into the query if those 2
> > fields are null prior to the beginning date of my parameter.
> > The reason for this (to help make this a little clearer)is to pull
> > production into a query if it is a 'new' for the month. That is, never
> > run before the dates entered. I have 2 fields that I am looking at to
> > see if it is a 'new' product, those fields are hours and cases.
> > Any help would be greatly appreciated!
> >
> > Norma[/color][/color]

[John Winterbottom]

Re: complicated query... help needed

"Norma" <njhildebrand@s uscom.net> wrote in message
news:207ecba8.0 406231351.3614a 883@posting.goo gle.com...[color=blue]
> I am trying to make a query pull data from between the dates I enter
> in the parameter but also look back 'in time' to see where 2 other
> fields have null values, and only pull data into the query if those 2
> fields are null prior to the beginning date of my parameter.
> The reason for this (to help make this a little clearer)is to pull
> production into a query if it is a 'new' for the month. That is, never
> run before the dates entered. I have 2 fields that I am looking at to
> see if it is a 'new' product, those fields are hours and cases.
> Any help would be greatly appreciated!
>[/color]

Post your table structure with some sample data and the output you need and
someone will be able to help you.

[Phil]

Re: complicated query... help needed

make a query on your table in the design screen. you want to return the
job and date fields of your table. in criteria for date field it should
be something like:
between [param1] and [param2]
save query and call it x or whatever.

make another one, same table and fields but also return the case and
hours fields.

hit summation button. in the summation row, the job field should be
'group by' the case and hours, 'sum' and the date field should be
'where' with criteria ' < [param1]'
save this as y or whatever

make a new query and select queries x and y and pick and drag the x.job
to the y.job to make a join line, right click the join line and choose
2. now you should have the join line with an arrow head on the y table.
Now return the x.job and y.job. Criteria for y.job should be 'is null'.
y.job can be cleared to not display.

if doesn't work post table fields and someone can write a query for cut
and paste. But you ought to try and learn this show you can do other
queries on your own.



Norma <njhildebrand@s uscom.net> posted in
news:207ecba8.0 406240258.4dc60 faa@posting.goo gle.com
[color=blue]
> Phil,
> I am trying to decipher what you wrote and put it into language that I
> would understand. I am a novice in access SQL. Is there anyway you can
> restate all that in laymans terms?
>
> Thanks,
> Norma
>
> "Phil" <stuff@basketba ll.net> wrote in message
> news:<WdpCc.806 0$6k3.8018@news svr22.news.prod igy.com>...[color=green]
>> select x.job from (select t.job from t where t.fldDate between param1
>> and param2) as x
>> left join (select t.job, sum(t.cases) as sc, sum(t.hours) as sh,
>> t.fldDate from
>> t group by t.job, t.fldDate having t.flddate<param 1) as y on x.job =
>> y.job where y.job is null;
>>
>> Norma <njhildebrand@s uscom.net> posted in
>> news:207ecba8.0 406231351.3614a 883@posting.goo gle.com
>>[color=darkred]
>>> I am trying to make a query pull data from between the dates I enter
>>> in the parameter but also look back 'in time' to see where 2 other
>>> fields have null values, and only pull data into the query if those
>>> 2 fields are null prior to the beginning date of my parameter.
>>> The reason for this (to help make this a little clearer)is to pull
>>> production into a query if it is a 'new' for the month. That is,
>>> never run before the dates entered. I have 2 fields that I am
>>> looking at to see if it is a 'new' product, those fields are hours
>>> and cases. Any help would be greatly appreciated!
>>>
>>> Norma[/color][/color][/color]

--
Phil