Join?

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.

"SAC" wrote in message
...
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.

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

(please reply to the newsgroup)

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" wrote in message
...
"SAC" wrote in message
...
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.

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

(please reply to the newsgroup)

"SAC" wrote in message
...
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.

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

(please reply to the newsgroup)

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.


"Dirk Goldgar" wrote in message
...
"SAC" wrote in message
...
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.

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

(please reply to the newsgroup)

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.


"Dirk Goldgar" wrote in message
...
"SAC" wrote in message
...
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.

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

(please reply to the newsgroup)

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.

"Dirk Goldgar" wrote in message
...
"SAC" wrote in message
...
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.

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

(please reply to the newsgroup)

On Tue, 18 May 2010 10:11:17 -0500, "SAC" wrote:

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;
--

John W. Vinson [MVP]

"SAC" wrote in message
...
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)

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.


"John W. Vinson" wrote in message
...
On Tue, 18 May 2010 10:11:17 -0500, "SAC" wrote:

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;
--

John W. Vinson [MVP]