473,325 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

complicated query... help needed

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
Nov 13 '05 #1
5 1476
"Norma" <nj**********@suscom.net> wrote in message
news:20**************************@posting.google.c om...
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


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.
Nov 13 '05 #2
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<param1) as y on x.job =
y.job where y.job is null;

Norma <nj**********@suscom.net> posted in
news:20**************************@posting.google.c om
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


--
Phil
Nov 13 '05 #3
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" <st***@basketball.net> wrote in message news:<Wd*****************@newssvr22.news.prodigy.c om>...
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<param1) as y on x.job =
y.job where y.job is null;

Norma <nj**********@suscom.net> posted in
news:20**************************@posting.google.c om
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

Nov 13 '05 #4
"Norma" <nj**********@suscom.net> wrote in message
news:20**************************@posting.google.c om...
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!


Post your table structure with some sample data and the output you need and
someone will be able to help you.
Nov 13 '05 #5
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 <nj**********@suscom.net> posted in
news:20**************************@posting.google.c om
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" <st***@basketball.net> wrote in message
news:<Wd*****************@newssvr22.news.prodigy.c om>...
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<param1) as y on x.job =
y.job where y.job is null;

Norma <nj**********@suscom.net> posted in
news:20**************************@posting.google.c om
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


--
Phil
Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Agoston Bejo | last post by:
Hi, I'm having trouble with implementing some constraints on the database level. An example: --Table A(AID, BID ref. B.BID, ATXT) --Table B(BID, CID ref. C.CID) --Table C(CID) upon insertion...
10
by: jqq | last post by:
SQL2K on W2Kserver I need some help revamping a rather complicated query. I've given the table and existing query information below. (FYI, changing the database structure is right out.) The...
3
by: Eagle | last post by:
Hi all, This one's drivin' me nuts. Any help would be appreciated. (Access 2000). I have 3 tables: tblTools: having the basic data of a tool and a field (txt) showing yes or no with regard to...
26
by: Jeff | last post by:
Ok gang. Here is something complicated, well, at least to me anyway. Using Access DB I have a table in my DB called members. In that table, I have 2 tables I will be using "username" and...
4
by: Matthew Crouch | last post by:
i suck so much that i don't even know if this is a JOIN or a subquery or who-knows what. Here's the idea: I want to select two things at the same time (form one table) average for columnX and...
8
by: Nancy | last post by:
Greetings: First, I apologize if my posting format is improper. The code below does what I intended it to do, but presently only displays 1 table entry. I've grown it to this point, but really...
0
by: Nick | last post by:
Hi, I have two tables Trade table and Cons table. Records are inserted in both the tables independent of each other. There are fields like Exc_Ref, Qty, Date in both the tables. I need to...
7
by: MarkNeumann | last post by:
I'm coming from a Corel paradox background and moving into an Access environment. So I'm struggling with something that I think is probably way simpler than I'm making it out to be. Access 2007...
20
by: exipnakias | last post by:
Hello Guys. In a form I created a listbox which looks up the values of a table. I want: 1) ..to create a query where a parameter will be needed in order to be loaded. But I do not want to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.