Solved From with One-To-Many Relationship table (1 Viewer)

[wmix]

Hello Everyone,
I have a database used in a vending machine business. If I'm told about a location's attributes I put the information in notes field associated with the property. Unfortunately this is not a very useful way of doing things. I cannot easily find data on the 250+ different properties when the owner asks me for things because I have no "list" of attributes. So when the owner says ."We are going to try a new product in all the apartments that have a lot of kids..." I have to wade through the property list and remember/guess or check the notes to see if the apartment caters to families. I have decided to create a property attribute table to track the data so it can be easily sorted.

Table # 1 (currently only has 12 attributes)
tblPropertyTypeAttributes
AttributeTypeID (Auto Number and PK)
AttributeName (A Level, B Level, Employee Only Lounge Access, etc.._)

I then created a table that will have one-to-many relationship's with the attributes, as each property will have at least one, but most likely 3+ attributes.
tblPropertyAttribues
PropertyID (FK tblProperty)
AttributeTypeID (FK tblPropertyTypeAttributes)

Here's where I need help, I need help creating a functional form.
I know an option box or check box form is very end-user friendly, but every time the list changes I'll have to go in and modify the form.
I would rather use a combo box or list, since those can change if the attribute type table changes. But I have no idea how to make a combo box or a list as user friendly as a check box form.

I've searched the forums but I think my brain is so fried trying to figure this out I'm not using the right key words because for the life of me I cannot figure out a best practice to build this form. Any guidance is greatly appreciated. Thank you.

 

[June7]

Need a form/subform arrangement.

Main form bound to tblProperty

Subform bound to tblPropertyAttributes with a combobox using tblPropertyTypeAttributes as its RowSource

This is essentially a many-to-many relationship where tblPropertyAttributes is a "junction" table that associates properties with attributes.

 

[jdraw]

wmix,
As for making a more structured PropertyAttributes table based on your Notes field, you might want to show readers a few examples of the Notes field contents. There may be some ways to reduce the variety of terms/meanings/spellings into a more authoritative, consistent set of attributes suited to your needs. Moving away from the "shoebox" method of storing multiple things in one field, is a great first step in providing a structure to respond to ad hoc queries and making more use of database techniques.

 

[plog]

Agree with jdraw and also recommend working on populating your tblPropertyTypeAttributes table so we can see your attributes. Some attributes may not need to go into there if they are attributes that all Properties have.

For example if you wanted to know the number of bedrooms you wouldn't list all possibilites (1 bedroom, 2 bedroom, 3 bedroom, etc.) in your attribute table, but instead make it a numeric field in the Property table and store the number of bedrooms there.

 

[MarkK]

Table # 1 (currently only has 12 attributes)
tblPropertyTypeAttributes
AttributeTypeID (Auto Number and PK)
AttributeName (A Level, B Level, Employee Only Lounge Access, etc.._)

I then created a table that will have one-to-many relationship's with the attributes, as each property will have at least one, but most likely 3+ attributes.
tblPropertyAttribues
PropertyID (FK tblProperty)
AttributeTypeID (FK tblPropertyTypeAttributes)

I find your naming insanely confusing. What is a 'Property?' Is it the same thing as a 'Location,' as in Real Estate? Is a PropertyTypeAttribute a location type, or does a location have x number of properties, and each of those properties has typed attributes? See what I'm saying? And tblPropertyTypeAttributes has an ID field called AttributeTypeID and the name of a PropertyTypeAttribute is AttributeName, but then there is a PropertyAttribute table? Like, my brain is melted cheese and I haven't even really got started.

I think the three words property, type, and attribute have a ton of overlapping meaning, so having a table called PropertyTypeAttribute is like having identical triplet daughters, and naming them Sue, Suzie and Susan.

Try something more like
tblSite -> tblSiteAttribute <- tblAttribute

