I\'m attempting to make a query in Access from two tables. The first table is Cl

Question

I'm attempting to make a query in Access from two tables.

The first table is Client and has the following attributes: client#, cname, gender, dob, state_of_residence, phone, address

The second table is payments and has the following attributes: client#, product#, date_paid, amt_paid

I need to type an SQL query list client#, client names, payment amount and date paid.

My first attempt was:

SELECT Client.[client#], Client.cname, payments.amt_paid, payments.date_paid

FROM Client, payments;

I was told this is wrong because to retrieve data from two tables, you need to find what attribute is common between the two tables and equate the values as in

Where   attr1a = attr 1b
but the rules of multi-table retrieval require us to include the name of the source tables as in:

WHERE table1.attr1a = table2.attr1b

I have tried:

SELECT [client#], cname, amt_paid, date_paid

FROM cleint, payments

WHERE client.[cleint#] = payments.[client#]

I need an explanation on how to correctly write this query.

Explanation / Answer

SELECT [client#], cname, amt_paid, date_paid

FROM cleint, payments

WHERE client.[cleint#] = payments.[client#];

The problem with this query is client# is present in both client and payments table . So in query this column is ambiguous . Add column name with client#.

SELECT client.[client#], cname, amt_paid, date_paid

FROM client, payments

WHERE client.[cleint#] = payments.[client#];

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.