Relating 3rd table Access DB 2003

[June27]

I am trying to relate 3 tables. Table 1 is "contacts", with contact name,
address, phone. ContactID is the primary key. Table 2 is "bids", which are
bids for the contacts. Table 2 has pricing and scheduling info.and BidListID
is the primary key. Those 2 tables relate splendidly, I can access the bids
subdatasheets when in the contacts table, and any info entered there is saved
in the Bids table. (One contact may have several bids) {relationship diagram:
contacts table/contactID relates to bid table/contact id}
Table 3 is "Invoices", contains billing info, payments, check number, late
notice, for the bids. and InvoiceID is the primary key. (not sure of the
relationship, I suppose one bid could have more than one bill, if it isn't
promptly paid). This table doesn't relate properly, if I am working from the
"contacts" table, and go to the subdatasheets, bids, then invoices, any info
enter there overwrites previous information, and only the last invoice info
entered ( overwrites?) is saved in the invoice table. Each invoice then ends
up with the same info, and if I delete a record under one invoice subsheet,
that is deleted under all . {relationship diagram: bids table/bidID relates
to invoice table/bidID.

I am grateful for any assistance, I am a new user, so please be patient.
Thanks!
June27

 

[KARL DEWEY]

To relate tables they must have a common field. Your table1 and table2 have
ContactsID in common.
You have contacts, bids, and invoices but I do not see "Contracts" in your
data unless your are only recording suscessful bids and that is the contracts.

 

[June27]

Thank you for your response. To clarify:
Table 1=contacts
table 2=bids
table 3=invoices

Table 1 ContactID (pk) one to may relationship to Table 2 that has
ContactID (fk)
Table 2 BidsID (pk) one to many relationship to Table that has3 BidsID (fk)
Table 3 InvoicesID (pk)

(only successful bids become jobs for which we will submit invoices.)

 

[KARL DEWEY]

You say "Table 2 BidsID (pk) one to many relationship to Table that has3
BidsID (fk)."

What common field are you using to relate table2 to table3?

 

[tina]

June, it sounds like you are trying to perform your data entry tasks
directly into the tables. You shouldn't - that's what forms are for (in
fact, the general recommendation is to set the Subdatasheet Name property in
each table to [None]). your table/relationship design sounds okay, as far as
it goes:

tblContacts (table1)
ContactID (primary key)

tblBids (table2)
BidListID (pk)
ContactID (foreign key)

tblInvoices (table3)
InvoiceID (pk)
BidListID (fk)

you can create a mainform (tblContacts) / subform (tblBids) / sub-subform
(tblInvoices) setup for easy data entry. there are many other ways to
configure your forms, depending on what best supports your data entry work
flow.

recommend you analyze the company's job-invoicing process thoroughly,
because it's important that you design the tables/relationships structure to
support it fully. for instance: if a single invoice may have multiple
payments, then you probably need a separate Payments table. if you track
each late notice sent for each invoice, then you may need a separate table
for LateNotices also.

a table is a collection of data elements (fields) that describes a single
entity. basically, if a single instance of an entity (a record in the table)
may have multiple occurrences of a single data element (or a group of
related data elements), then that data element/group should be moved into a
separate "child" table. recommend you read up on the basics of relational
data modeling, because designing your tables/relationships correctly is the
first and most crucial step of building any relational database. one good
text is Database Design for Mere Mortals by Michael Hernandez. you can also
find many links to useful information at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

hth

 

[June27]

Thanks for your response. I thought BidsID was the common field. I do realize
the poor structure when I get in form view, I would not enter Bid
iinformation by referring to its BidID. I would value your suggestion as to
common field. I imagine I would undoubtedly reference all tables by first
and last name.

(Perhaps I have over-simplied this task and am reviewing Microsoft Access
2003 Step by Step publication).

 

[June27]

Thanks for info! I did check out the links and took note of valuable
resources (books) that were mentioned
June

June, it sounds like you are trying to perform your data entry tasks
directly into the tables. You shouldn't - that's what forms are for (in
fact, the general recommendation is to set the Subdatasheet Name property in
each table to [None]). your table/relationship design sounds okay, as far as
it goes:

tblContacts (table1)
ContactID (primary key)

tblBids (table2)
BidListID (pk)
ContactID (foreign key)

tblInvoices (table3)
InvoiceID (pk)
BidListID (fk)

you can create a mainform (tblContacts) / subform (tblBids) / sub-subform
(tblInvoices) setup for easy data entry. there are many other ways to
configure your forms, depending on what best supports your data entry work
flow.

recommend you analyze the company's job-invoicing process thoroughly,
because it's important that you design the tables/relationships structure to
support it fully. for instance: if a single invoice may have multiple
payments, then you probably need a separate Payments table. if you track
each late notice sent for each invoice, then you may need a separate table
for LateNotices also.

a table is a collection of data elements (fields) that describes a single
entity. basically, if a single instance of an entity (a record in the table)
may have multiple occurrences of a single data element (or a group of
related data elements), then that data element/group should be moved into a
separate "child" table. recommend you read up on the basics of relational
data modeling, because designing your tables/relationships correctly is the
first and most crucial step of building any relational database. one good
text is Database Design for Mere Mortals by Michael Hernandez. you can also
find many links to useful information at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

hth

I am trying to relate 3 tables. Table 1 is "contacts", with contact name,
address, phone. ContactID is the primary key. Table 2 is "bids", which are
bids for the contacts. Table 2 has pricing and scheduling info.and BidListID
is the primary key. Those 2 tables relate splendidly, I can access the bids
subdatasheets when in the contacts table, and any info entered there is saved
in the Bids table. (One contact may have several bids) {relationship diagram:
contacts table/contactID relates to bid table/contact id}
Table 3 is "Invoices", contains billing info, payments, check number, late
notice, for the bids. and InvoiceID is the primary key. (not sure of the
relationship, I suppose one bid could have more than one bill, if it isn't
promptly paid). This table doesn't relate properly, if I am working from the
"contacts" table, and go to the subdatasheets, bids, then invoices, any info
enter there overwrites previous information, and only the last invoice info
entered ( overwrites?) is saved in the invoice table. Each invoice then ends
up with the same info, and if I delete a record under one invoice subsheet,
that is deleted under all . {relationship diagram: bids table/bidID relates
to invoice table/bidID.

I am grateful for any assistance, I am a new user, so please be patient.
Thanks!
June27

 

[tina]

you're welcome, good luck with your learning adventure - and your project!

Thanks for info! I did check out the links and took note of valuable
resources (books) that were mentioned
June

June, it sounds like you are trying to perform your data entry tasks
directly into the tables. You shouldn't - that's what forms are for (in
fact, the general recommendation is to set the Subdatasheet Name property in
each table to [None]). your table/relationship design sounds okay, as far as
it goes:

tblContacts (table1)
ContactID (primary key)

tblBids (table2)
BidListID (pk)
ContactID (foreign key)

tblInvoices (table3)
InvoiceID (pk)
BidListID (fk)

you can create a mainform (tblContacts) / subform (tblBids) / sub-subform
(tblInvoices) setup for easy data entry. there are many other ways to
configure your forms, depending on what best supports your data entry work
flow.

recommend you analyze the company's job-invoicing process thoroughly,
because it's important that you design the tables/relationships structure to
support it fully. for instance: if a single invoice may have multiple
payments, then you probably need a separate Payments table. if you track
each late notice sent for each invoice, then you may need a separate table
for LateNotices also.

a table is a collection of data elements (fields) that describes a single
entity. basically, if a single instance of an entity (a record in the table)
may have multiple occurrences of a single data element (or a group of
related data elements), then that data element/group should be moved into a
separate "child" table. recommend you read up on the basics of relational
data modeling, because designing your tables/relationships correctly is the
first and most crucial step of building any relational database. one good
text is Database Design for Mere Mortals by Michael Hernandez. you can also
find many links to useful information at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

hth

I am trying to relate 3 tables. Table 1 is "contacts", with contact name,
address, phone. ContactID is the primary key. Table 2 is "bids", which are
bids for the contacts. Table 2 has pricing and scheduling info.and BidListID
is the primary key. Those 2 tables relate splendidly, I can access the bids
subdatasheets when in the contacts table, and any info entered there

is
saved

in the Bids table. (One contact may have several bids) {relationship diagram:
contacts table/contactID relates to bid table/contact id}
Table 3 is "Invoices", contains billing info, payments, check number, late
notice, for the bids. and InvoiceID is the primary key. (not sure of the
relationship, I suppose one bid could have more than one bill, if it isn't
promptly paid). This table doesn't relate properly, if I am working

from
the

"contacts" table, and go to the subdatasheets, bids, then invoices,

any
info

enter there overwrites previous information, and only the last invoice info
entered ( overwrites?) is saved in the invoice table. Each invoice

then
ends

up with the same info, and if I delete a record under one invoice subsheet,
that is deleted under all . {relationship diagram: bids table/bidID relates
to invoice table/bidID.

I am grateful for any assistance, I am a new user, so please be patient.
Thanks!
June27