Struggling with relationships

[Colin Woolliscroft]

Hi there,
I was just wondering whether anyone can help me...
I have two tables, one containing customers' contact
details and the other containing last order dates for one
of the products we offer (only a third of the customers
have any data in this table).
I have setup an input data form containing this last
order date as a subform. However, I seem to be unable to
get the name of the customer added to that field in the
last order date table. This name is identical to the one
entered for the contact details on the input form. Just
trying to avoid entering the name twice everytime I add a
new customer.
Hope this is explained well enough,
Colin.

 

[Jeff Boyce]

Colin

If you have the customer's name already in the Contact details table, why do
you need to add it to a LastOrderDate table?

For that matter, why do you need a special table to keep track of "last
order date"? Do you also have special tables to keep track of "first",
"second", ... ?

If you have an Orders table, with OrderDate and CustomerID, a simple query
will tell you the "last" order date for a customer.

While what you are asking about doing is possible, it may not be a good
idea...

Good luck

Jeff Boyce
<Access MVP>

 

[Colin Woolliscroft]

The second table actually holds more fields than just the
last order date and I would like to include the customer
name so that we can open up the table for that certain
type of customer and see details of their orders and
notes just regarding that proportion of our customers.
I was using a subform and separate table so that these
specific fields do not appear on the basic contact
details form - they appear upon using a tab on the main
form.

I tryed to achieve what I am after by linking customer ID
and customer name in relationships but this only
automatically put the customer name into the table when
using it as a subreport in the main table and not my
preferred method of using the subform embedded in my main
data entry form.

Colin.

 

[John Vinson]

The second table actually holds more fields than just the
last order date and I would like to include the customer
name so that we can open up the table for that certain
type of customer and see details of their orders and
notes just regarding that proportion of our customers.

You're using a relational database. USE IT RELATIONALLY!!!

If you need to see the customer's name, use a Query joining the two
tables. It is NOT necessary - in fact it's a waste of space and a
major risk of update anomalies - to store the name redundantly in this
second table. The user can see the details of the order, and the
details of the customer, *without* having to store the data all in one
table!

 

[Colin]

Thank you all for your help,
I'm slowly getting the hang of it!

Will just set a query up.....