Help Needed (1 Viewer)

[CDMN]

Hi, I need some help with a query I have not yet been able to run successfully.

I have two tables "Customer Table"(1) and "Invoice Table"(2). I need to design a query that will show the "invoice numbers" and "invoice totals" of all purchases by customers with a 719 or 712 area code. Needed fields to be shown are "area code"(1), "invoice number"(2), and "invoice total"(2).

Approximately 15 out of the 20 total invoice records should be returned, however when I run the query it returns 280 records. Each individual invoice number is listed numerous times, with a noticible pattern. Each customer is listed with each invoice number, although the invoice number is suppose to be unique for each customer. (ex. Customer John M. is listed with invoice 101, 102, 103, 104, 105, etc.)

I will appreciate any help.

Thanks.

 

[RV]

Seems to me you're not joining your tables in your query.
Your query would be something like:

SELECT AreaCode, InvoiceNr, InvoiceTotal
FROM Invoices, Customer
WHERE Invoice.CustID=Customer.CustId
AND AreaCode in ("712", "719");

Otherwise, post your query.

RV

 

[CDMN]

I have made some progress using the code you suggested. I have the following code in SQL view:

SELECT Customers.Area_Code, Invoice.Invoice_Number, Invoice.Inovice_Total
FROM Customers, Invoice
WHERE Invoice.Customer_ID=Customer.Customer_ID
AND Area_Code in ("712", "719")

However I need to know what to enter when I am asked to "Enter Parameter Value" for "Customer.Customer_ID".

Thanks.

P.S. What is meant by "Characters found after end of SQL statement"?

 

[Pat Hartman]

The example given by RV is non-standard Access SQL syntax. Use the following:

SELECT Customers.Area_Code, Invoice.Invoice_Number, Invoice.Inovice_Total
FROM Customers Inner Join Invoice
On Invoice.Customer_ID = Customer.Customer_ID
Where Customers.Area_Code in ("712", "719");

 

[RV]

When you get a message " "Enter Parameter Value" it means you're referring to a column which isn't an actual column in your tables.

So check out Customer.Customer_ID and see if you're using the right name.

Pat, as for your reply, it is indeed the good old basic standard SQL I'm using..........
Your statement won't make any difference.

RV

 

[Jon K]

In Access, there is a subtle difference between an inner join and one linking the tables in the where clause. The former is updatable whereas the latter is not.

 

[RV]

Subtle difference

Jon K,

thanks, never to old to learn...

RV

 

[CDMN]

I have a field in both tables called "Customer_ID" however it has no relation to the query and I beleive this is where I got mixed up and that is why I was receiving the "Enter Parameter Value" message.

I have two tables "Customer Table"(1) and "Invoice Table"(2). I need to design a query that will show the "invoice numbers" and "invoice totals" of all purchases by customers with a 719 or 712 area code. Needed fields to be shown are "area code"(1), "invoice number"(2), and "invoice total"(2).

CDMN

 

[Pat Hartman]

I forgot to mention the not updateable part. The reason that RV's syntax does not produce an updateable recordset is because it causes Access to first create a Cartesian product (join every row in tabldA to every row in tableB) and then filters it by the Where clause.

If you are not familiar with Access syntax, the best thing to do is to let the query builder build the SQL for you. Just draw join lines between the related columns when viewing the query in QBE view. Joins of more than two tables can get complex and the syntax will be different depending on if you are joining tblA to tblB and tblB to tblC or tblA to tblB and tblA to tblC.

 

[RV]

Pat,

>The reason that RV's syntax does not produce an updateable recordset is because it causes Access to first create a Cartesian product (join every row in tabldA to every row in tableB) and then filters it by the Where clause.<

Didn't know that (Access is just a hobby for me )
Standard Access SQL behavior I guess.

Thanks for the update anyway (straight and clear as always).

RV

 

[CDMN]

The query is now running successfully. The two tables needed to be related by linking two fields.

Thanks for your help.