Job-Invoice Database Design/ Build - Help 1

[kam2006]

Hi there,

I'm really stuck at the moment and just can't sem to get anywhere with this and would really appreciate some help with this, my problem is that i am trying to:

Create an job-invoice database for my dad. so that he can store all his jobs that he does and invoice for them, the background is that there can be many jobs for a customer.

i'm trying to keep it simple and have got the following tables i've *'d the primary keys:

1.Customers
*Customer ID
Customer Name
Adress etc.

2.Jobs
*Job ID
Job Location
Description
Amount

3.Invoice-Jobs
*Invoice-Jobs ID
Invoice ID
Job ID

4. Invoices
*Invoice ID

i created the invoice-jobs table as a link/ intermediate table between jobs and invoices, to break the many to many relationship, but i dont see how to make the link come accross in access. i guess i'm a bit stuck with the design and also with how it links in in access - i just dont see how to link an invoice and many jobs physically......where should i set or see the connection..and how would this be represented in an invoice/ job i.e. if the link table is to have the invoice id against it, how would this get populated?

i'd appreciate any advice anyone can offer, as i am now suffering from a sore head, from hitting it against this brick wall.

Thanks in advance....

 

[Remou]

Have you looked at the Northwind sample database? If you take the following tables:
Customers = Customers
Products = Jobs
Orders = Invoices
Order Detail = Invoice-Jobs
I think you will have a rough outline.

 

[kam2006]

Thanks Remou, i have looked at the northwind example and havce tried to hack that to see how to build mine, but am still stuck. what i am really struggling with is creating the link between jobs, order-jobs and an invoice.

What i mean is how does the link get created, or how do i set link, i.e how does the invoice key, job order key or job key get written to the other table.

I can see that in the invoices table i can select one job, but how do i select another one and tie it to an invoice, additionally how do i tie the first to an invoice?

I guess what i'm sturggling with is that i can understand that if i was creating this with code, i could write an insert statement that would do it, but how do i do it through access alone.

Any help or examples would be greatly appreciated!

thanks in advance............

 

[Remou]

Have you looked at the relationships window? Consider also that in properly linked forms and subforms the link child field will be automatically added to the subform.
Here is some reading material:

http://r937.com/relational.html

http://www.alphabetcitydataworks.com/fundamentals.htm

(same as above, different format)
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database