I'd like to have relations...

[Matt]

What I have currently is two tables. The "JobTracker" table, and the
"ServiceTable" table. The JobTracker table has about 1800 entries. What I
would like to do is this. In the JobTracker table the "Job #" field is the
unique identifier that all other fields contain information about. (My Excel
background is probably screaming idiot to all of the more experienced Access
users right about now, but hey, I work with what I've got) To put it
clearly, I have about 30 columns, the first being the Job #, and the other 29
containing data related to that particular Job #. The ServiceTable needs to
have the ability to contain unique information about an individual
ServiceNumber, but each Job # needs to be able to be related to multiple
ServiceNumbers. The ServiceNumbers will have everything in common in the
JobTracker table with each Job # that it's related to. So if I have a Job #
1000, the JobTracker table contains address information, scheduling, pricing,
and contact information unique to that Job #. When adding a service number
in the ServiceTable, let's say 1000-S to denote the service number, that
table will contain information as to the type of service and the date it
needs to be performed, but I do not want to have to re-enter all address,
customer, production info et cetera, so it needs to be linked somehow to a
specific Job # in the JobTracker table. I have a service form that needs to
display the basic information about a particular Job #, PLUS the added
information necessary to complete the service work. Again, one Job # may
have 5 ServiceNumbers that need to be individually accessible from the other
ServiceNumbers for that same job. Currently the ServiceTable is completely
blank because I have not began tracking service through the database as of
yet. The Service Form is mainly going to be used for data entry, so it is
not absolutely necessary to be able to go from record to record showing
nothing but Jobs that have ServiceNumbers. When the need arises to create a
Service Request, I have added a button on my JobForm to link to a new entry
for the ServiceForm. If I left anything important out, let me know, and I
appreciate the help!

 

[*raul*]

As you say, It seems you just need to define the relationship between
both tables. If you haven´t done yet, use "Relationships" button in
Access "Database" menu bar. Then insert both tables, and drag the
Job# field from the JobTracker to ServiceTable, to define a "one to
many" relationship. If the result isn´t satisfactory, just try
differente options for the relationship.
From that point, Forms wizards will be a great help to create
"related" forms and subforms.

Sorry if I missed the point of your question,
Raul

 

[Matt]

I keep getting into problems with having multiple service numbers for one job
number. I've tried several different variations on the relationships, but
can't seem to get one that works correctly. I'm currently trying to set it
up as a subform, but not much luck there either. I'm not exactly an expert
at relationships. When I drag the field over, it wants to link it to a
certain field on the other table...and when I let that field be the service
number, then I add in a service number on my form, it changes the job number.

 

[Matt]

Error messge on subform reads

"Cannot add record(s); join key of table 'ServiceTable' not in recordset"

 

[John W. Vinson]

Error messge on subform reads

"Cannot add record(s); join key of table 'ServiceTable' not in recordset"

That means that the offending form's Recordsource query does not contain the
field that you're using to join the two tables.