Need to keep this data in one table, but errors keep coming up

[kleaverjr]

I have an accounting background, and there is a particular way I prefer keeping track of inventory. I do FIFO, and I want to know the profit for each specific item, so just knowing the total amounts for inventory for COGS and such is insufficient for me. The Inventory itself actually is split up in 4 different tables depending on what "department" it is in. That is not what the table I'm attempting to create does. Yes there are other ways I could record the data, but having a single table recording each individual transaction is the easiest solution as it is incorporated into the custom Register program I am writing. This table is my backup in case something happens and I can trace the transactions for the day and have that data in one record. Or so I hoped. I'm going back through my previous hand recorded sales to see the maximum number of transactions, and though it never has been over 38, I wanted a bigger "margin" than 2 and being able to have 50 unique SKU's in a transaction but I might have to risk limiting it to 40 and go from there.

I appreciate all of the explanations, I believe I understand now what is causing the error. The "too large" error is because of the type of fields and I must have reached the limit. I'm going to see if I changed long text to short text (though i'm not sure if that field then can handle 18 characters) and currency to number if that might allow me to have all 255 fields. We shall see.

Thanks all!

Ken L

[June7]

Short Text can handle 255 characters.

[CJ_London]

Ironically, long text is stored in a different hidden table with just a pointer in your table, so changing to short text will actually make things worse.

I’m an accountant of many years standing and can’t see what FIFO has to do with your solution. Clearly we are not able to get you away from your excel mindset, so I’m not going to spend any more of my time trying to persuade you it is the wrong approach

good luck with what you see as a solution

[June7]

I can change Long Text to Short Text without issue, except for loss of data when the data is truncated.

I expect if Long Text field has AppendOnly set to Yes, that a hidden table would be involved. I communicated with isladogs (Colin R.) who has done extensive research on hidden tables https://isladogs.co.uk/complex-deephide-mystery/.

To quote:

Standard long text field data will all get stored in the main table if the field length isn’t too large.
Above a certain point (page size?), the field contents are stored in a separate system table.
This isn’t clearly documented anywhere.
...
When AppendOnly = Yes, the data is ALWAYS stored in a deep hidden attached table – this is a completely different situation

Additional discussion in:
https://www.access-programmers.co.uk.../#post-1839833

[isladogs]

Here is a longer version of my reply to @June7

Standard long text field data does get stored in the main table if the field length isn’t too large.
Above a certain point (page size?), the field contents are stored in a separate system table.
This isn’t clearly documented anywhere.

Its because of that behaviour that long text fields are more prone to corruption (though much less so than in the past)
Some time ago, I did some tests into what the ’cut-off’ was and briefly mentioned my findings in an AWF thread:
https://www.access-programmers.co.uk/forums/threads/appreciate-opinions-on-longtext-fields.324229/#post-1839833.
Several very experienced developers discussed the issue at some length. All of us only had a partial understanding of this 'feature' at the time.

My tests indicated that where the long text data length exceeded the ‘page size’, the excess data is stored in one of the Lv (Long value) fields in the MSysObjects table record for that table.

When AppendOnly = Yes (column history feature), the data is ALWAYS stored in a deep hidden attached table – this is a completely different situation

[NoellaG]

Long text fields are mainly used as comment fields or so. I don't think you can index them, and they are very slow as search fields. So for fields you want to use as search criteria you better use short texts.
I also don't see the point why storing all data in 1 table. Normalizing the data in different tables and than using views/queries to bring it all together again could solve your problem.

[isladogs]

@NoellaG
Actually you can index long text fields in Access using the field property sheet though you can't do so via the Indexes feature in the Table Design ribbon
I reported this discrepancy to the Access team recently & it is likely that the behaviour will be made consistent in the near future.

I don't recommend indexing long text fields partly due to the field contents usually being different in almost every record (so it will have little benefit in speeding up searches) and due to the way that the data is stored