Also, a site may not have attributes, and similarly, an attribute may not have sites. In this sense, Site and Attribute should be agnostic about each other. Their relationship to each other is discretely defined by tblSiteAttribute, and so naming the Attribute table PropertyAttribute when it only may or may not be related to a property--and that relatedness already adequately defined by a separate table--oversteps a boundary.

It is possible that in tblAttribute you want to specify the scope of a member row, for instance, it might make sense in tblAttributes to have a column called IsSiteAttribute, and maybe other columns like, IsVendingMachineAttribute, IsGenderAttribute, IsOrderStatusAttribute and you can start to see how tblAttribute should be all and only about attributes, and if you define it that way it suddenly becomes much more useful, extensible and flexible.

Anyway, I am out of time.
hth

 

[wmix]

Agree with jdraw and also recommend working on populating your tblPropertyTypeAttributes table so we can see your attributes. Some attributes may not need to go into there if they are attributes that all Properties have.

For example if you wanted to know the number of bedrooms you wouldn't list all possibilites (1 bedroom, 2 bedroom, 3 bedroom, etc.) in your attribute table, but instead make it a numeric field in the Property table and store the number of bedrooms there.

The attributes table consists of items we want to quickly query to be sure we are putting in products and have signage that meet demographics; i.e., apartments have rankings, A, B, C, D and managers tend to tell us the current building ranking, seniors and 55+ tend to like different candy and drinks than younger kids, free coffee locations tend to like more pastry items in the machine, we have locations with high Spanish or Hmong speaking clients so we want to be sure signage isn't all in English, Employee only access vs Public access for vending machines will need different signs also, etc. . This list may grow, currently he has about 12 items on the list.

AttributeTypeID	AttributeName
1	A Level
2	B Level
3	C Level
4	D Level
5	Senior / 55+
6	Family Focus
7	Free Coffee
8	Spanish Speaking
9	Hmong Speaking
10	Employee Only Access

 

[wmix]

Need a form/subform arrangement.

Main form bound to tblProperty

Subform bound to tblPropertyAttributes with a combobox using tblPropertyTypeAttributes as its RowSource

This is essentially a many-to-many relationship where tblPropertyAttributes is a "junction" table that associates properties with attributes.

Thanks June7. Would you have a combo box and then write the answer to a table that the user sees and can continue to pick items and have an "add" button for each attribute they select?

 

[jdraw]

wmix,

How about showing/providing readers with 10-20 notes fields so we can see what you have/are collecting? Better if you provide such data as textfile or database. We're not interested in proprietary information, but want to understand the variety of info in these Notes fields. It may also help you and readers if you can provide some overview of the kinds of questions/queries that the contents of the Notes field is trying to answer/respond to.

 

[wmix]

I find your naming insanely confusing. What is a 'Property?' Is it the same thing as a 'Location,' as in Real Estate? Is a PropertyTypeAttribute a location type, or does a location have x number of properties, and each of those properties has typed attributes? See what I'm saying? And tblPropertyTypeAttributes has an ID field called AttributeTypeID and the name of a PropertyTypeAttribute is AttributeName, but then there is a PropertyAttribute table? Like, my brain is melted cheese and I haven't even really got started.

I think the three words property, type, and attribute have a ton of overlapping meaning, so having a table called PropertyTypeAttribute is like having identical triplet daughters, and naming them Sue, Suzie and Susan.

Try something more like
tblSite -> tblSiteAttribute <- tblAttribute

Also, a site may not have attributes, and similarly, an attribute may not have sites. In this sense, Site and Attribute should be agnostic about each other. Their relationship to each other is discretely defined by tblSiteAttribute, and so naming the Attribute table PropertyAttribute when it only may or may not be related to a property--and that relatedness already adequately defined by a separate table--oversteps a boundary.

It is possible that in tblAttribute you want to specify the scope of a member row, for instance, it might make sense in tblAttributes to have a column called IsSiteAttribute, and maybe other columns like, IsVendingMachineAttribute, IsGenderAttribute, IsOrderStatusAttribute and you can start to see how tblAttribute should be all and only about attributes, and if you define it that way it suddenly becomes much more useful, extensible and flexible.

