Relationships & Query

A

andrena

I'm learning from Access 2000 for dummies and need to have this repor
done today. I need some guidance. I have two tables:

Customers
name
address

Invoices
Invoice# (alphanumberic)
Date
Project
Amount

My end result is to be a report with One person's name selected on i
with all the invoices from a certain date. I know I need a query to d
this report. First off I am having touble connecting the relationshi
of these two tables. Can some one tell me how to have a one to man
relationship between these two tables??? Keep in mind every customer i
going to belong to all the invoices in the invoices table. I know ho
you drag one field to the other table in the relationship window bu
not sure if I need to create another field or what. Can't get th
little plus sign to drop down in the Name field to show all th
invoices entered
 
L

Lynn Trapp

Andrena,
If you are going to have many invoices for one customer, then you need a
field in the Invoices table that will link to the Customers table. You will
also want to add a field or fields to the Customer's table that will
uniquely identify the customer. This would become the link between the two
tables. I might use something like this:

Customers
Customer_ID
FName
LName
Address

Invoices
Invoice_ID
Customer_ID
Invoice#
InvoiceDate
Project
Amount

Note that I changed the name of the Date field. "Date" is a reserved word in
Access and you will save yourself lots of headaches if you will not use it
for a field or control name.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
A

andrena

Thank You for your repy. I added the fields and connected the EmployeeI
field between the two tables but when I press the plus sign next to th
employee name, the invoices don't show. There still must be somethin
I"m missing. Is there a way to attach or email this small db file t
you to look at??
 
L

Lynn Trapp

Andrena,
Go to Tools > Relationships. Put your two tables on the screen and drag the
appropriate ID field from one table to the other. If it gives you the
choice, select Enforce Referential Integrity and click the 2 choices below
it. Post back if that doesn't solve it for you. You can email me by
replying to this message and removing the _NOSPAM from my email address.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
A

andrena

Lynn

I had already done what you suggested. I emailed my small db1 file t
your ltcomputerdesign site email. Hope that was okay. Just figured i
would be easier for you to actually look at it than explaining.

Thanks
Andren
 
Top