In over my head

[Guest]

I have two tables (tblJobs and tblCustomers) whose primary keys are linked to
a third table called tblJobsCustomers. The idea being that jobs 1, 5 & 9
might all be customer 1 while job 2 might be customer 4. The tblJobsCustomers
is I believe what is called a 'junction table'. Told you I was in over my
head!

I next made a form that I use to browse through all the jobs. What I would
like to have is at the bottom of hte form a place that shows who the customer
is for that partuicular job. But I don't know how to do that. From what
little I know, I think I need to be using some kind of SQL statement with an
INNER JOIN but I'm sure some of you kknow better than I!

So just to reiterate, I have a form for browsing through our the tblJobs. I
would like to show who is the customer fo reach of those jobs. And I think
this is the relevant information about my database tables.

tblJobs has a primary key called intJobID. tblCustomers has a primary field
called intCustomerID. Finally, tblJobsCustomers has two fields, intJobID and
intCustomerID.

Thanks to any and all for your help!

 

[Damon Heron]

Does one job have more than one customer? If the answer is no, then you
don't need the junction table.
the relationship would be one customer can have many jobs. (one to many)
So the job table would have a foreign key CustomerID that links the two
tables -Jobs and Customers. On your form, select properties, data, click
in the recordsource field and notice the (...) button to the right. click
on that and build the query adding all of your job fields, which will
include the foreign key CustomerID, and add the table Customers and select
the Customer Name field. Save the query and assign control sources to all
your fields.

Good Luck.
Damon

 

[Carl Rapson]

I have two tables (tblJobs and tblCustomers) whose primary keys are linked
to
a third table called tblJobsCustomers. The idea being that jobs 1, 5 & 9
might all be customer 1 while job 2 might be customer 4. The
tblJobsCustomers
is I believe what is called a 'junction table'. Told you I was in over my
head!

I next made a form that I use to browse through all the jobs. What I would
like to have is at the bottom of hte form a place that shows who the
customer
is for that partuicular job. But I don't know how to do that. From what
little I know, I think I need to be using some kind of SQL statement with
an
INNER JOIN but I'm sure some of you kknow better than I!

So just to reiterate, I have a form for browsing through our the tblJobs.
I
would like to show who is the customer fo reach of those jobs. And I think
this is the relevant information about my database tables.

tblJobs has a primary key called intJobID. tblCustomers has a primary
field
called intCustomerID. Finally, tblJobsCustomers has two fields, intJobID
and
intCustomerID.

Thanks to any and all for your help!

Is it possible for a job to have more than one customer? If so, your design
looks OK, but if not, you don't need the junction table, just put a field
for intCustomerID in tblJobs. Then you can add that field to your form also.

If you really do have more than one customer per job, you can add to your
main form a subform that is bound to tblJobsCustomers, with parent and child
links of intJobID. The subform will automatically display all records from
tblJobsCustomers that match intJobID.

Carl Rapson

 

[Guest]

Thanks guys. I have actually been asking myself that same question about
"more than one customer". I am basing this project on a database I built from
a tutorial that was the way it was done there. Once we make the decision that
one job can only have one customer it does become a simpler task. Thanks
again!