table problem

[pieman]

i have a database with 3 tables:

1) Job table (job id eg. mikebaker, date started, job description, name of
customer, tel no of customer)
2) Invoice from customer (date, amount received, method of payment..)
3) invoices from supplier(amount i was billed, when, have i paid?, cheque no.)

i dont know if it will work with these tables! but i need to search for the
job id, then for all the money i paid out for the job, and all the money i
received for that job!

can anyone help? have i done the tables wrong of do i need to make a
relationship?
any help is gratefull!!

 

[Jerry Whittle]

mikebaker Possibly the Mike Baker who was assigned to HQ AMC/LG?
Probably not.

In your Job table do you have a primary key set up? It's, hopefully, one
field that indentifies that particular Job such as a job id field? If not,
you need such a field.

Then in your other two tables you need a foreign key field which has the
same datatype as the Job table. The appropriate records in these tables will
have the same data as the PK field in the Jobs table. The relationship
between the PK and FK fields in the tables is the glue that joins them
together so that you can ask questions such as the money for a particular job.

If the above doesn't make much sense, and it's not just my lousy
explaination, I highly suggest getting some relational database training or
reading "Database Design for Mere Mortals" by Hernandez before proceeding any
further.

 

[John W. Vinson/MVP]

i have a database with 3 tables:

1) Job table (job id eg. mikebaker, date started, job description, name of
customer, tel no of customer)
2) Invoice from customer (date, amount received, method of payment..)

Is there ANYTHING in this table that indicates which job ID is being
invoiced? Does each customer get one and only one job? Is there even
anything in this table that indicates the customer (preferably a unique
customerID, but *something*)?

3) invoices from supplier(amount i was billed, when, have i paid?, cheque
no.)

Is there ANYTHING in this table that indicates what job this invoice
pertains to?

i dont know if it will work with these tables! but i need to search for
the
job id, then for all the money i paid out for the job, and all the money i
received for that job!

can anyone help? have i done the tables wrong of do i need to make a
relationship?

Yes, and yes, if what you've posted is complete.

Think about it: if you had three stacks of papers, with the information in
your three tables, could you manually calculate the information you want in
this report? If not, how can you expect Access (which lacks any human
intelligence or intuition!) to do so?

You *must* have enough information in your tables to link each invoice to a
job, and (if it's in fact appropriate) each payment to a job. Typically this
would be done using a relationship (say by including the JobID field in each
of the other tables).

 

[pieman]

the question is, what do i relate!
btw, mikebaker is a made up name!

 

[John Vinson]

the question is, what do i relate!

I have no idea, since you have not told us the complete structure of
your tables or what LOGICAL information relates them.

btw, mikebaker is a made up name!

So I had assumed. So what?

John W. Vinson[MVP]