Anyway, I am out of time.
hth

The naming is directly related to the other tables in the database; it's how the owner describes things so it's directly named based on how he wanted things.
tblProperty - has a bunch of basic property information including address, city, state, county, tax rate, email, etc.
tblPropertyType - identities the property as either a Business (1) or Housing (2) and is a required field for every single property
tblPropertyTypeAttributes - a list of the types of attributes any property can have but nothing is required here. I want people to select from this list, no free-form information.
tblPropertyAttributes - this is the one-to-many table where the information will be stored. The table will have Properties unique ID and the Attributes unique ID.

 

[mike60smart]

The naming is directly related to the other tables in the database; it's how the owner describes things so it's directly named based on how he wanted things.
tblProperty - has a bunch of basic property information including address, city, state, county, tax rate, email, etc.
tblPropertyType - identities the property as either a Business (1) or Housing (2) and is a required field for every single property
tblPropertyTypeAttributes - a list of the types of attributes any property can have but nothing is required here. I want people to select from this list, no free-form information.
tblPropertyAttributes - this is the one-to-many table where the information will be stored. The table will have Properties unique ID and the Attributes unique ID.

If you upload a copy of the Db we can give you an example of how to do this.

 

[wmix]

wmix,

How about showing/providing readers with 10-20 notes fields so we can see what you have/are collecting? Better if you provide such data as textfile or database. We're not interested in proprietary information, but want to understand the variety of info in these Notes fields. It may also help you and readers if you can provide some overview of the kinds of questions/queries that the contents of the Notes field is trying to answer/respond to.

Hello jdraw, here's an example of how a new property gets added and notes we take.

We received an email from a property that does not currently have vending and would like us to come out and see if we can assist them. We have a property name / address / phone / email / managers name. I created the property in tblProperty with all the basic information given.

I also created a note in which gets stored in tblPropertyNotes, that includes the date/time/name of person who contacted us/how they contacted us/basic note about the inquire (no current machines, interested in adding them to the property).

The owner went out and met with the manager. I then recorded all the details he brought back in a new property note with date/time the owner went, who he met with and details about the property. Including...110 unit building, recently renovated now B-Level, all studios or 1 bedroom units. Most of the people in the building work at the airport and come/go at all hours of the day. They offer free hot coffee during office hours and a water bottle refill station in the lobby. Although they are close to many restaurants and specialty shopping no major grocery or convenience stores within walking distance; light rail access about 3 blocks away. Need FOB to get into building, property manager lives on site, security cameras throughout the entire building. Machines will go near the office area and Luxor package area. Manager wants a drink and a snack machine; specifically asked for Coke Zero and a cold coffee option.

 

[jdraw]

That's a good intro, but what exactly is in the Notes fields. You have provided a summary of a situation(s), but what do you record? How would you respond to a specific question about such a meeting in a month from now? What if the question involved several of the Properties? Do you have a list of "common questions"?

 

[June7]

Thanks June7. Would you have a combo box and then write the answer to a table that the user sees and can continue to pick items and have an "add" button for each attribute they select?

Not quite sure what you are asking for here. A combobox would be bound to field so a selection would automatically populate field of record. If a user needs to enter an item that does not yet exist in tblPropertyTypeAttributes, would require code to allow them to input "on the fly" during data entry. This involves combobox NotInList event.

 

[mike60smart]

Hello jdraw, here's an example of how a new property gets added and notes we take.

We received an email from a property that does not currently have vending and would like us to come out and see if we can assist them. We have a property name / address / phone / email / managers name. I created the property in tblProperty with all the basic information given.

I also created a note in which gets stored in tblPropertyNotes, that includes the date/time/name of person who contacted us/how they contacted us/basic note about the inquire (no current machines, interested in adding them to the property).

