Exclude Records based on a joined field?

[Jay]

Hi,

I have a table containing transaction data which includes a 'SoldDate'
field. This table contains records relating to all days of the week.

However, I want to exclude records relating to Saturdays in some of my
queries.

So I have imported another table containing the dates of all Saturdays.
I can add this table to my queries and join the two date fields to
select all Saturdays, but how do I do likewise to *exclude* all
Saturdays. In effect, not include records where the joined fields are
equal.

Any help would be greatly appreciated.

Regards

Jason

 

[Guest]

You do not need a table of all Saturdays but just check which day of the week
your dates is like this ---
Format([YourDateField],"w")
Use criteria of <>7

 

[Jay]

Thanks Carl, But I have a small problem - the database is one I
'inherited' and the date field is 'text' and not 'date' format. I've
tried to change it but Access won't let me (I get an error message).

So I imported the Saturday dates as text as well to give me
like-formatted fields to join. I know it's not ideal and if I could
change the field format from text to date in my main table I would.

So, is there a way to exclude where the joined fields are equal?

Many thanks,

Jason

 

[Guest]

Yes, use a left join from your data table to Saturday table. Pull down the
Saturday table field and use Is Null as criteria.
BUT it still might not work if the two fields are not of the same data
type.

If Access give you error when you try to convert the field then you probably
have some data that is bad. Best way is to insert a new DateTime datatype
field and run an update query. Then do a manual check on the records that
did not convert.

--
KARL DEWEY
Build a little - Test a little

Thanks Carl, But I have a small problem - the database is one I
'inherited' and the date field is 'text' and not 'date' format. I've
tried to change it but Access won't let me (I get an error message).

So I imported the Saturday dates as text as well to give me
like-formatted fields to join. I know it's not ideal and if I could
change the field format from text to date in my main table I would.

So, is there a way to exclude where the joined fields are equal?

Many thanks,

Jason

You do not need a table of all Saturdays but just check which day of the week
your dates is like this ---
Format([YourDateField],"w")
Use criteria of <>7

 

[Jay]

Thanks Carl, that's worked great. Could you just clarify my understanding
though.

The LEFT JOIN tells the query to 'Include all records from 'tblSalesData'
and only those records from 'tblSaturdays' where the joined fields are
equal. So how does having a where criteria of Is Null in the Saturday field
*exclude* all Saturdays from my query.

I see that it works but don't understand how the join type and Is Null makes
it work.

Sorry to be a bit slow.

Cheers,

Jason

Yes, use a left join from your data table to Saturday table. Pull down
the
Saturday table field and use Is Null as criteria.
BUT it still might not work if the two fields are not of the same data
type.

If Access give you error when you try to convert the field then you
probably
have some data that is bad. Best way is to insert a new DateTime datatype
field and run an update query. Then do a manual check on the records that
did not convert.

--
KARL DEWEY
Build a little - Test a little

Thanks Carl, But I have a small problem - the database is one I
'inherited' and the date field is 'text' and not 'date' format. I've
tried to change it but Access won't let me (I get an error message).

So I imported the Saturday dates as text as well to give me
like-formatted fields to join. I know it's not ideal and if I could
change the field format from text to date in my main table I would.

So, is there a way to exclude where the joined fields are equal?

Many thanks,

Jason

You do not need a table of all Saturdays but just check which day of
the week
your dates is like this ---
Format([YourDateField],"w")
Use criteria of <>7

 

[Guest]

Without the criteria the query would pull records where there is a match. It
would also pull ALL records in the left table and indicate that the right
table was null. With the criteria it pulls only records where the right is
null.
--
KARL DEWEY
Build a little - Test a little

Thanks Carl, that's worked great. Could you just clarify my understanding
though.

The LEFT JOIN tells the query to 'Include all records from 'tblSalesData'
and only those records from 'tblSaturdays' where the joined fields are
equal. So how does having a where criteria of Is Null in the Saturday field
*exclude* all Saturdays from my query.

I see that it works but don't understand how the join type and Is Null makes
it work.

Sorry to be a bit slow.

Cheers,

Jason

Yes, use a left join from your data table to Saturday table. Pull down
the
Saturday table field and use Is Null as criteria.
BUT it still might not work if the two fields are not of the same data
type.

If Access give you error when you try to convert the field then you
probably
have some data that is bad. Best way is to insert a new DateTime datatype
field and run an update query. Then do a manual check on the records that
did not convert.

--
KARL DEWEY
Build a little - Test a little

Thanks Carl, But I have a small problem - the database is one I
'inherited' and the date field is 'text' and not 'date' format. I've
tried to change it but Access won't let me (I get an error message).

So I imported the Saturday dates as text as well to give me
like-formatted fields to join. I know it's not ideal and if I could
change the field format from text to date in my main table I would.

So, is there a way to exclude where the joined fields are equal?

Many thanks,

Jason


