A
Access_User_13
Hi,
I’m fairly new to access. I have some experience with queries but not much
with forms, expressions or coding.
I'm trying to set up a subform into my customer add form to accommodate a
many to many relationship. My customer add form has fields for customer
name, date, etc which update the Customer Table. Each new customer record
will also have multiple service types with a corresponding number(count) of
each service.
I’ve set up the table structure for this many to many relationship as follows
Customer Table:
Customer Code
Name
Date
Service ID
Customer Services (Junction Table):
Service ID – joined with Customer Table by a one to many relationship
Service Code
Number of Services
Services:
Service Code– joined with Customer Services Table by a one to many
relationship
Service Type
What I want to do is set up a subform to list just two things: Service Type
and Number of Services
The user could then enter any positive integer in the Number of Services
field to record what services are applicable to that customer record. I’ve
attempted to create a sub form from the Customer Services table but I cannot
get the service types to be displayed.
I guess my questions are:
Is my current table design correct?
How can I display all the service types on my subform?
Should my subform be based on the Customer Services table or a query?
Or do I need multiple subforms for this task?
Thanks very much in advance for any answers/info you can provide.
I’m fairly new to access. I have some experience with queries but not much
with forms, expressions or coding.
I'm trying to set up a subform into my customer add form to accommodate a
many to many relationship. My customer add form has fields for customer
name, date, etc which update the Customer Table. Each new customer record
will also have multiple service types with a corresponding number(count) of
each service.
I’ve set up the table structure for this many to many relationship as follows
Customer Table:
Customer Code
Name
Date
Service ID
Customer Services (Junction Table):
Service ID – joined with Customer Table by a one to many relationship
Service Code
Number of Services
Services:
Service Code– joined with Customer Services Table by a one to many
relationship
Service Type
What I want to do is set up a subform to list just two things: Service Type
and Number of Services
The user could then enter any positive integer in the Number of Services
field to record what services are applicable to that customer record. I’ve
attempted to create a sub form from the Customer Services table but I cannot
get the service types to be displayed.
I guess my questions are:
Is my current table design correct?
How can I display all the service types on my subform?
Should my subform be based on the Customer Services table or a query?
Or do I need multiple subforms for this task?
Thanks very much in advance for any answers/info you can provide.