Storing and retrieving data for months of the year into one field (1 Viewer)

[Dave E]

I would welcome suggestions on the subject of economy, and efficiency, of field-use and VBA coding.

I have a gardening db which is recording all relevant data about plants in my garden.
It has gone well over the years and, with some help from other members of the forum, is improving year on year, but I want to change the way flowering times are recorded and extracted from the table.

I'm thinking along the lines of one field holding a 'binary-like' code with each month being one digit of the 12 digit text code, (one for each month).
The user could enter the data by selecting one or more yes/no boxes indicating in which month(s) a plant flowers.
VBA would create the 12 digit text string and save it to one field.

When a record is subsequently opened, VBA would check each digit of the code and highlight the relevant yes/no boxes on the form.

Does anyone have other methods that might be better?

I'd be interested and grateful for any input.

 

[bob fitz]

IMHO it would be better to save the month numbers in a separate table.

 

[CJ_London]

To be clear please confare thinking your binary code would look something like

nnnffffnnnnn

which would translate as flowers April-June ?

Can you have multiple flowering periods e.g April -June and Sept-November?

 

[Dave E]

IMHO it would be better to save the month numbers in a separate table.

Thanks, Bob. I have considered that but the issue of storing, retrieving and decoding the data remains.

 

[Dave E]

To be clear please confare thinking your binary code would look something like

nnnffffnnnnn

which would translate as flowers April-June ?

Can you have multiple flowering periods e.g April -June and Sept-November?

Hi CJ,

Indeed, that would be the method, with the field being 'decoded' into months dependent upon each code bit.
The string would be inspected by a looping VBA procedure on the OnCurrent event.

And yes, some flowers have two flowering periods in a single year, so this method would cater for that.
I did try using just seasons as that reduces the data to four choices, but it's just not quite accurate enough.

 

[KitaYama]

It's better to use a one to many table.
But if you want to go with your first idea, check the attached database.

-

 

[KitaYama]

I did some small modifications and updated above test database.

 

[Dave E]

It's better to use a one to many table.
But if you want to go with your first idea, check the attached database.

-

Thanks for that...

I've always wondered about using one-to-many table relationships.

In the context of data being repeated a lot within the db, I've always used one-to-many.
In this db, however, the plant has one unique flowering pattern so, I think, it can be held within the same table.
A one-to-many table might take more processing than a field within the same table?

If you can spare the time, can you explain the OnCurrent action on the form you supplied?
It would be useful to see the rationale behind the method.

Thanks again

DaveE

 

[CJ_London]

you might find this link helps

What Is Binary Code and How Does It Work?

The binary number system is at the heart of how computers work. Learn how the ones and zeros of the binary code convert into stored information.

www.lifewire.com

 

[ebs17]

A distinction should be made between what is stored and how and how stored information is displayed. In a database, people stick to storing information atomically for good reasons.

Actually there is a many to many relationship here (month - plant) and this should be used. The only difference is that you can use the number of the month directly as a primary and foreign key and it is better not to use an auto value as the original primary key (in month table).
All further configurations are then derived from this relationship. There are simplifications because the month number is already "speaking" as a foreign key and you don't always need the link to the month table.

 

[KitaYama]

If you can spare the time, can you explain the OnCurrent action on the form you supplied?
It would be useful to see the rationale behind the method.

Unfortunately I'm at work now and working on a project.
I save a long integer in bitwise field with after update of the checkboxes and later check its left 12 digit of its binary value as On-Off switches. (in on current event) using logical operators.
The following link explains it in details. ( a variation of the link @CJ_London shared)
It's a complete step by step on how to use bitwise in a database. The answer of your question is explained under Reading Bit Flags.

Using Bitwise Operators In VB

To demonstrate bitwise concepts, this article shows you how to store an array of Boolean flags within a single numeric variable.

www.jameshbyrd.com

This is a link to @theDBguy's site on how to use bitwise in a query.

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had...

thedbguy.blogspot.com

Actually there is a many to many relationship here (month - plant)

The months will never exceed 12. I was thinking of using a combo box with row source value 1 to 12 in a sub form.
I can also use MonthName function if I need their names.
Why do you need a table for months?
Thanks

 

[MajP]

