Forms

[Guest]

I am trying to create a table for our jobs with a record for the invoicing on
these jobs. I created a form to enter in the Invoice #, Date and description.
This works if there is only one invoice which is the most common amounts of
invoicing done per Job but there are the odd ones that are 2 or more ( up to
about 8 invoices). Is there a way to add more invoiceing info to some jobs
and leave the others at one?

Thank-you

 

[John Vinson]

I am trying to create a table for our jobs with a record for the invoicing on
these jobs. I created a form to enter in the Invoice #, Date and description.
This works if there is only one invoice which is the most common amounts of
invoicing done per Job but there are the odd ones that are 2 or more ( up to
about 8 invoices). Is there a way to add more invoiceing info to some jobs
and leave the others at one?

Thank-you

If you have a one-to-many relationship between Jobs and Invoices then
you need two tables in a one to many relationship:

Jobs
JobID <primary key>
<description of the job>

Invoices
InvoiceNo <primary key>
JobID <link to Jobs>
<other info about the invoice>

You'ld typically use a Form based on Jobs, with a subform based on
Invoices; this will let you have zero, one, two, or many invoices for
each job.

If you anticipate ever having a *many* to many relationship - that is,
one Job could have multiple invoices, or one invoice could cover a set
of more than one job - you'll need *three* tables. You'ld leave the
JobID out of the Invoices table and instead have a "resolver" table:

JobInvoices
JobID <link to Jobs>
InvoiceNo <link to Invoices>


John W. Vinson[MVP]

 

[Guest]

If you have a one-to-many relationship between Jobs and Invoices then
you need two tables in a one to many relationship:

Jobs
JobID <primary key>
<description of the job>

Invoices
InvoiceNo <primary key>
JobID <link to Jobs>
<other info about the invoice>

You'ld typically use a Form based on Jobs, with a subform based on
Invoices; this will let you have zero, one, two, or many invoices for
each job.

If you anticipate ever having a *many* to many relationship - that is,
one Job could have multiple invoices, or one invoice could cover a set
of more than one job - you'll need *three* tables. You'ld leave the
JobID out of the Invoices table and instead have a "resolver" table:

JobInvoices
JobID <link to Jobs>
InvoiceNo <link to Invoices>


John W. Vinson[MVP]

ISo I need to have multiple tables that all link together somehow? I am a
little confused i am just learning.

 

[John Vinson]

ISo I need to have multiple tables that all link together somehow? I am a
little confused i am just learning.

Absolutely. That's exactly how a relational database such as Access
works.

Each type of "Entity" - real-life person, thing, or event - gets its
own table. So you'll have a table of Jobs, a table of Invoices, and
probably tables of the people involved in jobs, of addresses, whatever
types of entities are of importance for your application.

There are some good tutorials on relational database design; one good
place to start is

http://support.microsoft.com/default.aspx?scid=kb;en-us;234208


John W. Vinson[MVP]