Table fields populated from query

[jcarstens]

I have two tables, one has a list of services (Services) and one has customer info (Customer). Both customers and Services will grow over time. I would like to create a table which I can track which service each client has.



My thought is create a new table (tblTest). the first field would be a lookup of Customer Name. The remainding fields should be the ServiceNames listed horizontally (qryServiceName). I would like each of these fields to be yes/no boxes to signify that that client has that service. As new services are added, they would need to add to the field heading running horizontally.

Any help on this would be extremely helpfull. Thank you in advance.

[jzwp11]

Welcome to the forum!

My thought is create a new table (tblTest). the first field would be a lookup of Customer Name. The remainding fields should be the ServiceNames listed horizontally (qryServiceName). I would like each of these fields to be yes/no boxes to signify that that client has that service. As new services are added, they would need to add to the field heading running horizontally.

You are correct in that you will need a new table, but if a customer can have many services associated with them that describes a one-to-many relationship which requires each applicable service to be a record in that table not a field. The problem with having each service as a field is that each time a customer requests/buys a new service, you would have to change your table structure as well as every associated form, query and report which is not something I would want to do.

Furthermore, since a service can apply to more than one customer, that is another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (customers and services) you have what is called a many-to-many relationship which requires a junction table.

Also, it is not a good idea to use lookups in your tables; they are best left to forms. See this site for details as to the problems table-level lookups can cause.

I would also recommend some more descriptive field names for the "ID" fields (i.e. your primary key fields) in your tables.

I have added the junction table in the attached database. I also renamed the ID fields. Further, I created a new table for the service types