KARL DEWEY wrote:
You do not need a table of all Saturdays but just check which day of
the week
your dates is like this ---
Format([YourDateField],"w")
Use criteria of <>7

 

[Jay]

I'm sorry Karl but I'm still confused. Doesn't Is Null mean and empty
field? There are no empty fields in the Saturdays table. Am I missing
some other meaning of Is Null.

Sorry to be a pain.

Jason

Without the criteria the query would pull records where there is a match.
It
would also pull ALL records in the left table and indicate that the right
table was null. With the criteria it pulls only records where the right
is
null.
--
KARL DEWEY
Build a little - Test a little

Thanks Carl, that's worked great. Could you just clarify my
understanding
though.

The LEFT JOIN tells the query to 'Include all records from 'tblSalesData'
and only those records from 'tblSaturdays' where the joined fields are
equal. So how does having a where criteria of Is Null in the Saturday
field
*exclude* all Saturdays from my query.

I see that it works but don't understand how the join type and Is Null
makes
it work.

Sorry to be a bit slow.

Cheers,

Jason

Yes, use a left join from your data table to Saturday table. Pull down
the
Saturday table field and use Is Null as criteria.
BUT it still might not work if the two fields are not of the same
data
type.

If Access give you error when you try to convert the field then you
probably
have some data that is bad. Best way is to insert a new DateTime
datatype
field and run an update query. Then do a manual check on the records
that
did not convert.

--
KARL DEWEY
Build a little - Test a little


:

Thanks Carl, But I have a small problem - the database is one I
'inherited' and the date field is 'text' and not 'date' format. I've
tried to change it but Access won't let me (I get an error message).

So I imported the Saturday dates as text as well to give me
like-formatted fields to join. I know it's not ideal and if I could
change the field format from text to date in my main table I would.

So, is there a way to exclude where the joined fields are equal?

Many thanks,

Jason


KARL DEWEY wrote:
You do not need a table of all Saturdays but just check which day of
the week
your dates is like this ---
Format([YourDateField],"w")
Use criteria of <>7

 

[Guest]

Let me repeat myself and expound on it --
An INNER JOIN without the criteria the query would pull records where there
is a match.
1/1/07 1/1/07 ------ These are not real dates ----
2/3/07 2/3/07

With a LEFT JOIN It would pull ALL records in the left table and indicate
that the right table did not have a matching record and therefore show a
null.
1/1/07 1/1/07
1/2/07
2/3/07 2/3/07
2/4/07
2/5/07

With the criteria it pulls only records where the right is showing a null.
1/2/07
2/4/07
2/5/07
--
KARL DEWEY
Build a little - Test a little

I'm sorry Karl but I'm still confused. Doesn't Is Null mean and empty
field? There are no empty fields in the Saturdays table. Am I missing
some other meaning of Is Null.

Sorry to be a pain.

Jason

Without the criteria the query would pull records where there is a match.
It
would also pull ALL records in the left table and indicate that the right
table was null. With the criteria it pulls only records where the right
is
null.
--
KARL DEWEY
Build a little - Test a little

Thanks Carl, that's worked great. Could you just clarify my
understanding
though.

The LEFT JOIN tells the query to 'Include all records from 'tblSalesData'
and only those records from 'tblSaturdays' where the joined fields are
equal. So how does having a where criteria of Is Null in the Saturday
field
*exclude* all Saturdays from my query.

I see that it works but don't understand how the join type and Is Null
makes
it work.

Sorry to be a bit slow.

Cheers,

Jason

Yes, use a left join from your data table to Saturday table. Pull down
the
Saturday table field and use Is Null as criteria.
BUT it still might not work if the two fields are not of the same
data
type.

If Access give you error when you try to convert the field then you
probably
have some data that is bad. Best way is to insert a new DateTime
datatype
field and run an update query. Then do a manual check on the records
that
did not convert.

--
KARL DEWEY
Build a little - Test a little


:

Thanks Carl, But I have a small problem - the database is one I
'inherited' and the date field is 'text' and not 'date' format. I've
tried to change it but Access won't let me (I get an error message).

So I imported the Saturday dates as text as well to give me
like-formatted fields to join. I know it's not ideal and if I could
change the field format from text to date in my main table I would.

So, is there a way to exclude where the joined fields are equal?

Many thanks,

Jason


KARL DEWEY wrote:
You do not need a table of all Saturdays but just check which day of
the week
your dates is like this ---
Format([YourDateField],"w")
Use criteria of <>7

 

[Jay]

Many thanks Karl, I've finally got it - Effectively any date *not* in
the Saturdays (right) is Null.

This has been a real help. My understanding of 'Is Null' was a little
limited, I understood it to only mean an empty field in an existing
record, so you can see how I was a little confused,

I appreciate you taking the time and having patience. In fact, this has
been really beneficial because now I understand this use of 'Is Null' I
can imagine lots of other instances where I could apply it.

Most appreciated! You're a real credit to this forum.

Regards,

Jay