How do I get this query to work?

[Karen Hill]

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
AND WHERE (([surgeries].[date_a]=Date()) Or
[surgeries].[date_b]=Date());

I'm trying to write a query that joins two table together, animals and
surgeries where surgeries.id = animals.id and only where the surgery
date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

Thanks in advance for all your help.

[Bob Stearns]

Karen Hill wrote:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
AND WHERE (([surgeries].[date_a]=Date()) Or
I'm trying to write a query that joins two table together, animals and
surgeries where surgeries.id = animals.id and only where the surgery
date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

Thanks in advance for all your help.

You you need some ddl to make this clear, but I'll take a stab at what I
think you want:
AND (([surgeries].[date_op]='2005-07-04') OR
([surgeries].[date_op]='2005-05-30')));
This is the standard sql technique. You'll have to adjust for Access's
method of substituting input parameters, I'm a db2 user myself.

[MGFoster]

Karen Hill wrote:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
AND WHERE (([surgeries].[date_a]=Date()) Or
[surgeries].[date_b]=Date());

I'm trying to write a query that joins two table together, animals and
surgeries where surgeries.id = animals.id and only where the surgery
date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure how a surgery equals an animal?! You sure you have your tables
set up correctly?

Your query has incorrect syntax - there is no AND before WHERE.

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
WHERE [surgeries].[date_a]=Date()
OR [surgeries].[date_b]=Date())

A good rule of thumb is to always use a column list in the SELECT
clause, not the ALL wildcard (*).

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtHi7YechKq OuFEgEQIERQCg8m 9qdvyTO9s3YJ1NT UwpdK1jSwcAn0T8
dqa96HvXQCHAkJd ZkrQyHrFJ
=a9kM
-----END PGP SIGNATURE-----

[David W. Fenton]

"Karen Hill" <ka**********@y ahoo.com> wrote in
news:11******** **************@ g49g2000cwa.goo glegroups.com:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON
[surgeries].[id]=[animals].[id] AND WHERE
(([surgeries].[date_a]=Date()) Or [surgeries].[date_b]=Date());

I'm trying to write a query that joins two table together, animals
and surgeries where surgeries.id = animals.id and only where the
surgery date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

Is the ID field in the two tables the same data? That is, does the
animals table have its own Autonumber field with the exact same name
as the Autonumber field in the surgeries table? If so, you don't
want to use it to join the two tables, as, despite the fact that
you've unwisely given it the same name, it's not the same data. One
is the ID for a surgery, and the other is an ID for an animal.

I never use "ID" as a field name. I always use names in the form
AnimalID or SurgeryID. I also name my tables in the form tblAnimal
and tblSurgery (singular, not plural, since the entity represented
by each record is a single instance of whatever entity the table is
storing data about). Then I can figure out the name of the primary
key by taking the table name, lopping off the first three characters
and adding "ID" to it.

Anyway, you should be storing the AnimalID in the surgeries table,
and linking on that:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON
surgeries.Anima lID=animals.Ani malID
WHERE ((surgeries.[date_a]=Date()) Or surgeries.[date_b]=Date());

Also note that there oughtn't be an AND before the word WHERE.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[--CELKO--]

You are giving the worst possible advise to the Newbie.

I also name my tables in the form tblAnimal and tblSurgery (singular, not plural, since the entity represented by each record [sic] is a single instance of whatever entity the table is storing data about). <<
First of all, there are no records and fields in RDBMS; rows and
columns are totally different concepts. I agree that "the entity
represented by each record [sic] is a single instance.." but that has
nothing with naming the table that holds those entities. What is the
name of the set, not the entities? This is one reason why a table is
not a file. Sets are not sequential, are not a list of entities. Read
any book on Sets. Or data modeling. You are doing record processing
in your head and have not moved to a set oriented model.

ISO-11179 Standards tells us not to put silly prefixes like "tbl-"
that describe the physical storage into a data element name. Name each
data element for what it is; never for how it is used in one place, how
it is encoded, where it occurs, etc. A data element should have one and
only one name.
Then I can figure out the name of the primary key by taking the table name, lopping off the first three characters and adding "ID" to it. <<

Amazing how you do not have to refer to industry standards, or even do
even minimal research! Gee, what a waste of time it was to create VIN,
ISBN, UPC, etc. codes! Of course it has no data integrity, but boy it
was quick!

Since autoincrement numbers cannot be validated or verified in the data
model, you would NEVER use them in an RDBMS. They are exposed physical
locators, usually an attmept to mimic the record numbers from a file
model.

[David W. Fenton]

"--CELKO--" <jc*******@eart hlink.net> wrote in
news:11******** *************@z 14g2000cwz.goog legroups.com:

You are giving the worst possible advise to the Newbie.

Oh, shut up, Celko.

You definitely know your stuff, but you are vastly impractical in
your advice.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David Schofield]

On Wed, 13 Jul 2005 02:03:51 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

"--CELKO--" <jc*******@eart hlink.net> wrote in
news:11******* **************@ z14g2000cwz.goo glegroups.com:

You are giving the worst possible advise to the Newbie.

Oh, shut up, Celko.

You definitely know your stuff, but you are vastly impractical in
your advice.

--

Hi
Its a good job Chris Date doesn't read this group.

Personally I do everything in code in stored procedures and use things
like mdiff as much as possible in my SQL. I'm just waiting for
everyone to update to the post-relational hierarchical model.

Just kidding!
David

[--CELKO--]

So why are so many of my consulting jobs doing clean up work on RDBMS
projects done in the manner you described:) I guess when the auditors
show up or the data integrity is gone or the whole thing falls apart,
the owners are willing to be "vastly impractical" and follow good
practices and standards.

[AK]

>>So why are so many of my consulting jobs doing clean up work on RDBMS
projects done in the manner you described:
<<

I guess that's because the startups that try to do it perfect the first
time just don't make it to profitability. Only the ones that take
shortcuts to get things done quickly grow up and eventually can afford
Mr Celko's advice

[Gene Wirchenko]

On 13 Jul 2005 14:59:11 -0700, "AK" <AK************ @hotmail.COM>
wrote:

So why are so many of my consulting jobs doing clean up work on RDBMS

projects done in the manner you described:

I guess that's because the startups that try to do it perfect the first
time just don't make it to profitability. Only the ones that take
shortcuts to get things done quickly grow up and eventually can afford
Mr Celko's advice

Maybe, they would do better if they did it right in the first
place. Doing it right does not necessarily mean taking a long time
about it.

Sincerely,

Gene Wirchenko