Relationship silly Question

  • Thread starter Thread starter Aardvark
  • Start date Start date
A

Aardvark

I have a client table which clientID is an autonumber, primary key and an
invoice table in which ClientID is a number field, with invoice number as a
foreign key. So far so good, however if I enter a new invoice presumably I
have to type the custoemr number into my invoice table (since otherwise how
will the pc know who has what invoice). If this is correct though it seems
far from ideal as I would need an encyclopaedic memory to recall who has
what customer ID. Any thoughts most welcome
 
Hi Aardvark,

Don't worry, you won't be needing an encyclopedic memory if you set up your database correctly.
First, make sure that your foreign key is the default number type, which is a long integer. I
recommend deleting the Default Value for numeric fields, which is shown as 0 in the lower window
of the table design when you have the field selected in the upper window. Then define a
relationship between the two tables. To do so, click on Tools > Relationships..., make sure that
client and invoice tables are added, and then create a relationship between the primary key in
the client table and the foreign key in the invoice table. I recommend placing a check in the
"Enforce Referential Integrity" option.

You can create a form based on the Clients table using the wizard. If you have first created a
relationship between the two tables, your form will include the "one-to-many" related data, and
the foreign key should not even appear on the form. Try adding some records via the form, and
then examine the results by opening the tables.

Tom
_____________________________________


I have a client table which clientID is an autonumber, primary key and an
invoice table in which ClientID is a number field, with invoice number as a
foreign key. So far so good, however if I enter a new invoice presumably I
have to type the custoemr number into my invoice table (since otherwise how
will the pc know who has what invoice). If this is correct though it seems
far from ideal as I would need an encyclopaedic memory to recall who has
what customer ID. Any thoughts most welcome
 
Hmm it was looking good until I tried to add additional invoice records,
then it said no go as additional info was required in my client table.

Any ideas?

Thanks,

Danny


Tom Wickerath said:
Hi Aardvark,

Don't worry, you won't be needing an encyclopedic memory if you set up your database correctly.
First, make sure that your foreign key is the default number type, which is a long integer. I
recommend deleting the Default Value for numeric fields, which is shown as 0 in the lower window
of the table design when you have the field selected in the upper window. Then define a
relationship between the two tables. To do so, click on Tools >
Click to expand...
Relationships..., make sure that
client and invoice tables are added, and then create a relationship between the primary key in
the client table and the foreign key in the invoice table. I recommend placing a check in the
"Enforce Referential Integrity" option.

You can create a form based on the Clients table using the wizard. If you have first created a
relationship between the two tables, your form will include the
Click to expand...
"one-to-many" related data, and
 
Aardvark said:
I have a client table which clientID is an autonumber, primary key and an
invoice table in which ClientID is a number field, with invoice number as a
foreign key. So far so good, however if I enter a new invoice presumably I
have to type the custoemr number into my invoice table (since otherwise how
will the pc know who has what invoice). If this is correct though it seems
far from ideal as I would need an encyclopaedic memory to recall who has
what customer ID. Any thoughts most welcome
Click to expand...

Use a wizard to build a combo box that will contain your customers. It will
hide the customer number if you want but the customer number from that
lookup would be the source of the customer on the header of your invoice.

Build a query with the invoice header table and the customer table.
Use the invoice header custID and the customer table name, address etc
fields on your form.
The combobox looks up the customer by name and fills in the custID on the
form which then "gets" the name and address from the customer base.
 
Aardvark said:
I have a client table which clientID is an autonumber, primary key and an
invoice table in which ClientID is a number field, with invoice number as a
foreign key. So far so good, however if I enter a new invoice presumably I
have to type the custoemr number into my invoice table (since otherwise how
will the pc know who has what invoice). If this is correct though it seems
far from ideal as I would need an encyclopaedic memory to recall who has
what customer ID. Any thoughts most welcome
Click to expand...
i was haveing the same problem, then i looked at the nortwind traders sample
aplication that came with the access... i learnd a lot from it...
i'm still leraning access... so i sugest you look there :-)
 
Thank you to everyone for your help...more help please. I have set up a
combo box and it displays my client list perfectly..yippee, title in column
1, first name in column 2 and surname in column 3. However when I click on
a client only the clients title (from column 1) appears in the actual
box...why is this and how do I get round it?

Thanks,
Danny
 
Back
Top