Storing and retrieving data for months of the year into one field

[MajP]

Typo

 

[sonic8]

Access does not have a packed binary data type, though SQL Server does.

Not sure what you mean by packed binary in this context.
Anyways, Access has a binary data type. For reasons beyond my knowledge, such a column just cannot be created in the visual table designer.

 

[The_Doc_Man]

@sonic8 - I may have used the wrong name. After looking it up, I see they call the data type "bit" and it occupies one bit. "Packed" was the name used when you had a "holder" for several "bit" types in the same physical storage element, but it might not have been in SQL context. I must admit that my memory is imperfect. From the description, if you have any BIT fields, SQL server will automatically pack them transparently to you so that you don't know anything about the packing aspect of things. You just see that there are one-bit fields.

bit (Transact-SQL) - SQL Server

The bit data type is an integer data type that can take a value of 1, 0, or NULL, representing Boolean values.

learn.microsoft.com

 

[Dave E]

... or three states.

Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

Making decision despite missing SQL data (NULL): How to avoid OR…IS NULL in the WHERE clause.

modern-sql.com

Yes. The check boxes are initialised to ensure a two-state operation, and the code determines whether each is True or False. That part of the code is completed a working satisfactorily. The code tests for True or False so won't look for values.

In other circumstances, I tend to use an IsNothing() function which I found on this site many (many,many) years ago, when I still worked for a living. It appears to deal with Null, zero, "", and empty input boxes without any problems.

 

[Dave E]

The reason being that I can make the search easier using one field rather than 12?
I can search for single months or multiple months (seasons, but not exclusively).

By my thinking, 400 records X 12 fields is a lot more than 400 fields...and would use less coding.

 

[MajP]

The reason being that I can make the search easier using one field rather than 12?
I can search for single months or multiple months (seasons, but not

If you wanted the "least code" then you create a field for each month allowing simple add, Edit, delete with no code. Then use a simple union query for all of the searching requiring little or no code.
Size would be irrelevant at 400 records or even 40,000 records.

The "best" most flexinble and efficient way is a normalized child table, which you still could design a simple form with check boxes to add delete child records.

Your solution seems to be neither the easiest or best approach, but you seem set on it and it will probably work fine. Seems unnecessarily kludgy. A solution without a problem.

 

[Dave E]

Thank you both for your comments. I still prefer my method but I accept it may not align with good practice.

 

[Dave E]

Thank you both for your comments. I still prefer my method but I accept it may not align with good practice.

And, of course, it will work.