Entering multiple items in a field and have each counted individually.

[Remedial]

I have a field where I have a selection of terms that I can enter. I would like to know what method of separating the items should I use in order for each item to be counted individually. For example: Let's say I have the two items "toothbrush" and "toothpaste". Would I enter them as: toothbrush;toothpaste Or: toothbrush; toothpaste In order for the toothbrush to be counted as one item and toothpaste to be counted as another. Let me know, please. Thank you.

 

[georgedwilkinson]

It's not exactly clear what you're doing or why you're doing it.

Typically, if you need to store data about 2 different things, you should store those 2 different things in their own row.

So, if your database were listing the equipment needed to do a common task performed within the enterprise, like brush your teeth, you would have a table related to the "common task" table through a 1:M that listed all the equipment needed to "brush your teeth." The "many" table would have a reference to the "common task" and a reference to the "equipment" tables. The data in the new table would be represented on your form as a sub-form.

Selecting them both "in the same field" will not accomplish the goal of "counting them" as their own items.

 

[Remedial]

It's not exactly clear what you're doing or why you're doing it.

Typically, if you need to store data about 2 different things, you should store those 2 different things in their own row.

So, if your database were listing the equipment needed to do a common task performed within the enterprise, like brush your teeth, you would have a table related to the "common task" table through a 1:M that listed all the equipment needed to "brush your teeth." The "many" table would have a reference to the "common task" and a reference to the "equipment" tables. The data in the new table would be represented on your form as a sub-form.

Selecting them both "in the same field" will not accomplish the goal of "counting them" as their own items.

I understand what you're saying, but here's an example of what I'm doing. I have a set of clients that have different types of needs. I have to keep track of what their reason for calling was. So, I might have Joe Bob who calls for tire rotation and brakes service. So, in my form (and in the respective table), I have a field for "Reason For Calling". Now, Joe Bob wanted to know about different services. I was thinking that since I have no need for another table like the "common task" one that you mentioned, that I could just somehow input both of Joe Bob's "reasons for calling" into one field in my form and then into the table for "Reason For Calling". If I'm correct, in your 1:M relationship, the Common Task table would be on the "one" side and the equipment table would be on the "many side"? So, I' guess what I'll have to do is create an table for the client (Last Name, First Name), a table for the reasons for calling (tire rotation, brakes service) and then a subform that will reflect each client's "reason for calling", right? Let me know if I'm getting this correct. Thank you.

 

[Mike375]

99% of the questions on this forum about more than one piece of info in a field are "How do I split into different fields"

If at times you might want the data from 2 or more fields put into one field and with commas, & and anything else between the data that is very easy to do.

 

[Remedial]

99% of the questions on this forum about more than one piece of info in a field are "How do I split into different fields"

If at times you might want the data from 2 or more fields put into one field and with commas, & and anything else between the data that is very easy to do.

I'm sure it might be pretty easy to do, but once those items are added into that one field and are comma delimited, can I get Access to count each of those items as one "individual" item?

 

[georgedwilkinson]

If you must count the "reasons for calling", i.e. do queries to count the number of "tire rotation" calls you've received over the last month, you must store the data in a separate table.

So you would have a table to record the call (who called, when, etc.), a table to look up the valid "reasons" (just a lookup table with "Tire change", "Back massage", "Sue large corporation", etc.) and a table to associate the call to the reasons.

Your "call" record will be in the main form and your "call reason" records will be in the sub-form.

Now if you don't need to query the data later, you can just append the reasons selected into a text field and be done with it. If that's what you need, post back and I'll (or somebody will) give you some starter code.

 

[missinglinq]

The problem is, if you build it, they will come! Sooner or later, as George suggested, someone's going to want to know how many requests for this oil changes did we get 2 months ago? How many did we get last month after we ran that expensive TV ad for the oil change special. One of the cardinal concepts of relational databases is that of "atomic" data. One piece of data one field.

What I would do for this type of situation, where you have a number of common procedures/requests, would be to have checkboxes for the common stuff, where you can check as many as necessary, then a Misc. field for things that don't fall into the predefined group.

 

[Mike375]

I'm sure it might be pretty easy to do, but once those items are added into that one field and are comma delimited, can I get Access to count each of those items as one "individual" item?

You don't have to when it is in different fields.

Joining the fields is done with a calculated field in a query or an unbound textbox on your form.

For example, in my own DB First and Last Name appear as a single entry but they are actually stored in two different fields.