Public Enum blMonth
  Jan = 2
  Feb = 4
  Mar = 8
  Apr = 16
  May = 32
  Jun = 64
  Jul = 128
  Aug = 256
  Sep = 512
  Oct = 1024
  Nov = 2048
  Dec = 4096
End Enum


Public Function BloomsIn(AllBloomMonths As Long, TheBloomMonth As blMonth) As Boolean
  If (AllBloomMonths And TheBloomMonth) = TheBloomMonth Then
    'MsgBox TheBloomMonth
    BloomsIn = True
  End If
End Function

Public Sub TestBloomMonths(BloomMonths As Long)

  Debug.Print "Blooms in Jan: " & BloomsIn(BloomMonths, blMonth.Jan)
  Debug.Print "Blooms in Feb: " & BloomsIn(BloomMonths, blMonth.Feb)
  Debug.Print "Blooms in Mar: " & BloomsIn(BloomMonths, blMonth.Mar)
  Debug.Print "Blooms in Apr: " & BloomsIn(BloomMonths, blMonth.Apr)
  Debug.Print "Blooms in May: " & BloomsIn(BloomMonths, blMonth.May)
  Debug.Print "Blooms in Jun: " & BloomsIn(BloomMonths, blMonth.Jun)
  Debug.Print "Blooms in July: " & BloomsIn(BloomMonths, blMonth.Jul)
  Debug.Print "Blooms in Aug: " & BloomsIn(BloomMonths, blMonth.Aug)
  Debug.Print "Blooms in Sep: " & BloomsIn(BloomMonths, blMonth.Sep)
  Debug.Print "Blooms in Oct: " & BloomsIn(BloomMonths, blMonth.Oct)
  Debug.Print "Blooms in Nov: " & BloomsIn(BloomMonths, blMonth.Nov)
  Debug.Print "Blooms in Dec: " & BloomsIn(BloomMonths, blMonth.Dec)

End Sub

Public Sub TestIt()
  Dim BloomMonths As Long
  'Jan Apr Oct
  BloomMonths = blMonth.Jan + blMonth.Apr + blMonth.Oct
  Debug.Print BloomMonths
  TestBloomMonths BloomMonths

End Sub

1042
Blooms in Jan: True
Blooms in Feb: False
Blooms in Mar: False
Blooms in Apr: True
Blooms in May: False
Blooms in Jun: False
Blooms in July: False
Blooms in Aug: False
Blooms in Sep: False
Blooms in Oct: True
Blooms in Nov: False
Blooms in Dec: False

same idea as @KitaYama just providing the enum to allow easy coding for other things.

 

[ebs17]

Why do you need a table for months?

This table is similar to a calendar table.
With appropriate planning, this contains formats for this value in addition to the actual value (date there).
Caution: Such auxiliary tables are far less subject to the rules of normalization.

With good query technology, such formats (name of the month, allocation to quarters and other time periods) can be looked up from a table more quickly than having to calculate them again and again. It gets even more drastic if you have to carry out your own operations (filter, grouping, sorting) with this format and should use an index for this.

My first approach in databases is always SQL (native language in a database) and the necessary. Highlights from other programming languages (classes, regular expressions) will come later.

I also usually assume that the database contains a great deal of data and that SQL solutions are structurally advantageous. What works well on a large scale will not be harmful on a small scale either.

Some distinguish between database developers and VBA programmers. Both don't have to be bad, both can have skills in the other area.

 

[The_Doc_Man]

I would welcome suggestions on the subject of economy, and efficiency, of field-use and VBA coding.

Lots of people have suggested ways to binary-encode your planting information. However, unless you are using an SQL engine that supports the data type known as "packed binary", a simple method is to have 12 yes/no flags, one per month. This is not optimum for normalization reasons, but very easy for programming reasons. Access does not have a packed binary data type, though SQL Server does.

The normalized method involves having a separate but sparse child table where you have an entry in the child table with the flower's ID and a month number. Frequently you will have only one child entry per flower, but if ever you needed two such entries, it would be trivial to show them. Then you build a query that JOINs the flower table to the planting table on the flower ID, and in the output of that query you have a list of all the flowers and the month-number you wanted, already spelled out and properly associated. Queries for flowers that require action in a certain month would be trivial and mostly intuitive. To find out flowers that bloom in June, you run a search of the QUERY, not the table. To sort by month, you sort the QUERY, not the table.

