Multiple table query question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Megan - 28 Jun 2005 14:17 GMT

Hi.
I'm trying to create 2 sets of invoices for a client.
We have return customers and custome/one-time customers.
The one-time customer information and the return customer information are
stored on different tables.
The invoice information is stored on one table.

So basically I have 2 invoice forms - one using invoices and customer and the
other using invoices and noncustomers.

I know this is a bad design. The original design did not include date for non-
customers. Now the client wants to create invoices for one time customers but
not create a regular record. I advise against this.

All this being said -

what i'm trying to do is create a summary report that will list all the
invoices by number but also include the customer name. I could just add the
customer name to the invoice table but I don't want to do that.

is there a way to create a summary that would list the invoice and be able to
retrieve the customer name from either the customer table or the noncustomer
table? both customers do have customer id so i thought i might be able to
search the first table for the id and if it's not there, search the second
table for the id and get the customer information from whichever table has it.

i just can't figure out how to do that.

Can anyone help me?
thanks.

Reply to this Message

David Lloyd - 28 Jun 2005 19:09 GMT

Megan:

One approach is to join the two customer tables with the Invoice table and
use an IIF statement to choose the correct customer name from either the
Customers or the Non-Customers table. By using an outer join, you are
getting all the records from the Invoice table and only the matching
customer names from either the Customers or Non-Customers tables. For
example:

SELECT IIf([Customers].[CustName] Is Not
Null,[Customers].[CustName],[NonCustomers].[CustName]) AS CustomerName,
Invoices.CustID, Invoices.InvoiceAmt
FROM NonCustomers RIGHT JOIN (Customers RIGHT JOIN Invoices ON
Customers.CustID = Invoices.CustID) ON NonCustomers.CustID =
Invoices.CustID;

This assumes there are no overlapping Customer IDs between the two customers
tables.

Signature

David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

Hi.
I'm trying to create 2 sets of invoices for a client.
We have return customers and custome/one-time customers.
The one-time customer information and the return customer information are
stored on different tables.
The invoice information is stored on one table.

So basically I have 2 invoice forms - one using invoices and customer and
the
other using invoices and noncustomers.

I know this is a bad design. The original design did not include date for
non-
customers. Now the client wants to create invoices for one time customers
but
not create a regular record. I advise against this.

All this being said -

what i'm trying to do is create a summary report that will list all the
invoices by number but also include the customer name. I could just add the
customer name to the invoice table but I don't want to do that.

is there a way to create a summary that would list the invoice and be able
to
retrieve the customer name from either the customer table or the noncustomer
table? both customers do have customer id so i thought i might be able to
search the first table for the id and if it's not there, search the second
table for the id and get the customer information from whichever table has
it.

i just can't figure out how to do that.

Can anyone help me?
thanks.

Signature

Message posted via http://www.accessmonster.com

Reply to this Message

Megan - 30 Jun 2005 18:20 GMT

Thank you!

In trying to work with your query I found my problem.
Thank you very much!

Reply to this Message