The owner went out and met with the manager. I then recorded all the details he brought back in a new property note with date/time the owner went, who he met with and details about the property. Including...110 unit building, recently renovated now B-Level, all studios or 1 bedroom units. Most of the people in the building work at the airport and come/go at all hours of the day. They offer free hot coffee during office hours and a water bottle refill station in the lobby. Although they are close to many restaurants and specialty shopping no major grocery or convenience stores within walking distance; light rail access about 3 blocks away. Need FOB to get into building, property manager lives on site, security cameras throughout the entire building. Machines will go near the office area and Luxor package area. Manager wants a drink and a snack machine; specifically asked for Coke Zero and a cold coffee option.

Hi
Here is an example of what you are describing.
The Form that opens on Startup allows you to select / add a Property and then add 1 or more attributes.

 

[wmix]

That's a good intro, but what exactly is in the Notes fields. You have provided a summary of a situation(s), but what do you record? How would you respond to a specific question about such a meeting in a month from now? What if the question involved several of the Properties? Do you have a list of "common questions"?

I don't think I'm explaining this well at all, I apologize.

We have a lot different tables with many related tables. Those tables house information on properties, machines, management companies, service calls, inventory, gross sales, vandalism, commission details, etc.

When the owner is in a form that shows information about a property he can see sub-forms that have all kinds of information (machines associated with the property, property notes, reports about the property, graphs about sales, etc..)

I now want to give him an area where he can "click" or "choose" all the attributes he wants to associate with the individual property.

He gave me a list of about 6 items to start. Then he decided he wanted more items, the list now has 12 attributes. If I create a form using Option Groups I run into the issue that every time he wants a new attribute I have to change option group. This is where I'm struggling.

 

[jdraw]

No need to apologize. You know your environment much better than any reader. You may have all the subforms etc that you need, and your proposed approach seemed to satisfy immediate needs. But as you are finding, there is an "unending list" of things and each new thing requires modifying the database and/or forms.
Do you have a list of the 12 attributes?
Can you provide 10 or 20 Notes fields? text or demo database

 

[wmix]

Hi
Here is an example of what you are describing.
The Form that opens on Startup allows you to select / add a Property and then add 1 or more attributes.

This is perfect, thank you so much.

It seemed like it should be so simple but I don't use anything like this anywhere else in my entire database. I could not wrap my head around this for all the peanuts in the circus. I greatly appreciate your taking the time and help me with this.

 

[mike60smart]

I don't think I'm explaining this well at all, I apologize.

We have a lot different tables with many related tables. Those tables house information on properties, machines, management companies, service calls, inventory, gross sales, vandalism, commission details, etc.

When the owner is in a form that shows information about a property he can see sub-forms that have all kinds of information (machines associated with the property, property notes, reports about the property, graphs about sales, etc..)

I now want to give him an area where he can "click" or "choose" all the attributes he wants to associate with the individual property.

He gave me a list of about 6 items to start. Then he decided he wanted more items, the list now has 12 attributes. If I create a form using Option Groups I run into the issue that every time he wants a new attribute I have to change option group. This is where I'm struggling.

Hi
I have given you the structure necessary to add 1 or more attributes. All you need now is to Google how to add attributes using the "Not In List" Event

 

[mike60smart]

This is perfect, thank you so much.

It seemed like it should be so simple but I don't use anything like this anywhere else in my entire database. I could not wrap my head around this for all the peanuts in the circus. I greatly appreciate your taking the time and help me with this.

No problem

 

[wmix]

Not quite sure what you are asking for here. A combobox would be bound to field so a selection would automatically populate field of record. If a user needs to enter an item that does not yet exist in tblPropertyTypeAttributes, would require code to allow them to input "on the fly" during data entry. This involves combobox NotInList event.

Thank you. mike60smart combo box example helped me immensely, I just couldn't visualize what needed to happen.

​