Problem with multi-column equi-join query

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

Guest

Here is the SQL syntax for the query:
SELECT qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number], qryCOMU_egg_first_seen.[Egg first seen],
qryCOMU_egg_first_seen.[Expected hatch date], qryCOMU_chick_first_seen.[Chick
first seen], qryCOMU_chick_first_seen.[Expected fledge date]
FROM qryCOMU_egg_first_seen INNER JOIN qryCOMU_chick_first_seen ON
qryCOMU_egg_first_seen.[Site number] = qryCOMU_chick_first_seen.[Site number]
ORDER BY qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number];

I run into a problem when I run this query b/c the even though [Chick first
seen] and [Expected fledge date] don't have data for some records, the query
automatically fills them in with todays date. How do I get this to run so
that data is only entered, when data actually exists in the record? Thanks
in advance.
 
Hi, Peter,

Try checking the SQL of the sub-query "qryCOMU_chick_first_seen." Something
tells me there's something there.

Sam

peter said:
Here is the SQL syntax for the query:
SELECT qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number], qryCOMU_egg_first_seen.[Egg first seen],
qryCOMU_egg_first_seen.[Expected hatch date], qryCOMU_chick_first_seen.[Chick
first seen], qryCOMU_chick_first_seen.[Expected fledge date]
FROM qryCOMU_egg_first_seen INNER JOIN qryCOMU_chick_first_seen ON
qryCOMU_egg_first_seen.[Site number] = qryCOMU_chick_first_seen.[Site number]
ORDER BY qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number];

I run into a problem when I run this query b/c the even though [Chick first
seen] and [Expected fledge date] don't have data for some records, the query
automatically fills them in with todays date. How do I get this to run so
that data is only entered, when data actually exists in the record? Thanks
in advance.
Click to expand...
 
Also check the table design to see if the default value is Date() or Now().

HTH;

Amy

OfficeDev18 via AccessMonster.com said:
Hi, Peter,

Try checking the SQL of the sub-query "qryCOMU_chick_first_seen."
Something
tells me there's something there.

Sam

peter said:
Here is the SQL syntax for the query:
SELECT qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number], qryCOMU_egg_first_seen.[Egg first seen],
qryCOMU_egg_first_seen.[Expected hatch date],
qryCOMU_chick_first_seen.[Chick
first seen], qryCOMU_chick_first_seen.[Expected fledge date]
FROM qryCOMU_egg_first_seen INNER JOIN qryCOMU_chick_first_seen ON
qryCOMU_egg_first_seen.[Site number] = qryCOMU_chick_first_seen.[Site
number]
ORDER BY qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number];

I run into a problem when I run this query b/c the even though [Chick
first
seen] and [Expected fledge date] don't have data for some records, the
query
automatically fills them in with todays date. How do I get this to run so
that data is only entered, when data actually exists in the record?
Thanks
in advance.
Click to expand...
Click to expand...
 
Hi Amy,

So I looked at the both the chick first seen query. It only returns the
results of records containing data for nests with chicks. However, when I do
the equi-join it still enters the current data for records where no chick has
been observed. The only thing I can think, is that in the original table
where this data is coming from, the default date is set for the current date.
However, the data goes through several steps including a make table query
before I do the equi-join, which makes me believe that this should no longer
be an issue. What is the difference between Date() and Now() and what would
the syntax look like? (I'm still a relative newbie with Access and SQL.
Thanks again,

Peter

Amy Blankenship said:
Also check the table design to see if the default value is Date() or Now().

HTH;

Amy

OfficeDev18 via AccessMonster.com said:
Hi, Peter,

Try checking the SQL of the sub-query "qryCOMU_chick_first_seen."
Something
tells me there's something there.

Sam

peter said:
Here is the SQL syntax for the query:
SELECT qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number], qryCOMU_egg_first_seen.[Egg first seen],
qryCOMU_egg_first_seen.[Expected hatch date],
qryCOMU_chick_first_seen.[Chick
first seen], qryCOMU_chick_first_seen.[Expected fledge date]
FROM qryCOMU_egg_first_seen INNER JOIN qryCOMU_chick_first_seen ON
qryCOMU_egg_first_seen.[Site number] = qryCOMU_chick_first_seen.[Site
number]
ORDER BY qryCOMU_egg_first_seen.[Sub-colony], qryCOMU_egg_first_seen.[Site
number];

I run into a problem when I run this query b/c the even though [Chick
first
seen] and [Expected fledge date] don't have data for some records, the
query
automatically fills them in with todays date. How do I get this to run so
that data is only entered, when data actually exists in the record?
Thanks
in advance.
Click to expand...
Click to expand...
Click to expand...
 
Hi, Peter,

Look at the design structure for your underlying table(s). Check all the date
fields. Somewhere, one of the date fields probably has "=Date()" (or, as Amy
suggests, "=Now()") in the Default Value property. Delete all these defaults.
They're obviously incorrect.

Sam

peter said:
Hi Amy,

So I looked at the both the chick first seen query. It only returns the
results of records containing data for nests with chicks. However, when I do
the equi-join it still enters the current data for records where no chick has
been observed. The only thing I can think, is that in the original table
where this data is coming from, the default date is set for the current date.
However, the data goes through several steps including a make table query
before I do the equi-join, which makes me believe that this should no longer
be an issue. What is the difference between Date() and Now() and what would
the syntax look like? (I'm still a relative newbie with Access and SQL.
Thanks again,

Peter
Also check the table design to see if the default value is Date() or Now().
Click to expand...
[quoted text clipped - 30 lines]
Click to expand...
 
peter kappes said:
Hi Amy,

So I looked at the both the chick first seen query. It only returns the
results of records containing data for nests with chicks. However, when I
do
the equi-join it still enters the current data for records where no chick
has
been observed. The only thing I can think, is that in the original table
where this data is coming from, the default date is set for the current
date.
However, the data goes through several steps including a make table query
before I do the equi-join, which makes me believe that this should no
longer
be an issue.
Click to expand...

I've never been a fan of making additional tables to process data. If you
have the data in the tables, you can make queries which you can then use as
if they were tables. Since I don't believe in this method, conceptually, I
have no experience with its pitfalls. So I can't help you.
What is the difference between Date() and Now() and what would
the syntax look like? (I'm still a relative newbie with Access and SQL.
Thanks again,
Click to expand...

Date() just contains the date. Now() contains the date and time.

If you see one of the above in the default value of the field in design view
of the table, then it is being used as the default value.

Hope this clarifies;

-Amy
 
Back
Top