To me, the problem with binary packing depends on how comfortable you are with Boolean operators. If you ARE comfortable with it, you can probably make that work OK. You can use a (word) INTEGER data type to hold 12 bits and have 4 bits left over, but just remember that if you ever have to sort on the packed month field that you could get some misleading results if you ever have a flower with a two-month period of interest. If you are NOT comfortable with the Boolean operators, then I would be hesitant to recommend binary packing.

The problem with economy is, "economy of what?" You can use 12 bits in a word integer, which is 2 bytes. Or you can use 12 bytes to encode 12 Y/N flags. Using the child table occupies a TableDef as overhead plus at least 6 bytes per record in the child table (and no bytes in the parent table). But - to my admittedly sometimes pragmatic attitude - it is more code-economical to normalize the table because you don't need to write more complex VBA functions to encode or decode the packed flags. If you have a database of a million flowers and keep 1000 bytes of various facts about each species and variety, then space might be at a premium and packing might be a solution. But make that a database of a thousand flowers with a few dozen facts and the extra space for the normalized table fits in perfectly well.

Computer languages are often designed to make the trade-off of speed vs. space. Therefore, your question that I quoted earlier boils down to this: How big is your database and are you willing to have extra code to manage data encoding and decoding of packed data fields?

I want you to understand that my colleagues are NOT WRONG - in that they answered the questions about packing data in a binary method, which is part of your question. My answer looks to the original question as a whole to recognize that there is a space/speed/simplicity trade to be made, so my suggestion includes an evaluation of which one makes you more comfortable.

If you decide that the binary packing method appeals to you, go for it - with the understanding that any choice you make at the design level involves having made a choice between size and speed and ease of programming.

 

[Dave E]

Thanks to all for your replies. I am going away to study and digest them...

Thanks again.

DaveE

 

[Pat Hartman]

A binary operation is overkill for the amount of data you will ever store. It also adds unnecessary programming and query complications. Just because you can do something, doesn't mean you should. If you are dealing with millions of rows, space matters but for a few thousand. you can be as inefficient as you want for simplicity. For the vast majority of applications, i would normalize the data and I would do that here rather than go the binary route, but for this particular situation, I would not normalize and I would not use a binary solution. So, I agree with Doc. I would use 12 Yes/No fields and that would not require any code to display or save the values. You click or unclick a bound control. No code required. The queries will be straight forward also.

I would choose to unnormalize (which the binary solution does also) because it simplifies, data entry and display.

If you still want to play with the binary solution just because, make very sure you understand how it all works AND how you will query the tables.

PS - another option, which hasn't been suggested is the abomination data type - multi-value field. The problem here is that it would be nice to use single digit codes but we have duplicates for A, J, and M and numbers are also unsatisfying so you have to go with month abbreviations. The data is displayed in a listbox. Also, the SQL for using the multi-value field is non-standard and you will need to understand how to use it.

 

[The_Doc_Man]

And in the (admittedly rare) case of having more than one month of interest, the multi-valued field suddenly gets very nasty.

 

[Dave E]

Thanks for that but I'm not looking at the binary method, having looked at the code, it seems a lot of effort for little return.
I am now clearer in my mind that my originally suggested method is the way to go.

I will have unbound 12 check boxes and a separate field that has 12 symbols representing each month ( the field will be the one stored in the table).
I'm will use the check boxes to enter the data to each record. OnClose, the check boxes will pass their individual state to the single field.

When reopened, the OnCurrent event the single field will populate the checkboxes for clarity and user input.

The single field can then be used in code when a search is done to find plants that flower at specific months.

I think this is the best method for me.

Thanks for the input. Yours, and the other contributions, has been very useful.

 

[CJ_London]

OnClose, the check boxes will pass their individual state to the single field.

Be aware that a checkbox has two states (0 and -1) so true/yes has two chars not 1

so to store use the abs function to convert negatives to positives

you might as well have 12 checkboxes in your table (excel style)

For either method you also need to consider how complex your code is going to get if you want to find flowers that flower over more than one month e.g flower between April and July or even more complex questions

 

[ebs17]

Be aware that a checkbox has two states (0 and -1) so true/yes has two chars not 1

... 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