|
 |
asp_databases thread: RE: Please help!-- Running Access Query--Comb ined criteria!!
Message #1 by "Thomas McMillin" <tmcmillin@h...> on Mon, 25 Mar 2002 13:05:45 -0600
|
|
Hmmmm... try
SELECT MasterHoldings.Title,
MasterHoldings.Format,
MasterHoldings.Location
FROM MasterHoldings
WHERE MasterHoldings.Location like '%rock%'
AND MasterHoldings.Location like '%periodical%'
We have a db where the following seemed to return the correct rows.....
SELECT acct_desc FROM acct_gl_account
WHERE acct_desc like '%reserve%' and acct_desc like '%bank%'
returned
-Reserve/Bank One
-Reserve/Key Bank
-Reserve/First Niagra Bank
-Reserve/M&T Bank
-Reserve/National City Bank
but correctly did not....
-Reserve/World Omni
Thomas
-----Original Message-----
From: Wang, Julia [mailto:Julia.Wang@R...]
Sent: Monday, March 25, 2002 12:13 PM
To: ASP Databases
Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
ined criteria!!
Thomas, thanks for the suggestion!
OR can not be used because I need to pull all the data that meets both
conditions that is,for example, title: "Science" journal that is both in
our
print subscription in "Rockhurst" column and is also provided on the
e-database "Periodical Abstracts...".
The table looks like this:
Title YearsA Holdings Format Location
SCIENCE 1953 v.118-182, 1953-1973 PRINT ROCKHURST LIBRARY:
PERIODICALS
SCIENCE 1974 v.183:4120-4131, 1974 PRINT ROCKHURST LIBRARY:
PERIODICALS
SCIENCE 1975 v.183-236, 1974-1987 MICROFILM ROCKHURST LIBRARY:
MICROFILM
SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE ROCKHURST LIBRARY:
MICROFICHE
Science 4000 Page Image: 1/1/88 + current; Text and Graphics: 6/21/96 _
current;
Electronic Periodical Abstracts Research II
SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
The criteria that I have been using are:
**For Print only which is no problem at all:
SELECT MasterHoldings.Title, MasterHoldings.Format,
MasterHoldings.Location
FROM MasterHoldings
WHERE (((MasterHoldings.Location) Like "Rock*"));
**For combined criteria whick is the reason I am requesting help:
SELECT MasterHoldings.Title, MasterHoldings.Format,
MasterHoldings.Location
FROM MasterHoldings
WHERE (((MasterHoldings.Location) Like "Rock*" And
(MasterHoldings.Location)
Like "Periodical *"));
I have reached my wits end. Please help!!
Julia
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Monday, March 25, 2002 9:38 AM
To: ASP Databases
Subject: [asp_databases] RE: Please help!-- Running Access
Query--Combined criteria!!
Please supply the query that works...just the sql ...and the one that
does not work....
-----Original Message-----
From: Wang, Julia [mailto:Julia.Wang@R...]
Sent: Monday, March 25, 2002 9:53 AM
To: ASP Databases
Subject: [asp_databases] Please help!-- Running Access Query--Combined
criteria!!
Any suggestions are appreciated!!
** We have a master table named "Maetro" which holds around 1,6000
records of journal titles of both print and electronic. The table
consists of Title column, Format (e.g. "Print" or "Electronic")column,
and Location (e.g. Library name (here "Rockhurst Unversity", and
Electronic Database names like "First Search", "Gale", "Proquest",
etc.") Since there are obvious overlaps in certain titles for both print
and electronic, I need to run a combined query to extract titles that
shows up BOTH in line with "Rockhurst University" and with one of the
e-databases such as "Gale", or "Proquest", etc. so that we can make
decisions to unsubscribe the duplicate print titles due to increasing
prices.
I tried using, under criteria, Like "Rock*" and Like "Gale*" or another
set Like "Rock*" and Like "Proquest *", etc. for Print to match each
type of electronics, but it returns me "0"!!! If I run single query,
there is just no problem. And I know there are definitely duplicates!
Could any of you take some time and help me as to how to run a combined
Query? --Thank You! Thank You! Thank You!
Please ask questions if this is not clearly stated!--Appreciated!
Message #2 by "Wang, Julia" <Julia.Wang@R...> on Mon, 25 Mar 2002 08:53:17 -0600
|
|
Any suggestions are appreciated!!
** We have a master table named "Maetro" which holds around 1,6000 records
of journal titles of both print and electronic. The table consists of Title
column, Format (e.g. "Print" or "Electronic")column, and Location (e.g.
Library name (here "Rockhurst Unversity", and Electronic Database names like
"First Search", "Gale", "Proquest", etc.") Since there are obvious overlaps
in certain titles for both print and electronic, I need to run a combined
query to extract titles that shows up BOTH in line with "Rockhurst
University" and with one of the e-databases such as "Gale", or "Proquest",
etc. so that we can make decisions to unsubscribe the duplicate print titles
due to increasing prices.
I tried using, under criteria, Like "Rock*" and Like "Gale*" or another set
Like "Rock*" and Like "Proquest *", etc. for Print to match each type of
electronics, but it returns me "0"!!! If I run single query, there is just
no problem. And I know there are definitely duplicates!
Could any of you take some time and help me as to how to run a combined
Query?
--Thank You! Thank You! Thank You!
Please ask questions if this is not clearly stated!--Appreciated!
Message #3 by "Drew, Ron" <RDrew@B...> on Mon, 25 Mar 2002 10:38:05 -0500
|
|
Please supply the query that works...just the sql ...and the one that
does not work....
-----Original Message-----
From: Wang, Julia [mailto:Julia.Wang@R...]
Sent: Monday, March 25, 2002 9:53 AM
To: ASP Databases
Subject: [asp_databases] Please help!-- Running Access Query--Combined
criteria!!
Any suggestions are appreciated!!
** We have a master table named "Maetro" which holds around 1,6000
records of journal titles of both print and electronic. The table
consists of Title column, Format (e.g. "Print" or "Electronic")column,
and Location (e.g. Library name (here "Rockhurst Unversity", and
Electronic Database names like "First Search", "Gale", "Proquest",
etc.") Since there are obvious overlaps in certain titles for both print
and electronic, I need to run a combined query to extract titles that
shows up BOTH in line with "Rockhurst University" and with one of the
e-databases such as "Gale", or "Proquest", etc. so that we can make
decisions to unsubscribe the duplicate print titles due to increasing
prices.
I tried using, under criteria, Like "Rock*" and Like "Gale*" or another
set Like "Rock*" and Like "Proquest *", etc. for Print to match each
type of electronics, but it returns me "0"!!! If I run single query,
there is just no problem. And I know there are definitely duplicates!
Could any of you take some time and help me as to how to run a combined
Query? --Thank You! Thank You! Thank You!
Please ask questions if this is not clearly stated!--Appreciated!
Message #4 by "Thomas McMillin" <tmcmillin@h...> on Mon, 25 Mar 2002 11:42:29 -0600
|
|
have you explicitly stated the field name on both conditions, i.e.
fldname like 'Print*' AND fldname like 'Electronic*'
Thomas
-----Original Message-----
From: Wang, Julia [mailto:Julia.Wang@R...]
Sent: Monday, March 25, 2002 8:53 AM
To: ASP Databases
Subject: [asp_databases] Please help!-- Running Access Query--Combined
criteria!!
Any suggestions are appreciated!!
** We have a master table named "Maetro" which holds around 1,6000
records
of journal titles of both print and electronic. The table consists of
Title
column, Format (e.g. "Print" or "Electronic")column, and Location (e.g.
Library name (here "Rockhurst Unversity", and Electronic Database names
like
"First Search", "Gale", "Proquest", etc.") Since there are obvious
overlaps
in certain titles for both print and electronic, I need to run a
combined
query to extract titles that shows up BOTH in line with "Rockhurst
University" and with one of the e-databases such as "Gale", or
"Proquest",
etc. so that we can make decisions to unsubscribe the duplicate print
titles
due to increasing prices.
I tried using, under criteria, Like "Rock*" and Like "Gale*" or another
set
Like "Rock*" and Like "Proquest *", etc. for Print to match each type of
electronics, but it returns me "0"!!! If I run single query, there is
just
no problem. And I know there are definitely duplicates!
Could any of you take some time and help me as to how to run a combined
Query?
--Thank You! Thank You! Thank You!
Please ask questions if this is not clearly stated!--Appreciated!
Message #5 by "Wang, Julia" <Julia.Wang@R...> on Mon, 25 Mar 2002 12:13:04 -0600
|
|
Thomas, thanks for the suggestion!
OR can not be used because I need to pull all the data that meets both
conditions that is,for example, title: "Science" journal that is both in our
print subscription in "Rockhurst" column and is also provided on the
e-database "Periodical Abstracts...".
The table looks like this:
Title YearsA Holdings Format Location
SCIENCE 1953 v.118-182, 1953-1973 PRINT ROCKHURST LIBRARY:
PERIODICALS
SCIENCE 1974 v.183:4120-4131, 1974 PRINT ROCKHURST LIBRARY:
PERIODICALS
SCIENCE 1975 v.183-236, 1974-1987 MICROFILM ROCKHURST LIBRARY:
MICROFILM
SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE ROCKHURST LIBRARY:
MICROFICHE
Science 4000 Page Image: 1/1/88 + current; Text and Graphics: 6/21/96 _
current;
Electronic Periodical Abstracts Research II
SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
The criteria that I have been using are:
**For Print only which is no problem at all:
SELECT MasterHoldings.Title, MasterHoldings.Format, MasterHoldings.Location
FROM MasterHoldings
WHERE (((MasterHoldings.Location) Like "Rock*"));
**For combined criteria whick is the reason I am requesting help:
SELECT MasterHoldings.Title, MasterHoldings.Format, MasterHoldings.Location
FROM MasterHoldings
WHERE (((MasterHoldings.Location) Like "Rock*" And (MasterHoldings.Location)
Like "Periodical *"));
I have reached my wits end. Please help!!
Julia
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Monday, March 25, 2002 9:38 AM
To: ASP Databases
Subject: [asp_databases] RE: Please help!-- Running Access
Query--Combined criteria!!
Please supply the query that works...just the sql ...and the one that
does not work....
-----Original Message-----
From: Wang, Julia [mailto:Julia.Wang@R...]
Sent: Monday, March 25, 2002 9:53 AM
To: ASP Databases
Subject: [asp_databases] Please help!-- Running Access Query--Combined
criteria!!
Any suggestions are appreciated!!
** We have a master table named "Maetro" which holds around 1,6000
records of journal titles of both print and electronic. The table
consists of Title column, Format (e.g. "Print" or "Electronic")column,
and Location (e.g. Library name (here "Rockhurst Unversity", and
Electronic Database names like "First Search", "Gale", "Proquest",
etc.") Since there are obvious overlaps in certain titles for both print
and electronic, I need to run a combined query to extract titles that
shows up BOTH in line with "Rockhurst University" and with one of the
e-databases such as "Gale", or "Proquest", etc. so that we can make
decisions to unsubscribe the duplicate print titles due to increasing
prices.
I tried using, under criteria, Like "Rock*" and Like "Gale*" or another
set Like "Rock*" and Like "Proquest *", etc. for Print to match each
type of electronics, but it returns me "0"!!! If I run single query,
there is just no problem. And I know there are definitely duplicates!
Could any of you take some time and help me as to how to run a combined
Query? --Thank You! Thank You! Thank You!
Please ask questions if this is not clearly stated!--Appreciated!
Message #6 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 25 Mar 2002 13:56:14 -0500
|
|
Hi Julia,
It looks as though you have no unique identifier for each record. Is
that correct? I may be wrong, but to me it looks as though your
database design is problematic. Also, do you want to delete all records
where the format is "PRINT" if there is also a format "Electronic"? If
that is the case, why don't you just do 2 queries. The first one to get
the count of all records with "Electronic". If the count is greater
than zero, then perform the second query to delete all of the PRINT
records. Or perhaps I just don't understand what it is that you are
asking for.
Pete
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 1:13 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
>
> Thomas, thanks for the suggestion!
>
> OR can not be used because I need to pull all the data that meets both
> conditions that is,for example, title: "Science" journal that
> is both in our
> print subscription in "Rockhurst" column and is also provided on the
> e-database "Periodical Abstracts...".
>
> The table looks like this:
>
> Title YearsA Holdings Format Location
> SCIENCE 1953 v.118-182, 1953-1973 PRINT
> ROCKHURST LIBRARY:
> PERIODICALS
> SCIENCE 1974 v.183:4120-4131, 1974 PRINT
> ROCKHURST LIBRARY:
> PERIODICALS
> SCIENCE 1975 v.183-236, 1974-1987 MICROFILM
> ROCKHURST LIBRARY:
> MICROFILM
> SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE
> ROCKHURST LIBRARY:
> MICROFICHE
> Science 4000 Page Image: 1/1/88 + current; Text and
> Graphics: 6/21/96 _
> current;
> Electronic Periodical Abstracts Research II
> SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
>
>
> The criteria that I have been using are:
> **For Print only which is no problem at all:
>
> SELECT MasterHoldings.Title, MasterHoldings.Format,
> MasterHoldings.Location
> FROM MasterHoldings
> WHERE (((MasterHoldings.Location) Like "Rock*"));
>
> **For combined criteria whick is the reason I am requesting help:
>
> SELECT MasterHoldings.Title, MasterHoldings.Format,
> MasterHoldings.Location
> FROM MasterHoldings
> WHERE (((MasterHoldings.Location) Like "Rock*" And
> (MasterHoldings.Location)
> Like "Periodical *"));
>
> I have reached my wits end. Please help!!
>
> Julia
>
> -----Original Message-----
> From: Drew, Ron [mailto:RDrew@B...]
> Sent: Monday, March 25, 2002 9:38 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access
> Query--Combined criteria!!
>
>
> Please supply the query that works...just the sql ...and the one that
> does not work....
>
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 9:53 AM
> To: ASP Databases
> Subject: [asp_databases] Please help!-- Running Access Query--Combined
> criteria!!
>
>
>
> Any suggestions are appreciated!!
>
> ** We have a master table named "Maetro" which holds around 1,6000
> records of journal titles of both print and electronic. The table
> consists of Title column, Format (e.g. "Print" or "Electronic")column,
> and Location (e.g. Library name (here "Rockhurst Unversity", and
> Electronic Database names like "First Search", "Gale", "Proquest",
> etc.") Since there are obvious overlaps in certain titles for
> both print
> and electronic, I need to run a combined query to extract titles that
> shows up BOTH in line with "Rockhurst University" and with one of the
> e-databases such as "Gale", or "Proquest", etc. so that we can make
> decisions to unsubscribe the duplicate print titles due to increasing
> prices.
>
> I tried using, under criteria, Like "Rock*" and Like "Gale*"
> or another
> set Like "Rock*" and Like "Proquest *", etc. for Print to match each
> type of electronics, but it returns me "0"!!! If I run single query,
> there is just no problem. And I know there are definitely duplicates!
>
> Could any of you take some time and help me as to how to run
> a combined
> Query? --Thank You! Thank You! Thank You!
>
> Please ask questions if this is not clearly stated!--Appreciated!
>
>
>
>
Message #7 by "Wang, Julia" <Julia.Wang@R...> on Mon, 25 Mar 2002 13:46:17 -0600
|
|
Thank you all for responding!!
This seems to hit the point. Right! The db created by my predessor does not
have ID numbers or primary key. If so, could this be the reason that I can
not run combined query using "And" (The syntax seems to be right!). (Using
OR is no problem but not needed!)
No, my purpose is not to delete the print records on the db but to print out
a list of titles that have both Print and Electronic format so we can make
decisions as to cancel some of the print subscriptions to save money. We are
paying money on duplicates!
Here is another example of the table to illustrate:
Title YearsA Holdings Format Location
ADOLESCENCE 1966 v.1-8, 1966-1973 PRINT ROCKHURST
LIBRARY: PERIODICALS
ADOLESCENCE 1974 v.9-21, 1974-1986 MICROFILM ROCKHURST
LIBRARY: MICROFILM
ADOLESCENCE 1987 v.22-36,1987-2001-MICROFICHE ROCKHURST LIBRARY:
MICROFICHE
Adolescence 4000 Text: 1/1/94 Electronic Periodical Abstracts
Research II
Adolescence 5000 1/1/95 _ current Electronic FirstSearch:
Wilson Select Full Text
Adolescence 5000 3/1/89 _ current Electronic FirstSearch:
Health and Wellness Information
Adolescent February 1999 to current Electronic Academic Universe
Thanks so very much for all your time! Please come up with something. I am
desperate and I am so new to ACCESS!
Julia
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, March 25, 2002 12:56 PM
To: ASP Databases
Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
ined criteria!!
Hi Julia,
It looks as though you have no unique identifier for each record. Is
that correct? I may be wrong, but to me it looks as though your
database design is problematic. Also, do you want to delete all records
where the format is "PRINT" if there is also a format "Electronic"? If
that is the case, why don't you just do 2 queries. The first one to get
the count of all records with "Electronic". If the count is greater
than zero, then perform the second query to delete all of the PRINT
records. Or perhaps I just don't understand what it is that you are
asking for.
Pete
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 1:13 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
>
> Thomas, thanks for the suggestion!
>
> OR can not be used because I need to pull all the data that meets both
> conditions that is,for example, title: "Science" journal that
> is both in our
> print subscription in "Rockhurst" column and is also provided on the
> e-database "Periodical Abstracts...".
>
> The table looks like this:
>
> Title YearsA Holdings Format Location
> SCIENCE 1953 v.118-182, 1953-1973 PRINT
> ROCKHURST LIBRARY:
> PERIODICALS
> SCIENCE 1974 v.183:4120-4131, 1974 PRINT
> ROCKHURST LIBRARY:
> PERIODICALS
> SCIENCE 1975 v.183-236, 1974-1987 MICROFILM
> ROCKHURST LIBRARY:
> MICROFILM
> SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE
> ROCKHURST LIBRARY:
> MICROFICHE
> Science 4000 Page Image: 1/1/88 + current; Text and
> Graphics: 6/21/96 _
> current;
> Electronic Periodical Abstracts Research II
> SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
>
>
> The criteria that I have been using are:
> **For Print only which is no problem at all:
>
> SELECT MasterHoldings.Title, MasterHoldings.Format,
> MasterHoldings.Location
> FROM MasterHoldings
> WHERE (((MasterHoldings.Location) Like "Rock*"));
>
> **For combined criteria whick is the reason I am requesting help:
>
> SELECT MasterHoldings.Title, MasterHoldings.Format,
> MasterHoldings.Location
> FROM MasterHoldings
> WHERE (((MasterHoldings.Location) Like "Rock*" And
> (MasterHoldings.Location)
> Like "Periodical *"));
>
> I have reached my wits end. Please help!!
>
> Julia
>
> -----Original Message-----
> From: Drew, Ron [mailto:RDrew@B...]
> Sent: Monday, March 25, 2002 9:38 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access
> Query--Combined criteria!!
>
>
> Please supply the query that works...just the sql ...and the one that
> does not work....
>
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 9:53 AM
> To: ASP Databases
> Subject: [asp_databases] Please help!-- Running Access Query--Combined
> criteria!!
>
>
>
> Any suggestions are appreciated!!
>
> ** We have a master table named "Maetro" which holds around 1,6000
> records of journal titles of both print and electronic. The table
> consists of Title column, Format (e.g. "Print" or "Electronic")column,
> and Location (e.g. Library name (here "Rockhurst Unversity", and
> Electronic Database names like "First Search", "Gale", "Proquest",
> etc.") Since there are obvious overlaps in certain titles for
> both print
> and electronic, I need to run a combined query to extract titles that
> shows up BOTH in line with "Rockhurst University" and with one of the
> e-databases such as "Gale", or "Proquest", etc. so that we can make
> decisions to unsubscribe the duplicate print titles due to increasing
> prices.
>
> I tried using, under criteria, Like "Rock*" and Like "Gale*"
> or another
> set Like "Rock*" and Like "Proquest *", etc. for Print to match each
> type of electronics, but it returns me "0"!!! If I run single query,
> there is just no problem. And I know there are definitely duplicates!
>
> Could any of you take some time and help me as to how to run
> a combined
> Query? --Thank You! Thank You! Thank You!
>
> Please ask questions if this is not clearly stated!--Appreciated!
>
>
>
>
Message #8 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 25 Mar 2002 15:17:32 -0500
|
|
Ok, if that is the case, then I would do it like this.
1. Perform a search that counts the number of records with Electronic
format
2. If the count is greater than zero, then Perform a search for the
records with Print format
<%
Dim SearchTitle = "ADOLESCENCE"
SQLStr = "SELECT Count(Title) As numRecords FROM Maetro " & _
"WHERE ((Format='Electronic') " & _
"AND (Title='" & SearchTitle & "'))"
Set oRS = Server.CreateObject("ADODB.Recordset")
' I assume here that you have named you database connection DConn
Set oRS = DConn.Execute( SQLStr, ,adCmdText )
If (CInt(oRS("numRecords")) > 0) Then
' We know we have electronic format of this SearchTitle
' So now print out all PRINT format records
SQLStr = "SELECT * FROM Maestro " & _
"WHERE ((Format='PRINT') " & _
"AND (Title='" & SearchTitle & "'))"
Set oRS = DConn.Execute( SQLStr, ,adCmdText )
Do While Not oRS.EOF
' Print the records
Response.Write(oRS("Title") & " - " & oRS("Format") & " - " &
oRS("Location") & "<br>")
oRS.MoveNext
Loop
End If
' Close oRS here
oRS.Close
Set oRS = Nothing
%>
See if that does what you need.
Regards,
Pete
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 2:46 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> Thank you all for responding!!
>
> This seems to hit the point. Right! The db created by my
> predessor does not
> have ID numbers or primary key. If so, could this be the
> reason that I can
> not run combined query using "And" (The syntax seems to be
> right!). (Using
> OR is no problem but not needed!)
>
> No, my purpose is not to delete the print records on the db
> but to print out
> a list of titles that have both Print and Electronic format
> so we can make
> decisions as to cancel some of the print subscriptions to
> save money. We are
> paying money on duplicates!
>
> Here is another example of the table to illustrate:
>
> Title YearsA Holdings Format Location
> ADOLESCENCE 1966 v.1-8, 1966-1973 PRINT
> ROCKHURST
> LIBRARY: PERIODICALS
> ADOLESCENCE 1974 v.9-21, 1974-1986 MICROFILM
> ROCKHURST
> LIBRARY: MICROFILM
> ADOLESCENCE 1987 v.22-36,1987-2001-MICROFICHE
> ROCKHURST LIBRARY:
> MICROFICHE
> Adolescence 4000 Text: 1/1/94 Electronic
> Periodical Abstracts
> Research II
> Adolescence 5000 1/1/95 _ current Electronic
> FirstSearch:
> Wilson Select Full Text
> Adolescence 5000 3/1/89 _ current Electronic
> FirstSearch:
> Health and Wellness Information
> Adolescent February 1999 to current Electronic
> Academic Universe
>
> Thanks so very much for all your time! Please come up with
> something. I am
> desperate and I am so new to ACCESS!
>
> Julia
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, March 25, 2002 12:56 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> Hi Julia,
>
> It looks as though you have no unique identifier for each record. Is
> that correct? I may be wrong, but to me it looks as though your
> database design is problematic. Also, do you want to delete
> all records
> where the format is "PRINT" if there is also a format
> "Electronic"? If
> that is the case, why don't you just do 2 queries. The first
> one to get
> the count of all records with "Electronic". If the count is greater
> than zero, then perform the second query to delete all of the PRINT
> records. Or perhaps I just don't understand what it is that you are
> asking for.
>
> Pete
>
>
> > -----Original Message-----
> > From: Wang, Julia [mailto:Julia.Wang@R...]
> > Sent: Monday, March 25, 2002 1:13 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> Query--Comb
> > ined criteria!!
> >
> >
> >
> > Thomas, thanks for the suggestion!
> >
> > OR can not be used because I need to pull all the data that
> meets both
> > conditions that is,for example, title: "Science" journal that
> > is both in our
> > print subscription in "Rockhurst" column and is also provided on the
> > e-database "Periodical Abstracts...".
> >
> > The table looks like this:
> >
> > Title YearsA Holdings Format Location
> > SCIENCE 1953 v.118-182, 1953-1973 PRINT
> > ROCKHURST LIBRARY:
> > PERIODICALS
> > SCIENCE 1974 v.183:4120-4131, 1974 PRINT
> > ROCKHURST LIBRARY:
> > PERIODICALS
> > SCIENCE 1975 v.183-236, 1974-1987 MICROFILM
> > ROCKHURST LIBRARY:
> > MICROFILM
> > SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE
> > ROCKHURST LIBRARY:
> > MICROFICHE
> > Science 4000 Page Image: 1/1/88 + current; Text and
> > Graphics: 6/21/96 _
> > current;
> > Electronic Periodical Abstracts Research II
> > SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
> >
> >
> > The criteria that I have been using are:
> > **For Print only which is no problem at all:
> >
> > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > MasterHoldings.Location
> > FROM MasterHoldings
> > WHERE (((MasterHoldings.Location) Like "Rock*"));
> >
> > **For combined criteria whick is the reason I am requesting help:
> >
> > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > MasterHoldings.Location
> > FROM MasterHoldings
> > WHERE (((MasterHoldings.Location) Like "Rock*" And
> > (MasterHoldings.Location)
> > Like "Periodical *"));
> >
> > I have reached my wits end. Please help!!
> >
> > Julia
> >
> > -----Original Message-----
> > From: Drew, Ron [mailto:RDrew@B...]
> > Sent: Monday, March 25, 2002 9:38 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> > Query--Combined criteria!!
> >
> >
> > Please supply the query that works...just the sql ...and
> the one that
> > does not work....
> >
> > -----Original Message-----
> > From: Wang, Julia [mailto:Julia.Wang@R...]
> > Sent: Monday, March 25, 2002 9:53 AM
> > To: ASP Databases
> > Subject: [asp_databases] Please help!-- Running Access
> Query--Combined
> > criteria!!
> >
> >
> >
> > Any suggestions are appreciated!!
> >
> > ** We have a master table named "Maetro" which holds around 1,6000
> > records of journal titles of both print and electronic. The table
> > consists of Title column, Format (e.g. "Print" or
> "Electronic")column,
> > and Location (e.g. Library name (here "Rockhurst Unversity", and
> > Electronic Database names like "First Search", "Gale", "Proquest",
> > etc.") Since there are obvious overlaps in certain titles for
> > both print
> > and electronic, I need to run a combined query to extract
> titles that
> > shows up BOTH in line with "Rockhurst University" and with
> one of the
> > e-databases such as "Gale", or "Proquest", etc. so that we can make
> > decisions to unsubscribe the duplicate print titles due to
> increasing
> > prices.
> >
> > I tried using, under criteria, Like "Rock*" and Like "Gale*"
> > or another
> > set Like "Rock*" and Like "Proquest *", etc. for Print to match each
> > type of electronics, but it returns me "0"!!! If I run single query,
> > there is just no problem. And I know there are definitely
> duplicates!
> >
> > Could any of you take some time and help me as to how to run
> > a combined
> > Query? --Thank You! Thank You! Thank You!
> >
> > Please ask questions if this is not clearly stated!--Appreciated!
> >
> >
> >
> >
>
>
>
Message #9 by "Wang, Julia" <Julia.Wang@R...> on Mon, 25 Mar 2002 16:29:43 -0600
|
|
I am so sorry. I just wanted to run a pure ACCESS query pulling up journal
titles that show up in more than one locations, Rockhurst University (For
Print or Microfom)or "Gale" or "Periodical Abstracts ..", etc.for
e-databases. This has nothing to do with ASP. Do I need to write an asp page
for this?
I appologize for not stating clearly and wasting your time. Hope this helps
make it easier.
Julia
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, March 25, 2002 2:18 PM
To: ASP Databases
Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
ined criteria!!
Ok, if that is the case, then I would do it like this.
1. Perform a search that counts the number of records with Electronic
format
2. If the count is greater than zero, then Perform a search for the
records with Print format
<%
Dim SearchTitle = "ADOLESCENCE"
SQLStr = "SELECT Count(Title) As numRecords FROM Maetro " & _
"WHERE ((Format='Electronic') " & _
"AND (Title='" & SearchTitle & "'))"
Set oRS = Server.CreateObject("ADODB.Recordset")
' I assume here that you have named you database connection DConn
Set oRS = DConn.Execute( SQLStr, ,adCmdText )
If (CInt(oRS("numRecords")) > 0) Then
' We know we have electronic format of this SearchTitle
' So now print out all PRINT format records
SQLStr = "SELECT * FROM Maestro " & _
"WHERE ((Format='PRINT') " & _
"AND (Title='" & SearchTitle & "'))"
Set oRS = DConn.Execute( SQLStr, ,adCmdText )
Do While Not oRS.EOF
' Print the records
Response.Write(oRS("Title") & " - " & oRS("Format") & " - " &
oRS("Location") & "<br>")
oRS.MoveNext
Loop
End If
' Close oRS here
oRS.Close
Set oRS = Nothing
%>
See if that does what you need.
Regards,
Pete
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 2:46 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> Thank you all for responding!!
>
> This seems to hit the point. Right! The db created by my
> predessor does not
> have ID numbers or primary key. If so, could this be the
> reason that I can
> not run combined query using "And" (The syntax seems to be
> right!). (Using
> OR is no problem but not needed!)
>
> No, my purpose is not to delete the print records on the db
> but to print out
> a list of titles that have both Print and Electronic format
> so we can make
> decisions as to cancel some of the print subscriptions to
> save money. We are
> paying money on duplicates!
>
> Here is another example of the table to illustrate:
>
> Title YearsA Holdings Format Location
> ADOLESCENCE 1966 v.1-8, 1966-1973 PRINT
> ROCKHURST
> LIBRARY: PERIODICALS
> ADOLESCENCE 1974 v.9-21, 1974-1986 MICROFILM
> ROCKHURST
> LIBRARY: MICROFILM
> ADOLESCENCE 1987 v.22-36,1987-2001-MICROFICHE
> ROCKHURST LIBRARY:
> MICROFICHE
> Adolescence 4000 Text: 1/1/94 Electronic
> Periodical Abstracts
> Research II
> Adolescence 5000 1/1/95 _ current Electronic
> FirstSearch:
> Wilson Select Full Text
> Adolescence 5000 3/1/89 _ current Electronic
> FirstSearch:
> Health and Wellness Information
> Adolescent February 1999 to current Electronic
> Academic Universe
>
> Thanks so very much for all your time! Please come up with
> something. I am
> desperate and I am so new to ACCESS!
>
> Julia
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, March 25, 2002 12:56 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> Hi Julia,
>
> It looks as though you have no unique identifier for each record. Is
> that correct? I may be wrong, but to me it looks as though your
> database design is problematic. Also, do you want to delete
> all records
> where the format is "PRINT" if there is also a format
> "Electronic"? If
> that is the case, why don't you just do 2 queries. The first
> one to get
> the count of all records with "Electronic". If the count is greater
> than zero, then perform the second query to delete all of the PRINT
> records. Or perhaps I just don't understand what it is that you are
> asking for.
>
> Pete
>
>
> > -----Original Message-----
> > From: Wang, Julia [mailto:Julia.Wang@R...]
> > Sent: Monday, March 25, 2002 1:13 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> Query--Comb
> > ined criteria!!
> >
> >
> >
> > Thomas, thanks for the suggestion!
> >
> > OR can not be used because I need to pull all the data that
> meets both
> > conditions that is,for example, title: "Science" journal that
> > is both in our
> > print subscription in "Rockhurst" column and is also provided on the
> > e-database "Periodical Abstracts...".
> >
> > The table looks like this:
> >
> > Title YearsA Holdings Format Location
> > SCIENCE 1953 v.118-182, 1953-1973 PRINT
> > ROCKHURST LIBRARY:
> > PERIODICALS
> > SCIENCE 1974 v.183:4120-4131, 1974 PRINT
> > ROCKHURST LIBRARY:
> > PERIODICALS
> > SCIENCE 1975 v.183-236, 1974-1987 MICROFILM
> > ROCKHURST LIBRARY:
> > MICROFILM
> > SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE
> > ROCKHURST LIBRARY:
> > MICROFICHE
> > Science 4000 Page Image: 1/1/88 + current; Text and
> > Graphics: 6/21/96 _
> > current;
> > Electronic Periodical Abstracts Research II
> > SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
> >
> >
> > The criteria that I have been using are:
> > **For Print only which is no problem at all:
> >
> > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > MasterHoldings.Location
> > FROM MasterHoldings
> > WHERE (((MasterHoldings.Location) Like "Rock*"));
> >
> > **For combined criteria whick is the reason I am requesting help:
> >
> > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > MasterHoldings.Location
> > FROM MasterHoldings
> > WHERE (((MasterHoldings.Location) Like "Rock*" And
> > (MasterHoldings.Location)
> > Like "Periodical *"));
> >
> > I have reached my wits end. Please help!!
> >
> > Julia
> >
> > -----Original Message-----
> > From: Drew, Ron [mailto:RDrew@B...]
> > Sent: Monday, March 25, 2002 9:38 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> > Query--Combined criteria!!
> >
> >
> > Please supply the query that works...just the sql ...and
> the one that
> > does not work....
> >
> > -----Original Message-----
> > From: Wang, Julia [mailto:Julia.Wang@R...]
> > Sent: Monday, March 25, 2002 9:53 AM
> > To: ASP Databases
> > Subject: [asp_databases] Please help!-- Running Access
> Query--Combined
> > criteria!!
> >
> >
> >
> > Any suggestions are appreciated!!
> >
> > ** We have a master table named "Maetro" which holds around 1,6000
> > records of journal titles of both print and electronic. The table
> > consists of Title column, Format (e.g. "Print" or
> "Electronic")column,
> > and Location (e.g. Library name (here "Rockhurst Unversity", and
> > Electronic Database names like "First Search", "Gale", "Proquest",
> > etc.") Since there are obvious overlaps in certain titles for
> > both print
> > and electronic, I need to run a combined query to extract
> titles that
> > shows up BOTH in line with "Rockhurst University" and with
> one of the
> > e-databases such as "Gale", or "Proquest", etc. so that we can make
> > decisions to unsubscribe the duplicate print titles due to
> increasing
> > prices.
> >
> > I tried using, under criteria, Like "Rock*" and Like "Gale*"
> > or another
> > set Like "Rock*" and Like "Proquest *", etc. for Print to match each
> > type of electronics, but it returns me "0"!!! If I run single query,
> > there is just no problem. And I know there are definitely
> duplicates!
> >
> > Could any of you take some time and help me as to how to run
> > a combined
> > Query? --Thank You! Thank You! Thank You!
> >
> > Please ask questions if this is not clearly stated!--Appreciated!
> >
> >
> >
> >
>
>
>
Message #10 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 25 Mar 2002 17:41:56 -0500
|
|
No problem. I just figured you wanted an ASP solution (the name of this
list is ASP Databases). :)
Still, you should be able to apply most of this code to an Access Macro.
I haven't worked with those in quite some time, so I'm not 100% sure
what you'd need to modify. But I know you can use VBA to write the
Macros, and since the ASP code below is all VBScript, it shouldn't be
too much work to port it over.
As for a purely query only solution... I don't know how you'd do it.
Sorry. :)
Pete
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 5:30 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> I am so sorry. I just wanted to run a pure ACCESS query
> pulling up journal
> titles that show up in more than one locations, Rockhurst
> University (For
> Print or Microfom)or "Gale" or "Periodical Abstracts ..", etc.for
> e-databases. This has nothing to do with ASP. Do I need to
> write an asp page
> for this?
>
> I appologize for not stating clearly and wasting your time.
> Hope this helps
> make it easier.
>
> Julia
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, March 25, 2002 2:18 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> Ok, if that is the case, then I would do it like this.
> 1. Perform a search that counts the number of records with Electronic
> format
> 2. If the count is greater than zero, then Perform a search for the
> records with Print format
>
> <%
> Dim SearchTitle = "ADOLESCENCE"
> SQLStr = "SELECT Count(Title) As numRecords FROM Maetro " & _
> "WHERE ((Format='Electronic') " & _
> "AND (Title='" & SearchTitle & "'))"
>
> Set oRS = Server.CreateObject("ADODB.Recordset")
> ' I assume here that you have named you database connection DConn
> Set oRS = DConn.Execute( SQLStr, ,adCmdText )
>
> If (CInt(oRS("numRecords")) > 0) Then
> ' We know we have electronic format of this SearchTitle
> ' So now print out all PRINT format records
> SQLStr = "SELECT * FROM Maestro " & _
> "WHERE ((Format='PRINT') " & _
> "AND (Title='" & SearchTitle & "'))"
>
> Set oRS = DConn.Execute( SQLStr, ,adCmdText )
>
> Do While Not oRS.EOF
> ' Print the records
> Response.Write(oRS("Title") & " - " & oRS("Format") & " - " &
> oRS("Location") & "<br>")
> oRS.MoveNext
> Loop
>
> End If
>
> ' Close oRS here
> oRS.Close
> Set oRS = Nothing
> %>
>
> See if that does what you need.
> Regards,
> Pete
>
>
> > -----Original Message-----
> > From: Wang, Julia [mailto:Julia.Wang@R...]
> > Sent: Monday, March 25, 2002 2:46 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> Query--Comb
> > ined criteria!!
> >
> >
> > Thank you all for responding!!
> >
> > This seems to hit the point. Right! The db created by my
> > predessor does not
> > have ID numbers or primary key. If so, could this be the
> > reason that I can
> > not run combined query using "And" (The syntax seems to be
> > right!). (Using
> > OR is no problem but not needed!)
> >
> > No, my purpose is not to delete the print records on the db
> > but to print out
> > a list of titles that have both Print and Electronic format
> > so we can make
> > decisions as to cancel some of the print subscriptions to
> > save money. We are
> > paying money on duplicates!
> >
> > Here is another example of the table to illustrate:
> >
> > Title YearsA Holdings Format Location
> > ADOLESCENCE 1966 v.1-8, 1966-1973 PRINT
> > ROCKHURST
> > LIBRARY: PERIODICALS
> > ADOLESCENCE 1974 v.9-21, 1974-1986 MICROFILM
> > ROCKHURST
> > LIBRARY: MICROFILM
> > ADOLESCENCE 1987 v.22-36,1987-2001-MICROFICHE
> > ROCKHURST LIBRARY:
> > MICROFICHE
> > Adolescence 4000 Text: 1/1/94 Electronic
> > Periodical Abstracts
> > Research II
> > Adolescence 5000 1/1/95 _ current Electronic
> > FirstSearch:
> > Wilson Select Full Text
> > Adolescence 5000 3/1/89 _ current Electronic
> > FirstSearch:
> > Health and Wellness Information
> > Adolescent February 1999 to current Electronic
> > Academic Universe
> >
> > Thanks so very much for all your time! Please come up with
> > something. I am
> > desperate and I am so new to ACCESS!
> >
> > Julia
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, March 25, 2002 12:56 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> Query--Comb
> > ined criteria!!
> >
> >
> > Hi Julia,
> >
> > It looks as though you have no unique identifier for each
> record. Is
> > that correct? I may be wrong, but to me it looks as though your
> > database design is problematic. Also, do you want to delete
> > all records
> > where the format is "PRINT" if there is also a format
> > "Electronic"? If
> > that is the case, why don't you just do 2 queries. The first
> > one to get
> > the count of all records with "Electronic". If the count is greater
> > than zero, then perform the second query to delete all of the PRINT
> > records. Or perhaps I just don't understand what it is that you are
> > asking for.
> >
> > Pete
> >
> >
> > > -----Original Message-----
> > > From: Wang, Julia [mailto:Julia.Wang@R...]
> > > Sent: Monday, March 25, 2002 1:13 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] RE: Please help!-- Running Access
> > Query--Comb
> > > ined criteria!!
> > >
> > >
> > >
> > > Thomas, thanks for the suggestion!
> > >
> > > OR can not be used because I need to pull all the data that
> > meets both
> > > conditions that is,for example, title: "Science" journal that
> > > is both in our
> > > print subscription in "Rockhurst" column and is also
> provided on the
> > > e-database "Periodical Abstracts...".
> > >
> > > The table looks like this:
> > >
> > > Title YearsA Holdings Format Location
> > > SCIENCE 1953 v.118-182, 1953-1973 PRINT
> > > ROCKHURST LIBRARY:
> > > PERIODICALS
> > > SCIENCE 1974 v.183:4120-4131, 1974 PRINT
> > > ROCKHURST LIBRARY:
> > > PERIODICALS
> > > SCIENCE 1975 v.183-236, 1974-1987 MICROFILM
> > > ROCKHURST LIBRARY:
> > > MICROFILM
> > > SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE
> > > ROCKHURST LIBRARY:
> > > MICROFICHE
> > > Science 4000 Page Image: 1/1/88 + current; Text and
> > > Graphics: 6/21/96 _
> > > current;
> > > Electronic Periodical Abstracts Research II
> > > SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
> > >
> > >
> > > The criteria that I have been using are:
> > > **For Print only which is no problem at all:
> > >
> > > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > > MasterHoldings.Location
> > > FROM MasterHoldings
> > > WHERE (((MasterHoldings.Location) Like "Rock*"));
> > >
> > > **For combined criteria whick is the reason I am requesting help:
> > >
> > > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > > MasterHoldings.Location
> > > FROM MasterHoldings
> > > WHERE (((MasterHoldings.Location) Like "Rock*" And
> > > (MasterHoldings.Location)
> > > Like "Periodical *"));
> > >
> > > I have reached my wits end. Please help!!
> > >
> > > Julia
> > >
> > > -----Original Message-----
> > > From: Drew, Ron [mailto:RDrew@B...]
> > > Sent: Monday, March 25, 2002 9:38 AM
> > > To: ASP Databases
> > > Subject: [asp_databases] RE: Please help!-- Running Access
> > > Query--Combined criteria!!
> > >
> > >
> > > Please supply the query that works...just the sql ...and
> > the one that
> > > does not work....
> > >
> > > -----Original Message-----
> > > From: Wang, Julia [mailto:Julia.Wang@R...]
> > > Sent: Monday, March 25, 2002 9:53 AM
> > > To: ASP Databases
> > > Subject: [asp_databases] Please help!-- Running Access
> > Query--Combined
> > > criteria!!
> > >
> > >
> > >
> > > Any suggestions are appreciated!!
> > >
> > > ** We have a master table named "Maetro" which holds around 1,6000
> > > records of journal titles of both print and electronic. The table
> > > consists of Title column, Format (e.g. "Print" or
> > "Electronic")column,
> > > and Location (e.g. Library name (here "Rockhurst Unversity", and
> > > Electronic Database names like "First Search", "Gale", "Proquest",
> > > etc.") Since there are obvious overlaps in certain titles for
> > > both print
> > > and electronic, I need to run a combined query to extract
> > titles that
> > > shows up BOTH in line with "Rockhurst University" and with
> > one of the
> > > e-databases such as "Gale", or "Proquest", etc. so that
> we can make
> > > decisions to unsubscribe the duplicate print titles due to
> > increasing
> > > prices.
> > >
> > > I tried using, under criteria, Like "Rock*" and Like "Gale*"
> > > or another
> > > set Like "Rock*" and Like "Proquest *", etc. for Print to
> match each
> > > type of electronics, but it returns me "0"!!! If I run
> single query,
> > > there is just no problem. And I know there are definitely
> > duplicates!
> > >
> > > Could any of you take some time and help me as to how to run
> > > a combined
> > > Query? --Thank You! Thank You! Thank You!
> > >
> > > Please ask questions if this is not clearly stated!--Appreciated!
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Message #11 by "Kim Iwan Hansen" <kimiwan@k...> on Mon, 25 Mar 2002 23:42:27 +0100
|
|
Not at all, just go to the query analyzer and type and run your sql.
To get to the query analyzer, do this:
1) Create query in design view
2) Close the table dialogue
3) Right-click and select "sql view")
4) Type in your sql and run it!
-Kim
-----Original Message-----
From: Wang, Julia [mailto:Julia.Wang@R...]
Sent: 25. marts 2002 23:30
To: ASP Databases
Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
ined criteria!!
I am so sorry. I just wanted to run a pure ACCESS query pulling up journal
titles that show up in more than one locations, Rockhurst University (For
Print or Microfom)or "Gale" or "Periodical Abstracts ..", etc.for
e-databases. This has nothing to do with ASP. Do I need to write an asp page
for this?
I appologize for not stating clearly and wasting your time. Hope this helps
make it easier.
Julia
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, March 25, 2002 2:18 PM
To: ASP Databases
Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
ined criteria!!
Ok, if that is the case, then I would do it like this.
1. Perform a search that counts the number of records with Electronic
format
2. If the count is greater than zero, then Perform a search for the
records with Print format
<%
Dim SearchTitle = "ADOLESCENCE"
SQLStr = "SELECT Count(Title) As numRecords FROM Maetro " & _
"WHERE ((Format='Electronic') " & _
"AND (Title='" & SearchTitle & "'))"
Set oRS = Server.CreateObject("ADODB.Recordset")
' I assume here that you have named you database connection DConn
Set oRS = DConn.Execute( SQLStr, ,adCmdText )
If (CInt(oRS("numRecords")) > 0) Then
' We know we have electronic format of this SearchTitle
' So now print out all PRINT format records
SQLStr = "SELECT * FROM Maestro " & _
"WHERE ((Format='PRINT') " & _
"AND (Title='" & SearchTitle & "'))"
Set oRS = DConn.Execute( SQLStr, ,adCmdText )
Do While Not oRS.EOF
' Print the records
Response.Write(oRS("Title") & " - " & oRS("Format") & " - " &
oRS("Location") & "<br>")
oRS.MoveNext
Loop
End If
' Close oRS here
oRS.Close
Set oRS = Nothing
%>
See if that does what you need.
Regards,
Pete
> -----Original Message-----
> From: Wang, Julia [mailto:Julia.Wang@R...]
> Sent: Monday, March 25, 2002 2:46 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> Thank you all for responding!!
>
> This seems to hit the point. Right! The db created by my
> predessor does not
> have ID numbers or primary key. If so, could this be the
> reason that I can
> not run combined query using "And" (The syntax seems to be
> right!). (Using
> OR is no problem but not needed!)
>
> No, my purpose is not to delete the print records on the db
> but to print out
> a list of titles that have both Print and Electronic format
> so we can make
> decisions as to cancel some of the print subscriptions to
> save money. We are
> paying money on duplicates!
>
> Here is another example of the table to illustrate:
>
> Title YearsA Holdings Format Location
> ADOLESCENCE 1966 v.1-8, 1966-1973 PRINT
> ROCKHURST
> LIBRARY: PERIODICALS
> ADOLESCENCE 1974 v.9-21, 1974-1986 MICROFILM
> ROCKHURST
> LIBRARY: MICROFILM
> ADOLESCENCE 1987 v.22-36,1987-2001-MICROFICHE
> ROCKHURST LIBRARY:
> MICROFICHE
> Adolescence 4000 Text: 1/1/94 Electronic
> Periodical Abstracts
> Research II
> Adolescence 5000 1/1/95 _ current Electronic
> FirstSearch:
> Wilson Select Full Text
> Adolescence 5000 3/1/89 _ current Electronic
> FirstSearch:
> Health and Wellness Information
> Adolescent February 1999 to current Electronic
> Academic Universe
>
> Thanks so very much for all your time! Please come up with
> something. I am
> desperate and I am so new to ACCESS!
>
> Julia
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, March 25, 2002 12:56 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Please help!-- Running Access Query--Comb
> ined criteria!!
>
>
> Hi Julia,
>
> It looks as though you have no unique identifier for each record. Is
> that correct? I may be wrong, but to me it looks as though your
> database design is problematic. Also, do you want to delete
> all records
> where the format is "PRINT" if there is also a format
> "Electronic"? If
> that is the case, why don't you just do 2 queries. The first
> one to get
> the count of all records with "Electronic". If the count is greater
> than zero, then perform the second query to delete all of the PRINT
> records. Or perhaps I just don't understand what it is that you are
> asking for.
>
> Pete
>
>
> > -----Original Message-----
> > From: Wang, Julia [mailto:Julia.Wang@R...]
> > Sent: Monday, March 25, 2002 1:13 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> Query--Comb
> > ined criteria!!
> >
> >
> >
> > Thomas, thanks for the suggestion!
> >
> > OR can not be used because I need to pull all the data that
> meets both
> > conditions that is,for example, title: "Science" journal that
> > is both in our
> > print subscription in "Rockhurst" column and is also provided on the
> > e-database "Periodical Abstracts...".
> >
> > The table looks like this:
> >
> > Title YearsA Holdings Format Location
> > SCIENCE 1953 v.118-182, 1953-1973 PRINT
> > ROCKHURST LIBRARY:
> > PERIODICALS
> > SCIENCE 1974 v.183:4120-4131, 1974 PRINT
> > ROCKHURST LIBRARY:
> > PERIODICALS
> > SCIENCE 1975 v.183-236, 1974-1987 MICROFILM
> > ROCKHURST LIBRARY:
> > MICROFILM
> > SCIENCE 1987 v.237-294:5551-,2001- MICROFICHE
> > ROCKHURST LIBRARY:
> > MICROFICHE
> > Science 4000 Page Image: 1/1/88 + current; Text and
> > Graphics: 6/21/96 _
> > current;
> > Electronic Periodical Abstracts Research II
> > SCIENCE 6000 v.131-266, 1960-1994 Electronic JSTOR
> >
> >
> > The criteria that I have been using are:
> > **For Print only which is no problem at all:
> >
> > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > MasterHoldings.Location
> > FROM MasterHoldings
> > WHERE (((MasterHoldings.Location) Like "Rock*"));
> >
> > **For combined criteria whick is the reason I am requesting help:
> >
> > SELECT MasterHoldings.Title, MasterHoldings.Format,
> > MasterHoldings.Location
> > FROM MasterHoldings
> > WHERE (((MasterHoldings.Location) Like "Rock*" And
> > (MasterHoldings.Location)
> > Like "Periodical *"));
> >
> > I have reached my wits end. Please help!!
> >
> > Julia
> >
> > -----Original Message-----
> > From: Drew, Ron [mailto:RDrew@B...]
> > Sent: Monday, March 25, 2002 9:38 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Please help!-- Running Access
> > Query--Combined criteria!!
> >
> >
> > Please supply the query that works...just the sql ...and
> the one that
> > does not work....
> >
> > -----Original Message-----
> > From: Wang, Julia [mailto:Julia.Wang@R...]
> > Sent: Monday, March 25, 2002 9:53 AM
> > To: ASP Databases
> > Subject: [asp_databases] Please help!-- Running Access
> Query--Combined
> > criteria!!
> >
> >
> >
> > Any suggestions are appreciated!!
> >
> > ** We have a master table named "Maetro" which holds around 1,6000
> > records of journal titles of both print and electronic. The table
> > consists of Title column, Format (e.g. "Print" or
> "Electronic")column,
> > and Location (e.g. Library name (here "Rockhurst Unversity", and
> > Electronic Database names like "First Search", "Gale", "Proquest",
> > etc.") Since there are obvious overlaps in certain titles for
> > both print
> > and electronic, I need to run a combined query to extract
> titles that
> > shows up BOTH in line with "Rockhurst University" and with
> one of the
> > e-databases such as "Gale", or "Proquest", etc. so that we can make
> > decisions to unsubscribe the duplicate print titles due to
> increasing
> > prices.
> >
> > I tried using, under criteria, Like "Rock*" and Like "Gale*"
> > or another
> > set Like "Rock*" and Like "Proquest *", etc. for Print to match each
> > type of electronics, but it returns me "0"!!! If I run single query,
> > there is just no problem. And I know there are definitely
> duplicates!
> >
> > Could any of you take some time and help me as to how to run
> > a combined
> > Query? --Thank You! Thank You! Thank You!
> >
> > Please ask questions if this is not clearly stated!--Appreciated!
> >
> >
> >
> >
>
>
>
|
|
|
View Cart
