Field for a single value or "ALL"

[Lord_Vader]

This is similar to my other, just posted question (apologies), but a bit different and much simpler.

Need to record the "Affected Cycle" you enter an integer like 1 to 50. But it is also common, to affect "All Cycles".

How should this be handled?


In addition:
What if there is a range of cycles (25-30)?

How should this be handled?

 

[boblarson]

What is the SQL for the query you are currently using?

 

[Lord_Vader]

What is the SQL for the query you are currently using?

I'm trying to figure out how to design a table or tables to handle the described case.

 

[boblarson]

I'm trying to figure out how to design a table or tables to handle the described case.

Sorry, I misread the question. So, can you describe a little more about the business process here? What does this have to do with what it is your are trying to capture and how do you foresee it being used later when trying to pull data back out?

 

[jdraw]

Perhaps "Affected Cycle" isn't the appropriate term.

If someone , who doesn't know your application, asked you to define
"Affected Cycle", what would it be? If we can understand some of the context, we may have some recommendations.

It sounds a bit like
How many? with responses ranging in the 0, about 5,, more than 10, many, a few. Hard to nail these to a wall.

 

[Lord_Vader]

Ah, sorry for not being more clear.

Ignore the field title. It's just a field that is normally a single integer, that could be a value between 1 and ~40.

But it's possible it could be a range, or "All"

 

[boblarson]

In order to store a number or a range or "ALL" you would need to use a text field. But I shudder to think what you might want to try to use as criteria on that field though.

So what JDraw and I asked about was what is the significance of this data? What are you really trying to store and what are you wanting it back out as? It makes no sense to us as it stands and with 3 different types of input it doesn't look to be a proper design nor does it stand a very good chance of getting any meaningful data out of it later on down the line.

 

[Lord_Vader]

Gotcha, sorry to be so circumspect I'm just trying to think of an example that isn't the actual example haha. (I don't know why I didn't think of the below example in the first place)

Lets think of it as a year. So say we have a list of documents. I have some data associated with the document, like title and whatnot, but I want to record what year (years) the document applies to. It can apply to a specific year, a range of years, or its a generic document that applies to all years.

 

[boblarson]

Okay, that makes more sense. In that case I would use a junction table and instead of entering in the number or the range, or all, I would use a multiselect listbox to be able to select the multiples and then use code to populate the junction table with that information. And in the form's On Current event you would then populate the listbox with the selections that have been stored.

I'm torn on the subject of ALL especially if the applicable years would include future years as well. I may have to think about that one.

 

[Lord_Vader]

Okay, that makes more sense. In that case I would use a junction table and instead of entering in the number or the range, or all, I would use a multiselect listbox to be able to select the multiples and then use code to populate the junction table with that information. And in the form's On Current event you would then populate the listbox with the selections that have been stored.

I'm torn on the subject of ALL especially if the applicable years would include future years as well. I may have to think about that one.

Yea the all is the rub, because of the future. I'm glad to hear you say junction table, because that's how I have it set up, but it obviously contains a lot of records, because of each separate year being a record. I guess I can write some code that updates it for the future? I dunno, I'd need an additional boolean field that says "Yes-this is a "all" choice"

I appreciate your help with this