Join?

[SAC]

I'd like to see all customers but only transactions since 5/1/10.

I have a query like this, but I'm getting a record for each Customer.

ARINH is a table for the transactions.

SELECT Customer.AcctNo, ARINH.Date
FROM ARINH RIGHT JOIN Customer ON ARINH.CustId = Customer.Key
WHERE (((ARINH.Date)>=#5/1/2010#));

How can I change it so some results in the date column would be null. They
should be.

Thanks for your help.

 

[Dirk Goldgar]

I'd like to see all customers but only transactions since 5/1/10.

I have a query like this, but I'm getting a record for each Customer.

You *are* getting a record for each customer, or you aren't? Your WHERE
clause as written below will give you only those customers who have
transactions sint May 1st.

ARINH is a table for the transactions.

SELECT Customer.AcctNo, ARINH.Date
FROM ARINH RIGHT JOIN Customer ON ARINH.CustId = Customer.Key
WHERE (((ARINH.Date)>=#5/1/2010#));

How can I change it so some results in the date column would be null.
They should be.

I'm not completely sure what you're asking, but maybe this is what you want:

SELECT Customer.AcctNo, ARINH.Date
FROM ARINH RIGHT JOIN Customer ON ARINH.CustId = Customer.Key
WHERE (ARINH.Date>=#5/1/2010#) Or (ARINH.Date Is Null);

Note: "Date" is not a good name for a field, because under some
circumstances it can be misinterpreted as a reference to the VBA Date()
function.

 

[SAC]

Thanks, Dick.

I'd like the query to return a record for all customers, such of which do
not have a transaction which meets the criteria. So, there should be an
acctno returned and no date column because a record does not exist for that
customer.

Thanks about the Date info.

I appreciate your help.

 

[Dirk Goldgar]

Thanks, Dick.

I'd like the query to return a record for all customers, such of which do
not have a transaction which meets the criteria. So, there should be an
acctno returned and no date column because a record does not exist for
that customer.

Thanks about the Date info.

I appreciate your help.

You're welcome. I believe the query I posted in my previous message may be
what you're looking for. Let me know how it works out.

 

[SAC]

Yes it did work. I guess I need to understand the join better. I thought I
could pick the type of join where ALL customers would be included and only
those records from ARINH where the joined fields are equal and that would do
the job.

Thanks for your help.

 

[SAC]

Well, maybe this doesn't work. I have 4216 customers and this query returns
1649. If there were no transactions for the date criteria, I would like to
have 4216 records returned. If some customers have a transaction, then the
number would grow.

Thanks for your help, Dick. I guess I just don't understand the ALL aspect
of the join type.

 

[SAC]

Well, maybe this doesn't work. I have 4216 customers and this query returns
1649. If there were no transactions for the date criteria, I would like to
have 4216 records returned. If some customers have a transaction, then the
number would grow.

Thanks for your help, Dick. I guess I just don't understand the ALL aspect
of the join type where ALL customers and only those of the ARINH table where
they join.

Thanks again, Dick.

 

[John W. Vinson]

Well, maybe this doesn't work. I have 4216 customers and this query returns
1649. If there were no transactions for the date criteria, I would like to
have 4216 records returned. If some customers have a transaction, then the
number would grow.

Thanks for your help, Dick. I guess I just don't understand the ALL aspect
of the join type where ALL customers and only those of the ARINH table where
they join.

I would suggest using a subsidiary query to select those records with the date
criterion, and outer-join that query to the customer table:

Query 1:

SELECT Customer.AcctNo
FROM ARINH
WHERE (((ARINH.Date)>=#5/1/2010#));

Query 2:

SELECT * FROM Customer LEFT JOIN Query1 ON Customer.CustomerID =
Query1.CustomerID;

 

[Dirk Goldgar]

Well, maybe this doesn't work. I have 4216 customers and this query
returns
1649. If there were no transactions for the date criteria, I would like
to
have 4216 records returned. If some customers have a transaction, then
the
number would grow.

Oh, I see now. Sorry, I didn't fully understand before. John Vinson's
two-query solution should work, or you can do it in one query like this:

SELECT Customer.AcctNo, A.Date
FROM
Customer
LEFT JOIN
(SELECT * FROM ARINH WHERE ARINH.Date>=#5/1/2010#)
As A
ON A.CustId = Customer.Key

I haven't tested that, but it ought to work.

Note that, if you enter that in SQL View, then switch to Design View, then
save it, the next time you look at it in SQL View, Access will probably have
reformatted it to its own special format, something like this:

SELECT Customer.AcctNo, A.Date FROM Customer LEFT JOIN [SELECT * FROM
ARINH WHERE ARINH.Date>=#5/1/2010#]. As A ON A.CustId = Customer.Key

But it works the same way.

 

[SAC]

Thanks, John!

Just wondering why the join type in design view doesn't work this way when I
select that I want to see all customers records and only those from the
transaction file where the fields equal each other?

Do I misundertsand the selection of join type?

I guess I was expecting it to do this for me when I selected that join type.

 

[SAC]

Thanks again, Dick!

Well, maybe this doesn't work. I have 4216 customers and this query
returns
1649. If there were no transactions for the date criteria, I would like
to
have 4216 records returned. If some customers have a transaction, then
the
number would grow.

Oh, I see now. Sorry, I didn't fully understand before. John Vinson's
two-query solution should work, or you can do it in one query like this:

SELECT Customer.AcctNo, A.Date
FROM
Customer
LEFT JOIN
(SELECT * FROM ARINH WHERE ARINH.Date>=#5/1/2010#)
As A
ON A.CustId = Customer.Key

I haven't tested that, but it ought to work.

Note that, if you enter that in SQL View, then switch to Design View, then
save it, the next time you look at it in SQL View, Access will probably
have reformatted it to its own special format, something like this:

SELECT Customer.AcctNo, A.Date FROM Customer LEFT JOIN [SELECT * FROM
ARINH WHERE ARINH.Date>=#5/1/2010#]. As A ON A.CustId = Customer.Key

But it works the same way.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks, John!

Just wondering why the join type in design view doesn't work this way when
I select that I want to see all customers records and only those from the
transaction file where the fields equal each other?

Do I misundertsand the selection of join type?

I guess I was expecting it to do this for me when I selected that join
type.

It's because you are applying criteria to a field that is only populated
when there is a matching record. The outer join by itself will return all
records from the Customer tabIe, joined to matching records from the ARINH
table. So in any records returned from that join, the [Date] field will
either have a value (from the ARINH table) or it will be Null.

When you apply a WHERE criterion after that, it is applied to each record
that results from the *join*. So the criterion in your original query was
filtering out all the records with Null in the [Date] field. In my first
(failed) attempt, I was returning all records that either had no matching
records *or* had a matching record meeting the date criterion, but leaving
out those that had matching records that didn't meet the date criterion.
That's why John and I both settled on queries that first extracted the
records meeting the date criterion from the ARINH table, and only then
outer-joined those records to the Customer table. It's all about where --
to what set of records -- the criterion is applied.

 

[SAC]

Excellent! Got it! Thanks a bunch!

Thanks, John!

Just wondering why the join type in design view doesn't work this way
when I select that I want to see all customers records and only those
from the transaction file where the fields equal each other?

Do I misundertsand the selection of join type?

I guess I was expecting it to do this for me when I selected that join
type.

It's because you are applying criteria to a field that is only populated
when there is a matching record. The outer join by itself will return all
records from the Customer tabIe, joined to matching records from the ARINH
table. So in any records returned from that join, the [Date] field will
either have a value (from the ARINH table) or it will be Null.

When you apply a WHERE criterion after that, it is applied to each record
that results from the *join*. So the criterion in your original query was
filtering out all the records with Null in the [Date] field. In my first
(failed) attempt, I was returning all records that either had no matching
records *or* had a matching record meeting the date criterion, but leaving
out those that had matching records that didn't meet the date criterion.
That's why John and I both settled on queries that first extracted the
records meeting the date criterion from the ARINH table, and only then
outer-joined those records to the Customer table. It's all about where --
to what set of records -- the criterion is applied.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)