Need to write a query

G

Guest

I have two tables, one the main table "AR Details" has all invoice records
with all the dollar amount, the second table "Nameaddress" has just customer
names, customer numbers, and addresses. I join the tables and wrote a query
to pull only the records that match and only have a CA number (one of the
fields) My query finds all the company numbers and names. I will list my
query below.

SELECT DISTINCT ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[divn-number], ARDetailAging.[ref-number],
ARDetailAging.[as-of-date], ARDetailAging.[due-date],
ARDetailAging.[job-number], ARDetailAging.[trans-code],
ARDetailAging.[item-type], ARDetailAging.[status-code],
ARDetailAging.[amount-code], ARDetailAging.[item-amount],
ARDetailAging.[cust-name], ARDetailAging.[slsm-number],
ARDetailAging.[PO-Num], NAMEADDR.ca



FROM ARDetailAging LEFT JOIN NAMEADDR ON ARDetailAging.[cust-name] =
NAMEADDR.[customer name]


ORDER BY ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[ref-number];
 
E

Eric D via AccessMonster.com

You need to add a WHERE clause after the FROM statement to filter your
records.
I have two tables, one the main table "AR Details" has all invoice records
with all the dollar amount, the second table "Nameaddress" has just customer
names, customer numbers, and addresses. I join the tables and wrote a query
to pull only the records that match and only have a CA number (one of the
fields) My query finds all the company numbers and names. I will list my
query below.

SELECT DISTINCT ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[divn-number], ARDetailAging.[ref-number],
ARDetailAging.[as-of-date], ARDetailAging.[due-date],
ARDetailAging.[job-number], ARDetailAging.[trans-code],
ARDetailAging.[item-type], ARDetailAging.[status-code],
ARDetailAging.[amount-code], ARDetailAging.[item-amount],
ARDetailAging.[cust-name], ARDetailAging.[slsm-number],
ARDetailAging.[PO-Num], NAMEADDR.ca

FROM ARDetailAging LEFT JOIN NAMEADDR ON ARDetailAging.[cust-name] =
NAMEADDR.[customer name]

ORDER BY ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[ref-number];
Click to expand...
 
G

Guest

Between the two tables that I joined, I want only the matches by customer
name, then customer number with the CA field that has a value in it. in other
words if out of 56000 records customer number 7010 might have 15 records, but
only 3 have a value in the CA field. I just want the records for 7010 with a
value in the CA field.

Chaim said:
What's the question?

--

Chaim


tamxwell said:
I have two tables, one the main table "AR Details" has all invoice records
with all the dollar amount, the second table "Nameaddress" has just customer
names, customer numbers, and addresses. I join the tables and wrote a query
to pull only the records that match and only have a CA number (one of the
fields) My query finds all the company numbers and names. I will list my
query below.

SELECT DISTINCT ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[divn-number], ARDetailAging.[ref-number],
ARDetailAging.[as-of-date], ARDetailAging.[due-date],
ARDetailAging.[job-number], ARDetailAging.[trans-code],
ARDetailAging.[item-type], ARDetailAging.[status-code],
ARDetailAging.[amount-code], ARDetailAging.[item-amount],
ARDetailAging.[cust-name], ARDetailAging.[slsm-number],
ARDetailAging.[PO-Num], NAMEADDR.ca



FROM ARDetailAging LEFT JOIN NAMEADDR ON ARDetailAging.[cust-name] =
NAMEADDR.[customer name]


ORDER BY ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[ref-number];
Click to expand...
Click to expand...
 
G

Guest

Thank you Sir,
I forgot the WHERE clause, I was trying to write a "WELL BECAUSE" clause.

Eric D via AccessMonster.com said:
You need to add a WHERE clause after the FROM statement to filter your
records.
I have two tables, one the main table "AR Details" has all invoice records
with all the dollar amount, the second table "Nameaddress" has just customer
names, customer numbers, and addresses. I join the tables and wrote a query
to pull only the records that match and only have a CA number (one of the
fields) My query finds all the company numbers and names. I will list my
query below.

SELECT DISTINCT ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[divn-number], ARDetailAging.[ref-number],
ARDetailAging.[as-of-date], ARDetailAging.[due-date],
ARDetailAging.[job-number], ARDetailAging.[trans-code],
ARDetailAging.[item-type], ARDetailAging.[status-code],
ARDetailAging.[amount-code], ARDetailAging.[item-amount],
ARDetailAging.[cust-name], ARDetailAging.[slsm-number],
ARDetailAging.[PO-Num], NAMEADDR.ca

FROM ARDetailAging LEFT JOIN NAMEADDR ON ARDetailAging.[cust-name] =
NAMEADDR.[customer name]

ORDER BY ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[ref-number];
Click to expand...
Click to expand...
 
C

Chaim

You have no WHERE clause to limit the records that are retrieved. You want
something like:

WHERE ARDetailAging.[cust-number] = [Enter the customer number: ]
AND not isnull(CA)

I'm not sure of the type of CA or if it can contain NULLs, so this may not
be the AND condition that you want. The [Enter the customer number: ] will
cause Access to ask you for the number of the customer you want.

Good Luck!

--

Chaim


tamxwell said:
Between the two tables that I joined, I want only the matches by customer
name, then customer number with the CA field that has a value in it. in other
words if out of 56000 records customer number 7010 might have 15 records, but
only 3 have a value in the CA field. I just want the records for 7010 with a
value in the CA field.

Chaim said:
What's the question?

--

Chaim


tamxwell said:
I have two tables, one the main table "AR Details" has all invoice records
with all the dollar amount, the second table "Nameaddress" has just customer
names, customer numbers, and addresses. I join the tables and wrote a query
to pull only the records that match and only have a CA number (one of the
fields) My query finds all the company numbers and names. I will list my
query below.

SELECT DISTINCT ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[divn-number], ARDetailAging.[ref-number],
ARDetailAging.[as-of-date], ARDetailAging.[due-date],
ARDetailAging.[job-number], ARDetailAging.[trans-code],
ARDetailAging.[item-type], ARDetailAging.[status-code],
ARDetailAging.[amount-code], ARDetailAging.[item-amount],
ARDetailAging.[cust-name], ARDetailAging.[slsm-number],
ARDetailAging.[PO-Num], NAMEADDR.ca



FROM ARDetailAging LEFT JOIN NAMEADDR ON ARDetailAging.[cust-name] =
NAMEADDR.[customer name]


ORDER BY ARDetailAging.[co-number], ARDetailAging.[cust-number],
ARDetailAging.[ref-number];
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with Where Clause 1
WHERE -IIF ? 20
IIF 7
UNION ALL- CALCULATING SUMS 1
calculating sums 21
Using NULL 1
USING THE "IN CLAUSE" 4
Help with Cartesian or cross joins 6

Top