Help with query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all..

What I need to do is create a query based on the following.

If the location has a hit before or on July 4th 2006. If it does show me
all the hits including the one(s) that are on July 4th, 2006.

This will be a fixed qry where a report will be generated off this query.
If that makes sense. There will be no dates put in as of yet.....

Thanks
R~
 
It can be done using a subquery.

SELECT Table.*
FROM Table
WHERE Table.Location in
(SELECT T1.Location
FROM Table as T1
Where T1.SomeDate <= #2006-07-01#)
 
Select *
From YourTable
Where DateField <= #4-July-2006# ;

or

Select *
From YourTable
Where DateField <= [Enter the Date] ;
 
Hi all....

thanks for the quick response.. Few questions thou...

"*" do I put in the main table which the query is based of.. IE

Select *
From tblMain
where datefield <= #4-July-2006#;

or

Select *
from tblMain
where DateField <=[Enter the Date];

I am getting a syntex error with this..... Do I put both of them in or just
one or the other???

Thanks again
R~

Jerry Whittle said:
Select *
From YourTable
Where DateField <= #4-July-2006# ;

or

Select *
From YourTable
Where DateField <= [Enter the Date] ;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Rhett_Y said:
Hi all..

What I need to do is create a query based on the following.

If the location has a hit before or on July 4th 2006. If it does show me
all the hits including the one(s) that are on July 4th, 2006.

This will be a fixed qry where a report will be generated off this query.
If that makes sense. There will be no dates put in as of yet.....

Thanks
R~
Click to expand...
Click to expand...
 
Also... It needs to show also the location that have the same address and
citystreet...with the below criteria... These are fields in the query
also...

Thanks
R~

Rhett_Y said:
Hi all....

thanks for the quick response.. Few questions thou...

"*" do I put in the main table which the query is based of.. IE

Select *
From tblMain
where datefield <= #4-July-2006#;

or

Select *
from tblMain
where DateField <=[Enter the Date];

I am getting a syntex error with this..... Do I put both of them in or just
one or the other???

Thanks again
R~

Jerry Whittle said:
Select *
From YourTable
Where DateField <= #4-July-2006# ;

or

Select *
From YourTable
Where DateField <= [Enter the Date] ;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Rhett_Y said:
Hi all..

What I need to do is create a query based on the following.

If the location has a hit before or on July 4th 2006. If it does show me
all the hits including the one(s) that are on July 4th, 2006.

This will be a fixed qry where a report will be generated off this query.
If that makes sense. There will be no dates put in as of yet.....

Thanks
R~
Click to expand...
Click to expand...
Click to expand...
 
Hi all....

Preface: Main table called tblMain. Here are three of the fields that I am
interested in setting this up for.

tblMain
Field 1: [IncidentDate]
Field 2: [Address] (actually street numbers)
Field 3: [CityStreet] (actually street name via lookup field off another
table)

Query based of the above tables.

What I want to query is this:

If location has an entry on July 4th, 2006 then it counts it, if the same
location has has another entry it counts it. If it only has a incident on
July 4th, 2006 an no other prior incidents do not display it... Does this
make sense.

So it would look something like this in a report generated off of the query.

07/04/06 123 Main St
07/03/06 123 Main St
05/05/06 123 Main St

07/04/06 125 South St
07/02/06 125 South St
06/22/06 125 South St


But not:
07/06/06 125 south st
Thanks
R~
 
Anyone....?? Aonyone please.. this has been driving me crazy trying to
figure this out....

R~
 
Dear Rhett:

First, I'd like to cover your definition of a "location". I presume that a
location is any unique combination of Address and CityStreet.

I would point out that this is tenuous at best. For example:

123 Main St.
123 Main St
123 Main Street
123 Main

Thes are 4 DIFFERENT locations. I believe you will find it virtually
impossible to prevent such variations if the columns Address and CityStreet
are freely typed.

Now, I understand you wish to exclude all locations that do not have an
entry for July 4, 2006. I would suggest a WHERE EXISTS() clause for this:

SELECT *
FROM tblMain T
WHERE EXISTS(SELECT *
FROM tblMain T1
WHERE T1.Address = T.Address
AND T1.CityStreet = T.CityStreet
AND T1.IncidentDate = #7/4/2006#)

John's solution earlier was quite similar, except he correlated on a column
"location" which you have subsequently clarified.

This does nothing to eliminate the difficulty with matching locations when
the CityStreet may differ, as I explained before. Short of qualifying all
addresses in the area, there may be no solution except to standardize the
entries programatically. Even then, it would be best to have a table of all
legal street names, perhaps with the address ranges on each one, and the
street suffix (St, Ave, Blvd, etc.)

Perhaps this may get you started. Please let me know.

Tom Ellison


Rhett_Y said:
Anyone....?? Aonyone please.. this has been driving me crazy trying to
figure this out....

R~

Rhett_Y said:
Hi all....

Preface: Main table called tblMain. Here are three of the fields that I
am
interested in setting this up for.

tblMain
Field 1: [IncidentDate]
Field 2: [Address] (actually street numbers)
Field 3: [CityStreet] (actually street name via lookup field off another
table)

Query based of the above tables.

What I want to query is this:

If location has an entry on July 4th, 2006 then it counts it, if the same
location has has another entry it counts it. If it only has a incident on
July 4th, 2006 an no other prior incidents do not display it... Does this
make sense.

So it would look something like this in a report generated off of the
query.

07/04/06 123 Main St
07/03/06 123 Main St
05/05/06 123 Main St

07/04/06 125 South St
07/02/06 125 South St
06/22/06 125 South St


But not:
07/06/06 125 south st
Thanks
R~
Click to expand...
Click to expand...
 
Tom...

I made the street thing pretty easy to do.. I have all the street names in
a drop down menu...... IE Main St..... 5th St.. etc... this way to
eliminate what you have point out.... I hope....lol....

In your where exists.... What I would like is....this.. If the location
has a entry for july 4th and before show it.. If all it has is an entry for
july 2nd but not july 4th I don't want to see it..... Basically.....if the
location has an entry for july 4th and before I want to know about it....

In the code you have, you have the "T1" is this what I would type in or is
this the name of one of my tables??? I am still pretty knew to the vb access
thing...

If this is the code I would have to put in...exactly where would this
go?????

Thanks agian for helping... I really do appreciate it!!!

Thanks
R~

Tom Ellison said:
Dear Rhett:

First, I'd like to cover your definition of a "location". I presume that a
location is any unique combination of Address and CityStreet.

I would point out that this is tenuous at best. For example:

123 Main St.
123 Main St
123 Main Street
123 Main

Thes are 4 DIFFERENT locations. I believe you will find it virtually
impossible to prevent such variations if the columns Address and CityStreet
are freely typed.

Now, I understand you wish to exclude all locations that do not have an
entry for July 4, 2006. I would suggest a WHERE EXISTS() clause for this:

SELECT *
FROM tblMain T
WHERE EXISTS(SELECT *
FROM tblMain T1
WHERE T1.Address = T.Address
AND T1.CityStreet = T.CityStreet
AND T1.IncidentDate = #7/4/2006#)

John's solution earlier was quite similar, except he correlated on a column
"location" which you have subsequently clarified.

This does nothing to eliminate the difficulty with matching locations when
the CityStreet may differ, as I explained before. Short of qualifying all
addresses in the area, there may be no solution except to standardize the
entries programatically. Even then, it would be best to have a table of all
legal street names, perhaps with the address ranges on each one, and the
street suffix (St, Ave, Blvd, etc.)

Perhaps this may get you started. Please let me know.

Tom Ellison


Rhett_Y said:
Anyone....?? Aonyone please.. this has been driving me crazy trying to
figure this out....

R~

Rhett_Y said:
Hi all....

Preface: Main table called tblMain. Here are three of the fields that I
am
interested in setting this up for.

tblMain
Field 1: [IncidentDate]
Field 2: [Address] (actually street numbers)
Field 3: [CityStreet] (actually street name via lookup field off another
table)

Query based of the above tables.

What I want to query is this:

If location has an entry on July 4th, 2006 then it counts it, if the same
location has has another entry it counts it. If it only has a incident on
July 4th, 2006 an no other prior incidents do not display it... Does this
make sense.

So it would look something like this in a report generated off of the
query.

07/04/06 123 Main St
07/03/06 123 Main St
05/05/06 123 Main St

07/04/06 125 South St
07/02/06 125 South St
06/22/06 125 South St


But not:
07/06/06 125 south st
Thanks
R~
Click to expand...
Click to expand...
Click to expand...
 
Back
Top