Multiple table query question

[Megan via AccessMonster.com]

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.

 

[David Lloyd]

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.

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

 

[Megan via AccessMonster.com]

Thank you!

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